1.Enable force logging.
2.Create SRL(standby redo logs).
3.Make proper changes in the parameter file of primary.
4.Backup the database that includes backup of datafiles, archivelogs and controlfile for standby and copy the backups to standby server.
5.Create the parameter file for standby,
6.Establish the connectivity from primary to standby.
7. Move backup to standby.
8 and 9. Start the standby instance.
10.Use RMAN Duplicate command to create the standby database.
Verify whether the log are shipped and applied properly @the standby
STEP: 1
Enable Force Logging on primary,
SQL> ALTER DATABASE FORCE LOGGING;
NOTE: Create password file if not present, also check if archiving enabled.
STEP: 2
Configure a Standby Redo Log on primary,
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 '/u01/app/oracle/oradata/SVRPRD/log3a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/SVRPRD/log4a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/SVRPRD/log5a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/SVRPRD/log6a.log' size 50m;
NOTE:
1. To check the number of SRL,
(maximum number of logfiles for each thread + 1) * maximum number of threads
2. The SRL size equal to ORL size.
STEP :3 initSVRPRD.ora
Modify the primary initialization parameter for dataguard on primary,
SQL> show parameter LOG_ARCHIVE_CONFIG;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=("SVRPRD","SVRPRDS")
*.log_archive_config='dg_config=("SVRPRD","SVRPRDS")'
*.log_archive_dest=''
*.log_archive_dest_1='Location=/archivelogs/SVRPRD'
*.log_archive_dest_2='service=SVRPRDS valid_for=(online_logfiles, primary_role) db_unique_name="SVRPRDS"'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='SVRPRD_%t_%s_%r.arc'
*.fal_client='SVRPRD'
*.fal_server='SVRPRDS'
Ex : use dynamically
QL>alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stdby)';
STEP:4 // if we use active Standby Database no need to take backup
Run the backup job at the primary by connecting to target and catalog DB(if any)
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/u01/app/oracle/databases/stage/%U';
backup archivelog all format '/u01/app/oracle/databases/stage/%U';
backup current controlfile for standby format '/u01/app/oracle/databases/stage/%U';
}
STEP :5
Create parameter file on primary copy it to standby and make the necessary changes,
SQL>create pfile='/u01/app/oracle/databases/prim/stage/initstdby.ora' from spfile;
Step :6 ---tnsnames.ora
Establish the connectivity,
Create net services on both primary and standby,
At prim server,
SVRPRDS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = svrdrebsd1.singiri.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SVRPRD)
)
)
SVRPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = svrprdebsd1.singiri.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SVRPRD)
)
)
++++++++At standby server+++++++++
# Generated by Oracle configuration tools.
SVRPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = svrprdebsd1.singiri.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SVRPRD)
)
)
SVRPRDS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = svrdrebsd1.singiri.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SVRPRD)
)
)
STEP : 7
Move the files to standby server,
a. Create the same directory on standby server and copy the backups.
os standby,
$mkdir -p /u01/app/oracle/databases/stage/
[oracle@raca prim]$ scp /u01/app/oracle/databases/stage/* core1.idc.oracle.com:/u01/app/oracle/databases/stage/
b. Alternatively we can copy the backups to standby different directory. On standby server connect to RMAN target as primary and auxiliary instance or if
your primary is having catalog database then connect to target as primary, catalog database and auxiliary instance. Catalog those backup pieces to let the
controlfile of primary or catalog database to know the backup information.
STEP: 8
Make the necessary changes on the copied initstdby.ora file on standby.
*.db_name='SVRPRD'
*.db_unique_name='SVRPRDS'
*.diagnostic_dest='/u01/app/oracle'
*.fal_client='SVRPRDS'
*.fal_server='SVRPRD'
*.local_listener='SVRPRD'
*.log_archive_config='dg_config=("SVRPRD","SVRPRDS")'
*.log_archive_dest=''
*.log_archive_dest_1='location=/archivelogs/SVRPRD'
*.log_archive_dest_2='service=SVRPRD valid_for=(online_logfiles, primary_role) db_unique_name="SVRPRD"'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='SVRPRD_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
STEP: 9
Create standby instance,
create the same password as that of primary. Alternatively we can copy the password file from primary and rename it on standby.
For example from primary copy the password file,
scp /u01/app/oracle/dbs/opapwprim standby.idc.oracle.com:/u01/app/oracle/dbs/
on standby,
$mv orapwprim orapwstdby
Ex:
$export ORACLE_SID
$orapwd file='$ORACLE_HOME/dbs/orapwstdby' password=sys entries=10;
STEP : 10 Use RMAN duplicate for standby on standby,
Make sure listener is running on both Primary/Standby database ,
On Standby :
sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/initSVRPRD.ora
$RMAN target /@prim catalog RMAN/RMAN@catdb auxiliary /
if no catalog database,
$RMAN target sys/<password>@prim auxiliary /
Ex :
rman target sys/svrprdmgr@SVRPRD auxiliary sys/svrprdmgr@SVRPRDS
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 16 01:36:08 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: SVRPRD (DBID=1765422269)
connected to auxiliary database: SVRPRD (not mounted)
RMAN> run {
duplicate target database for standby dorecover from active database nofilenamecheck;
}
Starting Duplicate Db at 16-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=474 device type=DISK
allocated channel: ORA_AUX_DISK_2
.......
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSVRPRD' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSVRPRD' ;
}
executing Memory Script
Starting backup at 16-JUL-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=492 device type=DISK
allocated channel: ORA_DISK_2
............
Finished backup at 16-JUL-14
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SVRPRD/control01.dbf';
restore clone controlfile to '/u01/app/oracle/oradata/SVRPRD/control02.dbf' from
'/u01/app/oracle/oradata/SVRPRD/control01.dbf';
}
executing Memory Script
Starting backup at 16-JUL-14
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/CF_snapshot_PROD_%F.bak tag=TAG20140716T023551 RECID=18 STAMP=853036556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 16-JUL-14
Starting restore at 16-JUL-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
................
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
......
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 16-JUL-14
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/SVRPRD/temp03.dbf";
..................
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/SVRPRD/system01.dbf" datafile
....................
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
........................
.......................
.......................
datafile 11 switched to datafile copy
input datafile copy RECID=28 STAMP=853054683 file name=/u01/app/oracle/oradata/SVRPRD/svrebindx02.dbf
contents of Memory Script:
{
set until scn 6047214831398;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 16-JUL-14
using channel ORA_AUX_DISK_1
........
using channel ORA_AUX_DISK_6
starting media recovery
archived log for thread 1 with sequence 70837 is already on disk as file /archivelogs/SVRPRD/SVRPRD_1_70837_744780970.arc
archived log for thread 1 with sequence 70838 is already on disk as file /archivelogs/SVRPRD/SVRPRD_1_70838_744780970.arc
archived log file name=/archivelogs/SVRPRD/SVRPRD_1_70837_744780970.arc thread=1 sequence=70837
archived log file name=/archivelogs/SVRPRD/SVRPRD_1_70838_744780970.arc thread=1 sequence=70838
media recovery complete, elapsed time: 00:01:32
Finished recover at 16-JUL-14
Finished Duplicate Db at 16-JUL-14
++++++++++++++++++once Finished Duplicate ++++++++++++
Step 1 :
sql> create spfile from pfile;
sql> shut immediate;
SQL> startup nomount;
SQL> alter database mount;
Database altered.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
( Or )SQL> alter database recover managed standby database disconnect;
STEP : 2
Enable the log_archive_dest_2 on primary, which is to send the logs to standby server.
SQL> Alter system set log_archive_dest_state_2=enable scope=both;
STEP :3
heck the standby whether it is in SYNC with primary,
SQL> select name,db_unique_name,database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
SVRPRD SVRPRDS PHYSICAL STANDBY
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL>select sequence#,applied from v$archived_log order by sequence#;
SQL> select ads.dest_id,max(sequence#) "Current Sequence",max(log_sequence) "Last Archived" from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;
SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
++++++++789370.1++++++++
