Goal:-Standby database creation with cold backup Prerequisites for current setup Operating system :- Rhel 5 Database version oracle 10g Primary database name : - alphadb Standby database name :- betadb 1. Before you getting started, ensure your self that operating system and database version is same on both standby and primary side. For server side check, uanme -a (that confirms os release and kernel information) and for database check, select * from v$version; 2. If your standby database is in remote location then, ping standby database from primary database system and From standby database to primary system. 1. On primary database side enable Force logging. Sql> Alter database force logging; 2. Check / create password file in primary database Sql > select * from V$pwfile_users; create password file in Primary database. Cd $ORACLE_HOME/dbs/ $orapwd file=orapwalphadb password=xxxxxxxxxxx force =y (Replace xxxxxxxxxx with your actual password of sys) 3. Configure a standby redo log . Th e Primary database redo log file size must match with standby redo log. Sql> Select bytes from v$log; BYTES -------- 52428800 52428800 52428800 4. Use the following command to determine your current log file groups: SQL> select group#, member from v$logfile; 5. Check /Enable Archiving on Primary database Modify The pfile_alphadb.ora accordingly and add new roles and parameters. REFERENCE - D 6 . configure Ur listener REFERENCE - A 7. Configure tnsnames.ora REFERENCE - C 8. Copy the pfile and rename the second copy as pfile_betadb.ora. Cd $ORACLE_HOME/dbs cp pfile_alphadb.ora pfile_betadb.ora 9. Modify The pfile_betadb.ora REFERENCE – D 10. Create the relevant directory in pfile_betadb.ora Ex . bdump,cdump,dpdump,pfile etc.. 11. Create spfile from modified pfile; Sql>Shut immediate; Sql>Startup nomount pfile=’$ORACLE_HOME/dbs/pfile_alphadb.ora’; Sql> create spfile from pfile=’$ORACLE_HOME/dbs/pfile_alphadb.ora’; Sql>shut immediate; Sql>startup 12. Create a copy of cold backup(datafile ,redo logfile,,temp file) while the database is shutdown. Sql>shut immediate 13. Create directory for datafile and redo log file. Copy all datafile from alphadb/DATAFILE to betadb/DATAFILE/ Copy all tempfile from alphadb/temp/ to betadb/temp/ Copy all redo log file from alphadb/REDO to betadb/REDO/ 14. Create a control file for standby database. Sql>Startup mount Sql>alter database create standby controlfile as ‘$ORACLE_HOME/dbs/BETA1.ctl’; Cp ‘$ORACLE_HOME/dbs/BETA1.ctl’ ‘$ORACLE_HOME/dbs/BETA2.ctl’; 15. Alter database open; 16. Copy the password file of primary database to standby database; Cd $ORACLE_HOME/dbs/ Cp orapwalphadb orapwbetadb 17. Login as root Vi /etc/oratab Add a new entry with sid of standby database with option : N 18. Login with oracle Su – oracle 19 . Configure listeners for the primary and standby databases. 1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener. $lsnrctl stop $lsnrctl start 20. On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener. $lsnrctl stop $lsnrctl start 21. ON STANDBY SIDE Export ORACLE_SID=betadb SQL>startup nomount pfile=’$ORACLE_HOME/dbs/pfile_betadb.ora’; SQL>create spfile from pfile=’$ORACLE_HOME/dbs/pfile_betadb.ora’; -- Restart the Standby database using the newly created SPFILE. SQL>shutdown immediate; SQL>startup mount; 12. Start Redo apply 1) On the standby database, to start redo apply: SQL>alter database recover managed standby database disconnect from session; If you ever need to stop log apply services: SQL> alter database recover managed standby database cancel; 13. Verify the standby database is performing properly: 1) On Standby perform a query: SQL>select sequence#, first_time, next_time from v$archived_log; 2) On Primary, force a logfile switch: SQL>alter system switch logfile; 3) On Standby, verify the archived redo log files were applied: SQL>select sequence#, applied from v$archived_log order by sequence#; 14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply. To start real-time apply: SQL> alter database recover managed standby database using current logfile disconnect; 15. To create multiple standby databases, repeat this procedure. IV. Maintenance: 1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment. 2. Cleanup the archive logs on Primary and Standby servers. I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary. For the standby database, I run RMAN to backup and delete the archive logs once per week. $rman target /@STAN; RMAN>backup archivelog all delete input; To delete the archivelog backup files on the standby server, I run the following once a month: RMAN>delete backupset; 3. Password management The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.