Physical Standby Database

Create Physical Standby Database

 

SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE ADD STANDBY LOGFILE [THREAD 1] [GROUP n] ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 50M;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
SQL> CREATE PFILE FROM SPFILE;
SQL> !vi $ORACLE_HOME/dbs/initPRIM.ora

PRIM.__db_cache_size=1090519040
PRIM.__java_pool_size=16777216
PRIM.__large_pool_size=16777216
PRIM.__shared_pool_size=335544320
PRIM.__streams_pool_size=0

*.audit_file_dest       ='/opt/app/oracle/admin/PRIM/adump'
*.background_dump_dest  ='/opt/app/oracle/admin/PRIM/bdump'
*.core_dump_dest        ='/opt/app/oracle/admin/PRIM/cdump'
*.user_dump_dest        ='/opt/app/oracle/admin/PRIM/udump'

*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_file_multiblock_read_count=16

*.db_domain=''
*.db_name='PRIM'
*.db_unique_name='PRIM'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAND)'
*.control_files='/u01/oradata/PRIM/control01.ctl','/u01/oradata/PRIM/control02.ctl','/u01/oradata/PRIM/control03.ctl'

*.db_recovery_file_dest='/backup/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.log_archive_dest_10='
        LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5
        VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
        DB_UNIQUE_NAME=PRIM'
*.log_archive_dest_9='
        SERVICE=STAND LGWR SYNC AFFIRM
        VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
        DB_UNIQUE_NAME=STAND'
LOG_ARCHIVE_DEST_STATE_10=ENABLE
LOG_ARCHIVE_DEST_STATE_9=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_format='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=STAND
FAL_CLIENT=PRIM
DB_FILE_NAME_CONVERT='/u01/oradata/STAND/','/u01/oradata/PRIM/','/u02/oradata/STAND/','/u02/oradata/PRIM/'
LOG_FILE_NAME_CONVERT='/u01/oradata/STAND/','/u01/oradata/PRIM/','/u02/oradata/STAND/','/u02/oradata/PRIM/'

*.dispatchers='(protocol=TCP)'
*.job_queue_processes=10
*.max_dispatchers=10
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1468006400
*.shared_servers=10
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
RMAN> backup tag "full_db_archivelog" database plus archivelog;
RMAN> backup tag "standby_controlfile" current controlfile for standby; 

SQL> shutdown immediate
SQL> startup nomount
RMAN> set DBID=1111111111111
RMAN> restore standby controlfile from "/backup/STANDBY_CONTROLFILE_from_PRIMary_.bkp"
SQL> CREATE PFILE='/backup/initSTAND.ora' FROM SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> !vi /backup/initSTAND.ora

PRIM.__db_cache_size=1090519040
PRIM.__java_pool_size=16777216
PRIM.__large_pool_size=16777216
PRIM.__shared_pool_size=335544320
PRIM.__streams_pool_size=0

*.audit_file_dest ='/opt/app/oracle/admin/STAND/adump'
*.background_dump_dest ='/opt/app/oracle/admin/STAND/bdump'
*.core_dump_dest ='/opt/app/oracle/admin/STAND/cdump'
*.user_dump_dest ='/opt/app/oracle/admin/STAND/udump'

*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_file_multiblock_read_count=16

*.db_domain=''
*.db_name='PRIM'
*.db_unique_name='STAND'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAND)'
*.control_files='/u01/oradata/STAND/control01.ctl','/u01/oradata/STAND/control02.ctl','/u01/oradata/STAND/control03.ctl'

*.db_recovery_file_dest='/backup/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.log_archive_dest_10='
        LOCATION=USE_DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN=5
        VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
        DB_UNIQUE_NAME=STAND'
*.log_archive_dest_9='
        SERVICE=PRIM LGWR SYNC AFFIRM
        VALID_FOR=(ONLINE_LOGFILE, PRIMARY_ROLE)
        DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_STATE_10=ENABLE
LOG_ARCHIVE_DEST_STATE_9=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
*.log_archive_format='%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAND
DB_FILE_NAME_CONVERT='/u01/oradata/PRIM/','/u01/oradata/STAND/','/u02/oradata/PRIM/','/u02/oradata/STAND/'
LOG_FILE_NAME_CONVERT='/u01/oradata/PRIM/','/u01/oradata/STAND/','/u02/oradata/PRIM/','/u02/oradata/STAND/'

*.dispatchers='(protocol=TCP)'
*.job_queue_processes=10
*.max_dispatchers=10
*.open_cursors=300
*.pga_aggregate_target=524288000
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1468006400
*.shared_servers=10
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

SQL> STARTUP pfile='/backup/initSTAND.ora' MOUNT
RMAN> restore database;
RMAN> list backup of archivelog all;
RMAN> recover database until sequence (above_seq# + 1);
SQL> SELECT GROUP#, SEQUENCE#, THREAD#, BYTES, USED, ARCHIVED, STATUS FROM V$STANDBY_LOG;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP pfile='/backup/initPRIM.ora' NOMOUNT
SQL> CREATE SPFILE FROM PFILE='/backup/initPRIM.ora'
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Switchover

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY *
  ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL> SELECT SID, SERIAL#,PROCESS, PROGRAM FROM V$SESSION WHERE TYPE='USER' AND SID <>(SELECT DISTINCT SID FROM V$MYSTAT);
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Failovers

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'file_paths'; 
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG; SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'file_paths';
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP; 
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;