Create Standby using RMAN Duplicate.

Environment Setup

OS Version: Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Database Version: 10.2.0.1.0
Primary - 192.168.1.10 - server1.oracleadmin.com - db10gp
Standby - 192.168.1.11 - server2.oracleadmin.com - db10gs

Changes in primary

1. Create password file if not present.
orapwd file=orapwdb10gp password=suresh force=y

2. Enable Force logging
SQL>
ALTER DATABASE FORCE LOGGING;
SELECT force_logging FROM v$database;

3. Create standby logfiles
SQL>
col MEMBER format a60
select * from v$logfile;

SQL>
select bytes/1024/1024 "LogSize(MB)" from v$log;

SQL>
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/product/10.2.0/oradata/db10gp/redo04.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/product/10.2.0/oradata/db10gp/redo05.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/product/10.2.0/oradata/db10gp/redo06.log' SIZE 50M;

SQL>
col MEMBER format a60
select * from v$logfile;

4. Enable archive log mode.
SQL>
archive log list;

SQL>
startup mount;
alter database archivelog;
alter database open;

5. Create latest pfile
SQL>
create pfile from spfile;


6. Add standby parameters

vi initdb10gp.ora

# Standby parameters -------------------------------------------------------------------------
db_unique_name='db10gp'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(db10gp,db10gs)'
LOG_ARCHIVE_DEST_2='SERVICE=db10gs LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db10gs'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
# Standby role parameters --------------------------------------------------------------------
*.fal_server=db10gs
*.fal_client=db10gp
*.standby_file_management=auto
*.db_file_name_convert='/u01/app/oracle/product/10.2.0/oradata/db10gs/','/u01/app/oracle/product/10.2.0/oradata/db10gp/','/u01/app/oracle/admin/db10gs/','/u01/app/oracle/admin/db10gp/'
*.log_file_name_convert='/u01/app/oracle/product/10.2.0/oradata/db10gs/','/u01/app/oracle/product/10.2.0/oradata/db10gp/'
# ---------------------------------------------------------------------------------------------

7. Clean shut down the database
SQL>
shutdown immediate;

8. Startup using new file in nomount state
SQL>
startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdb10gp.ora'

9. create spfile from pfile
SQL>
create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdb10gp.ora';

10. Restart the datasbase using spfile.
SQL>
shutdown immediate;

SQL>
startup;
show parameter log_archive_dest

11. create listener.ora, sqlnet.ora, tnsnames.ora

[oracle@server1 admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
  )

[oracle@server1 admin]$ cat sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES)

[oracle@server1 admin]$ cat tnsnames.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521)(IP = FIRST))
    )
  )

DB10GP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = db10gp.oracleadmin.com)
      (INSTANCE_NAME = db10gp)
    )
  )

DB10GS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (INSTANCE_NAME = db10gs)
      (SERVICE_NAME = db10gs.oracleadmin.com) (UR=A)
    )
  )

12. Start the listner and check the status

lsnrctl start
lsnrctl status

13. Remove old backups/archive logs and Take full backup of primary database using RMAN

mkdir -p /u01/app/oracle/backup
cd /u01/app/oracle/backup
chmod 777 -R /u01/app/oracle/backup


rman target /

RMAN>
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/%d_%u_%s_%p.bkp';
crosscheck backup;
delete noprompt expired backup;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all backed up 1 times to DEVICE TYPE disk;
backup database plus archivelog;
backup current controlfile for standby format '/u01/app/oracle/backup/standbycontrol.ctl';
backup spfile format '/u01/app/oracle/backup/spfiledb10gp_%s.ora';
exit

standby
--------
[server2] >
14. Ensure database binaries are installed
echo $ORACLE_HOME

15. Create backup location same as primary
mkdir -p /u01/app/oracle/backup
cd /u01/app/oracle/backup
chmod 777 /u01/app/oracle/backup/*

16. Copy the backup from primary to standby in same location
scp /u01/app/oracle/backup/*.* server2:/u01/app/oracle/backup/

17. Create necessary directories

mkdir -p /u01/app/oracle/admin/db10gs/adump
mkdir -p /u01/app/oracle/admin/db10gs/bdump
mkdir -p /u01/app/oracle/admin/db10gs/cdump
mkdir -p /u01/app/oracle/admin/db10gs/udump
mkdir -p /u01/app/oracle/product/10.2.0/oradata/db10gs

18. Copy the parameter file and password file from primary to standby.
scp /u01/app/oracle/product/10.2.0/db_1/dbs/initdb10gp.ora server2:/u01/app/oracle/product/10.2.0/db_1/dbs/

19. Edit parameter file for standby

cd /u01/app/oracle/product/10.2.0/db_1/dbs
mv initdb10gp.ora  initdb10gs.ora
vi initdb10gs.ora

> *.audit_file_dest='/u01/app/oracle/admin/db10gs/adump'
> *.background_dump_dest='/u01/app/oracle/admin/db10gs/bdump'
> *.control_files='/u01/app/oracle/product/10.2.0/oradata/db10gs/control01.ctl','/u01/app/oracle/product/10.2.0/oradata/db10gs/control02.ctl','/u01/app/oracle/product/10.2.0/oradata/db10gs/control03.ctl'
> *.core_dump_dest='/u01/app/oracle/admin/db10gs/cdump'
> *.user_dump_dest='/u01/app/oracle/admin/db10gs/udump'
> db_unique_name='db10gs'
> LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db10gs'
> LOG_ARCHIVE_DEST_2='SERVICE=db10gp LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=db10gp'
> *.fal_server=db10gp
> *.fal_client=db10gs
> *.db_file_name_convert='/u01/app/oracle/product/10.2.0/oradata/db10gp/','/u01/app/oracle/product/10.2.0/oradata/db10gs/','/u01/app/oracle/admin/db10gp/','/u01/app/oracle/admin/db10gs/'
> *.log_file_name_convert='/u01/app/oracle/product/10.2.0/oradata/db10gp/','/u01/app/oracle/product/10.2.0/oradata/db10gs/'


diff initdb10gp.ora initdb10gs.ora | grep ">"

vi /etc/oratab
db10gs:/u01/app/oracle/product/10.2.0/db_1:N

20. Set env and startup standby database using nomount.
SQL>
startup nomount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdb10gs.ora';
exit

21. Create listener.ora, sqlnet.ora, tnsnames.ora

[oracle@server2 admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    )
  )

[oracle@server2 admin]$ cat sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES)

[oracle@server2 admin]$ cat tnsnames.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)(IP = FIRST))
    )
  )

DB10GS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
    (CONNECT_DATA =
      (INSTANCE_NAME = db10gs)
      (SERVICE_NAME = db10gs.oracleadmin.com) (UR=A)
    )
  )

DB10GP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    (CONNECT_DATA =
      (INSTANCE_NAME = db10gp)
      (SERVICE_NAME = db10gp.oracleadmin.com)
    )
  )

22. Start the listner and check the status

lsnrctl start
lsnrctl status

23. Connect to rman target as primary and standby as auxiliary

rman target sys/suresh@db10gp auxiliary /
RMAN>
CATALOG START WITH '/u01/app/oracle/backup';
duplicate target database for standby;
exit

24. Standby database should be in mount status
SQL>
select INSTANCE_NAME,status from v$instance;

25. create spfile
SQL>
show parameter spfile
create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initdb10gs.ora';

25. Shutdown and start standby database.
SQL>
shutdown immediate;
startup nomount
alter database mount standby database;
select name,DB_UNIQUE_NAME,DATABASE_ROLE,open_mode from v$database;

26. Start Managed Recovery Process and check status.
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;
select process, status from v$managed_standby;
!ps -ef | grep mrp

27. Check archive logs in primary and standby are in sync
SQL>
archive log list

28. Check archived logs applied at standby
SQL>
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Graceful Switchover & Switchback

Steps to be performed on Primary

1. Check switchover status
SQL>
select  switchover_status FROM v$database;

2. If SWITCHOVER_STATUS=TO STANDBY
SQL>
alter database commit to switchover to physical standby ;

3. If SWITCHOVER_STATUS=SESSIONS ACTIVE
SQL>
alter database commit to switchover to physical standby with session shutdown;

4. Shutdown and start as standby
shutdown immediate
startup nomount ;
alter database mount standby database;
alter system set log_archive_dest_state_2 = defer;


Steps to be performed on Standby Database                                 

5. Check switchover status
SQL>
select  switchover_status FROM v$database;

6. If SWITCHOVER_STATUS=TO PRIMARY
SQL>
alter database commit to switchover to primary;

7. If SWITCHOVER_STATUS=SESSIONS ACTIVE
SQL>
alter database commit to switchover to primary with session shutdown;

8. Shutdown and start as standby
SQL>
shutdown immediate;
startup ;

9. Start MRP on New Standby (Old Primary)              
SQL>
alter database recover managed standby database disconnect from session;

10. Enable log_archive_dest_state_2 in new Primary (Old Standby)              
SQL>
alter system set log_archive_dest_state_2 = enable;

SQL > alter system archive log current;


FAILOVER Steps (Primary failed)

shut immediate;
lsnrctl stop

At standby 

1. Check archive gap
SQL>
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

2. Copy the required archivelog and register
SQL>
alter database register physical logfile '/u01/app/oracle/flash_recovery_area/1_111_892560559.arc';

3. Stop the redo apply process in standby database.
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4. Finish to apply archive logs copied from primary.
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

5. Activate standby
SQL>
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

6. Switch standby database to primary database.
SQL>
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

7. Open database.
SQL>
ALTER DATABASE OPEN;
select name,DB_UNIQUE_NAME,DATABASE_ROLE,open_mode from v$database;

Upgrade 10201 to 10204

Shutdown all running components

$ isqlplusctl stop
$ emctl stop dbconsole
$ lsnrctl stop
$ sqlplus / as sysdba
SQL> shutdown immediate

Update Binaries

$ cd /home/oracle
$ unzip p6810189_10204_Linux-x86.zip
$ cd Disk1/
$ ./runInstaller

Post upgrade steps

SQL> startup upgrade
SQL> spool pre_upgrade.log
SQL> @utlu102i.sql
SQL> spool upgrade.log
SQL> @catupgrd.sql
SQL> spool off
SQL> shutdown immediate

SQL> startup
SQL> conn / as sysdba
SQL> spool recompile.log
SQL> @utlrp.sql
SQL> spool off


#Check the all components are upgraded.

SQL>
set lines 10000
set pages 1000
column comp_name format a40
column version format a12
column status format a6
select comp_name, version, status from sys.dba_registry;