Oracle Dataguard setup and configuration

From n0r1sk software solutions
Jump to: navigation, search

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
  • Primary: Switch the primary database to “FORCE LOGGING”
SQL> ALTER DATABASE FORCE LOGGING;
  • Primary: Check if the password-file has been created:
/opt/oracle/product/10.2.0/db_1/dbs/orapwxxx
  • Primary: Create redolog-groups and members:
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 the standby-redologs

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 a PFile from the SPFile:
CREATE PFILE FROM SPFILE

- The file will be created at /dbs (xxx.ora)

  • Primary: Edit the created PFile so that it looks as follows: (the fat printed lines are important)
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
*.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
OPEN_CURSORS=3000 -> We had to change this parameter for our application
*.pga_aggregate_target=173015040
processes=300
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’
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: TNS Names for our two server-rooms sr1,sr2 and configurexxx, create Listener (database) and edit /etc/hosts

<pre< netmgr vi /etc/hosts lsnrctl stop lsnrctl start </pre>

  • Primary: Absorb these configurations and activate archivelog
shutdown immediate
CREATE SPFILE FROM PFILE=’/opt/oracle/product/10.2.0/db_1/dbs/initxxx.ora’;
STARTUP MOUNT;
ARCHIVE LOG LIST; -> check
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Check archive folder
alter system switch logfile;
Check archive folder
  • Secondary: Create folders as user “oracle”:
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
  • Primary: Shutdown the database and copy it to the Standby-Server (secondary):
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: Create Standby control file:
strartup mount;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/standby01.ctl’;
scp /tmp/standby01.ctl oracle@10.x.x.x:/database_dbf/xxx
</pre<

*Secondary: Copy Standby control file (3 TIMES!)
<pre>cp /database_dbf/xxx/standby01.ctl /database_dbf/xxx/control01.ctl
  • Secondary: Configure TNS Names for sr1,sr2 and configure xxx, create Listener (database) and edit /etc/hosts!
netmgr
vi /etc/hosts
lsnrctl stop
lsnrctl start
  • Secondary: Edite the copied PFile, so that it looks as follows: (the fat printed lines are important)
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
*.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
processes=300
*.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’
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: Create SPFile from PFile (DATABASE IS NOT STARTED YET!)
CREATE SPFILE FROM PFILE=’/opt/oracle/product/10.2.0/db_1/dbs/initxxx.ora’;
  • Primary: Stop database
SHUTDOWN IMMEDIATE
  • Secondary: Start Standby database
STARTUP MOUNT
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Check alert log
  • Primary: Start database
STARTUP
Check alert log
  • Primary: Make log switch and check it
ALTER SYSTEM SWITCH LOGFILE;
  • Secondary: Check
ls -al /database_arc/xxx/arc

Switchover (you can repeat these steps, both database’s have to be OK!)

  • Primary: Check database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
  • Secondary: Check database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
  • Primary: Switch database to standby (database has to be in status “OPEN”!)
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
  • Primary (SR1): Shutdown database and mount it as standby
SHUTDOWN IMMEDIATE
STARTUP MOUNT
  • On both databases there has to stand “TO_PRIMARY” after “SELECT SWITCHOVER_STATUS FROM V$DATABASE;”

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” !

ALTER DATABASE OPEN;
  • Primary (SR1): Activate logapply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  • Secondary: Make log switch and check it
ALTER SYSTEM SWITCH LOGFILE;
  • Primary: Check
ls -al /database_arc/xxx/arc

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