Active Datagaurd with Read Only with Apply

Active Datagaurd with Read Only with Apply

Detail	Primary	Standby
DB NAME	PRIM	STAND
IP	192.168.171.10	192.168.171.20
SERVER	prod.som.com	stand.som.com

If your primary not in archivelog please convert it to archivelog:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  369098752 bytes

Fixed Size                  2924544 bytes

Variable Size             289406976 bytes

Database Buffers           71303168 bytes

Redo Buffers                5464064 bytes

Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING  LOG_MODE
————        ————
YES                               ARCHIVELOG

Adding Redolog for standby database

SQL> alter database add standby logfile group 4 '/u01/app/12c/database/oradata/prim/redo04.log' size 50M;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/12c/database/oradata/prim/redo05.log' size 50M;
Database altered.

SQL> alter database add standby logfile group 6 '/u01/app/12c/database/oradata/prim/redo06.log' size 50M;
Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

All status will show as UNASSIGNED

TNS ENTRIES on BOTH the NODES:

STAND =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = stand.som.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stand)

    )

  )

PRIM =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.som.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prim.som.com)

    )

  )

LISTENER ENTRIES

[oracle@prod admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/12c/database/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

PRIM =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = prod.som.com)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = prim)

(ORACLE_HOME = /u01/app/12c/database/dbhome_1)

(SID_NAME = prim)

)

(SID_DESC =

(GLOBAL_DBNAME = stand)

(ORACLE_HOME = /u01/app/12c/database/dbhome_1)

(SID_NAME = stand)

)

)

[oracle@stand admin]$ cat listener.ora

LISTENER_STAND =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = TCP)(HOST = stand.som.com)(PORT = 1521))

      (QUEUESIZE = 50)

     )

  )

SID_LIST_LISTENER_STAND =

        (SID_LIST =

        (SID_DESC =

         (GLOBAL_DBNAME=prim)

         (SID_NAME = prim)

         (ORACLE_HOME= /u01/app/12c/database/dbhome_1)

        )

        (SID_DESC =

        (SID_NAME = stand)

        (ORACLE_HOME = /u01/app/12c/database/dbhome_1)

        (GLOBAL_DBNAME=stand)

        )

        )

ONCE above done , please check tnsping from both the servers. ie.

tnsping prim

tnsping stand 

above should work on both the direction.

Change below parameters on primary database:

ALTER SYSTEM SET db_unique_name='prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_config='dg_config=(prim,stand)' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=stand' SCOPE=SPFILE;

ALTER SYSTEM SET fal_server='STAND' SCOPE=SPFILE;

ALTER SYSTEM SET fal_client='PRIM' SCOPE=SPFILE;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;

ALTER SYSTEM SET db_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim' SCOPE=SPFILE;

ALTER SYSTEM SET log_file_name_convert='/u01/app/12c/database/oradata/stand','/u01/app/12c/database/oradata/prim' SCOPE=SPFILE;

Password file creation on standby

copy the remote login password file (orapwprim) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapwstand.

Changing parameters in standby database init file

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=stand

[oracle@standby admin]$ cd $ORACLE_HOME/dbs
[oracle@standby dbs]$ cat initstand.ora
db_name=stand

Create directory Structure on standby

cd $ORACLE_BASE/admin/
mkdir stand
cd stand
mkdir adump pfile dpdump
mkdir -p /u01/app/12c/database/oradata/stand

Start the standby database using pfile

. ./stand.env
sqlplus ‘/as sysdba’startup nomount pfile=’initstand.ora’ 

RUN db duplicate:

rman target sys/oracle@prim

connect auxiliary sys/oracle@stand

run

{

allocate channel p1 type disk;

allocate channel p2 type disk;

allocate auxiliary channel s1 type disk;

allocate auxiliary channel s2 type disk;

duplicate target database for standby from active database

spfile

parameter_value_convert 'prime','stand'

set db_unique_name='stand'

set db_file_name_convert='/u01/app/12c/database/oradata/prim','/u01/app/12c/database/oradata/stand'

set log_file_name_convert='/u01/app/12c/database/oradata/prim','/u01/app/12c/database/oradata/stand'

set control_files='/u01/app/12c/database/oradata/stand/standby1.ctl'

set log_archive_max_processes='5'

set fal_client='stand'

set fal_server='prim'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(prim,stand)'

set compatible='12.1.0.2.0'

set memory_target='350m'

nofilenamecheck;

}

On standby database

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

Database altered.

Check log sync:

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SQL> alter database open;

Database altered.

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

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRIM      READ ONLY WITH APPLY