create 12c physical standby database from active database using rman duplicate

=========================

primary

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u03

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL>

ALTER DATABASE FORCE LOGGING;

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/test/redo01a.log

/u01/app/oracle/oradata/test/redo01b.log

/u01/app/oracle/oradata/test/redo02a.log

/u01/app/oracle/oradata/test/redo02b.log

/u01/app/oracle/oradata/test/redo03a.log

/u01/app/oracle/oradata/test/redo03b.log

SQL> select group# from v$log;

    GROUP#

----------

         1

         2

         3

Database altered.

alter database add standby logfile group 4 '/u01/app/oracle/oradata/test/redo04b.log' size 300m;

alter database add standby logfile group 5 '/u01/app/oracle/oradata/test/redo05a.log' size 300m;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/test/redo06a.log' size 300m;

if multiplexing you can use as below

alter database add standby logfile group 7 ('/u01/app/oracle/oradata/test/redo07a.log','/u01/app/oracle/oradata/test/redo07b.log') size 300m;

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

    GROUP#    THREAD#  SEQUENCE# ARC STATUS

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

         4          0          0 YES UNASSIGNED

         5          0          0 YES UNASSIGNED

         6          0          0 YES UNASSIGNED

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

ALTER SYSTEM SET log_archive_config='dg_config=(test,test_stand)' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/archive valid_for=(all_logfiles,all_roles) db_unique_name=test' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_dest_2='service=test_stand async valid_for=(online_logfiles,primary_role) db_unique_name=test_stand' scope=spfile;

SQL> ALTER SYSTEM SET fal_server='test_stand' SCOPE=SPFILE;

System altered.

11gr2 no longer required for fal_client;

SQL> ALTER SYSTEM SET fal_client='test' SCOPE=SPFILE;

System altered.

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

system altered.

ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/test_stand','/u01/app/oracle/oradata/test' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/test_stand','/u01/app/oracle/oradata/test' SCOPE=SPFILE; 

System altered.

[oracle@PRD01 dbs]$ scp orapwtest prd02:/u02/app/oracle/product/12.2.0/dbhome_1/dbs

oracle@prd02's password:

orapwtest                                                                   100% 6144     2.6MB/s00:00

[oracle@PRD01 dbs]$

[oracle@prd02 dbs]$ mv orapwtest orapwtest_stand

[oracle@prd02 dbs]$

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

 ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL>  ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

configure listener and

 tnsnames.ora on both the side , it should be ping able from both the end.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

cp pfile from primary to secondary 

make neccessary change in secondary/

startup nomount =======standby

on primary

rman target sys/Welcome_231@test auxiliary sys/Welcome_231@test_stand

RMAN> connect auxiliary sys/XX@dup

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

recreate password for sys in oracle and create orapwd file as below

orapwd file=orapwtest password=Welcome_231 force=y entries=10

rman TARGET sys/Welcome_123@test  AUXILIARY sys/Welcome_123@test_stand

[oracle@demo dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Feb 23 13:50:24 2021

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

connected to target database: TEST (DBID=2362193459)

RMAN> connect auxiliary sys/Welcome_231@test_stand

connected to auxiliary database: TEST (not mounted)

duplicate target database for standby from active database nofilenamecheck;

select name,database_role,open_mode from v$database;

Enable the recovery:

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

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

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

How To Enable Active Dataguard In Physical Standby Database

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

 SEQUENCE# APPLIED

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

        16 YES

        17 YES

        18 IN-MEMORY

SQL> /

 SEQUENCE# APPLIED

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

        16 YES

        17 YES

        18 YES

        19 YES

        20 YES

        21 IN-MEMORY

6 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

alter database recover managed standby database cancel;

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

TEST      READ ONLY

start the media recover

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

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

TEST      READ ONLY WITH APPLY

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

select * from v$logfile;

select b.thread#,a.group#, a.type, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

Thanks

Ravi 

ORACLE DATABASE 12C – CREATE DATA GUARD STANDBY DATABASE USING DBCA

syntax : dbca -createDuplicateDB 

    -gdbName global_database_name 

    -primaryDBConnectionString easy_connect_string_to_primary

    -sid database_system_identifier

    [-createAsStandby 

        [-dbUniqueName db_unique_name_for_standby]]

--

primary : prid01

standby : prid02

We will run the command from the standby  server  prid02 as shown below.

dbca -silent -createDuplicateDB -gdbName psfp -primaryDBConnectionString prid01:1521/pfspsb -sid psfp -createAsStandby -dbUniqueName psfpsb

enter sys user password

once done connect standby  and check the role.

select database_role from v$database:;

now the sp file and orapwd file automatically created.

tnsnames.ora

add the standby and prod tnsnames files

Databroker configuation

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> quit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@prod01 archivelog]$ dgmgrl

DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Mar 1914:47:27 2019

connect /

DGMGRL> create configuration 'psfp_dg'

> as primary database is 'psfp'

> connect identifier is 'psfpsb';

DGMGRL> add database 'psfpsb' as connect identifier is 'psfpsb';

DGMGRL> enable configuration;
Enabled.

Create the Standby Redo Log Files on the primary database

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oradata/psfp/redo03.log
/u01/app/oradata/psfp/redo02.log
/u01/app/oradata/psfp/redo01.log

SQL> select bytes/1048576 from v$log;

BYTES/1048576
-------------
     200
     200
     200


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.


Create the Standby Redo Log Files on the standby database



DGMGRL> connect /
Connected to "psfp"
Connected as SYSDG.

DGMGRL> edit database 'psfp_sb' set state='APPLY-OFF';
Succeeded.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size          8619984 bytes
Variable Size           436209712 bytes
Database Buffers   721420288 bytes
Redo Buffers              8155136 bytes
Database mounted.

SQL>  alter database add standby logfile '/u01/app/oradata/psfp/standy_redo1.log' size 200m;

Database altered.


SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo2.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo3.log' size 200m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oradata/psfp/standy_redo4.log' size 200m;

Database altered.

SQL> alter database open;

Database altered.

SQL>


Verify the Data Guard Configuration



DGMGRL> edit database 'psfp_sb' set state='APPLY-ON';
Succeeded.


DGMGRL> show configuration;

Configuration - psfp_dg

 Protection Mode: MaxPerformance

 psfp    - Primary database
   psfp_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 8 seconds ago)


Set the property StaticConnectIdentifier to prevent errors during switchover operations



Edit database ‘psfp’ set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit database ‘psfp_sb’ set property StaticConnectIdentifier=StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prid02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=psfp_sb_DGMGRL)(INSTANCE_NAME=psfp)(SERVER=DEDICATED)))';
Edit listener.ora on primary database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Edit listener.ora on standby database host and add the lines shown below. Reload the listener.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = psfp_sb_DGMGRL)
      (SID_NAME = psfp)
        )
  )



Enjoy

failover testing

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- RDP    READ WRITE           PRIMARY

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered.

select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED ---------- -------------------- -------------------- ---------        491 01-OCT-19 02-OCT-19 NO        491 01-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 NO

select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY

alter system archive log current;

alter database commit to switchover to standby; database altered; or alter database commit to switchover to standby with session shutdown;

shut immedite

startup nomount

alter database mount standby database; database altered;

alter database recover managed standby database disconnect from session;

alter system set log_archive_dest_state_2=defer;

Standby database  SDEFR2PB02

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- PRD    MOUNTED              PHYSICAL STANDBY

alter database recover managed standby database cancel;

alter database commit to switchover to primary with session shutdown; or alter database commit to switchover to primary;

shut immediate

startup mount

Alter database open; database altered.

SQL> select INSTANCE_NAME,name,open_mode,database_role from v$database,v$instance; INSTANCE_NAME    NAME      OPEN_MODE            DATABASE_ROLE ---------------- --------- -------------------- ---------------- RDSBY        PRD    READ WRITE           PRIMARY SQL> ! [oracle@SDEFRB02 ~]$ date Wed Oct  2 09:12:57 CEST 2019

Alter system switch logfile

SQL> alter system switch logfile   2  ; System altered. SQL> ! [oracle@SDEFB02 ~]$ date Wed Oct  2 09:15:53 CEST 2019

CONNECTIVITY CHECKING BY O & M TEAM

[oracle@SDEFB02 ~]$ sqlplus SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 2 09:16:37 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle.  All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> show user; USER is "GBO_"

database switch over

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.

-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the original standby database issue the following commands.

-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

create of physical standby through active database

source and target database should be same configuration



primary





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



NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME

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

Ram    READ WRITE           PRIMARY          Ram





SQL> select force_logging from v$database;



FORCE_LOGGING

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

NO







SQL> ALTER DATABASE FORCE LOGGING;



Database altered.



SQL> select force_logging from v$database;



FORCE_LOGGING

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

YES





[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$ rm hc_apac.dat

[oracle@rac1 dbs]$ ls -ltr

total 20

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Jan  6 22:46 lkW5005PR

-rw-r----- 1 oracle oinstall 1536 Jan  6 22:47 orapww5005pr

-rw-r----- 1 oracle oinstall   41 Jan  6 22:48 initw5005pr.ora

-rw-rw---- 1 oracle oinstall 1544 Jan  6 22:48 hc_w5005pr.dat

[oracle@rac1 dbs]$





configure standby redolog on primary



SQL> set lines 180

col MEMBER for a60

select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;SQL> SQL>



   THREAD#     GROUP# MEMBER                                                            BYTES

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

         1          3 +DATA01/Ram/ONLINELOG/group_3.259.1018027407               209715200

         1          3 +FLASH01/Ram/ONLINELOG/group_3.1590.1018027409             209715200

         1          2 +DATA01/Ram/ONLINELOG/group_2.267.1018027407               209715200

         1          2 +FLASH01/Ram/ONLINELOG/group_2.1589.1018027409             209715200

         1          1 +DATA01/Ram/ONLINELOG/group_1.260.1018027407               209715200

         1          1 +FLASH01/Ram/ONLINELOG/group_1.1591.1018027409             209715200



6 rows selected.





ALTER DATABASE ADD standby logfile thread 1 group 4 ( '+DATA01','+FLASH01') SIZE 200m;



SQL> ALTER DATABASE ADD standby logfile thread 1 group 5 ('+DATA01','+FLASH01') size 200m;



Database altered.



SQL> ALTER DATABASE ADD standby logfile thread 1 group 6 ('+DATA01','+FLASH01') size 200m;



Database altered.



SQL>  ALTER DATABASE ADD standby logfile thread 1 group 7 ('+DATA01','+FLASH01') size 200m;



Database altered.





SQL> show parameter pfile;



NAME       TYPE         VALUE

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

spfile     string       +DATA/w5005pr/spfilew5005pr.ora



SQL> create pfile='/home/oracle/initw5005pr.ora.bkp' from spfile;



File created.



SQL> alter system set db_unique_name='Ram' scope=spfile;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(Ram,RamSBY)' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RamSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RamSBY' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;



System altered.



SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;



System altered.



SQL> ALTER SYSTEM SET fal_client=Ram scope=both;



System altered.



SQL> ALTER SYSTEM SET fal_server=RamSBY scope=both;



System altered.



SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;



System altered.



SQL> create pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRam' from spfile;



File created.



SQL>





Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.

The best practice for this is to copy the passwordfile as suggested.

The password file name must match the instance name/SID used at the standby site, not the DB_NAME





copy the initora file to standylocation and change it accordingly.



prod pfile

Ram.__data_transfer_cache_size=0

Ram.__db_cache_size=22817013760

Ram.__inmemory_ext_roarea=0

Ram.__inmemory_ext_rwarea=0

Ram.__java_pool_size=268435456

Ram.__large_pool_size=335544320

Ram.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment

Ram.__pga_aggregate_target=3422552064

Ram.__sga_target=26910654464

Ram.__shared_io_pool_size=536870912

Ram.__shared_pool_size=2885681152

Ram.__streams_pool_size=0

*.audit_file_dest='/oraclebase/app/oracle/admin/Ram/adump'

*.audit_trail='db'

*.compatible='18.0.0'

*.control_files='+DATA01/Ram/CONTROLFILE/current.261.1018026219','+FLASH01/Ram/CONTROLFILE/current.256.1018026219'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA01'

*.db_create_online_log_dest_1='+DATA01'

*.db_create_online_log_dest_2='+FLASH01'

*.db_name='Ram'

*.db_recovery_file_dest_size=100006m

*.db_recovery_file_dest=''

*.db_unique_name='Ram'

*.diagnostic_dest='/oraclebase/app/oracle'

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

*.fal_client='Ram'

*.fal_server='RamSBY'

*.local_listener='LISTENER_Ram'

*.log_archive_config='DG_CONFIG=(Ram,RamSBY)'

*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram'

*.log_archive_dest_2='SERVICE=RamSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RamSBY'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=3201m

*.processes=1280

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=25602m

*.standby_file_management='AUTO'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'





standby pfile





RamSBY.__data_transfer_cache_size=0

RamSBY.__db_cache_size=22817013760

RamSBY.__inmemory_ext_roarea=0

RamSBY.__inmemory_ext_rwarea=0

RamSBY.__java_pool_size=268435456

RamSBY.__large_pool_size=335544320

RamSBY.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment

RamSBY.__pga_aggregate_target=3422552064

RamSBY.__sga_target=26910654464

RamSBY.__shared_io_pool_size=536870912

RamSBY.__shared_pool_size=2885681152

RamSBY.__streams_pool_size=0

*.audit_file_dest='/oraclebase/app/oracle/admin/RamSBY/adump'

*.audit_trail='db'

*.compatible='18.0.0'

*.control_files='+DATA01','+FLASH01'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA01'

*.db_create_online_log_dest_1='+DATA01'

*.db_create_online_log_dest_2='+FLASH01'

*.db_name='Ram'

*.db_recovery_file_dest_size=100006m

*.db_recovery_file_dest=''

*.db_unique_name='RamSBY'

*.diagnostic_dest='/oraclebase/app/oracle'

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

*.fal_client='RamSBY'

*.fal_server='Ram'

*.local_listener='LISTENER_RamSBY'

*.log_archive_config='DG_CONFIG=(RamSBY,Ram)'

*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RamSBY'

*.log_archive_dest_2='SERVICE=Ram LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Ram'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='DEFER'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=3201m

*.processes=1280

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=25602m

*.standby_file_management='AUTO'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'





***********************************************







mkdir -p /u01/app/oracle/admin/Ramsby/adump





startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';









create spfile='+DATA01/RamSBY/spfileRamSBY.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';









startup nomount force;



show parameter pfile



SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;







--Example for two members

--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.log') SIZE 200M;


--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;



configure listener and tnsnames



srvctl modify scan_listener -p TCP:1523



srvctl modify listener -p TCP:1523



Verify connection ‘AS SYSDBA’ from Primary



Verify connection ‘AS SYSDBA’ from Standby



Run the duplicate from active database command from primary



rman target sys/ChangeMe2019@Ram auxiliary sys/ChangeMe2019@RamSBY

duplicate target database for standby from active database nofilenamecheck;

once completed


"alter database mount standby database;
database altered;"
alter database recover managed standby database disconnect from session;
alter system set log_archive_dest_state_2=defer;


on primary



select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;


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

SQL> alter system switch logfile;



System altered.



SQL> alter system switch logfile;



System altered.



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



MAX(SEQUENCE#)

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

            10



standyb





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



NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME

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

ddd   READ ONLY WITH APPLY PHYSICAL STANDBY standy



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



 SEQUENCE# FIRST_TIM NEXT_TIME

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

         6 07-JAN-16 07-JAN-16

         7 07-JAN-16 07-JAN-16

         8 07-JAN-16 07-JAN-16

         9 07-JAN-16 07-JAN-16

        10 07-JAN-16 07-JAN-16



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



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



SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;



System altered.



SQL> ALTER SYSTEM SET log_archive_dest_state_2 =enable';

ALTER SYSTEM SET log_archive_dest_state_2 =enable'

                                                 *

ERROR at line 1:

ORA-01756: quoted string not properly terminated





SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;



System altered.







[oracle@rac2 ~]$ which srvctl

/u01/app/oracle/product/11.2.0/db_1/bin/srvctl

[oracle@rac2 ~]$ srvctl add database -d w5005prg -o /u01/app/oracle/product/11.2.0/db_1/ -r physical_standby -s 'READ ONLY'

[oracle@rac2 ~]$ srvctl start database -d w5005prg



[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/crsctl stat res -t

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

NAME           TARGET  STATE        SERVER                   STATE_DETAILS

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

Local Resources

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





applying retention policy for rman on both primary and standby



CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

or


CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;



On Primary Site :
Connect with sys and :
SQL>Alter system set  "_log_deletion_policy" = 'ALL' scope=both;

The Archivelogs will be deleted on stanby site when a "Backup Database occurs in Primary Site" !!! To be confirmed



SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

dataguard log shipping problem

•  Print 
•  Email

How to stop and start the Log shipping? With this procedure, we can simulate a disaster crash at the main site.

For our example, we use PRD and PRD-STBY to differentiate the two sites, and SAP is installed in Windows.

1 - Stop the log shipping at the PRD Site

C:\>sqlplus / as sysdba

*** Do Some validations Infos
SQL> select name,open_mode from v$database;
PRD       READ WRITE

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

SQL> alter system switch logfile;

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

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch
VALID      LOG_ARCHIVE_DEST_2    prd_standby

SQL> show parameter LOG_ARCHIVE_DEST_2;
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby
SQL>

NOW DEACTIVATE THE LOG SHIPPING!!!!

SQL> alter system set log_archive_dest_state_2=defer scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

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

SQL> alter system switch logfile;

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

You can check the Alert Log of the DR Side, you will see which last is applied and see Network Error

2 - Login to the PRD-STBY Site

C:\> NOTEPAD E:\oracle\PRD\saptrace\background\alert_PRD.log

RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54276.1.834712720.DBF'
Primary database is in MAXIMUM PERFORMANCE mode
Sun Jun 19 12:33:32 2013
Media Recovery Log E:\ORACLE\PRD\SAPARCH\PRD_54268.1.834712720.DBF
Media Recovery Delayed for 240 minute(s) (thread 1 sequence 54269)
Sun Jun 19 12:51:27 2013
RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54277.1.834712720.DBF'
RFS[2]: Possible network disconnect with primary database

3 -  RE-START LOG SHIPPING

3.1 – Validate the Status of Standby Database.

3.2 – Validate the last available redo-log

DIR E:\oracle\PRD\saparch\*dbf (You will see only the last shipped)

3.3 – Activate the Log Shipping (Real Production Site-PRD)

C:\>sqlplus / as sysdba

*** Do Some validations Infos

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- ----------
PRD       READ WRITE

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

         54279

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

STATUS     DEST_NAME             DESTINATION
---------  ----------            ----------------------------------
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch

SQL> show parameter LOG_ARCHIVE_DEST_2;

NAME                   TYPE        VALUE
---------------------- ----------- ---------------------------------------------
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

NOW ACTIVATE THE LOG SHIPPING!!!!

SQL> alter system set log_archive_dest_state_2=enable scope=both;

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      ENABLE


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

SQL> alter system switch logfile;

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

3.4 – Validate the shipping logs and appliance in theStandby Site (PRD-STBY)

DIR E:\oracle\PRD\saparch\*dbf

3.5 – Rebuild Synchronization without Delay (You are logged into the PRD-STBY Site)

C:\> SQLPLUS / AS SYSDBA
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

3.6 – Validate the Status of Standby Database. (PRD_STBY)
 

3.7 – Rebuild Synchronization With 180mn Delay (3 hours for example)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 180 DISCONNECT FROM SESSION;
EXIT

And you are done!

HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

Step by Step Process to Resolved gap on Standby database.

Summary
1. Check the name and status of database.
2. Check for GAP on standby
3. Check redo received on standby
4. Check redo applied on standby
5. Identify missing archive log files
6. Copy archive log files
7. Register archive log files with standby
8. Restart the managed recovery operations

******************************************************************************************************************

Step 1 : Check the status of database on both server.
On Primary Server.
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   READ WRITE PRIMARY

SQL> set sqlprompt “PRIMARY’@’_connect_identifier>”
PRIMARY@MYDB>

On Standby Server.

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   MOUNTED    PHYSICAL STANDBY

SQL> set sqlprompt “SECONDARY’@’_connect_identifier>”
STANDBY@MYDB>

Step 2 : Check for GAP on Standby
PRIMARY@MYDB>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
         76921
STANDBY@MYDB>select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
         76921

STANDBY@MYDB>SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
 FROM V$ARCHIVED_LOG
          WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
          ORDER BY 1;
    Thread Last Sequence Generated
———- ———————–
         1                   76921



Step 3 & 4: Check redo received and applied on standby.

STANDBY@MYDB> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”,
APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
    Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
         1                  76922                 20931      55991

Step 5: Identify the missing archive log file.

 STANDBY@MYDB>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected

—-If found gap

Step 6: Copy missing archive log file
After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:
PRIMARY@MYDB>  SELECT name
 FROM v$archived_log
 WHERE thread# = 1
 AND dest_id = 1
 AND sequence# BETWEEN 20931 and 76922;
Output:
/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc
56027 rows selected.

Step 7: Register archive logfile with standby.
Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE … SQL statement on the physical standby database.
For example:
STANDBY@MYDB> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;

Step 8: Restart the managed recovery operations.
— After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations.
  For example, to put the physical standby database into automatic recovery managed mode:
STANDBY@MYDB> alter database recover managed standby database disconnect from session;

Open Standby in Read-write Mode When Primary is Lost

Open Standby in Read-write Mode When Primary is Lost
There may be scenario  where Primary database is lost and we are only left with the standby database . In this scenario's we have to open the standby database in read-write mode. Below are the steps to convert standby database to Primary database.

1.)  Open standby database in mount state :
SQL> select name,open_mode from v$database;
NAME       OPEN_MODE
------     -------------
NOIDA      READ ONLY

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
 
Total System Global Area      263639040  bytes
Fixed Size                             1373964      bytes
Variable Size                         230689012  bytes
Database Buffers                  25165824     bytes
Redo Buffers                        6410240       bytes
Database mounted.

SQL> select open_mode ,protection_mode , database_role from v$database ;
OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------        ----------------------       ----------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY

2.) Recover if there is any archive logs:

SQL>recover standby database;
ORA-01153: an incompatible media recovery is active

To solve this issue, we cancel the media recovery by using the below command .

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> recover standby database
ORA-00279: change 2698969 generated at 10/05/2011 16:46:58 needed for thread
ORA-00289: suggestion : D:\ARCHIVE\ARC0000000133_0761068614.0001
ORA-00280: change 2698969 for thread 1 is in sequence #133

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

3.) Finish the Recovery process : 
The below command will perform the role transition as quickly as possible with little or no data loss and without rendering other standby databases unusable and to open the database in read-write mode we fire the below command :

SQL>alter database recover managed standby database finish;
Database altered.

4.) Activate the Standby Database :

SQL> alter database activate physical standby database ;
Database altered.

5.) Check the new status

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE     PROTECTION_MODE           DATABASE_ROLE
---------         ----------------------      ---------------------
MOUNTED       MAXIMUM PERFORMANCE    PHYSICAL STANDBY


6.) Open the Database
SQL> alter database open ;
Database altered.

SQL> select open_mode ,protection_mode , database_role from v$database ;

OPEN_MODE       PROTECTION_MODE             DATABASE_ROLE
---------           ----------------------        --------------------
READ WRITE       MAXIMUM PERFORMANCE      PHYSICAL STANDBY



or


Shutdown immediate

Startup mount

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

select switchover_status from v$database;

 ALTER DATABASE ACTIVATE STANDBY DATABASE;

 select open_mode ,protection_mode , database_role from v$database ;








Enjoy   :-)