RMAN Active Duplicate on RAC fails with ORA-17629 and ORA-17627: ORA-12154

RMAN active duplication of RAC database fails on restart operation with the following error messages:

oracle@said$ rman target sys@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 2 14:19:40 2017

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

target database Password:
connected to target database: ORCL (DBID=209740460)

RMAN> connect auxiliary sys@stdby

auxiliary database Password:
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1921 instance=ORCL1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwORCL2' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwORCL1'   ;
}
executing Memory Script

Starting backup at 02-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2689 instance=ULTIMUS1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/02/2017 14:20:46
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/02/2017 14:20:45
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

RMAN> exit

Reason:

ORA-12154 is raised whenever the TNS alias is not found on tnsnames.ora

After verification of tnsanames.ora files from both installations on Target and Auxiliary we see all the proper entries are there Still the RMAN duplicate command fails on ORA-12154

The reason for ORA-12154 being raised is because the process is looking for tnsnames.ora in a different ORACLE_HOME on the Auxiliary side.   This different ORACLE_HOME on Auxiliary side is usually the GRID HOME.

On 11G RAC Customers can configure SCAN Listener to handle failover connections on DB and ASM instances.

When RMAN duplicate is executed it needs to shutdown/startup the database several times.  Once the DB is down, a TNS connection can get lost on next startup since the corresponding service associated with the instance is no longer available.  The connection from RMAN thus fails when looking for the tnsnames.ora file under the wrong home.

Resolution:

There are two ways to workaround this problem

1) Copy the tnsnames.ora file form the RDBMS Home to the GRID Home

2) Establish a direct connection instead of using a TNS alias.  For example:

RMAN> connect auxiliary sys/pwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myauxiliaryhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=QRM)(UR=A)))

For Example:

oracle@said$ rman target sys@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 2 14:21:10 2017

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

target database Password:
connected to target database: ORCL (DBID=209740460)

RMAN> connect auxiliary sys/sys123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.250.75)(PORT=1599))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))

connected to auxiliary database: ORCL (not mounted)


RMAN> duplicate target database for standby from active database;


Hope this will work for you!!!!!!!

Resize Redo Log size in Data Guard ( Primary and Standby) Oracle 11g

-----------Resize Redo Log in Primary Database-------

alter database drop logfile group 2;
alter database add logfile  group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 5m;

alter database drop logfile group 3;
alter database add logfile  group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 5m;

alter system switch logfile

alter database drop logfile group 1;

--Error Come----

ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

select group#,status from v$log;

alter system checkpoint;

alter database drop logfile group 1;

alter database add logfile  group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 5m;


alter database drop logfile group 4;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/stby04.log' size 5m;

alter database drop logfile group 5;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcl/stby05.log' size 5m;

alter database drop logfile group 6;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcl/stby06.log' size 5m;

-----------Resize Redo Log in Standby Database-------

Check Database Status

SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         orcls         PHYSICAL STANDBY

SQL>

Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto
SQL>


SQL> alter system set standby_file_management=manual;

System altered.

SQL>
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL>

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

    GROUP# STATUS
---------- ----------
         4 ACTIVE
         5 UNASSIGNED
         6 UNASSIGNED

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         3 CURRENT

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Now to solve this issue we have cancel the managed recovery session
and set  "standby_file_management"  to manual and drop the standby redolog file  as

SQL> alter database recover managed standby database cancel ;

Database altered.

SQL>
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcls (thread 1)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcls/redo01.log'

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         3 CURRENT


If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status
logs,and for that we have to sync with Primary and clear the log first before dropping as

SQL> alter database clear logfile group 1;

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 CLEARING
         3 CURRENT

SQL>  alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile  group 1 '/u01/app/oracle/oradata/orcls/redo01.log' size 5m;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL>  alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile  group 2 '/u01/app/oracle/oradata/orcls/redo02.log' size 5m;
SQL> alter database add logfile  group 3 '/u01/app/oracle/oradata/orcls/redo03.log' size 5m;

---Now Remove Standby Redolog file---

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

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 ACTIVE
         6 UNASSIGNED

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcls/stby04.log' size 5m;

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/orcls/stby05.log' size 5m;

SQL> alter database drop standby logfile group 6;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/orcls/stby06.log' size 5m;

----Now check the status of red log and standby redo log status----

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

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

-------Check the size of redo log and standby redo log file size

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6          5
         4          5
         5          5

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1          5
         2          5
         3          5


Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database,
set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.

SQL> alter system set standby_file_management=auto;
SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
.
.
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING              66
.
.
.

RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                 68
MRP0      APPLYING_LOG         68

41 rows selected.

SQL>


-----In Primary Database

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            67

-----In Standby Database

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            66


Cheers !!!!!!!!!!!!! If any confusion Please give me buzz !!!!!

ORA-01196 ORA-01110: ORA-10458 signalled during: alter database open read only...

Error Log: Following errors are coming in alert log

Recovery of Online Redo Log: Thread 1 Group 5 Seq 47 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/agentdbs/stby05.log
Sun Aug 21 11:19:07 2016
Standby crash recovery failed to bring standby database to a consistent
point because needed redo hasn't arrived yet.
MRP: Wait timeout: thread 1 sequence# 0
Standby crash recovery aborted due to error 16016.
Errors in file /u01/app/oracle/diag/rdbms/agentdbs/agentdbs/trace/agentdbs_ora_23900.trc:
ORA-16016: archived log for thread 1 sequence# 47 unavailable
Recovery interrupted!
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Completed standby crash recovery.
Errors in file /u01/app/oracle/diag/rdbms/agentdbs/agentdbs/trace/agentdbs_ora_23900.trc:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/agentdbs/system01.dbf'
ORA-10458 signalled during: alter database open read only...



Solution:   The error mention in the trace file

[oracle@agentdbstdsrv ~]$ vim /u01/app/oracle/diag/rdbms/agentdbs/agentdbs/trace/agentdbs_ora_23900.trc

*** 2016-08-21 11:19:07.359
ORA-16016: archived log for thread 1 sequence# 47 unavailable
MRP: Prodding archiver at standby for thread 1 seq 47

*** 2016-08-21 11:19:07.360
Media Recovery drop redo thread 1
KCBR: Number of read descriptors = 1024
KCBR: Influx buffers flushed = 3 times

*** 2016-08-21 11:19:07.360
Completed Media Recovery
In-flux buffer recovery was not started because datafiles were fuzzy beyond in-flux recovery target.
Highest datafile fuzzy SCN: 0.1396075
In-flux buffer recovery target SCN: 0.1383731

Managed Recovery: Not Active posted.

We need to do the following changes in Primary Database

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
SQL> alter system set log_archive_dest_state_2=DEFER;

System altered.

SQL> alter system set log_archive_dest_state_2=ENABLE;

System altered.

Now we need to do the following in Standby Database

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.7413E+10 bytes
Fixed Size                  2236168 bytes
Variable Size            1.7314E+10 bytes
Database Buffers         1.9998E+10 bytes
Redo Buffers               98414592 bytes
Database mounted.

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

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

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

Database altered.

The Data Guard status of is Error ORA-19502: write error on file "", block number (block size=).

Error:  The Data Guard status of STLBAST is Error ORA-19502: write error on file "", block number (block size=).

Reason: Archive Destination become full in Standby Database server location.

Solution:

Step1: Check alert log both primary and standby database server.





Step2: Check Data Guard status log in primary Database server using following query


2015-DEC-01 09:58:18   ARC8: Completed archiving thread 1 sequence 57395 (10616070471665-10616070713099)
2015-DEC-01 09:58:51   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (19502)
2015-DEC-01 09:58:51   LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 09:58:51   Error 19502 for archive log file 4 to 'STLBAST'
2015-DEC-01 09:58:51   LNS: Failed to archive log 4 thread 1 sequence 57396 (19502)
2015-DEC-01 10:02:22   ARC9: Beginning to archive thread 1 sequence 57396 (10616070713099-10616070916651)
2015-DEC-01 10:02:23   ARC9: Completed archiving thread 1 sequence 57396 (10616070713099-10616070916651)
2015-DEC-01 10:04:48   ARCa: Beginning to archive thread 1 sequence 57397 (10616070916651-10616070982261)
2015-DEC-01 10:04:48   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
2015-DEC-01 10:04:48   LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 10:04:48   Error 270 for archive log file 6 to 'STLBAST'
2015-DEC-01 10:04:48   ARCa: Completed archiving thread 1 sequence 57397 (10616070916651-10616070982261)
2015-DEC-01 10:04:49   ARCb: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
2015-DEC-01 10:04:49   ARCb: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 10:04:49   FAL[server, ARCb]: Error 270 creating remote archivelog file 'STLBAST'
2015-DEC-01 10:10:49   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)

Step3: Login to Standby Database server and check the maximum archive log sequence that have already applied in Standby Database using following query

SELECT max(sequence#) AS "STANDBY", applied
FROM v$archived_log GROUP BY applied; 

or

SELECT THREAD#, SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG;

1 57405 YES
1 57404 YES
1 57403 YES
1 57402 YES
1 57401 YES
1 57400 YES

Step4: Purge old archive log that already applied in standby Database. Now check alert log in both primary and standby Database servers.


Thanks......Cheer

Step by step to configure Oracle DataGuard in Oracle 10g

Step by step to configure Oracle DataGuard in Oracle 10g

The Enviroment 

2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
Oracle Home is on identical path on both nodes

Implementation notes: 

Once you have your primary database up and running these are the steps to follow:

1.  Enable Forced Logging
2.  Create a Password File
3.  Configure a Standby Redo Log
4.  Enable Archiving
5.  Set Primary Database Initialization Parameters

Having followed these steps to implement the Physical Standby you need to follow these steps:

1.  Create a Control File for the Standby Database
2.  Backup the Primary Database and transfer a copy to the Standby node.
3.  Prepare an Initialization Parameter File for the Standby Database
4.  Configure the listener and tnsnames to support the database on both nodes
5.  Set Up the Environment to Support the Standby Database on the standby node.
6.  Start the Physical Standby Database
7.  Verify the Physical Standby Database Is Performing Properly


Step by Step Implementation of a Physical Standby Environment


Primary Database Steps
Primary Database General View

SQL> select name from v$database;

NAME
---------
WHITEOWL

SQL> select file_name from dba_data_files;


/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf

7 rows selected.

SQL> select name from v$database;

NAME
---------
WHITEOWL

SQL> show parameters unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      whiteowl

Enable Forced Logging

In order to implement Standby Database we enable 'Forced Logging'.

This option ensures that even in the event that a 'nologging' operation is done, force logging takes precedence and all operations are logged
into the redo logs.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.  

Create a Password File

A password file must be created on the Primary and copied over to the Standby site. The sys password must be identical on both sites. This is
a key pre requisite in order to be able to ship and apply archived logs from Primary to Standby.

cd $ORACLE_HOME/dbs
[vmractest1] > orapwd file=orapwwhiteowl password=oracle force=y

Configure a Standby Redo Log

A Standby Redo log is added to enable Data Guard Maximum Availability and Maximum Protection modes. It is important to configure the
Standby Redo Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need to provide the SRL path and file name. If you are not using OMF's
you then must pass the full qualified name.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
         3         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log      NO
         2         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log      NO
         1         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log      NO

SQL> select bytes from v$log;

     BYTES
----------
  52428800
  52428800
  52428800

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.

SQL> select * from v$logfile
  2  /

    GROUP# STATUS  TYPE    MEMBER                                                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
         3         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log      NO
         2         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log      NO
         1         ONLINE  /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log      NO
         4         STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log      NO 
         5         STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log      NO 
         6         STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log      NO 

6 rows selected.

Enable Archiving

On 10g you can enable archive log mode by mounting the database and executing the archivelog command:

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              75499088 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database archivelog; 

Database altered.

SQL> alter database open; 

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vmasmtest/whiteowl/archdest/arch
Oldest online log sequence     92
Next log sequence to archive   94
Current log sequence           94

Set Primary Database Initialization Parameters

Data Guard must use spfile, in order to configure it we create and configure the standby parameters on a regular pfile, and once it is ready we
convert it to an spfile.
Several init.ora parameters control the behavior of a Data Guard environment. In this example the Primary database init.ora is configured so
that it can hold both roles, as Primary or Standby.

SQL> create pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora'  from spfile;

File created.

Edit the pfile to add the standby parameters, here shown highlighted:

db_name='whiteowl'
db_unique_name='whiteowl' 
LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)' 
control_files='/vmasmtest/od01/WHITEOWL/WHITEOWL/controlfile/o1_mf_310n1xf0_.ctl'
LOG_ARCHIVE_DEST_1= 
 'LOCATION=/vmasmtest/whiteowl/archdest/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=whiteowl' 
LOG_ARCHIVE_DEST_2= 
 'SERVICE=blackowl LGWR ASYNC 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=blackowl' 
LOG_ARCHIVE_DEST_STATE_1=ENABLE 
LOG_ARCHIVE_DEST_STATE_2=ENABLE 
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE 
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc 
LOG_ARCHIVE_MAX_PROCESSES=30 
# Standby role parameters --------------------------------------------------------------------
*.fal_server=blackowl 
*.fal_client=whiteowl 
*.standby_file_management=auto 
*.db_file_name_convert='BLACKOWL/BLACKOWL','WHITEOWL/WHITEOWL' 
*.log_file_name_convert='/vmasmtest/od01/BLACKOWL/BLACKOWL/','/vmasmtest/od01/WHITEOWL/WHITEOWL/' 
# ---------------------------------------------------------------------------------------------
audit_file_dest='/oradisk/app01/oracle/admin/whiteowl/adump'
background_dump_dest='/oradisk/app01/oracle/admin/whiteowl/bdump'
core_dump_dest='/oradisk/app01/oracle/admin/whiteowl/cdump'
user_dump_dest='/oradisk/app01/oracle/admin/whiteowl/udump'
compatible='10.2.0.1.0'
db_block_size=8192
db_create_file_dest='/vmasmtest/od01/WHITEOWL'
db_domain=''
db_file_multiblock_read_count=16
job_queue_processes=10
open_cursors=300
pga_aggregate_target=94371840
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=283115520
undo_management='AUTO'
undo_tablespace='UNDOTBS1'

Once the new parameter file is ready we create from it the spfile: 

SQL> shutdown immediate; 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora'; 
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora'; 

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup; 
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

Standby Database Steps


Create a Control File for the Standby Database 

The standby database will use a control file that is generated on the primary database

SQL> startup mount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 
'/oradisk/app01/oracle/product/10gDB/dbs/blackowl.ctl'; 

Database altered.
SQL> ALTER DATABASE OPEN;

Database altered.

Backup the Primary Database and transfer a copy to the Standby node.

Generate a script to copy datafiles

SQL> set pages 50000 lines 120 head off veri off flush off ti off
SQL> spool cpfiles
SQL> select 'scp -p '||file_name||' $v_dest' from dba_data_files;

scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf $v_dest

7 rows selected.

SQL> select 'scp -p '||file_name||' $v_dest' from dba_temp_files;

scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest

SQL> select 'scp -p '||member||'  $v_dest' from v$logfile;

scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log  $v_dest

6 rows selected.

SQL> spool off
Shutdown the database, edit the script to add the v_dest location, and execute it.

[vmractest1] > mv cpfiles.lst cpfiles

#!/bin/ksh
v_dest=vmractest2:/oradisk/od01/BLACKOWL/datafile/
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest
v_dest= vmractest2:/oradisk/od01/BLACKOWL/onlinelog/
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log  $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log  $v_dest

On the standby node create the required directories to get the datafiles

[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/datafile/
[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/onlinelog/
[root@vmractest2 root]# chown -R oracle:dba /oradisk/od01

On the primary node execute the script to copy the database while the main database is down (or in backup mode)

[vmractest1] > chmod 700 cpfiles
[vmractest1] > ./cpfiles
o1_mf_users_310mzml9_.dbf              100% 2801MB   7.0MB/s   06:37
o1_mf_sysaux_310mzm34_.dbf             100%  340MB   7.4MB/s   00:45
o1_mf_undotbs1_310mzmk2_.dbf           100%  100MB   8.0MB/s   00:12
o1_mf_system_310mzm27_.dbf             100%  490MB   7.0MB/s   01:09
o1_mf_test2_3117h15v_.dbf              100%  100MB   6.5MB/s   00:15
o1_mf_test3_3117h8nv_.dbf              100%  100MB   6.0MB/s   00:16
o1_mf_test4_3117hk7d_.dbf              100%  100MB   6.4MB/s   00:15
o1_mf_temp_310n2bnj_.tmp               100%  433MB   5.8MB/s   01:14
o1_mf_3_310n22jj_.log                  100%   50MB   7.5MB/s   00:06
o1_mf_2_310n21sx_.log                  100%   50MB   8.4MB/s   00:05
o1_mf_1_310n215q_.log                  100%   50MB   8.8MB/s   00:05
o1_mf_4_3gznjc9v_.log                  100%   50MB   7.7MB/s   00:06
o1_mf_5_3gznnrh0_.log                  100%   50MB   8.2MB/s   00:06
o1_mf_6_3gznrwd7_.log                  100%   50MB   4.9MB/s   00:10


Prepare an Initialization Parameter File for the Standby Database 


Copy and edit the primary init.ora to set it up for the standby role

*.db_name='whiteowl'
*.db_unique_name='blackowl' 
*.audit_file_dest='/oradisk/app01/oracle/admin/blackowl/adump' 
*.background_dump_dest='/oradisk/app01/oracle/admin/blackowl/bdump' 
*.core_dump_dest='/oradisk/app01/oracle/admin/blackowl/cdump' 
*.user_dump_dest='/oradisk/app01/oracle/admin/blackowl/udump' 
*.compatible='10.2.0.1.0'
*.control_files='/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl','/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl' 
*.db_block_size=8192
*.db_create_file_dest='/oradisk/od01/BLACKOWL' 
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/oradisk/od01/BLACKOWL/datafile/','/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/' 
*.log_file_name_convert='/oradisk/od01/BLACKOWL/onlinelog/','/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/' 
*.fal_server='whiteowl' 
*.fal_client='blackowl' 
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradisk/od01/BLACKOWL/archives/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 
  DB_UNIQUE_NAME=blackowl' 
*.LOG_ARCHIVE_DEST_2='SERVICE=whiteowl LGWR ASYNC 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=whiteowl' 
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'


Create all required directories for dump directories and archived log destination

 [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/

Copy from the primary the standby controlfile to its destination

[vmractest1] > scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl
blackowl.ctl                                                                   100% 6992KB   7.2MB/s   00:00
[vmractest1] > scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl
blackowl.ctl                                                                   100% 6992KB   6.9MB/s   00:00



Configure the listener and tnsnames to support the database on both nodes


Configure listener.ora on both servers to hold entries for both databases

# ON VMRACTEST1
LISTENER_VMRACTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_VMRACTEST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = whiteowl)
      (ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
      (SID_NAME = whiteowl)
    )
  )


# ON VMRACTEST2
LISTENER_VMRACTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_VMRACTEST =  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = blackowl)
      (ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
      (SID_NAME = blackowl)
    )
  )

Configure tnsnames.ora on both servers to hold entries for both databases

# ON VMRACTEST1
LISTENER_VMRACTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRS
    )
  )

WHITHEOWL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = whiteowl)
    )
  )

BLACKOWL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522))
    (CONNECT_DATA =      (SERVER = DEDICATED)
      (SERVICE_NAME = blackowl)
    )
  )

# ON VMRACTEST2
LISTENER_VMRACTEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
    )
  )

BLACKOWL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = blackowl)
    )
  )

WHITHEOWL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = whiteowl)
    )
)

Start the listener and check tnsping on both nodes to both services

[vmractest1.partnergsm.co.il] > tnsping whiteowl      
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00

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

Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
{oracle} /oradisk/app01/oracle/product/10gDB/network/admin
[vmractest1.partnergsm.co.il] > tnsping blackowl      
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)


Set Up the Environment to Support the Standby Database on the standby node. 

Copy the password file from Primary to Standby, sys password must be identical

[vmractest1]> scp orapwwhiteowl 
vmractest2:/oradisk/app01/oracle/product/10gDB/dbs/orapwblackowl 
orapwwhiteowl               100% 1536     4.0MB/s   00:00

Setup the environment variables to point to the Satndby database 

ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=blackowl

Startup nomount the Standby database and generate an spfile

{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007

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

Connected to an idle instance.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initblackowl.ora'
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initblackowl.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

Startup mount the Standby database and perform recovery

SQL> startup mount
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

The alert log of the standby will show the operations taking place

…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log


Start the Primary Database

The alert log of the primary will show how it recognize the standby and start shipping archived logs

******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
  Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2


Verify the Physical Standby Database Is Performing Properly

Check archived redo log on Standby

SQL> show parameters db_unique_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      blackowl
SQL> l
  1* SELECT NAME FROM V$DATABASE
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
        96 19-SEP-07 19-SEP-07
        97 19-SEP-07 19-SEP-07
        98 19-SEP-07 19-SEP-07
        99 19-SEP-07 19-SEP-07
       100 19-SEP-07 19-SEP-07

Switch logfiles on Primary 

SQL> alter system switch logfile; 
System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vmasmtest/whiteowl/archdest/
Oldest online log sequence     100
Next log sequence to archive   102
Current log sequence           102

SQL> alter system switch logfile; 

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /vmasmtest/whiteowl/archdest/
Oldest online log sequence     101
Next log sequence to archive   103
Current log sequence           103

Check archived redo log on Standby

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME     NEXT_TIME
---------- -------------- --------------
        96 19/09/07 09:35 19/09/07 09:45
        97 19/09/07 09:45 19/09/07 15:20
        98 19/09/07 15:20 19/09/07 15:48
        99 19/09/07 15:48 19/09/07 16:00
       100 19/09/07 16:00 19/09/07 16:05
       101 19/09/07 16:05 19/09/07 16:08
       102 19/09/07 16:08 19/09/07 16:08

7 rows selected.

Background Media Recovery process shutdown due to add datafile in Primary / MRP0: Background Media Recovery terminated with error 1111

Error:

Managed Standby Recovery starting Real Time Apply
Tue Oct 21 09:48:09 2014
Errors in file /u01/app/oracle/diag/rdbms/orclt/orclT/trace/orclT_dbw0_27220.trc:
ORA-01186: file 59 failed verification tests
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
File 59 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/orclt/orclT/trace/orclT_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
Errors in file /u01/app/oracle/diag/rdbms/orclt/orclT/trace/orclT_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (orclT)


vim /u01/app/oracle/diag/rdbms/orclt/orclT/trace/orclT_dbw0_27220.trc

DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-01186: file 59 failed verification tests
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED0005


Reason:

Adding a Tablespace / Datafile in Primary Database causes the MRP in Physical Standby Database to terminate with the Error below. STANDBY_FILE_MANAGEMENT = MANUAL is set on the Standby Database.

Solution:

Following steps need to perform in Standby Database

Step 1: Ensure the standby_file_management = 'MANUAL' 

Step 2: Identify the File which is "unnamedn" in standby
SQL> select name from v$datafile;

Step 3: Rename/create the Datafile to the correct Filename

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059' as '/idx01/oradata/orclt/orbind02.dbf';

Step 4: Verify the Filename is correct

SQL> select name from v$datafile;

Step 5: Change the STANDBY_FILE_MANAGMENT to AUTO

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Step 6: Start the MRP (this is using Real Time Apply)

alter database recover managed standby database using current logfile disconnect;


Step 7: Verify the MRP is running as expected


SQL> select process, status , sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING           28392
ARCH      CLOSING           28393
ARCH      CLOSING           28394
ARCH      CLOSING           28366
ARCH      CLOSING           28367
ARCH      CLOSING           28368
ARCH      CLOSING           28369
ARCH      CLOSING           28370
RFS       IDLE                  0
RFS       IDLE              28395
RFS       IDLE                  0

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
RFS       IDLE                  0
RFS       IDLE                  0
MRP0      APPLYING_LOG      28395

36 rows selected.

Datafile added in Primary Database but not applied in Standby Database or MRP0: Background Media Recovery terminated with error 1111

Errors

Managed Standby Recovery starting Real Time Apply
Tue Oct 21 09:48:09 2014
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_dbw0_27220.trc:
ORA-01186: file 59 failed verification tests
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
File 59 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-1111 exception
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_1637.trc:
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
ORA-01157: cannot identify/lock data file 59 - see DBWR trace file
ORA-01111: name for data file 59 is unknown - rename to correct file
ORA-01110: data file 59: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00059'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Recovery Slave PR00 previously exited with exception 1111

MRP0: Background Media Recovery process shutdown (STLBAST)

Symptoms

Adding a Tablespace / Datafile in Primary Database causes the MRP in Physical Standby Database to terminate with the Error below. STANDBY_FILE_MANAGEMENT = MANUAL is set on the Standby Database.

MRP0: Background Media Recovery terminated with error 1274
ORA-01274: cannot add datafile '...dbf' - file could not be created

In Unix Environment you will get the below Message when try to restart the MRP

ORA-01111: name for data file 163 is unknown - rename to correct file

Cause

This Error occurs if we add a Datafile OR Tablespace in PRIMARY Database and that could not be translated to the Standby Database due to these Reasons:

• Standy_file_management is set to MANUAL
• Primary & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in Primary and Standby
• Insufficient Space or wrong Permissions on the Standby Database to create the Datafile

The Redo Log generated from Primary will have Information about the Tablespace / Datafile added however it could not be created successfully in Physical Standby Database due to the standby_file_management = MANUAL
or is not able to find the specified Folder due to a missing / incorrect Filename Conversion.
The File Entry is added to Standby Controlfile as "UNNAMED0000n" in /dbs or /database
folder depends on the Operating System and eventually the MRP terminates.

Alert Log in Standby Shows MRP is terminated with below error
=================================================================
File #5 added to control file as 'UNNAMED00005' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Thu Sep 25 19:02:35 2008
Errors in file c:\oracle\product\10.2.0\admin\mystd\bdump\mystd_mrp0_3436.trc:
ORA-01274: cannot add datafile 'D:\ORADATA\PRIM\SALES01.DBF' - file could not be created

By default it is AUTO by broker.

Solution

Perform all mentioned Steps on the Standby Database:

Step 1: Ensure the standby_file_management = 'MANUAL'

NOTE : For the parameter db_file_name_convert change if the Data Guard Broker is enabled then edit the Parameters using the Broker,

DGMGRL>edit database '' set property DbFileNameConvert='<>','<>';
DGMGRL>edit database '' set property StandbyFileManagement=manual;

By default StandbyFileManagement is AUTO by broker.

Step 2: Identify the File which is "unnamedn"

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORADATA\MYSTD\SYSTEM.DBF
D:\ORADATA\MYSTD\UNDO.DBF
D:\ORADATA\MYSTD\SYSAUX.DBF
D:\ORADATA\MYSTD\SERVICE01.DBF
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005



Step 3: Rename/create the Datafile to the correct Filename

SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'D:\oradata\mystd\sales01.dbf';

If the standby is in ASM + OMF then use the below command,

SQL> alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as <'+ASMDISKGROUPNAME'> size ;

or

SQL>alter database create datafile '/oracle/product/GSIPRDGB/dbs/UNNAMED00210' as new;

Step 4: Verify the Filename is correct

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORADATA\MYSTD\SYSTEM.DBF
D:\ORADATA\MYSTD\UNDO.DBF
D:\ORADATA\MYSTD\SYSAUX.DBF
D:\ORADATA\MYSTD\SERVICE01.DBF
D:\ORADATA\MYSTD\SALES01.DBF


Step 5: Change the STANDBY_FILE_MANAGMENT to AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Step 6: Start the MRP (this is using Real Time Apply)

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.


Step 7: Verify the MRP is running as expected

SQL> select process, status , sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 99
ARCH CLOSING 103
MRP0 APPLYING_LOG 104
RFS IDLE 0
RFS IDLE 0
RFS IDLE 104

MRP process not start in standby / Archive log not applying in standby Database

Applies to:

 Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 31-Aug-2012***
***Checked for relevance on 5-Aug-2014*** 

Symptoms

Physical standby recovery reports error in alert log:

Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /back/flash_recovery_area/STLBAST/archivelog/1_28158_842022897.arc
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_27378.trc  (incident=640459):
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/stlbast/STLBAST/incident/incdir_640459/STLBAST_pr00_27378_i640459.trc
Sun Oct 19 16:40:53 2014
Completed:  alter database recover managed standby database disconnect from session
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors with log /back/flash_recovery_area/STLBAST/archivelog/1_28158_842022897.arc
MRP0: Background Media Recovery terminated with error 600
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_27378.trc:
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []
Recovery interrupted!
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_27378.trc:
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []
Slave exiting with ORA-600 exception
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_27378.trc:
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []
Sun Oct 19 16:40:53 2014
Dumping diagnostic data in directory=[cdmp_20141019164053], requested by (instance=1, osid=27378 (PR00)), summary=[incident=640459].
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_mrp0_27376.trc  (incident=640451):
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []

Cause

ORA-600[2619] is raised due to an invalid next_change# detected in archive log.

In this case, it is caused by the archive log disk space ran out on standby site, causing that archive log not fully written on disk. This lead to ORA-600[2619] when the archive log was applied.

Per standby alert log:



Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /back/flash_recovery_area/STLBAST/archivelog/1_28158_842022897.arc
Errors in file /u01/app/oracle/diag/rdbms/stlbast/STLBAST/trace/STLBAST_pr00_27378.trc  (incident=640459):
ORA-00600: internal error code, arguments: [2619], [28158], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/stlbast/STLBAST/incident/incdir_640459/STLBAST_pr00_27378_i640459.trc

Solution

1. Clear the disk space where archive log stored on standby site

2. Copy the problem archive log (eg: 1_28158_842022897.arc) from the primary site and replace the one on the standby, then restart Managed Recovery.

Archive log should be applied properly now


SQL> alter database recover managed standby database cancel;  --- Cancel first

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step by step to configure Data Guard in Oracle 11g in OEL environment

---The following scripts is tested in my lab....

--primary---

archive log list;

select name from v$database;

show parameters unique

ALTER DATABASE FORCE LOGGING;

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

select group#,type,member from v$logfile;

select bytes from v$log;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/ORCL/stby04.log' size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/ORCL/stby05.log' size 50m;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/ORCL/stby06.log' size 50m;

SELECT GROUP#,TYPE,MEMBER FROM V$LOGFILE;

CREATE PFILE FROM SPFILE;
---Do necessary changes in initORCL.ora file---
shutdown immediate;

startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux-x86_64 Error: 2: No such file or directory
SQL>

mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/archivelog/

startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

shutdown immediate;

startup mount

alter database archivelog;

alter database open;

archive log list;
alter system switch logfile;

mkdir -p /u01/rmanback
rman target=/
backup full database format '/u01/rmanback/%d_%U.bckp' plus archivelog format '/u01/rmanback/%d_%U.bckp';

configure channel device type disk format '/u01/rmanback/%U';
BACKUP CURRENT CONTROLFILE FOR STANDBY;
BACKUP ARCHIVELOG ALL;


--Standby---

mkdir -p /u01/rmanback

scp /u01/rmanback/*  oracle@192.168.56.4: /u01/rmanback/

scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/trace

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/cdump

mkdir -p /u01/app/oracle/diag/rdbms/orcl/ORCLS/trace

mkdir -p /u01/app/oracle/oradata/ORCLS

mkdir -p /u01/app/oracle/fast_recovery_area/ORCL/onlinelog
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLS/archivelog/
mkdir -p /u01/app/oracle/admin/ORCLS/adump


---Do necessary changes in initORCLS.ora file---

---configure tnsnames.ora and listener in primary

[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ORCLS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.4)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcls)
    )
  )

  [oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
 )
 )
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = ORCL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
 (SID_NAME = ORCL)
 )
 )


---configure tnsnames.ora and listener in Standby

Create a passwordfile for the standby:

orapwd file=$ORACLE_HOME/dbs/orapwORCLS password=sys123




[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.4)(PORT = 1521))
 )
 )
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = ORCLS)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
 (SID_NAME = ORCLS)
 )
 )

 --- Now do tnsping from both node
 tnsping orcl
 tnsping orcls

 ----Standby Server--
 copy the initORCLS.ora file to dbs location

startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCLS.ora'

create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCLS.ora';
shutdown immediate
startup nomount;

rman target=sys/sys123@orcl auxiliary=/

----output should be as below----

[oracle@standby dbs]$ rman target=sys/sys123@orcl auxiliary=/

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Aug 15 14:21:40 2013

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

connected to target database: ORCL (DBID=1351299913)
connected to auxiliary database: ORCL (not mounted)
-------------------------------------------

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

----Start the redo apply:

alter database recover managed standby database disconnect from session;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

----Primary---

alter system switch logfile;
alter system archive log current;

archive log list;

----Standby---

archive log list;


SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG  ORDER BY SEQUENCE#;

---Stop the managed recovery process on the standby:

alter database recover managed standby database cancel;



-----Test Database status---
---Primary
select database_role from v$database;
---Standby
select database_role from v$database;

Monitoring Commands for Data Guard

Following commands will be helpful for monitoring Data Guard environment.

---From Primary:

select protection_mode,protection_level,database_role,switchover_status
from v$database;

select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||'   '||message
from v$dataguard_status;

SELECT max(sequence#) AS "PRIMARY" FROM v$log_history;


---From standby database:

select protection_mode,protection_level,database_role,switchover_status
from v$database;

select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||'   '||message from v$dataguard_status;

select process,status,thread#,sequence#,block#,blocks
from v$managed_standby;

SELECT max(sequence#) AS "STANDBY", applied
FROM v$archived_log GROUP BY applied;

SELECT THREAD#, SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG;

SELECT THREAD#, SEQUENCE#, APPLIED,to_char(FIRST_TIME,'YYYY-MON-DD HH24:MI:SS') FIRST_TIME
FROM V$ARCHIVED_LOG
where APPLIED!='YES';

SELECT thread#, low_sequence#, high_sequence# from V$archive_gap;

Oracle Data Guard Configuration in 11g Database

Oracle Data Guard Configuration in 11g Database

Step 1: OS installation similar to Primary Database OS. [Standby]
Step 2:   Install Database Software similar version to Primary Database. Only change the standby database’s bash Profile. [Standby]
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_UNQNAME=sbsdr; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=sbs; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Step 3: Create folder (Data files, control files, Redo logs and dump files) similar to Primary Database.
Step 4: Create Listener and tns in Standby Database and modify the tns Primary Database.
tnsnames.ora at Primary Database
sbs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.74)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
sbsdr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
listener.ora file at standby side
SID_LIST_LISTENER_SBSDR =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = sbs)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = sbs)
)
)
LISTENER_SBSDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
)
ADR_BASE_LISTENER_OEMGC = /u01/app/oracle
tnsname.ora file at standby side
sbs =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.74)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
sbsdr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.1.73)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sbs)
)
)
Check Listener and Start (StandBy Database):
$ lsnrctl
$ status
$ start LISTENER_SBSDR — (Listener_Name)

Check the Both Server tns is working or not (tns Service):
$ tnsping sbsdr [from primary]
$ tnsping sbs   [from primary]
$ tnsping sbs [from standby]
$ tnsping sbsdr [from standby]
Step 5: PREPARE Primary DATABASE
(1) Moving database at force logging mode
SQL> ALTER DATABASE FORCE LOGGING;
(2) Create Standby redolog [Primary Database]
Connect to the physical standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs. The recommended number of standby redo logs is:
(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.
You can check the number and group numbers of the redo logs by querying the V$LOG view: [Primary Databse]
SQL> SELECT * FROM V$LOG;
SQL> SELECT * FROM V$LOGFILE;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/sdata2/redologs/sbs/redo04.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘/sdata2/redologs/sbs/redo05.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 (‘/sdata1/redologs/sbs/redo06.log’) SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 (‘/sdata1/redologs/sbs/redo07.log’) SIZE 50M;
(3) CONFIGURE PRIMARY DATABASE PARAMETER CHANGE
*.audit_file_dest=’/u01/app/oracle/admin/sbs/adump’
*.audit_sys_operations=TRUE
*.audit_trail=’DB’,'EXTENDED’
*.cell_offload_processing=FALSE
*.compatible=’11.2.0.0.0′
*.control_files=’/sdata1/controlfiles/sbs/control01.ctl’,'/sdata2/controlfiles/sbs/control02.ctl’#Restore Controlfile
*.db_block_size=8192
*.db_domain=”
*.db_name=’sbs’
*.diagnostic_dest=’/u01/app/oracle’
*.log_archive_dest_1=’location=/sdata3/archivelog’
*.memory_target=8879341568
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=665
*.shared_pool_size=524288000
*.undo_tablespace=’UNDOTBS1′
Parameter changing required:
*.fal_client=’sbs’
alter system set fal_client=’sbs’ scope=both;
*.fal_server=’sbsdr’
alter system set fal_server=’sbsdr’ scope=both;
*.log_archive_config=’dg_config=(sbs,sbsdr)’
alter system set log_archive_config=’dg_config=(sbs,sbsdr)’ scope=both;
*.log_archive_dest_2=’service=IRISDR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=IRISDR’
alter system set log_archive_dest_2=’service=SBSDR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=SBSDR’ scope=both;
*.log_archive_dest_state_2=’ENABLE’
alter system set log_archive_dest_state_2=’DEFER’ scope=both;
*.standby_file_management=’AUTO’
alter system set standby_file_management=’AUTO’ scope=both;

(4) GATHER FILES AND PERFORM BACK UP
On the primary node, create a Backup directory at primary Database :
$ mkdir -p /u01/rman/backup_dg/
Create the same exact path on the [ standby host ]:
$ mkdir -p /u01/rman/backup_dg/
On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. [primary node 1]
SQL> CREATE PFILE=’/u01/rman/backup_dg/initsbs.ora’  FROM SPFILE;
On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory.
$ rman target /
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
backup database format ‘/u01/rman/backup_dg/DATA_%U’;
backup archivelog all format ‘/u01/rman/backup_dg/ARC_%U’;
backup current controlfile for standby format ‘/u01/rman/backup_dg/CTL_%U’;
release channel c1;
release channel c2;
release channel c3;
}
Copy the contents of the staging directory on the primary node to the staging directory on the standby host.
$ scp /u01/rman/backup_dg/*  oracle@sbsdr: /rman/backup_dg/

TRANSFER PASSWORD FILE AND RENAME
To enable secure transmission of redo data, make sure the primary and physical standby databases use a password file, and make sure the password for the SYS user is identical on every system.
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwsbs  password=XXXXX
            $ ftp sbsdr
            $cd $ORACLE_HOME/dbs
            $mput orapwsbs1
Rename orapwsbs to orapwsbs at standby side
Copy and rename the primary database PFILE from the staging area on the standby host to the $ORACLE_HOME/dbs directory on the standby host. [ standby host ]: 
$scp initsbs.ora  $ORACLE_HOME/dbs/initsbs.ora
Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table: [ standby host ]:
*.audit_file_dest=’/u01/app/oracle/admin/sbs/adump’
*.audit_sys_operations=TRUE
*.audit_trail=’DB’,'EXTENDED’
*.cell_offload_processing=FALSE
*.compatible=’11.2.0.0.0′
*.control_files=’/sdata1/controlfiles/sbs/control01.ctl’,'/sdata2/controlfiles/sbs/control02.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’sbs’
*.db_unique_name=’sbsdr’
*.diagnostic_dest=’/u01/app/oracle’
*.memory_target=8879341568
*.open_cursors=300
*.processes=600
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=665
*.shared_pool_size=524288000
*.undo_tablespace=’UNDOTBS1′
*.log_archive_config=’dg_config=(sbs,sbsdr)’
*.fal_client=’sbsdr’
*.fal_server=’sbs’
*.log_archive_dest_1=’location=/sdata3/archivelog MANDATORY’
*.log_archive_dest_2=’service=SBS ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=SBS’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’DEFER’
*.standby_file_management=’AUTO’
STANDBY DATABASE RESTOR & RECOVER
After setting up the appropriate environment variables on the standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the physical standby database instance without mounting the control file. [ standby host ]
SQL> STARTUP NOMOUNT
From the standby host, duplicate the primary database .  [ standby host ]
$ rman target sys/XXXXX@sbs  auxiliary /
 RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
Perform the following activity on Standby Database Server
Start Physical standby database
SQL> alter database mount standby database;
Initiate Log apply services
Run the following command on standby database
The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Put standby database on normal stage as before ( Log Apply services mode)
Create Server Parameter File on Standby Database Server
SQL> CREATE SPFILE FROM PFILE=’Path/Name.ora’;
SQL>shutdown immediate;

SQL>startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database open into Read only mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
Step 6: VERIFY DATA GUARD CONFIGURATION
Test Case 1:
-Verify database role
On the primary server
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PRIMARY
On the standby server
SQL> select database_role from v$database;
DATABASE_ROLE
—————-
PHYSICAL STANDBY
Test Case 2:
-The “alter system switch logfile” command forces a log switch.
On the primary server
-The “alter system switch logfile” command forces a log switch.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /sdata3/archivelog
Oldest online log sequence     8043
Next log sequence to archive   8045
Current log sequence           8045
SQL>
On the standby server
======================
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /sdata3/archivelog
Oldest online log sequence     8043
Next log sequence to archive   0
Current log sequence           8045
SQL>
Test Case 3:
-Below SQL will show Max sequence number of the archived log
On the primary server
=========================
SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;
PRIMARY
———-
8044
On the standby server
=========================
SQL> SELECT max(sequence#) AS “STANDBY”, applied
FROM v$archived_log GROUP BY applied;
STANDBY APPLIED
———- ———
8044 YES
Test Case 4:
-Create a tablespace on primary server
On the primary server
=========================
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SBS
TBL_BBL_AUDIT_DAY30
7 rows selected.
On the standby server
=========================
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SBS
TBL_BBL_AUDIT_DAY30
7 rows selected.

V$ARCHIVE_GAP displays information about archive gaps on a standby database. This view can be used to find out the current archive gap that is blocking recovery.