step by step Oracle 12c Data Guard Failover

 Step:1 In standby database check the database role and open_mode

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE         PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY

  Step:2 Check archive log gap sequence

 SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

 Step:3 Cancel MRP process in standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered. 

 Step:4 Bring up standby database as primary 

SQL> alter database recover managed standby database finish;

Database altered.

SQL> alter database activate standby database;

Database altered.

Managed recovery process has been stopped between primary and standby database and standby becomes primary database.

Step:5 Shutdown and startup database and verify database name its open mode and its role.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1.6106E+11 bytes
Fixed Size            7653336 bytes
Variable Size         8.1604E+10 bytes
Database Buffers     7.8920E+10 bytes
Redo Buffers          529215488 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role from v$database;

OPEN_MODE         DATABASE_ROLE
-------------------- ----------------
READ WRITE         PRIMARY

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.

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.

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;