Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

ReConfigure Data Guard using Cold Backup from Primary Database

Posted by Hendry chinnapparaj on December 20, 2010

Hendry Dasan

Monday, 20 December 2010

 

ReConfigure 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;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: