RECOVERY USING STANDBY DATABASE

                                      RECOVERY USING STANDBY DATABASE

In this post, I will demonstrate the use of standby database to restore a datafile which got

corrupted on primary.

In  the following scenario we have OP1 primary database and standby database is OP2 and catalog

database is CDB. TNS entries for op1 and op2 are there on the machne hosting recovery catalog

database cdb. Now the plan is to recover a lost datafile of primary database from standby

database.....

OVERVIEW:

- Create Recovery Catalog
- Register Primary database with catalog.
- Connect to catalog and primary. Configure persistent RMAN settings for primary .
- Connect to catalog and standby. Configure persistent RMAN settings for standby .
- Switch logs on primary so that tablespace is created on standby also
- Corrupt the datafile fo tbs_Sales on primary.
- Connect to standby database as target database using RMAN.
- Connect to primary database as auxiliary .
- Take backup of tbs_sales from standby database so that backup file is created on primary.
- connect to catalog and primary.
- Catalog the backup file created earlier
- Restore/recover tablespace

IMPLEMENTATION:

-- Create Recovery Catalog --

Log in to CDB catalog and do the following

SQL> grant connect ,resource to catown identified by c;

Grant succeeded.

SQL> grant recovery_catalog_owner to catown;

Grant succeeded.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb/system01.dbf
/u01/app/oracle/oradata/cdb/sysaux01.dbf
/u01/app/oracle/oradata/cdb/undotbs01.dbf
/u01/app/oracle/oradata/cdb/users01.dbf

SQL> create tablespace cattbs datafile '/u01/app/oracle/oradata/cdb/cat1a.dbf' size 300m;

Tablespace created.

SQL> alter user catown default tablespace cattbs quota unlimited on cattbs;

User altered.

[oracle@node1 ~]$ rman catalog catown/c

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:28:08 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog tablespace cattbs ;

recovery catalog created


#### Register OP1 with catalog CDB

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';

# default

RMAN> report schema for db_unique_name op1;

Report of database schema for database with db_unique_name OP1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    670      SYSTEM               YES     /u01/app/oracle/oradata/op1/system01.dbf
2    490      SYSAUX               NO      /u01/app/oracle/oradata/op1/sysaux01.dbf
3    45       UNDOTBS1             YES     /u01/app/oracle/oradata/op1/undotbs01.dbf
4    5        USERS                NO      /u01/app/oracle/oradata/op1/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/op1/temp01.dbf

RMAN> show all for db_unique_name op1;

RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';

# default

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

-- Configure persistent setting so that archivelogs are not deleted
-- until they have been applied to standby
RMAN> configure archivelog deletion policy to applied on all standby ;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> show all for db_unique_name op1;

RMAN configuration parameters for database with db_unique_name OP1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op1.f';

# default

RMAN> configure db_unique_name op1 connect identifier 'op1' ;

new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'op1' CONNECT IDENTIFIER  'op1';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

####REGISTER SERVICE OF STANDBY WITH RMAN
RMAN> configure db_unique_name op2 connect identifier 'op2' ;

new RMAN configuration parameters:
CONFIGURE DB_UNIQUE_NAME 'op2' CONNECT IDENTIFIER  'op2';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> list db_unique_name of database;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
161     OP1      588960404        PRIMARY          OP1               
161     OP1      588960404        STANDBY          OP2    

RMAN> report schema for db_unique_name op2;

Report of database schema for database with db_unique_name OP2

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    670      SYSTEM               YES   
2    490      SYSAUX               NO    
3    45       UNDOTBS1             YES   
4    5        USERS                NO  

####connect to standby database
[oracle@node1 ~]$ rman target sys@op2

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:42:40 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: OP1 (DBID=588960404, not open)

RMAN> connect catalog catown@cdb

recovery catalog database Password:
connected to recovery catalog database

RMAN> show all for db_unique_name op2;

RMAN configuration parameters for database with db_unique_name OP2 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'op1' CONNECT IDENTIFIER  'op1';
CONFIGURE DB_UNIQUE_NAME 'op2' CONNECT IDENTIFIER  'op2';
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_op2.f';

# default

-- Configure persistent settings for Standby database
-- Controlfile autobackup on
-- backup optimization
-- archivelogs are not deleted unless they are backed up once

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON ;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE BACKUP OPTIMIZATION ON ;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DEVICE TYPE DISK ;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

#### On primary create the tablespace and create a table in the tablespace

SQL> create tablespace tbs_sales datafile '/u01/app/oracle/oradata/op1/tbsales.dbf' size 50m;

Tablespace created.

SQL> create table scott.sales3 (id numbeR) tablespace tbs_sales;

Table created.

SQL> insert into scott.sales3 values(90);

1 row created.

SQL> commit;

Commit complete.

-- On primary, corrupt the datafile for tbs_sales tablespace with echo command but before this make

sure the log group should be switched so that tablespace is created on standby also...
SYS@OP1>alter system switch logfile;

[oracle@node1 ~]$ echo > /u01/app/oracle/oradata/op1/tbsales.dbf

-- Flush buffer cache and query newly created table. The query will fail as the
   datafile has been corrupted
SQL> alter system flush buffer_cache;

System altered.

SQL> select * from scott.sales3;
select * from scott.sales3
                    *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/op1/tbsales.dbf'


-- Now is the time to recover primary tablespace from standby
[oracle@node1 ~]$ rman target sys@op2

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:50:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: OP1 (DBID=588960404, not open)

-- Connect to primary database using auxiliary channel

RMAN> connect auxiliary sys@op1

auxiliary database Password:
connected to auxiliary database: OP1 (DBID=588960404)

-- Take image copy backup of tbs_sales tablespace from standby database
   on primary database

RMAN> backup as copy datafile 5 auxiliary format '/u01/app/sales_sby.dbf' ;

Starting backup at 29-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/op2/tbsales.dbf
output file name=/u01/app/sales_sby.dbf tag=TAG20120929T095124
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-12

####CONNECT TO PRIMARY

[oracle@node1 ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 10:07:06 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OP1 (DBID=588960404)

RMAN> connect catalog catown@cdb

recovery catalog database Password:
connected to recovery catalog database

-- Catalog the image copy backup with primary since the backup was taken without
   connecting to catalog

RMAN> catalog datafilecopy '/u01/app/sss5.dbf' ;

starting full resync of recovery catalog
full resync complete
cataloged datafile copy
datafile copy file name=/u01/app/sss5.dbf RECID=1 STAMP=795262055

RMAN>alter tablespace tbs_sales offline;

RMAN> run {
           restore datafile 5  ;
           recover datafile 5 ; }


####CHECK ON PRIMARY

SQL>select * from scott.sales5
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/op1/tbsales5.dbf'

SQL> alter tablespace tbs_sales5 online;
alter tablespace tbs_sales5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/app/sss5.dbf'

#### DO THE RECOVERY FROM RMAN on Primary
RMAN> recover tablespace tbs_sales5;

Starting recover at 29-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 29-SEP-12

#### COME BACK TO PRIMARY

SQL> alter tablespace tbs_sales5 online;

Tablespace altered.

SQL> select * from scott.sales5;

        ID
----------
        90
        90

#### ENJOY ####
---------------------------------------------------------------------
Related links:

HOME

http://koenigocm.blogspot.in/

11G DATA GUARD NEW FEATURES

http://koenigocm.blogspot.in/2012/03/11g-data-guard-new-features.html

DATA GUARD SETUP

http://koenigocm.blogspot.in/search/label/DATA%20GUARD%20SETUP

11g DATA GUARD ADMINISTRATION AND SETUP TIPS

Assume we have a production database i.e "PC01PRMY" which is keep up and running in 24x7 environment and their master DBA's has decided to setup physical standby i.e. "PC01SBY1" at remote location for disaster recover solutions as well as to make production available all the time while routine maintenance operations on production.

I have already posted on our blog, how to setup physical standby database for production but this time I would like to share few tips while setup and while administrating DR site.

Tips 1> Better to use Active database duplication to setup standby database in spite of using backup copy. As you know about Active database duplication where duplicate database is created using online copy of production data files and in fact it doesn't require any staging area as well and we also don't need to apply any redo logs. When you use active database duplication better to perform some log switching manually on primary side cause if there is no log switching happened so far on primary side, in this case your duplicate database failed and you have to restart it after cleanup everything.

Tips 2> Configure Oracle Net properly between Primary and Standby because what I found students normally does mistakes there and later on we face lot of difficulties to resolve them, especially when your database managed by Oracle restart where we have separate grid and database home and default listener is running from grid home.

Oracle Net configuration includes three main steps-

Configure Listener (Keep in mind Listener home and port number)

Register database service with Listener (Use correct service name and Oracle database home)

Configure TNS alias for database service (Use correct hostname, Listener port number and database service name)

Tips 3> Though LOG_ARCHIVE_DEST_STATE_N parameter default value is ENABLE but still I recommend you to set this parameter manually.

Tips 4> Better to setup Standby Redo log groups in advance on primary side. (You know better why?)

Tips 5> Better to configure LOCAL_LISTENER parameter manually and set the address of those Listener which we are using for Oracle Net connectivity so that PMON will register a service with the name "db_unique_name_DGB.db_domain" automatically at run time which is use by broker internally for inter-communication.

Tips 6> If you are managing Data Guard using Broker then please consider followings points-

=> when you remove database or entire broker configuration using REMOVE DATABASE or REMOVE CONFIGURATION better to use PRESERVE DESTINATIONS options else when you remove the database from configuration broker will reset their corresponding parameters like LOG_ARCHIVE_DEST_N, LOG_ARCHIVE_CONFIG etc.

=> Use SPFILE if you are managing Data Guard using broker and modify archive dest attributes through broker only else there would be inconsistency in broker configuration and spfile settings if we modify using ALTER SYSTEM command.

=> Identify inconsistent attribute settings between broker and spfile using SHOW DATABASE <DB_NAME> INCONSISTENTPROPERTIES. If you want to remove inconsistency with broker values then just disable and enable the database using broker and if you want to remove inconsistency with spfile values then update the values using EDIT DATABASE broker command.

=> Sometimes we need to recreate the broker configuration but even after recreating broker reads previous broker configuration from memory. To overcome from this problem you have to change broker configuration files location just to enforce broker to read new configuration settings.

--------------------------------------------------------------------------------------------------------

Related links:

HOME

http://koenigocm.blogspot.in/

11G DATA GUARD NEW FEATURES

http://koenigocm.blogspot.in/2012/03/11g-data-guard-new-features.html

DATA GUARD SETUP

http://koenigocm.blogspot.in/search/label/DATA%20GUARD%20SETUP

11g Data Guard Configuration using Active Dataguard on RHEL/OEL- 5
http://oracleinaction.blogspot.in/2012/11/DATAGUARD.html

RECOVERY USING STANDBY DATABASE

http://koenigocm.blogspot.in/search/label/RMAN

11G DATA GUARD NEW FEATURES

Real-Time Query. In Oracle Database 8i it was possible to bring a standby database into READ ONLY mode so that it could be used for reporting purposes, but it was necessary to switch it back to standby mode for reapplication of pending change vectors from the archived redo logs transported from the primary database. Oracle Database 11g now lets me run queries in real time against any physical standby database without any disturbance to receipt and application of redo.



Snapshot Standby Databases. Oracle Database 11g offers another intriguing prospect: the ability to open a physicalstandby database for testing or QA purposes while simultaneously collecting production changes for immediate reapplication in case disaster recovery is required. This snapshot standbydatabase still accepts redo information from its primary, but unlike the first two standby types, it does not apply the redo to the database immediately; instead, the redo is only applied when the snapshot standby database is reconverted back into a physical standby. This offers significant leverage because in theory, a QA environment that requires specifically dedicated, identical hardware is no longer required.



Improved Handling of Role Transitions. The addition of standby snapshot databases brings the total of different Data Guard standby database types to three (physical, logical, and snapshot), so Oracle Database 11g also makes it much easier to transition between these different roles via either Data Guard Broker (DGB) command line execution or Enterprise Manager Grid Control. Role transitions are simpler to execute and complete more quickly than in earlier releases.



Improvements to Rolling Database Upgrades.Oracle Database 11g supports rolling database upgrades to be performed against a physical standby database by first transforming it into a logical standby database with a few simple commands before the upgrade begins. Once the upgrade is done, the logical standby database is reverted to its original physical standby state. Oracle 11gleverages this capability as well as the improved speed and simplicity of role transitions to perform system and database patching in a fraction of the time it would’ve taken in earlier releases, and it’s especially powerful in a Real Application Clusters (RAC) database environment.



SQL Apply Enhancements. Logical standby databases are obviously central to these new role transition features, but they use SQL Apply technology to apply change vectors to data. It therefore makes sense that Oracle Database 11g provides significant improvements to this crucial part of Data Guard architecture. SQL Apply now supports parallel DDL execution, Fine-Grained Auditing (FGA), Virtual Private Database (VPD), and Transparent Data Encryption (TDE), as well as simpler real-time SQL Apply reconfiguration and tuning.



Enhanced Redo Logs Transport. Physical standby databases have always used archived redo logs for application of change vectors to data. Oracle Database 11g augments redo transport with some long-overdue features, including compressionand SSL authentication of redo logs while they’re being transmitted between the primary and standby sites.



Heterogeneous DataGuard., Oracle Database 11g allows the primary and standby databases to use different operating systems (for example, Windows 2003 Server and Oracle Enterprise Linux) as long as both operating systems support the same endianness.



Fast Start Failover Improvements. Oracle introduced this feature set in Release 10gR2, but it’s been enhanced significantly in Oracle 11g to permit much finer-grained control over the conditions under which a fast-start failover would be initiated.



“Live Cloning” of Standby Databases. Finally, Oracle 11g has made it extremely simple to set up a standby database environment because Recovery Manager(RMAN) now supports the ability to clone the existing primary database directly to the intended standby database site over the network via the DUPLICATE DATABASE command set while the target database is active. This means it’s no longer necessary to first generate, then transmit, and finally restore and recover RMAN backups of the primary database on the standby site via tedious (and possibly error-prone!) manual methods; instead, RMAN automatically generates a conversion script in memory on the primary site and uses that script to manage the cloning operation on the standby site with virtually no DBA intervention required.
------------------------------------------------------------------------------------------------------------

Related links:

HOME

http://koenigocm.blogspot.in/

11g DATA GUARD ADMINISTRATION AND SETUP TIPS

http://koenigocm.blogspot.in/search/label/11g%20DATA%20GUARD%20ADMINISTRATION%20AND%20SETUP%20TIPS

DATA GUARD SETUP

http://koenigocm.blogspot.in/search/label/DATA%20GUARD%20SETUP

11g Data Guard Configuration using Active Dataguard on RHEL/OEL- 5
http://oracleinaction.blogspot.in/2012/11/DATAGUARD.html

 

RECOVERY USING STANDBY DATABASE
http://koenigocm.blogspot.in/search/label/RMAN

DATA GUARD SETUP

DATA GUARD SETUP

Follow the following steps to configure data guard with broker configuration

Assumption: - Let’s suppose we have two machine machine1 & machine2 with the hostname machine1.oracle.com & machine2.oracle.com respectively. For the beginning machine1 will be considered as primary machine and machine2 will be considered as standby machine.

On machine1 we have a primary database 'dg01' and we will configure a physical standby database dg02 on machine2.

Configuration on primary site:-

Step 1) Configure the database in archive log mode and enable the force logging.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE FORCE LOGGING;

ALTER DATABASE OPEN;

Step 2) Configure standby redo log files to enable fast failover and real time apply. (Assume we have three redo log group)

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u01/app/oracle/oradata/dg01/sredo04.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u01/app/oracle/oradata/dg01/sredo05.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u01/app/oracle/oradata/dg01/sredo06.log’ size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u01/app/oracle/oradata/dg01/sredo07.log’ size 50m;

Note: - Number of standby redo log group must be more than number of redo log group and size of the standby logfile must be same as the size of redo log file.

Steps 3) CREATE PFILE FROM SPFILE.

Now configure following parameters for primary database:-

LOG_ARCGIVE_CONFIG='DG_CONFIG=(dg01,dg02)'

DB_UNIQUE_NAME=dg01

SERVICE_NAMES=dg01

DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg02’,’/u01/app/oracle/oradata/dg01’

LOG_ARCHIVE_DEST_1=’location=/u01/app/oracle/oradata/dg01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG01’

LOG_ARCHIVE_DEST_2=’service=dg02 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG02’

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg01

FAL_SERVER=dg02

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 4) Take the entire database backup on pre configured staging directory.

mkdir /home/oracle/rman (Staging directory for rman backup)

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ DATABASE;

RMAN> BACKUP FORMAT ‘/home/oracle/rman/%U’ CURRENT CONTROLFILE FOR STANDBY;

RMAN> SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”;

Step 5) Copy the following items from machine1 to machine2.

PASSWORD FILE

PARAMETER FILE

RMAN BACKUP TAKEN ABOVE

Note: - RMAN backup must be copy at the same directory on machine2 i.e. /home/oracle/rman.

Step 6) Configure net service for dg01 (primary database) as well as for dg02 (standby database).

DG01 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = machine1.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg01)

(INSTANCE_NAME = dg01)

)

)

DG02 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = machine2.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg02)

(INSTANCE_NAME = dg02)

)

)

Configuration on standby site:-

Step 7) Rename the password file and parameter file to initdg02.ora and orapwdg02 respectively. Now do the following parameter changes for standby database.

LOG_ARCGIVE_CONFIG='DG_CONFIG=(dg01,dg02)'DB_UNIQUE_NAME=dg02

SERVICE_NAMES=dg02

DB_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg01’,’/u01/app/oracle/oradata/dg02’

LOG_FILE_NAME_CONVERT=’/u01/app/oracle/oradata/dg01’,’/u01/app/oracle/oradata/dg02’

LOG_ARCHIVE_DEST_1=’location=/u01/app/oracle/oradata/dg01/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DG02’

LOG_ARCHIVE_DEST_2=’service=dg01 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=DG01’

LOG_ARCHIVE_DEST_STATE_1=enable

LOG_ARCHIVE_DEST_STATE_2=enable

STANDBY_FILE_MANAGEMENT=auto

FAL_CLIENT=dg02

FAL_SERVER=dg01

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

Step 8) Create the required directory for standby database

cd /u01/app/oracle/admin

mkdir dg02

cd dg02

mkdir bdump udump adump cdump create pfile scripts

mkdir -p /u01/app/oracle/oradata/dg02/archive

Step 9) Configure net service for dg01 (primary) as well as dg02 (standby) database.

DG01 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = machine1.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg01)

(INSTANCE_NAME = dg01)

)

)

DG02 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = machine2.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dg02)

(INSTANCE_NAME = dg02)

)

)

Step 10) Start dg02 at nomount stage.

export ORACLE_SID=dg02

CREATE SPFILE FROM PFILE;

STARTUP NOMOUNT

EXIT

Step 11) Now using RMAN duplicate command create standby database.

RMAN TARGET SYS/ORACLE@DG01

RMAN> CONNECT AUXILIARY SYS/ORACLE@DG02

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;

Note: execute above commands on machine1.

Above are the simple steps to configure physical standby database for your primary database.

As you know about data guard broker which makes data guard management ease and centralized. Following steps will tel you how to configure data guard broker using command line utility DGMGRL.

Step 1) Register a service db_name.db_domain_DGMGRL statically with the listener for primary as well as for standby database on primary as well as standby server.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dg01_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = dg01)
)
(SID_DESC =
(GLOBAL_DBNAME = dg02_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/asm)
(SID_NAME = dg02)
)
)

Note:- Reload the listener after modifying the setting. (LSNRCTL RELOAD)

Step 2) Configure a parameter DG_BROKER_START=TRUE on primary as well as on standby side.

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Step 3) Invoke DGMGRL and create the configuration

$ dgmgrl

DGMGRL> connect sys/oracle@dg01

DGMGRL> CREATE CONFIGURATION DGCONFIG1 AS PRIMARY DATABASE IS DG01 CONNECT IDENTIFIER IS 'DG01';

DGMGRL> ADD DATABASE DG02 AS CONNECT IDENTIFIER IS 'DG02' MAINTAINED AS PHYSICAL;

Step 4) Enable the configuration and check their status

DGMGRL> SHOW CONFIGURATION

DGMGRL> ENABLE CONFIGURATION

DGMGRL> SHOW CONFIGURATION

----------------------------------------------------------------------------------------------------------

Related links:
HOME

http://koenigocm.blogspot.in/

11g DATA GUARD ADMINISTRATION AND SETUP TIPS

http://koenigocm.blogspot.in/search/label/11g%20DATA%20GUARD%20ADMINISTRATION%20AND%20SETUP%20TIPS

11G DATA GUARD NEW FEATURES

http://koenigocm.blogspot.in/2012/03/11g-data-guard-new-features.html