Oracle – Create Physical Standby Database 10g RAC

Creating a Physical Standby Database OASSTBY

Make sure database is in archivelog mode
(If it’s not then shutdown instance 2, shutdown then startup mount instance 1, enable archivelog mode, startup both instances)

node 1$  ssh node2
node 2$  sqlplus ‘/as sysdba’
SQL>  shutdown immediate
SQL>  exit
$  exit
node 1$  sqlplus ‘/as sysdba’
SQL>  shutdown immediate
SQL>  startup mount
SQL>  alter database archivelog;
SQL>  alter database open;
SQL>  exit
node 1$  ssh node2
node 2$  sqlplus ‘/as sysdba’
SQL>  startup open

Backup primary database OASLIVE

(If using ASM then you must use RMAN to create the backup)

$  export ORACLE_SID=OASLIVE1
$  rman
RMAN> connect target /
connected to target database: OASLIVE (DBID=3404061247)
RMAN> backup database plus archivelog format ‘/c04/oracle/oraback/OASLIVE/rman/OASLIVE_%U.rbak’

Copy control file from primary database for standby

RMAN> copy current controlfile for standby to '/home/oracle/OASSTBY.stby';
Copy backup file and control file to standby node (Node11)
$  cd /home/oracle/kerri
$  scp OASSTBY.stby node11:/home/oracle/.
$  cd /c04/oracle/oraback/OASLIVE/rman/
$  scp OASLIVE_*.rbak node11:/c04/oracle/oraback/OASLIVE/rman/.


Whilst waiting for the backup to copy across, create standby redo logs in the primary database OASLIVE

(Amount of standby logs per thread = no. of redo logs +1)

node 1$ sqlplus ‘/as sysdba’
SQL> select * from v$log;
SQL> select group#, thread#, sequence#, status from v$standby_log;
SQL> alter database add standby logfile thread 1 ('+LIVEDATA') size 256M;
SQL> /
SQL>  /
SQL>  /
SQL>  /
SQL>  alter database add standby logfile thread 2 ('+LIVEDATA') size 250M;
SQL>  /
SQL>  /
SQL>  /
SQL>  /
SQL> select group#, thread#, sequence#, status from v$standby_log;

Copy the server details of the standby node to the primary node tnsnames.ora

node 11$  cd $TNS_ADMIN
Copy the following from OASSTBY tnsnames.ora to OASLIVE tnsnames.ora
OASSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgoh-data-11-vip)(PORT = 1511))
(CONNECT_DATA =
(UR=A)
(SERVER = DEDICATED)
(SERVICE_NAME = OASSTBY.dudleygoh-tr.wmids.nhs.uk)
)
)

Log into ASM and delete all pre-existing OASSTBY files

Check that both OASLIVE nodes have sysdba rman privileges if they don’t then run the following:

node 1$  sqlplus ‘/as sysdba’
SQLPLUS>  grant sysdba to rman;

Create spfile for physical standby using modified pfile (see end of post for pfile details)

node 11$  sqlplus ‘/as sysdba’
SQLPLUS>  startup nomount pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL> create spfile from pfile='/home/oracle/kerri/OASSTBYpfile.ora.bkup';
SQL>  shutdown immediate
SQL>  startup nomount

Log into RMAN and recover the standby database

node 11$  rman
RMAN>  connect target rman/o45rm4n@OASLIVE
RMAN>  connect auxiliary rman/o45rm4n@OASSTBY
RMAN>  duplicate target database for standby;

Copy across the password file

$  scp orapwOASLIVE node11:/u01/oracle/ora102/dbs/orapwOASSTBY
Recover database

SQLPLUS>  alter database recover managed standby database disconnect from session;

Appendix

Physical Standby spfile details for OASSTBY:

OASSTBY1.__db_cache_size=1191182336
OASSTBY.__java_pool_size=16777216
OASSTBY1.__java_pool_size=16777216
OASSTBY.__large_pool_size=16777216
OASSTBY1.__large_pool_size=16777216
OASSTBY.__shared_pool_size=352321536
OASSTBY1.__shared_pool_size=369098752
OASSTBY.__streams_pool_size=0
OASSTBY1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/OASSTBY/adump'
*.background_dump_dest='/u01/oracle/admin/OASSTBY/bdump'
*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4.0'
*.control_files='+LIVEDATA’
*.core_dump_dest='/u01/oracle/admin/OASSTBY/cdump'
*.db_block_size=8192
*.db_create_file_dest='+LIVEDATA'
*.db_domain='dudleygoh-tr.wmids.nhs.uk'
*.db_file_multiblock_read_count=8
*.db_file_name_convert='OASLIVE','OASSTBY'
*.db_name='OASLIVE'
*.db_unique_name='OASSTBY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OASSTBYXDB)'
*.fal_client='OASSTBY1'
*.fal_server='OASLIVE1,OASLIVE2'
OASSTBY1.instance_number=1
OASSTBY2.instance_number=2
*.job_queue_processes=1
OASSTBY2.local_listener='LISTENER_OASSTBY2'
OASSTBY1.local_listener='LISTENER_OASSTBY1'
*.log_archive_config='DG_CONFIG=(OASLIVE,OASSTBY,OASREP)'
*.log_archive_dest_1='LOCATION=+LIVEARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=OASSTBY'
*.log_archive_dest_2='SERVICE=OASLIVE VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASLIVE'
*.log_archive_dest_3='SERVICE=OASREP LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OASREP'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.open_cursors=300
*.pga_aggregate_target=2503999488
*.processes=600
*.remote_listener='LISTENERS_OASSTBY'
*.remote_login_passwordfile='exclusive'
*.sga_target=1610612736
*.standby_file_management='AUTO'
OASSTBY2.thread=2
OASSTBY1.thread=1
*.undo_management='AUTO'
OASSTBY1.undo_tablespace='UNDOTBS1'
OASSTBY2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/oracle/admin/OASSTBY/udump'
*.utl_file_dir='*'