Changing dataguard mode

 Changing Dataguard mode from Maximum performance mode to maximum availability

alter system set log_archive_dest_2='SERVICE=oradb_s2 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 REOPEN=50 DB_UNIQUE_NAME=oradb_s2';

SERVICE=oradb_s2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_s2

NET_TIMEOUT – Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN – Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 50 seconds.

Note : Shut down the primary database and restart it in mounted mode if the protection mode is being set to Maximum Protection or being changed from Maximum Performance to Maximum Availability. If the primary database is an Oracle Real Applications Cluster, shut down all of the instances and then start and mount a single instance.

Now, shutdown database :

STARTUP MOUNT

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

alter database open;

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

alter system dump logfile '/u01/app/oracle/fra/ORADB_S2/onlinelog/o1_mf_5_ho2pm6m3_.log' validate;

PR00 (PID:16647): Media Recovery Log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

Errors with log /u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc

PR00 (PID:16647): MRP0: Background Media Recovery terminated with error 328

2022-01-27T16:58:26.816034+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_16647.trc:

ORA-00328: archived log ends at change 3830620, need later change 4142985

ORA-00334: archived log: '/u01/app/oracle/fra/ORADB_S2/archivelog/2022_01_27/o1_mf_1_124_jz4w8rdo_.arc'

PR00 (PID:16647): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

col name for a80

col thread# for 99

col sequence# for 9999

col archived for a5

col status for a10

select name, thread#, sequence#, archived, applied, status from v$archived_log where 4142985 between FIRST_CHANGE# and NEXT_CHANGE#;

select name, thread#, sequence#, archived, applied, status from v$archived_log where 3830620 between FIRST_CHANGE# and NEXT_CHANGE#;

The standby database is not-in-sync after converting maximum performance mode to maximum availability with the above error

Lets do roll forward for this , as we have no solution and will see the result..

what happens at DR side if space is exhausted during tablespace creation at primary side

oradb--> primary db

oradb_s2 -->Standby db

SQL> CREATE BIGFILE TABLESPACE TESTTBS2 DATAFILE '/backup/testtbs02.dbf' SIZE 200M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Tablespace created.

SQL> show parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      oradb.localdomain     

The below is the alert log from DR side, when space is exhausted at DR side while tablespace creation at primary side. 

WARNING: File being created with same name as in Primary

Existing file may be overwritten

2022-01-07T22:09:20.122725+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

2022-01-07T22:09:20.179041+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-19502: write error on file "/backup/testtbs02.dbf", block number 12288 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 12288

Additional information: 237568

File #14 added to control file as 'UNNAMED00014'.

Originally created as:

'/backup/testtbs02.dbf'

Recovery was unable to create the file as:

'/backup/testtbs02.dbf'

PR00 (PID:4229): MRP0: Background Media Recovery terminated with error 1274

2022-01-07T22:09:20.343651+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

PR00 (PID:4229): Managed Standby Recovery not using Real Time Apply

2022-01-07T22:09:22.638703+04:00

Recovery interrupted!

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

Recovered data files to a consistent state at change 3262529

stopping change tracking

2022-01-07T22:09:23.132280+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_4229.trc:

ORA-01274: cannot add data file that was originally created as '/backup/testtbs02.dbf'

2022-01-07T22:09:23.184841+04:00

Background Media Recovery process shutdown (oradb_s2)

Again if we forcefully starting the MRP, it is terminating , below is the log from alert log.

alter database recover managed standby database disconnect

2022-01-07T22:17:03.094994+04:00

Attempt to start background Managed Standby Recovery process (oradb_s2)

Starting background process MRP0

2022-01-07T22:17:03.116141+04:00

MRP0 started with pid=60, OS id=5031

2022-01-07T22:17:03.118382+04:00

Background Managed Standby Recovery process started (oradb_s2)

2022-01-07T22:17:08.146408+04:00

 Started logmerger process

2022-01-07T22:17:08.164904+04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

PR00 (PID:5037): Managed Standby Recovery starting Real Time Apply

2022-01-07T22:17:08.216985+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01186: file 14 failed verification tests

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

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

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.217231+04:00

File 14 not verified due to error ORA-01157

2022-01-07T22:17:08.218311+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_dbw0_1947.trc:

ORA-01157: cannot identify/lock data file 203 - see DBWR trace file

ORA-01110: data file 203: '/u01/app/oracle/oradata/ORADB/pdb1/temp01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

2022-01-07T22:17:08.431502+04:00

max_pdb is 3

PR00 (PID:5037): MRP0: Background Media Recovery terminated with error 1111

2022-01-07T22:17:08.462884+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_pr00_5037.trc:

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

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

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

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

PR00 (PID:5037): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2022-01-07T22:17:08.626152+04:00

Recovery Slave PR00 previously exited with exception 1111

2022-01-07T22:17:08.674098+04:00

Errors in file /u01/app/oracle/diag/rdbms/oradb_s2/oradb_s2/trace/oradb_s2_mrp0_5031.trc:

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

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

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

ORA-01110: data file 14: '/u01/app/oracle/product/19.0.0/db_1/dbs/UNNAMED00014'

2022-01-07T22:17:08.674347+04:00

Background Media Recovery process shutdown (oradb_s2)

2022-01-07T22:17:09.182195+04:00

Completed: alter database recover managed standby database disconnect

DATAGUARD

 DATAGUARD

select name,db_unique_name,open_mode,log_mode,database_role from v$database;

alter database recover managed standby database disconnect;

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#;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby;

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where STATUS='IDLE';

select PROCESS, PID, STATUS, THREAD#, SEQUENCE# from gv$managed_standby where PROCESS='MRP0';

set lines 555

col DEST_NAME for a30

 col DESTINATION for a30

col ERROR for a30

 select INST_ID,DEST_ID,DEST_NAME,DESTINATION,ERROR,STATUS from gv$archive_dest where DESTINATION IS NOT NULL;