Sunday, December 7, 2014

Generate_Create_Tablespaces.sql

spool gents.lst
set serveroutput on size 1000000
DECLARE

  CURSOR get_ts IS SELECT * FROM dba_tablespaces
                   WHERE tablespace_name != 'SYSTEM';

  CURSOR get_df (p_ts VARCHAR2) IS
                   SELECT * from dba_data_files
                   WHERE tablespace_name = p_ts;

  l_str VARCHAR2(10);

BEGIN

FOR ts_rec IN get_ts LOOP

  dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name);

  -- For each tablespace loop through the datafiles
  FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP

    IF get_df%ROWCOUNT = 1 THEN
       l_str := 'DATAFILE';
    ELSE
       l_str := ',';
    END IF;

    dbms_output.put_line (l_str||' '
                ||chr(39)||df_rec.file_name||chr(39)
                ||' SIZE '||df_rec.bytes||' REUSE ');

    if df_rec.autoextensible = 'YES' then

       dbms_output.put_line (' AUTOEXTEND ON'
                             ||' NEXT '||df_rec.increment_by );

       if df_rec.maxbytes = 68719443968 then
          dbms_output.put_line (' MAXSIZE UNLIMITED');
       else
          dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes);
       end if;

    end if;

  END LOOP;

/* Extent Management Clause */

   dbms_output.put_line  ('EXTENT MANAGEMENT ' ||ts_rec.extent_management );

   if ts_rec.extent_management = 'LOCAL' then

      if ts_rec.allocation_type = 'SYSTEM' then

         dbms_output.put_line (' AUTOALLOCATE ');

      else

         dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent);

      end if;

   end if;

   if ts_rec.extent_management = 'DICTIONARY' then

     dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent
        ||' NEXT '||ts_rec.next_extent
        ||' MINEXTENTS '||ts_rec.min_extents
        ||' MAXEXTENTS '||ts_rec.max_extents
        ||' PCTINCREASE '||ts_rec.pct_increase||' ) ');

   end if;

   dbms_output.put_line  (' ONLINE;');
   dbms_output.new_line;

END LOOP;

END;
/
spool off

FLASHBACK/ACTIVATION OF STANDBYDB STEPS

  • alter database recover managed standby database cancel;
  • create restore point Before_App_Test guarantee flashback database;
  • shutdown immediate;
  • startup mount exclusive;
  • flashback database to timestamp to_timestamp(‘2011-02-18  11:35:00′,’YYYY-MM-DD HH24:MI:SS’);
  • alter database activate standby database;
  • alter database open;
  • Run script on UAT “Payroll_delete” present in V:\Scripts\Qadb_cardswipe (For UAT only)
  • alter system set  job_queue_processes=0;
  • alter user dbas identified by uat;
  • grant dba to dbas;
  • alter user dbas default role all;
—————Recover back to the original ——————————-
  • shutdown immediate;
  • startup mount force;
  • flashback database to restore point before_app_test;
  • alter database convert to physical standby;
  • STARTUP MOUNT FORCE;
  • alter database recover managed standby database disconnect;
  • ALTER SYSTEM SET log_archive_dest_state_3=’ENABLE’
  • DROP RESTORE POINT Before_App_Test;
  Snapshot Standby
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.
sql> SELECT flashback_on FROM v$database;
FLASHBACK_ON
——————
NO
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY
SQL>
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;
FLASHBACK_ON
——————
NO

Create Standby from standby DB

There are numarous ways to create physical standby from running standbydb, but here i am giving easiest way to build StandbyDb:

  1. OS and Oracle version should be same as current physical standby.
  2. At OS level create same mount points  as in other.
  3. Create Pfile and SPfile with convenience to newly created standbydb.
  4. Copy  coldbackup from current standbydb to newly created standby.
  5. Create/Recreate Passowrd file, OR copy Password file (initdbname) to newly created standbydb.
  6. startup mount;
  7. Run Recovery.

Thats it! 
:)
Regards
Mohsin Ali
mohsin.uetion@gmail.com

Automate Oracle Datbase 11g Switchover using Shell Script

Please Follow the Sequence :
On the machine where the Primary Database is running we need to run the following scripts
$ cd /u01/switch
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh
On the machine where the Standby Database is running we need to run the following script:
$ cd /u01/switch
$ ./make_me_primary.sh
After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:
$ cd /u01/switch
$ ./start_recovery.sh

./pre_switchover_check.sh
echo “”
echo “##################################################################”
echo ” Author : Mohsin Ali #”
echo “# Designation : Database Administrator #”
echo “# Completion Date : 20-NOV-2013 #”
echo “# Description :PERFORMING PRE-SWITCHOVER CHECKS FOR $ORACLE_SID #”
echo “##################################################################”
echo “”
DBROLE=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect / as sysdba;
set feedback off pause off pagesize 0 heading off verify off linesize 500 term off
select database_role from v\\$database;
EOF`
#echo $DBROLE
FILESTAT=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect / as sysdba;
set feedback off pause off pagesize 0 heading off verify off linesize 500 term off
set sqlprompt ”
SELECT COUNT(FILE#) FILESTAT FROM V\\$DATAFILE WHERE STATUS IN (‘RECOVER’, ‘OFFLINE’);
EOF`
#echo $FILESTAT
LOGGAP=`$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect / as sysdba;
set feedback off pause off pagesize 0 heading off verify off linesize 500 term off
set sqlprompt ”
SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V\\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’ AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\\$ARCHIVED_LOG)),(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V\\$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\\$ARCHIVED_LOG));
EOF`
#echo $LOGGAP
echo “CHECKING CURRENT DATABASE ROLE…”
sleep 2
if [ “$DBROLE” = “PHYSICAL STANDBY” ]
then
echo “”
echo “##################################################################”
echo “# ERROR ! ! ! NOT A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY #”
echo “##################################################################”
echo “”
sleep 5
exit
else
echo “”
echo ” OK “
echo “”
fi
echo “CHECKING LOG GAP BETWEEN PRIMARY & STANDBY…”
sleep 2
if [ $LOGGAP -ne 0 ]
then
echo “”
echo “######################################################################”
echo “# ERROR ! ! ! PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP #”
echo “######################################################################”
echo “”
exit
else
echo “”
echo ” OK “
echo “”
fi
echo “CHECKING FILES OFFLINE OR RECOVER STATUS…”
sleep 2
if [ $FILESTAT -ne 0 ]
then
echo “”
echo “##################################################################”
echo “# ERROR ! ! ! ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS #”
echo “##################################################################”
echo “”
exit
else
echo “”
echo ” OK “
echo “”
fi
echo “”
echo “##################################################################”
echo “# ALL PRE-SWITCHOVER CHECKS SUCCEEDED… PROCEED WITH SWITCHOVER #”
echo “##################################################################”
echo “”
./make_me_standby.sh
. /home/oracle/.bash_profileecho $ORACLE_SID
echo “”
echo “##################################################################”
echo ”          Author : Mohsin Ali                           #”
echo “#         Designation : Database Administrator          #”
echo “#         Completion Date : 20-NOV-2013         #”
echo “#        SCRIPT USAGE : PRIMARY to STANDBY              #”
echo “##################################################################”
echo “#              ALL PRE-SWITCHOVER CHECKS SUCCEEDED…            #”
echo ”       SWITCHING $ORACLE_SID TO STANDBY ROLE, PLEASE CONFIRM …          “
echo “##################################################################”
echo “”
echo “”$ORACLE_HOME/bin/sqlplus -s /nolog << EOFconnect / as sysdba;set feedback off pause off pagesize 0 heading off verify off linesize 500 term offset sqlprompt ”shutdown immediate;startup;alter database commit to switchover to standby with session shutdown;shutdown immediate;startup nomount;alter database mount standby database;select database_role from v\$database;
EOF
END
#$ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DB
echo “”
echo “####################################################################”
echo “# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW “STANDBY”      #”
echo “#                                                                  #”#
echo “# On OLD STANDBY Host please run the following script:         #”#”
echo “####################################################################”
echo “”
./make_me_primary.sh
. /home/oracle/.bash_profile
echo $ORACLE_SID
echo “”
echo “##################################################################”
echo ” Author : Mohsin Ali #”
echo “# Designation : Database Administrator #”
echo “# Completion Date : 20-NOV-2013 #”
echo “# Description : Performing STANDBY to PRIMARY #”
echo “##################################################################”
echo “”
DBROLE=`$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set feedback off pause off pagesize 0 heading off verify off linesize 500 term off
set sqlprompt ”
select database_role from v\\$database;
EOF`
echo $DBROLE
echo “CHECKING CURRENT DATABASE ROLE…”
if [ “$DBROLE” = “PRIMARY” ]
then
echo “”
echo “##################################################################”
echo “# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #”
echo “##################################################################”
echo “”
exit
fi
echo “”
echo “##################################################################”
echo “# SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM … #”
echo “###############################################################”
echo “”
echo “Continue? – Please enter ‘Y’ to proceed & ‘N’ to exit”
read ans
if [ “$ans” = ‘Y’ -o “$ans” = ‘y’ ]
then
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF
connect / as sysdba;
set feedback off pause off pagesize 0 heading off verify off linesize 500 term off
set sqlprompt ”
alter database commit to switchover to primary with session shutdown;
shutdown immediate;
startup mount;
alter system set log_archive_dest_state_2=enable scope=both;
alter database set standby database to maximize performance;
alter database open;
select database_role from v\$database;
EOF
#cat /u01/make_me_primary.log
$ORACLE_HOME/bin/lsnrctl start
else
echo “Quitting …..”
exit
fi
echo “####################################################################”
echo “# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW “PRIMARY” #”
echo “# #”
echo “# On NEW STANDBY Host please run the recovery script: #”
echo “####################################################################”
./start_recovery.sh
if [ “$1″ = “” ]
then
echo “”
echo “##################################################################”
echo “# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #”
echo “# SCRIPT USAGE : start_recovery.sh #”
echo “##################################################################”
echo “”
exit
fi
ORACLE_SID=`echo $1 |tr ‘[A-Z]’ ‘[a-z]’`; export ORACLE_SID;
ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null
if [ $? != 0 ]
then
echo “”
echo “##################################################################”
echo “# ENVIRONMENT SETTING SET FILE NOT FOUND…!!! #”
echo “# /var/opt/oracle/cronjobs/set$ORACLE_SID #”
echo “##################################################################”
echo “”
exit
fi
. /var/opt/oracle/cronjobs/set$ORACLE_SID
DB=`echo $1 |tr ‘[a-z]’ ‘[A-Z]’`; export DB;
sqlplus -s /nolog |&
print -p “connect / as sysdba”
print -p “set feedback off pause off pagesize 0 heading off verify off linesize 500 term off”
print -p “set sqlprompt ””
read -p JUNK
print -p “SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;”
read -p DBROLE
print -p “exit”
if [ “$DBROLE” = “PRIMARY” ]
then
echo “”
echo “##################################################################”
echo “# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #”
echo “##################################################################”
echo “”
exit
fi
echo “”
echo “##################################################################”
echo “# STARTING RECOVERY FOR $DB STANDBY, PLEASE CONFIRM … #”
echo “##################################################################”
echo “”
echo “Continue? – Please enter ‘Y’ to proceed & ‘N’ to exit”
read ans
if [ “$ans” = ‘Y’ -o “$ans” = ‘y’ ]
then
sqlplus -s /nolog < /tmp/start_recovery.log
connect / as sysdba;
recover managed standby database disconnect;
alter system set log_archive_dest_state_2=defer scope=both;
EOF
cat /tmp/start_recovery.log
ps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/null
if [ $? != 0 ]
then
echo “”
echo “##################################################################”
echo “# RECOVERY PROCESS NOT RUNNING… PLEASE CHECK #”
echo “##################################################################”
echo “”
exit
else
echo “##################################################################”
echo “# MRP PROCESS SUCESSFULLY STARTED #”
echo “##################################################################”
echo “”
echo “####################################################################”
echo “# SWITCHOVER COMPLETE #”
echo “”
echo “* Perform Database Post-Switchover Checklist! “
echo “####################################################################”
fi
else
echo “Quitting …..”
exit
fi

11gR2 Active Dataguard creation steps

Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in 11g where we
can create a standby database without having to take a backup on the primary database. Datafiles are copied over the network.
Primary machine – gg01
Standby machine – gg02
Database Name – DEVDB
TNS alias for Primary – node1
TNS alias for standby – standbydb
Enable force logging on the Primary database
Pre – req :
Select * from v$database_block_corruption ;
SQL> alter database force logging;
Database altered.
SELECT STATUS FROM V$LOG WHERE GROUP#=2;
Note : If the status did not CURRENT then simply drop the log file by:
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
Create the Standby log files on the Primary database
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u02/oracle/oradata/11g/DEVDB/sredo04.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u02/oracle/oradata/11g/DEVDB/sredo05.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u02/oracle/oradata/11g/DEVDB/sredo06.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u02/oracle/oradata/11g/DEVDB/sredo07.log’ size 50m;
Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbs on the standby server
Update listener.ora on Standby machine
(SID_DESC=
(GLOBAL_DBNAME=standbydb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=DEVDB)
)
Stop and Restart the listener on the standby site
Update tnsnames.ora on Standby as well as Primary site with the alias ‘standbydb’ and ‘node1’
standbydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standbydb )
)
)
node1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB.bankwest.com)
)
)
Create an init.ora on the Standby machine with just a single line which is the db_name parameter
DEVDB:/u01/app/oracle/product/11.2.0/db_1/dbs> cat initDEVDB.ora
db_name=DEVDB
Startup the Standby instance in nomount state
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2152328 bytes
Variable Size 159385720 bytes
Database Buffers 50331648 bytes
Redo Buffers 5287936 bytes
On the Primary launch RMAN and establish an auxiliary connection to the standby instance
DEVDB:/u01/oracle> rman target sys/oracle@node1 auxiliary sys/oracle@standbydb
rman target / auxiliary sys/oracle@standbydb
Recovery Manager: Release 11.1.0.7.0 – Production on Fri Dec 4 10:28:51 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DEVDB (DBID=2860177231)
connected to auxiliary database: DEVDB (not mounted)
Run the command to create the Standby Database
run {
SET MAXCORRUPT FOR DATAFILE 76 to 10;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME=”standbydb”
SET LOG_ARCHIVE_DEST_2=”service=node1 LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”node1″
SET FAL_CLIENT=”standbydb”
SET
CONTROL_FILES=’/u02/oracle/oradata/11g/DEVDB/control01.ctl’,’/u02/oracle/oradata/11g/DEVDB/control02.ctl’,’/u02/oracle/oradat
a/11g/DEVDB/control03.ctl';
}
Change the init.ora parameters related to redo transport and redo apply
On standby and primary
SQL> alter system set standby_file_management=AUTO scope=both;
System altered.
On Primary
SQL> alter system set fal_server=standbydb scope=both;
System altered.
SQL> alter system set fal_client=node1 scope=both;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=standbydb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standbydb’
scope=both; 2
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=
(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DEVDB’;
System altered.
Shutdown the Standby and enable managed recovery (active standby mode)
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2160352 bytes
Variable Size 775948576 bytes
Database Buffers 260046848 bytes
Redo Buffers 5730304 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>
Check if the MRP process is running
SQL> !ps -ef |grep mrp
oracle 446526 1 0 10:59:01 – 0:00 ora_mrp0_DEVDB
TEST
On Primary
SQL> conn system/xxx
Connected.
SQL> create table test_dr
2 (mydate date);
Table created.
SQL> insert into test_dr
2 values
3 (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
On Standby
SQL> conn system/xxx
Connected.
SQL> select to_char(mydate,’DD-MON-YY HH24:MI:SS’) from test_dr;
TO_CHAR(MYDATE,’DD-MON-YYHH
—————————
04-DEC-09 11:15:49

About me

I am currently working as a Senior Oracle DBA in Shaukat Khanum Lahore, Pakistan.
Oracle Certified Professional (Oracle 11g/12c OCP ) with about 6 years of Oracle experience.