Pages

Tuesday 18 September 2012


Data guard configuration using RMAN backup

Versions:
CentOs-5.5
Oracle 10.2.0

Databases information:
Primary database name:ACDBP
Primary db unique_name=ACDBP
Standby  database name=ACDBP
Standby db_unique_name=ACDBS
1.       Create the primary database
2.       Set the parameter file for the primary database:
initACDBP.ora

db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBP/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBP/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBP/control01.ctl','/opt/app/oracle/admin/ACDBP/controlfile/control02.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBP/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBP/udump'
service_names=ACDBP
#data guard parameters
db_unique_name='ACDBP'
fal_client='ACDBP'
fal_server='ACDBS'
log_archive_config='DG_CONFIG=(ACDBP,ACDBS)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBP/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS lgwr sync affirm reopen=10 #VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBS'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
log_archive_max_processes=2
ACDBP.standby_archive_dest='/opt/app/oracle/admin/ACDBP/arch'
standby_file_management='AUTO'




Create the appropriate directories for the stand by database and set the
parameter file for the standby database.

initACDBS.ora

db_cache_size=125M
java_pool_size=8M
large_pool_size=8M
shared_pool_size=100M
streams_pool_size=20M
audit_file_dest='/opt/app/oracle/admin/ACDBS/adump'
background_dump_dest='/opt/app/oracle/admin/ACDBS/bdump'
compatible='10.2.0'
control_files='/opt/app/oracle/oradata/ACDBS/standby1.ctl'
core_dump_dest='/opt/app/oracle/admin/ACDBS/cdump'
db_block_size=8192
db_domain=''
db_file_multiblock_read_count=16
db_name='ACDBP'
db_recovery_file_dest='/opt/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2048M
job_queue_processes=10
open_cursors=100
pga_aggregate_target=70M
processes=50
remote_login_passwordfile='EXCLUSIVE'
sga_target=536M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/opt/app/oracle/admin/ACDBS/udump'
service_names=ACDBS
#data guard parameters
db_unique_name='ACDBS'
fal_client='ACDBS'
fal_server='ACDBP'
log_archive_config='DG_CONFIG=(ACDBS,ACDBP)'
log_archive_dest_1='LOCATION=/opt/app/oracle/admin/ACDBS/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ACDBS'
#log_archive_dest_2='SERVICE=ACDBS LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) #DB_UNIQUE_NAME=ACDBS'
log_archive_dest_2='SERVICE=ACDBP LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ACDBP'
log_archive_dest_state_1='ENABLE'
log_archive_dest_state_2='ENABLE'
log_archive_format='%t_%s_%r.dbf'
db_file_name_convert='/opt/app/oracle/oradata/ACDBP/datafile','/opt/app/oracle/oradata/ACDBS/datafile'
log_file_name_convert='/opt/app/oracle/oradata/ACDBP/logfile','/opt/app/oracle/oradata/ACDBS/logfile','/opt/app/oracle/admin/ACDBP/logfile','/opt/app/oracle/admin/ACBS/logfile'
log_archive_max_processes=2
standby_archive_dest='/opt/app/oracle/admin/ACDBS/arch'
standby_file_management='AUTO'


3.       Configure the listener and tns entries on both primary and standby database
servers:

Primary listener.ora and tnsnames.ora:
listener.ora:
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = ACDBP))
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
     )
    )
 )

SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = ACDBP)
    )
 
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = PLSExtProc)
     (PROGRAM = extproc)
    )
    )

tnsnames.ora:

ACDBS =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
    )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ACDBS)
    )
 )

Standby listener .ora and tnsnames.ora:
listener.ora:
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = ACDBS))
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521))
     )
    )
 )

SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = ACDBS)
    )
 
    (SID_DESC =
     (ORACLE_HOME = /opt/app/oracle/product/10.2.0)
     (SID_NAME = PLSExtProc)
     (PROGRAM = extproc)
    )
    )

tnsnames.ora:
ACDBP =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
    )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = ACDBSP)
    )
 )

4.       Enable the archive for the primary database.
SQL>shut immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;


5.       Create the standby logfiles in the primary database.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log','/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log') size 10M;
   /
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/opt/app/oracle/admin/ACDBP/logfile/redo_stand_1a.log','/opt/app/oracle/oradata/ACDBP/logfile/redo_stand_1b.log') size 10M;
  /



a.       Launch RMAN to connect to the ACDBP database and check the current
RMAN configuration(On production server)

$rman target /
RMAN>show all;


b.      Backup the current production database to create a standby database:

RMAN> backup database include current controlfile for standby plus archivelog;

c.       Manually copy the backup sets from production to the standby database
server. (Location of backups must match on both production and DR).


$cd /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date
$mkdir –p /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date (on standby server)
$scp * oracle@192.168.1.211: /u01/opt/app/oracle/flash_recovery_area/ACDBP/backupsets/date



d.      On Standby database server startup ACDBS database in nomount
mode( standby server)
$export ORACLE_SID=ACDBS
$sqlplus / as sysdba
SQL>startup nomount
SQL>exit


e.      Create the standby database using RMAN (This assumes the database file
structures will be identical on both servers)(Standby server):

$RMAN target ‘sys/abacus@ACDBP’ auxiliary /
RMAN>duplicate target database for standby nofilenamecheck dorecover;


f.        Put the ACDBS database into managed standby mode:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect;

g.       On the production database switch logs to initiate replication:


SQL> alter system switch logfile;




The configuration of Dataguard is now complete.


Note: Monitor  the alertlog file of the production for the redolog shipping
status and the heartbeat status.



nofilenamecheck: The NOFILENAMECHECK option is required when the standby and primary datafiles and logs have the same names.

Dorecover:   By default, RMAN does not recover the standby database after creating it. If you do not specify the DORECOVER option of the DUPLICATE command, then RMAN automates these steps of the standby creation procedure during duplication:

A.        RMAN establishes connections both to the primary and standby databases, and the recovery catalog (if used).
B.        RMAN queries the repository, which is either the primary control file or the recovery catalog, to identify the backups of primary database datafiles and the standby control file.
C.        If you use a media manager, then RMAN contacts the media manager on the standby host to request the backup data.
D.       RMAN restores the standby control file to the standby host, thereby creating the standby control file.
E.        RMAN restores the primary datafile backups and copies to the standby host, thereby creating the standby database datafiles.
F.        RMAN leaves the standby database mounted, but does not place the standby database in manual or managed recovery mode. RMAN disconnects and does not perform media recovery of the standby database. Note that you should not register the standby database in the recovery catalog.












No comments:

Post a Comment