Step by Step to create physical standby database using RMAN

Dear blog readers and friends, In my busy schedule do not have time to write oracle tech..,  This week and next week planing to write something in oracle hints query's , data guard switch over etc.. 

Today I have done physical standby database using recovery manager and sharing my experience with you., Please let me know your feedback / comments

Step by Step to create physical standby database using RMAN

RMAN will take care the following procedure for creating standby database

a. Standby controlfile restore

b. Primary data files backup restore 

c. After restore standby controlfile, Rman recover the standby database by specify time or latest archive log generated. 

c. Finally, RMAN leave the standby database once it's mounted. If needed manually we can start MRP process. 

According to oracle documentation, RMAN cannot full automate for create standby database, there are some manual process require. 

Manual Process:

1. Starting database with out mount stage. 

2. Configuring INIT parameter in primary & standby database.  ( In windows env., manually need to create password file)

3. And we must have backup of database including current standby controlfile.

1. How to Configure NET Service (INIT) parameter in primary & standby database. 

Refer : http://babudba.blogspot.com/2008/03/data-guard.html

2. Once completed net service configuration in primary database, copy same init parameter for standby database & needed some necessary changes like FAL_SERVER, FAL_CLIENT, LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST2

3. In standby database manually create password file & start the database with out mount stage

4. As i said, In production need to take complete backup with current standby controlfile. I used the below command for taking complete backup. 

Execute the below script in Target database:-

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup incremental level 0 format 'e:\DBbackup\i001_%T_%u_s%s_p%p' database;

backup format 'e:\DBbackup\i001_arch_%T_%u_s%s_p%p' archivelog all;

backup current controlfile for standby format'e:\DBbackup\i001_CF_%T_%u_s%s_p%p' ; 

}

released channel: ORA_DISK_1

....

...

..

5. Once completed your full backup, EXIT your session, We need to connect AUXILIARY by using TARGET as well Catalog / NoCatalog

Ex:

C:\>rman target / nocatalog auxiliary sys/sys@dg_i001

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Aug 23 02:56:06 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: I001 (DBID=2720927431)

using target database control file instead of recovery catalog

connected to auxiliary database: I001 (DBID=2720927431, not open)

Script for Standby Database creation by using Duplicate RMAN::-

run

{

allocate auxiliary channel c1 device type disk;

allocate auxiliary channel c2 device type disk;

duplicate target database for standby

nofilenamecheck;

#dorecover;

}

Duplicate Standby RMAN Logs::-

RMAN> run

2> {

3> allocate auxiliary channel c1 device type disk;

4> allocate auxiliary channel c2 device type disk;

5> duplicate target database for standby

6> nofilenamecheck;

7> #dorecover;

8> }

allocated channel: c1

channel c1: sid=155 devtype=DISK

allocated channel: c2

channel c2: sid=158 devtype=DISK

Starting Duplicate Db at 23-AUG-10

contents of Memory Script:

{

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore

channel c1: restoring control file

channel c1: reading from backup piece E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_

P1

channel c1: restored backup piece 1

piece handle=E:\DBBACKUP\I001_CF_20100823_0ULM19RE_S30_P1 tag=TAG20100823T020325

channel c1: restore complete, elapsed time: 00:00:03

output filename=E:\ORADATA\I001\CONTROL01.CTL

output filename=E:\ORADATA\I001\CONTROL02.CTL

output filename=E:\ORADATA\I001\CONTROL03.CTL

Finished restore at 23-AUG-10

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "E:\ORADATA\I001\TEMP01.DBF";

   switch clone tempfile all;

   set newname for datafile  1 to

 "E:\ORADATA\I001\SYSTEM01.DBF";

   set newname for datafile  2 to

 "E:\ORADATA\I001\UNDOTBS01.DBF";

   set newname for datafile  3 to

 "E:\ORADATA\I001\SYSAUX01.DBF";

   set newname for datafile  4 to

 "E:\ORADATA\I001\USERS01.DBF";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to E:\ORADATA\I001\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-AUG-10

channel c1: starting datafile backupset restore

channel c1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to E:\ORADATA\I001\UNDOTBS01.DBF

restoring datafile 00003 to E:\ORADATA\I001\SYSAUX01.DBF

channel c1: reading from backup piece E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1

channel c2: starting datafile backupset restore

channel c2: specifying datafile(s) to restore from backup set

restoring datafile 00001 to E:\ORADATA\I001\SYSTEM01.DBF

restoring datafile 00004 to E:\ORADATA\I001\USERS01.DBF

channel c2: reading from backup piece E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1

channel c1: restored backup piece 1

piece handle=E:\DBBACKUP\I001_20100823_0OLM199H_S24_P1 tag=TAG20100823T015352

channel c1: restore complete, elapsed time: 00:01:05

channel c2: restored backup piece 1

piece handle=E:\DBBACKUP\I001_20100823_0NLM199G_S23_P1 tag=TAG20100823T015352

channel c2: restore complete, elapsed time: 00:01:40

Finished restore at 23-AUG-10

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=6 stamp=727710709 filename=E:\ORADATA\I001\SYSTEM01.DB

F

datafile 2 switched to datafile copy

input datafile copy recid=7 stamp=727710709 filename=E:\ORADATA\I001\UNDOTBS01.D

BF

datafile 3 switched to datafile copy

input datafile copy recid=8 stamp=727710709 filename=E:\ORADATA\I001\SYSAUX01.DB

F

datafile 4 switched to datafile copy

input datafile copy recid=9 stamp=727710710 filename=E:\ORADATA\I001\USERS01.DBF

Finished Duplicate Db at 23-AUG-10

released channel: c1

released channel: c2

RMAN>

Successfully completed standby database creating by using RMAN & both database are in Sync.,

Sync Checking:

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:21 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            D:\ArchiveLog\i001

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

SQL>

SQL> exit

C:\>sqlplus sys/sys@dg_i001 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 23 03:00:40 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\ArchiveLog\i001

Oldest online log sequence     21

Next log sequence to archive   23

Current log sequence           23

SQL>

Thanks., 

Monitoring Logical Standby Database

Monitoring Logical Standby Database:

DBA_LOGSTDBY_EVENTS:

It’s very interesting events that occurred during SQL Apply. By default, the view records most 100 events. If need needed we can changed using DBMS_LOGSTDBY_APPLY_SET()

DBA_LOSTDBY_LOG:

It’s providing dynamic information about archive logs being processed by SQL apply.

V$LOGSTDBY_STATE:

It’s provide fail-over characteristics about logical standby database

V$LOGSTDBY_PROCESS:

This view provides current state and various process involved with SQL apply. Including

· Identifying information (sid | serial# | spid)

· SQL Apply process: COORDINATOR, READER, BUILDER, PREPARER, ANALYZER, or APPLIER (type)

· Status of the process's current activity (status_code | status)

· Highest redo record processed by this process (high_scn)

V$LOGSTDBY_PROGRESS:

This view provide detailed information regarding process made by SQL apply.

V$LOGSTDBY_STATE:

This view provides a synopsis of the current state of SQL ApplY

V$LOGSTDBY_STATS:

This view provides SQL Apply statistics.

Data Guard

How to configure Logical Standby Database:

Database Version: Oracle 10.2.0.3.0

Operating System: Windows 2000

DB Mode: Archive log.

1. Pre-Request:

Before create logical standby database you must ensure primary database is properly configured or not.

A) Determine supported data types and objects

Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database.

Unsupported data types in logical standby database:

BFILE
ROWID, UROWID
User-defined types
Collections (including VARRAYS and nested tables)
XML type
Encrypted columns
Multimedia data types (including Spatial, Image, and Context)

Unsupported PL/SQL packages.

Oracle supplied pl/sql packaged that modify system metadata typically not supported by SQL apply.

Example:

DBMS_JAVA, DBMS_REGISTRY, DBMS_SPACE, DBMS_REFRESH, DBMS_REDEFINATION, DBMS_SCHEDULER and DBMS_AQ

Unsupported TABLES, VIEWS and SEQUENCES

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED;

Find out the following schema skipping in logical standby database:

SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';

B) Ensure table’s rows in the primary key can be uniquely identified.

select owner,table_name from dba_logstdby_not_unique where (owner,table_name) not in ( select distinct owner,table_name from dba_logstdby_unsupported ) and bad_column='Y';

Create Physical standby database

A. Enable Force logging

In Primary database we want to enable FORCE LOGGING after database creation.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

PS: This statement can take a considerable amount of time to complete because it wall all in unlogged direct write in I/O to finish.

B. Create Password file in Primary database

Create password in your primary database. If already exist no problem if no create password file (using orapwd). Every Data Guard configuration must be need password file.

C. Configure standby redo log

D. Primary database initialization parameters.

In Primary database, we should be defining some initialization

Parameters, this parameters that control redo transport services while the database is primary role.

For Example:

Here I am using the below diagram.

Database	DB_UNIQUE_NAME	Oracle Net Service Name
Primary	WASIM	wasim.samiindia
Physical standby	BABU	babu.samiindia

Primary Database Initialization parameter:

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string wasim

SQL> show parameter db_unique_name

NAME TYPE VALUE

------------------------------------ ----------- -------------db_unique_name string wasim

SQL> show parameter service_names

NAME TYPE VALUE

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

service_names string wasim.samiindia

SQL> alter system set log_archive_config = 'dg_config = (wasim,babu)' scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=

2 'LOCATION=E:\oracle\OraBak\Archive

3 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

4 DB_UNIQUE_NAME=wasim';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=

2 'SERVICE=babu LGWR ASYNC

3 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

4* DB_UNIQUE_NAME=babu';

System altered.

SQL> alter system set log_archive_dest_state_1=enable;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system set remote_login_passwordfile = exclusive scope=spfile;

System altered.

SQL>alter system set log_archive_max_processes=30 scope=spfile;

System altered.

Primary Initialization parameters for Standby database:

SQL> alter system set fal_server = babu ;

System altered.

SQL> alter system set fal_client = wasim ;

System altered.

SQL> alter system set db_file_name_convert =

'E:\oracle\product\10.2.0\oradata\wasim', 'E:\oracle\data\babu' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert =

‘E:\oracle\product\10.2.0\oradata\wasim\', ‘E:\oracle\data\babu\' scope=spfile;

System altered.

SQL> alter system set standby_file_management = auto scope=spfile;

System altered.

E. Physical Standby database configuration steps.

1. Take control file, primary database backups (only datafiles)

C:\>set oracle_sid=wasim

C:\>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:00:44 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

Sys@Wasim.Samiindia> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289508 bytes

Variable Size 130024156 bytes

Database Buffers 33554432 bytes

Redo Buffers 2904064 bytes

Database mounted.

SQL> alter database create standby controlfile as 'C:\babu.ctl';

Database altered.

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Prepare initialization parameters for standby database.

This step we need to create standby initialization parameters using primary database (init.ora).

babu.__db_cache_size=88080384

babu.__java_pool_size=4194304

babu.__large_pool_size=4194304

babu.__shared_pool_size=62914560

babu.__streams_pool_size=0

*.archive_lag_target=1800

*.audit_file_dest='d:\oracle\product\10.2.0\/admin/babu/adump'

*.background_dump_dest='d:\oracle\product\10.2.0\/admin/babu/bdump'

*.compatible='10.2.0.3.0'

*.control_files='e:\oracle\data\babu\control01.ctl'

*.core_dump_dest='d:\oracle\product\10.2.0\/admin/babu/cdump'

*.db_block_size=8192

*.db_domain='samiindia'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.db_name='wasim'

*.db_unique_name='babu'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=wasimXDB)'

*.fal_client='babu'

*.fal_server='wasim'

*.global_names=TRUE

*.job_queue_processes=10

*.log_archive_config='dg_config = (wasim,babu)'

*.log_archive_dest=''

*.log_archive_dest_1='LOCATION=E:\oracle\OraBak\Archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=babu'

*.log_archive_dest_2='SERVICE=wasim LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=wasim'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_max_processes=30

*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\wasim','E:\oracle\data\babu'

*.open_cursors=300

*.parallel_max_servers=20

*.parallel_min_servers=0

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='babu.samiindia'

*.sga_target=167772160

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='d:\oracle\product\10.2.0\/admin/babu/udump'

And need networking configuration both primary and standby database files system.

In TnsNames.ORA (Both Primary and Standby database)

BABU =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = babu.samiindia)

)

)

WASIM =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY MACHINE)(PORT =

1521)

)

)

(CONNECT_DATA =

(SERVICE_NAME = wasim.samiindia)

)

)

In SQLNET.ORA (Standby database)

Add the below line to broken connection detection on primary file system.

SQLNET.EXPIRE_TIME=2

3. Copy files from primary database to standby database.

In primary database, copy all the datafiles to standby machine. (Primary database already down)

Sys@Wasim.Samiindia> select file_name from dba_data_files;

FILE_NAME

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

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\USERS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSAUX01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\UNDOTBS01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\SYSTEM01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EDK01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\EXAMPLE01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\BABU01.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\RMAN_TS.DBF

E:\ORACLE\PRODUCT\10.2.0\ORADATA\WASIM\MIS01.DBF

4. Create standby database environment.

a) Create password file for standby database.

b) Create standby instance using oradim command.

               oradim -NEW -SID babu -INTPWD password -STARTMODE manual

c) I hope already created OFA.

5. Start the physical standby database.

C:\>set oracle_sid=babu

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Mar 11 04:28:02 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

            Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1289484 bytes

Variable Size 75498228 bytes

Database Buffers 83886080 bytes

Redo Buffers 7098368 bytes

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

This statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

6. Verification primary and standby database.

Logical Standby database Configuration

Before configuring logical standby database we need to configure physical standby database.

1. Pre-Request condition for creating logical standby database.

Before creating logical standby database we must ensure primary database properly configured of not. Determine supported data type, PL/SQL Packages and ensure tables rows in uniquely identified in primary database.

2. Enable Supplemental Logging.

Supplemental logging on the primary database generates additional information in the redo log. That info. Is then used during the redo application process in the standby database to make sure the correct rows are affected by the generated SQL.

We need to enable supplemental logging in primary and physical standby database.

In primary database:

System@Wasim.Samiindia> alter database add supplemental log data

2 (all) columns;

Database altered.

In Standby database:

SQL> alter database add supplemental log data

2 (all) columns;

Database altered.

3. Stop Redo apply in physical standby database.

You can run redo apply in physical standby database for any length of time It to a logical standby database. How ever before converting to logical standby database we need to stop redo apply on the physical standby database.

On Physical standby database.

SQL> alter database recover managed standby database cancel;

Database altered.

4. Prepare primary database to support a logical standby database.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3

'LOCATION=E:\oracle\orabak2\wasim\archive VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

DB_UNIQUE_NAME=WASIM';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3 = ENABLE;

System altered.

SQL> alter system set undo_retention=3600 scope=spfile;

System altered.

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

PL/SQL procedure successfully completed.

5. Logical standby database configuration

This steps need to configure database using with out NID command. Using

SQL> alter database recover to logical standby babu;

Database altered.

If you using SPIFLE just execute the above command. If you using PFILE edit your parameter file and try to re-open your database.

Alert log:

*** DBNEWID utility started ***

DBID will be changed from 2126122585 to new DBID of 48127288 for database WASIM

DBNAME will be changed from WASIM to new DBNAME of BABU

Starting datafile conversion

Setting recovery target incarnation to 1

Datafile conversion complete

Database name changed to BABU.

Modify parameter file and generate a new password file before restarting.

Database ID for database BABU changed to 48127288.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open with RESETLOGS option.

Succesfully changed database name and ID.

*** DBNEWID utility finished succesfully ***

Completed: alter database recover to logical standby babu

6. New Password file

In logical standby database, we must re-create password file because

Database name changed.

7. Configure initialization parameter in logical standby database.

This steps logical database configuration steps. Before starting this steps need to re-start (in Mount stage only) your logical standy database.

log_archive_dest_1='location=E:\oracle\Orabak\archive valid_for=(All_logfiles,all_roles) db_unique_name=babu'

log_archive_dest_2='SERVICE=WASIM LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=WASIM'

log_archive_dest_3='location=E:\Oracle\Orabak\archive2 valid_for=(standby_logfiles,standby_role) db_unique_name=babu'

log_archive_dest_state_1='ENABLE'

log_archive_dest_state_2='ENABLE'

log_archive_dest_state_3='ENABLE'

8. Open your standby database.

The new database is logically the same as your primary database, but it is transactionally inconsistent with the primary database, and thus incompatible for recovery operations.

To open the new logical standby database, you must open it with the RESETLOGS option by issuing the following statement:

               SQL> ALTER DATABASE OPEN RESETLOGS;

Because this is the first time the database is being opened, the database's global name is adjusted automatically to match the new DB_NAME initialization parameter.

Issue the following statement to begin applying redoes data to the logical standby database. For example:

               SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;