Data Guard using Cold Backup from Primary Database
The current status is
Primary001 – primary server
Standby001 – Standby server, but no database running.
Primary Server Details
Server name: Primary001
LOG_MODE FORCE_LOGGING
———— ————-
ARCHIVELOG YES
1 row selected.
Time End: 16/12/2010 5:59:54 PM
Elapsed Time for Script Execution: 1 sec
SELECT DBID,
NAME,
DB_UNIQUE_NAME,
LOG_MODE,
OPEN_MODE,
PROTECTION_MODE,
PROTECTION_LEVEL,
DATABASE_ROLE,
FLASHBACK_ON,
CURRENT_SCN
FROM V$DATABASE;
Time Start: 16/12/2010 5:15:40 PM
DBID NAME DB_UNIQUE_NAME LOG_MODE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
———- ——— —————————— ———— ———- ——————– ——————– —————-
FLASHBACK_ON CURRENT_SCN
—————— ———–
224883408 REPLICA REPLICA_fc ARCHIVELOG READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY
YES 2.6969E+10
1 row selected.
Time End: 16/12/2010 5:15:41 PM
Elapsed Time for Script Execution: 1 sec
SELECT COUNT(*) TOTAL_DATAFILES FROM V$DATAFILE;
SELECT COUNT(*) TOTAL_LOGFILES_AND_STBY_FILES FROM V$LOGFILE;
SELECT COUNT(*) TOTAL_CONTROLFILES FROM V$CONTROLFILE;
Time Start: 16/12/2010 5:16:24 PM
TOTAL_DATAFILES
—————
625
1 row selected.
TOTAL_LOGFILES_AND_STBY_FILES
—————————–
13
1 row selected.
TOTAL_CONTROLFILES
——————
3
1 row selected.
Time End: 16/12/2010 5:16:25 PM
Elapsed Time for Script Execution: 1 sec
SELECT SUM(BYTES)/(1024*1024*1024) DF_SIZE_IN_GB FROM V$DATAFILE;
SELECT SUM(BYTES)/(1024*1024*1024) LOGFILE_SIZE_IN_GB FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP#;
SELECT SUM(BYTES)/(1024*1024*1024) TEMPFILE_IN_GB FROM V$TEMPFILE WHERE STATUS=’ONLINE’;
Time Start: 16/12/2010 5:17:11 PM
DF_SIZE_IN_GB
————-
1995.90629
1 row selected.
LOGFILE_SIZE_IN_GB
——————
16
1 row selected.
TEMPFILE_IN_GB
————–
13.8212891
1 row selected.
Time End: 16/12/2010 5:17:19 PM
Elapsed Time for Script Execution: 8 secs
SELECT SUM(BYTES) /1024/1024/1024 “Read only in GB” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;
Time Start: 16/12/2010 5:17:55 PM
Read only in GB
—————
985.458984
1 row selected.
Time End: 16/12/2010 5:17:56 PM
Elapsed Time for Script Execution: 1 sec
– SHOW THE DATAFILES WHICH ARE OFFLINE
select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,'SYSTEM’);
Time Start: 16/12/2010 5:19:05 PM
no rows selected.
Time End: 16/12/2010 5:19:06 PM
Elapsed Time for Script Execution: 687 msecs
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/oracle/product/10.2/db_1/
dbs/spfileREPLICA.ora
Select * from v$tempfile;
Time Start: 16/12/2010 5:20:29 PM
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
———- —————- ————- ———- ———- ——- ———- ———- ———- ———— ———-
NAME
——————————————————————————————————————————————–
1 78931268 06-AUG-08 3 1 ONLINE READ WRITE 1.4840E+10 1811584 1.4840E+10 8192
/u02/oradata/REPLICA/temp01.dbf
1 row selected.
Time End: 16/12/2010 5:20:30 PM
Elapsed Time for Script Execution: 641 msecs
Select * from dba_temp_files;
Select count(*) from dba_data_files;
SELECT NAME “Read only” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;
Time Start: 16/12/2010 5:22:03 PM
Read only
——————————————————————————————————————————————–
/u04/oradata/REPLICA/CB001_01.DBF
/u04/oradata/REPLICA/CB002_01.DBF
/u04/oradata/REPLICA/CB003_01.DBF
/u04/oradata/REPLICA/CB004_01.DBF
/u04/oradata/REPLICA/CB005_01.DBF
/u04/oradata/REPLICA/CB006_01.DBF
/u04/oradata/REPLICA/CB007_01.DBF
/u04/oradata/REPLICA/CB008_01.DBF
/u04/oradata/REPLICA/CB009_01.DBF
/u04/oradata/REPLICA/CB010_01.DBF
/u04/oradata/REPLICA/CB011_01.DBF
/u04/oradata/REPLICA/CB012_01.DBF
/u04/oradata/REPLICA/CB013_01.DBF
/u04/oradata/REPLICA/CB014_01.DBF
/u04/oradata/REPLICA/CB015_01.DBF
/u04/oradata/REPLICA/CB016_01.DBF
/u04/oradata/REPLICA/CB017_01.DBF
/u04/oradata/REPLICA/CB018_01.DBF
/u04/oradata/REPLICA/CB019_01.DBF
/u04/oradata/REPLICA/CB020_01.DBF
/u04/oradata/REPLICA/CB021_01.DBF
/u04/oradata/REPLICA/CB022_01.DBF
/u04/oradata/REPLICA/CB023_01.DBF
/u04/oradata/REPLICA/CB024_01.DBF
/u04/oradata/REPLICA/CB025_01.DBF
/u04/oradata/REPLICA/CB026_01.DBF
/u04/oradata/REPLICA/CB027_01.DBF
/u04/oradata/REPLICA/CB028_01.DBF
/u04/oradata/REPLICA/CB029_01.DBF
/u04/oradata/REPLICA/CB030_01.DBF
/u04/oradata/REPLICA/CB031_01.DBF
/u04/oradata/REPLICA/CB032_01.DBF
/u04/oradata/REPLICA/CB033_01.DBF
/u04/oradata/REPLICA/CB034_01.DBF
/u04/oradata/REPLICA/CB035_01.DBF
/u04/oradata/REPLICA/CB036_01.DBF
/u04/oradata/REPLICA/CB037_01.DBF
/u04/oradata/REPLICA/CB038_01.DBF
/u04/oradata/REPLICA/CB039_01.DBF
/u04/oradata/REPLICA/CB040_01.DBF
/u04/oradata/REPLICA/CB041_01.DBF
/u04/oradata/REPLICA/CB042_01.DBF
/u04/oradata/REPLICA/CB043_01.DBF
/u04/oradata/REPLICA/CB044_01.DBF
/u04/oradata/REPLICA/CB045_01.DBF
/u04/oradata/REPLICA/CB046_01.DBF
/u04/oradata/REPLICA/CB047_01.DBF
/u04/oradata/REPLICA/CB048_01.DBF
/u04/oradata/REPLICA/CB049_01.DBF
/u04/oradata/REPLICA/CB050_01.DBF
/u04/oradata/REPLICA/CB051_01.DBF
/u04/oradata/REPLICA/CB052_01.DBF
/u04/oradata/REPLICA/CB053_01.DBF
/u04/oradata/REPLICA/CB054_01.DBF
/u04/oradata/REPLICA/CB055_01.DBF
/u04/oradata/REPLICA/CB056_01.DBF
/u04/oradata/REPLICA/CB057_01.DBF
/u04/oradata/REPLICA/CB058_01.DBF
/u04/oradata/REPLICA/CB059_01.DBF
/u04/oradata/REPLICA/CB060_01.DBF
/u04/oradata/REPLICA/CB061_01.DBF
/u04/oradata/REPLICA/CB062_01.DBF
/u04/oradata/REPLICA/CB063_01.DBF
/u04/oradata/REPLICA/CB064_01.DBF
/u04/oradata/REPLICA/CB065_01.DBF
/u04/oradata/REPLICA/CB066_01.DBF
/u04/oradata/REPLICA/CB067_01.DBF
/u04/oradata/REPLICA/CB068_01.DBF
/u04/oradata/REPLICA/CB069_01.DBF
/u04/oradata/REPLICA/CB070_01.DBF
70 rows selected.
Time End: 16/12/2010 5:22:04 PM
Elapsed Time for Script Execution: 797 msecs
Standby Server Details
Server name: standby001
OPERARTIONS
Shutdown the PRIMARY Database, Listeners and Standby
Login to primary001
SQL> shutdown immediate
oracle@primary001 scptest>ps -ef|grep tns
oracle 831554 1 0 Mar 12 – 167:37 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_fc_dg -inherit
oracle 1122458 1 0 Jul 22 – 64:15 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_public –inherit
lsnrctl stop REPLICA_public
lsnrctl stop REPLICA_fc_dg
Login to standby server – standby001 and stop the Listener is any running
oracle@standby001 oracle>ps -ef|grep tns
oracle 659472 880772 0 10:15:42 pts/1 0:00 grep tns
oracle 950348 1 0 Jul 16 - 9:53 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_js_dg –inherit
lsnrctl stop REPLICA_js_dg
Copy / scp the datafiles to DR Server – standby001
Do a cleanup on the DR Server if required – check the relevant directories
scp -pr /u04/oradata/REPLICA/* oracle@standby001:/u04/oradata/REPLICA
scp -pr /u02/oradata/REPLICA/* oracle@standby001:/u02/oradata/REPLICA
pwd
lpwd
cd
lcd
Note:- scp by default is binary copy, so no worries
if required you can use TOAD to compare the files under both server directories after capturing them on the excel sheet
Create STANDBY Controlfile in Primary Server / Database
Login to primary001 Primary Server
SQL> startup
SQL> Alter database create standby controfile as ‘/u01/oracle/control01.ctl’;
Do few Log switches on Primary Server
SQL> alter system switch logfile;
/
/
SQL>
Copy / sftp this file to /u02/oradata/REPLICA
sftp oracle@standby001
pwd: *****
cd /u02/oradata/REPLICA
lcd /u01/oracle
Also Copy / sftp the Archive logs to DR Server – standby001
Login to standby001 – DR Server and create the Archivelog Directory
cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog
oracle@standby001 archivelog>pwd
/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog
mkdir 2010_12_19
sftp oracle@standby001
pwd:****
cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19
lcd /u03/oradata/flash_recovery_area/REPLICA_FC/archivelog/2010_12_19
sftp> mput *
oracle@standby001 archivelog>pwd
/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog
oracle@standby001 archivelog>ls -lrt
total 8
drwxr-xr-x 2 oracle dba 2048 Dec 19 22:10 2010_12_19
drwxr-x— 2 oracle dba 2048 Dec 20 12:55 2010_12_20
oracle@standby001 archivelog>cd 2010_12_19
oracle@standby001 2010_12_19>pwd
/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19
oracle@standby001 2010_12_19>ls -lrt
total 3212664
-r–r—– 1 oracle dba 167424 Dec 19 15:31 o1_mf_1_1802_1E4rhl3O2_.arc
-r–r—– 1 oracle dba 45288960 Dec 19 15:31 o1_mf_1_1803_1E4s16McD_.arc
-r–r—– 1 oracle dba 2191360 Dec 19 15:31 o1_mf_1_1805_1E4sC2jT2_.arc
-r–r—– 1 oracle dba 76392960 Dec 19 15:31 o1_mf_1_1804_1E4sBwGUH_.arc
-r–r—– 1 oracle dba 45288960 Dec 19 15:38 o1_mf_1_1803_1E4sbI1M8_.arc
-r–r—– 1 oracle dba 2191360 Dec 19 15:38 o1_mf_1_1805_1E4sbkaFE_.arc
-r–r—– 1 oracle dba 76392960 Dec 19 15:38 o1_mf_1_1804_1E4sbT251_.arc
Startup the Standby Database in DR Server
Login to standby001 and recheck the initREPLICA.ora parameters if needed for stby parameters
Ensure the controlfile is pointing to the just copied standby controlfile under “/u02/oradata/REPLICA” directory.
Sqlplus / as sysdba
Startup nomount pfile=initREPLICA.ora
Alter database mount standby database;
Create Standby Log files and temp file
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
‘/u02/oradata/REPLICA/temp01.dbf’ SIZE 14153M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
TABLESPACE GROUP ”
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
alter system set standby_file_management=manual;
.alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_09.log’ SIZE 2G reuse;
alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_10.log’ SIZE 2G reuse;
alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_11.log’ SIZE 2G reuse;
alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_12.log’ SIZE 2G reuse;
alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_13.log’ SIZE 2G reuse;
alter system set standby_file_management=auto;
Enable Block Change Tracking in standby server
SQL> alter database enable block change tracking using file ‘/u01/oracle/product/10.2.0/db_1/dbs/REPLICA_bct.dbf’ reuse;
Stop the job_queue_processes STANDBY database by setting them to 0
SQL> alter system set job_queue_processes=0 scope=memory;
SQL> alter database recover managed standby database disconnect from session
IF needed , do check in Primary Server / database
- CHECK THE STATUS IF VALID FOR STBY
select dest_id, status from v$archive_dest;
show parameter dest_state_2
– TO disable log shipping to stby site
alter system set log_archive_dest_state_2=defer scope=memory;
– TO disable log shipping to stby site
alter system set log_archive_dest_state_2=enable scope=memory;
Check the open_mode, protection_mode, database_role, Flashback Status in the STANDBY Server
SQL> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE FLASHBACK_ON
——— ———- ——————– —————- ——————
REPLICA MOUNTED MAXIMUM AVAILABILITY PHYSICAL STANDBY YES
SQL> select * from v$archive_gap;
Check the open_mode, protection_mode, database_role, Flashback Status in the PRIMARY Server
SQL> select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,FLASHBACK_ON from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE FLASHBACK_ON
——— ———- ——————– —————- ——————
REPLICA READ WRITE MAXIMUM AVAILABILITY PRIMARY YES
Also run the ControlM Job and verify if the standby database is in sync with Primary
Run the Script from the PRIMARY Database
oracle@primary001 logs>cat REPLICA.stby_check.20101220_1134.log
SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 11:34:58 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK
INST_NAME HOST_NAME PROTECTION_MODE SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED LOG_GAP
——— ———- ——————– ———————- ———— ———– ————– ——-
REPLICA primary001 MAXIMUM AVAILABILITY OK 1855 1855 20/12 11:30:57 0
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Making initREPLICA.ora changes permanent in Standby
Alter system job_queue_processes=0 scope=spfile
….
Create spfile from pfile;
Shutdown immediate
Startup nomount
Alter database mount standby database
alter database recover managed standby database disconnect from session
TEST THE STANDBY IN READ ONLY MODE
In Primary – primary001
Login as db user
Bb15908
Create table test as select * from dba_users;
Alter system switch logfile;
/
Login to standby server – standby001
Note:- if you have enable DB Auditing , then the following is required to open the database in read only mode.
SQL> Recover managed standby database cancel;
Media recovery complete.
SQL> alter database open readonly;
alter database open readonly
*
ERROR at line 1:
ORA-02288: invalid OPEN mode
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string DB
SQL> alter system set audit_trail=OS scope=spfile;
System altered.
Shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3321888768 bytes
Fixed Size 2087680 bytes
Variable Size 1828717824 bytes
Database Buffers 1476395008 bytes
Redo Buffers 14688256 bytes
SQL> alter database mount standby database;
Database altered.
SQL> show parameter audit_trail
NAME TYPE VALUE
———————————— ———– ——————————
audit_trail string OS
SQL> alter database open read only;
SQL> select * from bb15908.test;
Make sure you set back the audit_trail=DB after this
Shutdown immediate
Startup nomount
Alter database mount standby database
SQL> alter system set audit_trail=DB scope=spfile;
System altered.
Shutdown immediate
Startup nomount
Alter database mount standby database;
alter database recover managed standby database disconnect from session;
