CREATE STANDBY DATABASE USING RMAN

[oracle@linux1 database]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Mar 2 19:10:41 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

ORCL      orcl                           PRIMARY          READ WRITE

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR

NOARCHIVELOG NO

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           3

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/flash_recovery _area

db_recovery_file_dest_size           big integer 2G

change archivelog_dest:-

SQL> !mkdir -p /u01/app/oracle/oradata/chicago/arch

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/chicago/arch' scope=both;

System altered.

enable archivelog with new destination:

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     1

Current log sequence           3

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

SQL> !ls -al /u01/app/oracle/oradata/chicago/arch

total 23704

drwxr-xr-x 2 oracle oinstall     4096 Mar  2 19:32 .

drwxr-xr-x 3 oracle oinstall     4096 Mar  2 19:22 ..

-rw-r----- 1 oracle oinstall 24233984 Mar  2 19:32 1_3_809031038.dbf

SQL> select force_logging from v$database;

FOR

---

YES

SQL> select max(bytes),count(1) from v$log;

MAX(BYTES)   COUNT(1)

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

  52428800          3

adding standby logfiles at same location as of archivelog:

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog01.log' size 52M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog02.log' size 52M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog03.log' size 52M;

Database altered.

SQL> select group#,type,member from v$logfile where type='STANDBY';

    GROUP# TYPE

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

MEMBER

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

         4 STANDBY

/u01/app/oracle/oradata/chicago/sbylog01.log

         5 STANDBY

/u01/app/oracle/oradata/chicago/sbylog02.log

         6 STANDBY

/u01/app/oracle/oradata/chicago/sbylog03.log

To check the status of standby logfiles created

SQL> select group#,dbid,thread#,sequence#,status from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#

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

STATUS

----------

         4 UNASSIGNED                                        0          0

UNASSIGNED

         5 UNASSIGNED                                        0          0

UNASSIGNED

         6 UNASSIGNED                                        0          0

UNASSIGNED

setting DG_CONFIG:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

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

log_archive_config                   string

SQL> alter system set log_archive_config='DG_CONFIG=(chicago,boston)' scope=spfile;

System altered.

setting log archive dest for primary database for all logfiles and all roles:

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/chicago/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=spfile;

System altered.

setting log archive transport from primary destination to standby database visa service name called boston :

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=spfile;

System altered.

enable the archive dests

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;

System altered.

setting db_convert  to change the location stdby when a datafile is added on primary location use this if you are using different path from primary:

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;

addding below will add redologfiles on standby with changed location:

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;

System altered.

making the file management auto this will add datafiles added on primary to standby:

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

System altered.

SQL> alter system set FAL_SERVER=boston scope=spfile;

System altered.

SQL> alter system set FAL_CLIENT=chicago scope=spfile;

System altered.

restart the db to take these parameters effect:

add listener config on standby as primary already has its settings:

[oracle@linux2 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

(SID_LIST =

 (SID_DESC =

  (GLOBAL_DBNAME = boston)

   (ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)

   (SID_NAME = boston)

  )

 )

tnsnames.ora on both should have values like:

# tnsnames.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

BOSTON =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = boston)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

create a initboston.ora file with db_name=primarydb (orcl)

create directory structure on standby, same as primary:

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/admin/boston/adump

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston/arch

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area/boston

check the database connectivity between both the databases through the sqlplus:

SQL> conn sys/oracle@chicago as sysdba

SQL> conn sys/oracle@boston as sysdba

[oracle@linux2 ~]$ rman target sys/oracle@chicago auxiliary sys/oracle@boston

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Mar 3 10:57:18 2013

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

connected to target database: ORCL (DBID=1336811768)

connected to auxiliary database (not started)

RMAN> run {

 allocate channel p1 type disk;

 allocate channel p2 type disk;

 allocate auxiliary channel s1 type disk;

 duplicate target database for standby from active database

 spfile

 parameter_value_convert'chicago','boston'

set db_unique_name='boston'

 set db_file_name_convert='/orcl/','/boston/'

 set log_file_name_convert='/orcl/','/boston/'

 set control_files='/u01/app/oracle/oradata/boston/control01.ctl','/u01/app/oracle/oradata/boston/control02.ctl'

 set log_archive_max_processes='5'

 set fal_client='boston'

 set fal_server='chicago'

 set standby_file_management='AUTO'

 set log_archive_config='dg_config=(chicago,boston)'

 set log_archive_dest_1='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'

 ;

 }

created physical db

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  DATABASE_ROLE

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

ORCL      boston                         MOUNTED    PHYSICAL STANDBY

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     6

Next log sequence to archive   0

Current log sequence           8

SQL> !ps -ef|grep mrp

oracle    3973  3965  0 12:05 pts/1    00:00:00 /bin/bash -c ps -ef|grep mrp

[oracle@linux2 trace]$ pwd

/u01/app/oracle/diag/rdbms/boston/boston/t

[oracle@linux2 trace]$ vi alert_boston.log

[oracle@linux2 trace]$ tail -f alert_boston.log

start applying the archive logs :

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

open database in read only mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL> alter database open read only;

start applying the logs again back to database (if you forget to add disconnect from session the mrp process with stop applying after you close session )

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;