Oracle Dataguard setup and configuration

INSTALLATION DATAGUARD
In our example-configuration the database is called “xxx” and the two servers are “Primary” and “Secondary”. For our configuration the two servers stand in seperate server-rooms (sr1 & sr2).


 * Primary: The database on the primary side has to be configured
 * a. Reboot with automatic shutdown and startup of the database
 * b. Listener and TNS have to function correctly

SQL> ALTER DATABASE FORCE LOGGING;
 * Primary: Switch the primary database to “FORCE LOGGING”

/opt/oracle/product/10.2.0/db_1/dbs/orapwxxx
 * Primary: Check if the password-file has been created:

ALTER DATABASE ADD LOGFILE GROUP 4 (’/database_dbf/xxx/redo04a.log’,'/database_dbf/xxx/redo04b.log’) SIZE 500M; SELECT * FROm V$LOG; ALTER DATABASE DROP LOGFILE GROUP 4;
 * Primary: Create redolog-groups and members:

It’s important, that you create one more group as you created redolog-files ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 (’/database_arc/xxx/sredo/sredo10a.log’,'/database_arc/xxx/sredo/sredo10b.log’) SIZE 500M; SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG
 * Primary: Create the standby-redologs

CREATE PFILE FROM SPFILE - The file will be created at /dbs (xxx.ora)
 * Primary: Create a PFile from the SPFile:

xxx.__db_cache_size=364904448 xxx.__java_pool_size=4194304 xxx.__large_pool_size=4194304 xxx.__shared_pool_size=138412032 xxx.__streams_pool_size=0 OPEN_CURSORS=3000 -> We had to change this parameter for our application processes=300 remote_login_passwordfile=’EXCLUSIVE’ DB_UNIQUE_NAME=’sr1′ FAL_CLIENT=’sr1′ FAL_SERVER=’sr2′ LOG_ARCHIVE_CONFIG=’DG_CONFIG=(sr1,sr2)’ LOG_ARCHIVE_DEST_1=’LOCATION=/database_arc/xxx/arc/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sr1′ LOG_ARCHIVE_DEST_2=’SERVICE=sr2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sr2′ LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ LOG_ARCHIVE_MAX_PROCESSES=10 STANDBY_FILE_MANAGEMENT=AUTO
 * Primary: Edit the created PFile so that it looks as follows: (the fat printed lines are important)
 * .audit_file_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/adump’
 * .background_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/bdump’
 * .compatible=’10.2.0.3.0′
 * .control_files=’/database_dbf/xxx/control01.ctl’,'/database_dbf/xxx/control02.ctl’,'/database_dbf/xxx/control03.ctl’
 * .core_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/cdump’
 * .db_block_size=8192
 * .db_domain=”
 * .db_file_multiblock_read_count=16
 * .db_name=’xxx’
 * .job_queue_processes=10
 * .pga_aggregate_target=173015040
 * .sga_target=519045120
 * .undo_management=’AUTO’
 * .undo_tablespace=’UNDOTBS1′
 * .user_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/udump’

 check ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Check archive folder alter system switch logfile; Check archive folder
 * Primary: Absorb these configurations and activate archivelog

mkdir -p /database_dbf/xxx/ mkdir -p /database_arc/xxx/ mkdir -p $ORACLE_HOME/admin/xxx/adump mkdir -p $ORACLE_HOME/admin/xxx/bdump mkdir -p $ORACLE_HOME/admin/xxx/cdump mkdir -p $ORACLE_HOME/admin/xxx/dpdump mkdir -p $ORACLE_HOME/admin/xxx/pfile mkdir -p $ORACLE_HOME/admin/xxx/udump
 * Secondary: Create folders as user “oracle”:

shutdown immediate b. copy the database files (ALWAYS AS USER “oracle”!!!) scp orapwxxx oracle@10.x.x.x:/opt/oracle/product/10.2.0/db_1/dbs scp initxxx.ora oracle@10.x.x.x:/opt/oracle/product/10.2.0/db_1/dbs scp -r /database_dbf/xxx/ oracle@10.x.x.x:/database_dbf/ scp -r /database_arc/xxx/ oracle@10.200.30.4:/database_arc/
 * Primary: Shutdown the database and copy it to the Standby-Server (secondary):

strartup mount; ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/standby01.ctl’; scp /tmp/standby01.ctl oracle@10.x.x.x:/database_dbf/xxx </pre<
 * Primary: Create Standby control file:

cp /database_dbf/xxx/standby01.ctl /database_dbf/xxx/control01.ctl
 * Secondary: Copy Standby control file (3 TIMES!)

netmgr vi /etc/hosts lsnrctl stop lsnrctl start
 * Secondary: Configure TNS Names for sr1,sr2 and configure xxx, create Listener (database) and edit /etc/hosts!

xxx.__db_cache_size=364904448 xxx.__java_pool_size=4194304 xxx.__large_pool_size=4194304 xxx.__shared_pool_size=138412032 xxx.__streams_pool_size=0 processes=300 DB_NAME=’xxx’ DB_UNIQUE_NAME=’sr2′ FAL_CLIENT=’sr2′ FAL_SERVER=’sr1′ LOG_ARCHIVE_CONFIG=’DG_CONFIG=(sr1,sr2)’ LOG_ARCHIVE_DEST_1=’LOCATION=/database_arc/xxx/arc/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sr2′ LOG_ARCHIVE_DEST_2=’SERVICE=sr1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sr1′ LOG_ARCHIVE_DEST_STATE_1=’ENABLE’ LOG_ARCHIVE_DEST_STATE_2=’ENABLE’ LOG_ARCHIVE_FORMAT=’%t_%s_%r.arc’ LOG_ARCHIVE_MAX_PROCESSES=10 STANDBY_FILE_MANAGEMENT=AUTO
 * Secondary: Edite the copied PFile, so that it looks as follows: (the fat printed lines are important)
 * .audit_file_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/adump’
 * .background_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/bdump’
 * .compatible=’10.2.0.3.0′
 * .control_files=’/database_dbf/xxx/control01.ctl’,'/database_dbf/xxx/control02.ctl’,'/database_dbf/xxx/control03.ctl’
 * .core_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/cdump’
 * .db_block_size=8192
 * .db_domain=”
 * .db_file_multiblock_read_count=16
 * .job_queue_processes=10
 * .open_cursors=3000
 * .pga_aggregate_target=173015040
 * .remote_login_passwordfile=’EXCLUSIVE’
 * .sga_target=519045120
 * .undo_management=’AUTO’
 * .undo_tablespace=’UNDOTBS1′
 * .user_dump_dest=’/opt/oracle/product/10.2.0/db_1/admin/xxx/udump’

CREATE SPFILE FROM PFILE=’/opt/oracle/product/10.2.0/db_1/dbs/initxxx.ora’;
 * Secondary: Create SPFile from PFile (DATABASE IS NOT STARTED YET!)

SHUTDOWN IMMEDIATE
 * Primary: Stop database

STARTUP MOUNT ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Check alert log
 * Secondary: Start Standby database

STARTUP Check alert log
 * Primary: Start database

ALTER SYSTEM SWITCH LOGFILE;
 * Primary: Make log switch and check it

ls -al /database_arc/xxx/arc
 * Secondary: Check

Switchover (you can repeat these steps, both database’s have to be OK!)
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 * Primary: Check database

SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 * Secondary: Check database

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
 * Primary: Switch database to standby (database has to be in status “OPEN”!)

SHUTDOWN IMMEDIATE STARTUP MOUNT
 * Primary (SR1): Shutdown database and mount it as standby

Secondary (SR2): Make database to primary (database has to be in status “MOUNT”!) ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; SELECT SWITCHOVER_STATUS FROM V$DATABASE; has to show “TO_STANDBY” !
 * On both databases there has to stand “TO_PRIMARY” after “SELECT SWITCHOVER_STATUS FROM V$DATABASE;”

ALTER DATABASE OPEN;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 * Primary (SR1): Activate logapply

ALTER SYSTEM SWITCH LOGFILE;
 * Secondary: Make log switch and check it

ls -al /database_arc/xxx/arc
 * Primary: Check

YOU CAN DO THIS THE SAME WAY AROUND SO THAT THE FORMER PRIMARY IS THE PRIMARY AGAIN!