Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore. Please note that this site uses cookies.

Updated: 4 hours 16 min ago

SQL Server Migration Assistant for Oracle

Sat, 2021-11-20 08:08

 Here's a Video Demo of the SQL Server Migration Assistant for Oracle   https://youtu.be/zNTF1ncr45g  


The tool is available for download here

Categories: DBA Blogs

Indexing Documents in Oracle

Sun, 2021-11-14 03:27

 In the previous post, I had demonstrated how to load documents into an Oracle Database.  

(Note : I am referring to documents as in PDF files, PowerPoint presentations, Word documents and text files in the traditional sense, not "JSON documents").

Here I demonstrate how to use Oracle Text to create indexes and make such documents searchable.

I assume that Oracle Text (the "CTXSYS" schema) has been installed when the database was created.

First, I grant the appropriate privileges.



SQL> alter session set container=pdb1;

Session altered.

SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MYDOCDB;

Grant succeeded.

SQL>


Next, I create a CONTEXT Index on my documents table.

SQL> connect mydocdb/mydocdb@pdb1
Connected.
SQL> CREATE INDEX my_documents_ctx_idx ON my_documents(file_content)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /

Index created.

SQL>


My documents are now indexed and searchable. The syntax for a context index query is slightly different.

SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'DBA Role') > 0
4 /

DOC_TITLE DOC_DESCRIPTION FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Security Security Mechanisms in Oracle 12c PDF
Monitoring and Diagnostics without OEM Presentation at Oracle SOS Conference PDF

SQL>
SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'sqlplus hemant/hemant@orclpdb1') > 0
4 /

DOC_TITLE DOC_DESCRIPTION FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback Database and DG DR Testing How to use Flasback Database in DataGuard DOCX
for DR Testing


SQL>
SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'Flex ASM') > 0
4 /

DOC_TITLE DOC_DESCRIPTION FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Availability High Availability Options in Oracle 12c PDF

SQL>
SQL>
SQL> -- unfortunately, some queries on my plain-text SQL file don't work as expected
SQL>
SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'doc_number number generated always as identity,') > 0
4 /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 48


SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'doc_number number generated always as identity') > 0
4 /

no rows selected

SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'doc_number number generated always') > 0
4 /

no rows selected

SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, '(doc_number number generated always') > 0
4 /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 36


SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'drop table my_documents') > 0
4 /

no rows selected

SQL>


Also, apparently the inclusion of "(" and "," and such are tricky when creatimg a query.

Note : The "CONTAINS ... >0" clause is to check the SCORE returned by the filter in the CONTAINS section and to see that it is greater than zero.

Here I have created a CTXSYS.CONTEXT index and it works well for the PDF and DOCX files.  This type of Index is *not* automatically updated when you load a new document.  To update the Index you must call the "CTX_DDL.SYNC_INDEX" procedure.

Using the same method as in my previous blog post, I load a new PPT file (callled "Flashback.ppt").


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 14 17:04:55 2021
Version 19.12.0.0.0

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

Path used: Conventional
Commit point reached - logical record count 1

Table MY_DOCUMENTS:
1 Row successfully loaded.

Check the log file:
my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


And then sync the index and query again

SQL> select doc_number, doc_title, file_type
2 from my_documents
3 order by doc_number
4 /

DOC_NUMBER DOC_TITLE FILE_TYPE
---------- ------------------------------------------ ----------------
1 Oracle 12c Availability PDF
2 Oracle 12c Security PDF
3 Monitoring and Diagnostics without OEM PDF
4 Setting up Data Guard in 11gR2 DOCX
5 Flashback Database and DG DR Testing DOCX
6 Create Docs Table SQL
7 Flashback PPT

7 rows selected.

SQL> exec CTX_DDL.SYNC_INDEX('my_documents_ctx_idx','4M');

PL/SQL procedure successfully completed.

SQL>
SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'Thomas Kyte') > 0
4 /

DOC_TITLE DOC_DESCRIPTION FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback Tom Kyte's Presentation on Flashback PPT

SQL> select doc_title, doc_description, file_type
2 from my_documents
3 where contains(file_content, 'Rowids therefore will change') > 0
4 /

DOC_TITLE DOC_DESCRIPTION FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback Tom Kyte's Presentation on Flashback PPT

SQL>


The call to CTX_DDL.SYNC_INDEX contains a memory parameter '4M' specifying the memory to be used for synchronization.

Besides the CTXSYS.CONTEXT IndexType, Oracle also allows a "CTXSYS.CTXCAT" IndexType which can be used to index small documents or text fragements.  The CTXSYS.CTXCAT Index is updated synchronously when a new document is loaded into the table (i.e. a call to CTX_DDL.SYNC_INDEX is not required).

Typically, with a document database with moderate to large documents, you would use CTXSYS.CONTEXT IndexType and configure a scheduler job to call CTX_DDL.SYNC_INDEX periodically (e.g. every 30minutes or every 4hours) to update the Index for new documents that are loaded infrequently and do not need to be searched immediately.

The IndexType automically excludes certain popular words called "STOPWORD"s that may be in "STOPLIST"s.  These are very commonly used words that do not need to be indexed (e.g. words like "the" or "this" or "that".  Such STOPLISTs can be customised as well.
So, your implementation of the indexes is customisable.


See the Oracle Text Application Developers Guide and  Oracle Text Reference for more examples and a detailed study.


Categories: DBA Blogs

Loading Documents into Oracle

Sun, 2021-11-07 03:17
A short demonstration of loading documents into an Oracle Database.

SQL> @create_docs_table
SQL> spool create_docs_table
SQL>
SQL> drop table my_documents purge;

Table dropped.

SQL> create table my_documents
2 (doc_number number generated always as identity,
3 doc_title varchar2(100),
4 doc_description varchar2(256),
5 os_file_name varchar2(256),
6 file_content blob,
7 file_type varchar2(16))
8 lob(file_content) store as securefile (compress high keep_duplicates)
9 /

Table created.

SQL>
SQL> spool off
SQL>


I have created table with a SecureFile LOB to hold the documents.
Next, I use sqlloader to load the documents, based on a "list file".


C:\load_docs_to_oracle>type List_of_Files_to_Upload.csv
Oracle 12c Availability,High Availability Options in Oracle 12c,12c_Availability.pdf,PDF
Oracle 12c Security,Security Mechanisms in Oracle 12c,12c_Security.pdf,PDF
Monitoring and Diagnostics without OEM,Presentation at Oracle SOS Conference,Monitoring and Diagnostics without OEM.pdf,PDF
Setting up Data Guard in 11gR2,My document on DG setup in 11gR2 ,Setting up DataGuard in 11gR2.docx,DOCX
Flashback Database and DG DR Testing,How to use Flasback Database in DataGuard for DR Testing,Using FLASHBACK DATABASE for Destructive DR Testing.docx,DOCX
Create Docs Table,Source Code for Script to create DOCS Table,create_docs_table.sql,SQL

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.ctl
load data
infile 'List_of_Files_to_Upload.csv'
into table my_documents
fields terminated by ','
(
doc_title ,
doc_description ,
os_file_name,
file_content lobfile(os_file_name) terminated by EOF,
file_type
)

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Path used: Conventional
Commit point reached - logical record count 6

Table MY_DOCUMENTS:
6 Rows successfully loaded.

Check the log file:
my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.log

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Control File: my_documents.ctl
Data File: List_of_Files_to_Upload.csv
Bad File: List_of_Files_to_Upload.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional

Table MY_DOCUMENTS, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_TITLE FIRST * , CHARACTER
DOC_DESCRIPTION NEXT * , CHARACTER
OS_FILE_NAME NEXT * , CHARACTER
FILE_CONTENT DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field OS_FILE_NAME
FILE_TYPE NEXT * , CHARACTER


Table MY_DOCUMENTS:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 258000 bytes(250 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Nov 07 17:01:33 2021
Run ended on Sun Nov 07 17:01:35 2021

Elapsed time was: 00:00:01.17
CPU time was: 00:00:00.06

C:\load_docs_to_oracle>



Note : In "List_of_Files_to_Upload.csv" the incoming file names are *not* enclosed in quotes and there is no space-character before the filename.

Once you have loaded the documents, SQLDeveloper gives you the option of either opening the file (e.g. PDF files opened by Acrobat Reader) or downloading the file that you query.  (In my installation, DOCX and SQL files have to be downloaded for viewing as the Exernal Editor is not configured for these file-extensions -- the DOCX is identified as a "jar" file).

[click on the images below to view them better]

SQL Developer view




External Editor Preferences in SQL Developer







Categories: DBA Blogs

My Posts on Standby Database[s] -- Data Guard

Sun, 2021-10-17 04:10
Although I have, in previous posts, covered Data Guard in 12c (and 12cR1 RAC), this is a list of recent Blog Posts on Data Guard. 





















Categories: DBA Blogs

My Posts on Database Flashback

Sun, 2021-10-17 03:43

 

My Blog Posts on Database Flashback

(The first 5 demonstrations are in 11gR2.  The last demonstration is in 19c)


(not in the series)



Categories: DBA Blogs

My Posts on Parallel Execution

Sun, 2021-10-17 03:28
Categories: DBA Blogs

Download Oracle Database Virtual Box / Vagrant / Docker Images

Sat, 2021-10-02 22:09

 A few websites where you can download Oracle Database images for practice at home under single user licences

Prebuilt Oracle Virtual Box VMs from Oracle's download site

Oracle's Vagrant Projects on GitHub

Oracle's Database Examples on GitHub

Oracle-Base (Tim Hall) GitHub  (Docker and Vagrant)


Categories: DBA Blogs

Resuming Standby Databases after a RESETLOGS (without RECOVER DATABASE using Online Redo Logs !!) at the Primary

Sun, 2021-09-26 10:30

 Supposing that you lose the Online Redo Logs of the Primary [after it has been SHUTDOWN NORMAL/IMMEDIATE] and you have to  OPEN RESETLOGS, how do you handle the Standbys ?

This scenario demonstrated below works only if you did not have to issue a RESTORE DATABASE and RECOVER DATABASE at the Primary.  In such a case you would have to FLASHBACK all the Standbys to a time/SCN earlier than the Recovery time OR you'd have to Rebuild the Standbys.

In my demonstration below, the RECOVER DATABASE was only from the ArchiveLogs that have already been shipped or applied to the Standby -- upto Sequence#505You must verify that the last active Online Redo Log at the Primary has been Archived and Shipped (not necessarily applied) to the Standby[s] before you release your Primary for storage/server maintenance.  If they were not Shipped automatically, you can manually copy them to the Standbys and use the ALTER DATABASE REGISTER LOGFILE command at the Standbys,

Let's assume that the Primary had been shutdown for storage/server maintenance  (but the Standbys were yet running).  The Standby's are *not* shutdown in my scenario.

Then, during the storage/server maintenance, the disk/filesystem for the  Online Redo Logs was lost/corrupt.  This necessitated an OPEN RESETLOGS.

The key thing is than an OPEN RESETLOGS creates a new Incarnation of the database.  What happens at the Standbys subsequently ?

Let's assume that the Primary created a Controlfile Autobackup and then did SHUTDOWN IMMEDIATE before the storage/server maintenance.



Starting Control File and SPFILE Autobackup at 26-SEP-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-SEP-21

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:34:25 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

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


entries from the alert log show that SEQUENCE#505 was the last Online Redo Log that was active
and was also archived at shutdown (as is done in recent versions)

2021-09-26T21:33:34.741801+08:00
Thread 1 advanced to log sequence 505 (LGWR switch), current SCN: 15237281
Current log# 1 seq# 505 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-09-26T21:33:35.533318+08:00
NET (PID:13253): Archived Log entry 1295 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:34:40.355271+08:00
Shutting down ORACLE instance (immediate) (OS id: 13897)
...
...
ALTER DATABASE CLOSE NORMAL
Stopping Emon pool
2021-09-26T21:34:54.826891+08:00
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
2021-09-26T21:34:55.811566+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
Stopping Emon pool
Closing sequence subsystem (1373431005).
2021-09-26T21:34:56.082543+08:00
Stopping change tracking
2021-09-26T21:35:00.702496+08:00
TT04 (PID:4521): Shutdown in progress, stop CF update
2021-09-26T21:35:01.439450+08:00
Thread 1 advanced to log sequence 506 (thread close)
2021-09-26T21:35:01.537622+08:00
LGWR (PID:4387): Waiting for ORLs to be archived
2021-09-26T21:35:01.694980+08:00
ARC0 (PID:4457): Archived Log entry 1298 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:01.709616+08:00
LGWR (PID:4387): ORLs successfully archived
2021-09-26T21:35:02.711736+08:00
Shutting down archive processes
2021-09-26T21:35:02.712053+08:00
TT00 (PID:4453): Gap Manager exiting
2021-09-26T21:35:03.712018+08:00
Archiving is disabled
2021-09-26T21:35:03.712297+08:00
ARC3 (PID:4464): ARCH shutting down




However, it so happens that the Online Redo Logs are "lost" during server/storage maintenance.  Maybe, even the Controlfiles are "lost".  I decide to RESTORE CONTROLFILE and  to OPEN RESETLOGS.
In the  commands below, I deliberately use the SQL*Plus command line so that I can show the ArchiveLog Sequences#s that are applied before I can OPEN RESETLOGS  (RMAN command line would hide the details) and I know that I can CANCEL and stop after Sequence#505 

oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:39:59 2021
Version 19.12.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1207955544 bytes
Fixed Size 9134168 bytes
Variable Size 872415232 bytes
Database Buffers 318767104 bytes
Redo Buffers 7639040 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 26 21:40:34 2021
Version 19.12.0.0.0

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

connected to target database: ORCLCDB (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 26-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK

recovery area destination: /opt/oracle/FRA/ORCLCDB
database name (or database unique name) used for search: ORCLCDB
channel ORA_DISK_1: AUTOBACKUP /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/opt/oracle/oradata/ORCLCDB/control01.ctl
output file name=/opt/oracle/oradata/ORCLCDB/control02.ctl
Finished restore at 26-SEP-21

RMAN>
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/26/2021 21:41:17
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/26/2021 21:41:35
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:54:27 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15237057 generated at 09/26/2021 21:28:26 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_503_1036108814.dbf
ORA-00280: change 15237057 for thread 1 is in sequence #503


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15237253 generated at 09/26/2021 21:33:26 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_504_1036108814.dbf
ORA-00280: change 15237253 for thread 1 is in sequence #504
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_503_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15237281 generated at 09/26/2021 21:33:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_505_1036108814.dbf
ORA-00280: change 15237281 for thread 1 is in sequence #505
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_504_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15238147 generated at 09/26/2021 21:34:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_506_1036108814.dbf
ORA-00280: change 15238147 for thread 1 is in sequence #506
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_505_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> set pages60
SQL> set linesize132
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI STATUS RESETLOGS_ID PRIOR_INCARNATION#
------------ ----------------- ------------------ ----------------------- ------------------ ------- ------------ ------------------
FLASHBACK_DATABASE_ALLOWED CON_ID
-------------------------- ----------
1 1 17-APR-19 00:55:59 0 PARENT 1005785759 0
NO 0

2 1920977 04-MAY-19 23:21:26 1 17-APR-19 00:55:59 PARENT 1007421686 1
NO 0

3 4797184 27-MAR-20 00:00:14 1920977 04-MAY-19 23:21:26 PARENT 1036108814 2
NO 0

4 15238148 26-SEP-21 22:00:48 4797184 27-MAR-20 00:00:14 CURRENT 1084312848 3
NO 0


SQL>


So, my Primary Database has now switched to INCARNATION#4 with a RESETLOGS as of 26-Sep-21 22:00:48 with RESETLOGS_ID "1084312848"

Let me check my Standby alert logs.
First, STDBYDB which shows that Sequence#505 was applied (ie. "Recovery of Onlin Redo Log ... Seq 505 .." message completed by showing the next message "Media Recovery Waiting for T-1.5-506" 


2021-09-26T21:33:28.002521+08:00
rfs (PID:4667): Standby controlfile consistent with primary
2021-09-26T21:33:28.339263+08:00
rfs (PID:4667): Selected LNO:4 for T-1.S-504 dbid 2778483057 branch 1036108814
2021-09-26T21:33:28.667001+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 504 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T21:33:33.555294+08:00
ARC3 (PID:3201): Archived Log entry 108 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:33:33.827814+08:00
PR00 (PID:3847): Media Recovery Waiting for T-1.S-505
2021-09-26T21:33:33.991340+08:00
rfs (PID:4667): Standby controlfile consistent with primary
2021-09-26T21:33:34.547950+08:00
rfs (PID:4667): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:33:35.283450+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 505 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T21:35:00.057442+08:00
ARC1 (PID:3197): Archived Log entry 109 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:00.289539+08:00
PR00 (PID:3847): Media Recovery Waiting for T-1.S-506
2021-09-26T21:35:02.732262+08:00
rfs (PID:4502): Possible network disconnect with primary database


At this point, 21:35:02, it shows a "network disconnect with the primary database" which is when the Primary had fnished archiving Sequence#505 and was continuing the shutdown.
Subsequently, when the Primary goes through the OPEN RESETLOGS, the Standby shows 
[note the "rfs (PID:20837): A new recovery destination branch has been registered" and " rfs (PID:20837): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848" and "rfs (PID:20837): New Archival REDO Branch(resetlogs_id): 1084312848 Prior: 1036108814" messages" which match the RESETLOGS_ID information from the Primary's v$database_incarnation (listed above)  :

2021-09-26T22:01:32.924024+08:00
rfs (PID:20634): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:14755)
2021-09-26T22:01:44.591773+08:00
rfs (PID:20820): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is SYNC (PID:14565)
rfs (PID:20820): New archival redo branch: 1084312848 current: 1036108814
rfs (PID:20820): Primary database is in MAXIMUM AVAILABILITY mode
rfs (PID:20820): Changing standby controlfile to RESYNCHRONIZATION level
rfs (PID:20820): Standby controlfile consistent with primary
rfs (PID:20820): No SRLs available for T-1
Clearing online log 5 of thread 1 sequence number 0
2021-09-26T22:01:49.532864+08:00
rfs (PID:20820): Selected LNO:5 for T-1.S-3 dbid 2778483057 branch 1084312848
2021-09-26T22:01:49.535634+08:00
Clearing online log 4 of thread 1 sequence number 0
2021-09-26T22:01:56.788073+08:00
rfs (PID:20837): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14751)
2021-09-26T22:01:59.406342+08:00
rfs (PID:20837): Selected LNO:4 for T-1.S-2 dbid 2778483057 branch 1084312848
2021-09-26T22:02:00.964530+08:00
rfs (PID:20837): A new recovery destination branch has been registered
rfs (PID:20837): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848
rfs (PID:20837): Incomplete Recovery SCN:0x0000000000e88403
rfs (PID:20837): Resetlogs SCN:0x0000000000e88404
rfs (PID:20837): SBPS:0x00000000004c4f04
rfs (PID:20837): New Archival REDO Branch(resetlogs_id): 1084312848 Prior: 1036108814
rfs (PID:20837): Archival Activation ID: 0xaa3191f1 Current: 0xa7521ccd
rfs (PID:20837): Effect of primary database OPEN RESETLOGS
rfs (PID:20837): Managed Standby Recovery process is active
2021-09-26T22:02:00.965240+08:00
Incarnation entry added for Branch(resetlogs_id): 1084312848 (STDBYDB)
2021-09-26T22:02:02.317070+08:00
Setting recovery target incarnation to 4
2021-09-26T22:02:02.421791+08:00
PR00 (PID:3847): MRP0: Incarnation has changed! Retry recovery...
2021-09-26T22:02:02.422333+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_3847.trc:
ORA-19906: recovery target incarnation changed during recovery
PR00 (PID:3847): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Stopping change tracking
2021-09-26T22:02:02.735890+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_3847.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-26T22:02:03.908863+08:00
Started logmerger process
2021-09-26T22:02:04.088714+08:00
PR00 (PID:20863): Managed Standby Recovery starting Real Time Apply
2021-09-26T22:02:05.658988+08:00
ARC1 (PID:3197): Archived Log entry 110 added for T-1.S-2 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:06.477510+08:00
max_pdb is 5
2021-09-26T22:02:07.696019+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T22:02:08.510123+08:00
Stopping change tracking
2021-09-26T22:02:10.459429+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-1
PR00 (PID:20863): Fetching gap from T-1.S-1 to T-1.S-1
2021-09-26T22:02:10.779495+08:00
rfs (PID:20986): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14753)
2021-09-26T22:02:12.270336+08:00
rfs (PID:20986): Opened log for T-1.S-1 dbid 2778483057 branch 1084312848
2021-09-26T22:02:12.385769+08:00
rfs (PID:20986): Archived Log entry 111 added for B-1084312848.T-1.S-1 ID 0xaa3191f1 LAD:2
2021-09-26T22:02:13.251841+08:00
PR00 (PID:20863): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_1_1084312848.dbf
2021-09-26T22:02:14.796992+08:00
PR00 (PID:20863): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_2_1084312848.dbf
2021-09-26T22:02:22.220965+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-3 (in transit)
2021-09-26T22:02:23.679612+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 3 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.dbf
2021-09-26T22:02:33.322073+08:00
ARC2 (PID:3199): Archived Log entry 112 added for T-1.S-3 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:34.236382+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-4
2021-09-26T22:02:34.559830+08:00
rfs (PID:20820): Changing standby controlfile to MAXIMUM AVAILABILITY level
2021-09-26T22:02:35.020783+08:00
rfs (PID:20820): Selected LNO:4 for T-1.S-4 dbid 2778483057 branch 1084312848
2021-09-26T22:02:35.329328+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T22:10:24.128439+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-5
2021-09-26T22:10:24.194710+08:00
ARC0 (PID:3193): Archived Log entry 113 added for T-1.S-4 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:25.894496+08:00
rfs (PID:20820): Standby controlfile consistent with primary
2021-09-26T22:10:26.415829+08:00
rfs (PID:20820): Selected LNO:4 for T-1.S-5 dbid 2778483057 branch 1084312848
2021-09-26T22:10:32.885773+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T22:10:33.484509+08:00
ARC2 (PID:3199): Archived Log entry 114 added for T-1.S-5 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:33.653161+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-6 (in transit)
2021-09-26T22:10:33.666695+08:00
rfs (PID:20820): Standby controlfile consistent with primary
2021-09-26T22:10:33.983459+08:00
rfs (PID:20820): Selected LNO:4 for T-1.S-6 dbid 2778483057 branch 1084312848
2021-09-26T22:10:34.715704+08:00
2021-09-26T22:10:34.715704+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


The STDBYDB instance is already at Sequence#6 of the new Incarnation because the Primary database has been released for use and is already receiving new transactions and generating redo and ArchiveLogs which it is now shipping to all the Standbys.

The other current Standby STDB2 also shows that is now running with the new Incarnation and Sequence#6 :


2021-09-26T22:10:33.848217+08:00
PR00 (PID:20945): Media Recovery Waiting for T-1.S-6 (in transit)
2021-09-26T22:10:34.910903+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 6 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBNEW/stdbredo02.dbf


What about the Stanndy Database STDB2 that I had set to lag by 1hour. 
I present the alert log entries that shows it is aware of (Received and Archived, and in some cases, Applied, older Archives going back to the time before the Primary was shutdown).


2021-09-26T21:24:03.036631+08:00
ARC0 (PID:3517): Archive log for T-1.S-499 available in 59 minute(s)
2021-09-26T21:24:15.615120+08:00
rfs (PID:4480): Selected LNO:4 for T-1.S-501 dbid 2778483057 branch 1036108814
2021-09-26T21:24:16.085817+08:00
ARC1 (PID:3523): Archived Log entry 246 added for T-1.S-500 ID 0xa7521ccd LAD:1
2021-09-26T21:24:16.085881+08:00
ARC1 (PID:3523): Archive log for T-1.S-500 available in 60 minute(s)
2021-09-26T21:27:03.617096+08:00
db_recovery_file_dest_size of 10240 MB is 10.63% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2021-09-26T21:28:21.459249+08:00
rfs (PID:4480): Selected LNO:5 for T-1.S-502 dbid 2778483057 branch 1036108814
2021-09-26T21:28:25.263818+08:00
ARC2 (PID:3525): Archived Log entry 247 added for T-1.S-501 ID 0xa7521ccd LAD:1
2021-09-26T21:28:25.264142+08:00
ARC2 (PID:3525): Archive log for T-1.S-501 available in 60 minute(s)
2021-09-26T21:28:29.572537+08:00
rfs (PID:4480): Selected LNO:4 for T-1.S-503 dbid 2778483057 branch 1036108814
2021-09-26T21:28:29.580802+08:00
ARC3 (PID:3527): Archived Log entry 248 added for T-1.S-502 ID 0xa7521ccd LAD:1
2021-09-26T21:28:29.580964+08:00
ARC3 (PID:3527): Archive log for T-1.S-502 available in 59 minute(s)
2021-09-26T21:33:30.720113+08:00
rfs (PID:4480): Selected LNO:5 for T-1.S-504 dbid 2778483057 branch 1036108814
2021-09-26T21:33:30.737222+08:00
ARC0 (PID:3517): Archived Log entry 249 added for T-1.S-503 ID 0xa7521ccd LAD:1
2021-09-26T21:33:30.737298+08:00
ARC0 (PID:3517): Archive log for T-1.S-503 available in 60 minute(s)
2021-09-26T21:33:35.794308+08:00
rfs (PID:4480): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:33:35.825498+08:00
ARC1 (PID:3523): Archived Log entry 250 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:33:35.825563+08:00
ARC1 (PID:3523): Archive log for T-1.S-504 available in 60 minute(s)
2021-09-26T21:35:02.021214+08:00
rfs (PID:4493): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:35:02.225079+08:00
ARC2 (PID:3525): Archived Log entry 251 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:02.226252+08:00
ARC2 (PID:3525): Archive log for T-1.S-505 available in 59 minute(s)
2021-09-26T21:35:02.733024+08:00
rfs (PID:4488): Possible network disconnect with primary database
2021-09-26T22:01:13.848495+08:00
rfs (PID:20585): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:20568)
rfs (PID:20585): New archival redo branch: 1084312848 current: 1036108814
rfs (PID:20585): Primary database is in MAXIMUM PERFORMANCE mode
2021-09-26T22:01:13.995667+08:00
rfs (PID:20587): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ARCH (PID:14747)
rfs (PID:20587): New archival redo branch: 1084312848 current: 1036108814
2021-09-26T22:01:15.034084+08:00
rfs (PID:20585): No SRLs available for T-1
2021-09-26T22:01:15.317462+08:00
rfs (PID:20587): No SRLs available for T-1
Clearing online log 5 of thread 1 sequence number 0
2021-09-26T22:01:22.282624+08:00
rfs (PID:20587): Selected LNO:5 for T-1.S-1 dbid 2778483057 branch 1084312848
2021-09-26T22:01:22.283691+08:00
Clearing online log 4 of thread 1 sequence number 0
2021-09-26T22:01:22.380789+08:00
rfs (PID:20603): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:14755)
2021-09-26T22:01:24.283184+08:00
rfs (PID:20587): A new recovery destination branch has been registered
rfs (PID:20587): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848
rfs (PID:20587): Incomplete Recovery SCN:0x0000000000e6e7e1
rfs (PID:20587): Resetlogs SCN:0x0000000000e88404
rfs (PID:20587): SBPS:0x00000000004c4f04
rfs (PID:20587): New Archival REDO Branch(resetlogs_id): 1084312848 Prior: 1036108814
rfs (PID:20587): Archival Activation ID: 0xaa3191f1 Current: 0xa7521ccd
rfs (PID:20587): Effect of primary database OPEN RESETLOGS
rfs (PID:20587): Managed Standby Recovery process is active
2021-09-26T22:01:24.283999+08:00
Incarnation entry added for Branch(resetlogs_id): 1084312848 (STDB2)
2021-09-26T22:01:25.510452+08:00
Setting recovery target incarnation to 4
2021-09-26T22:01:27.818244+08:00
PR00 (PID:3913): MRP0: Incarnation has changed! Retry recovery...
2021-09-26T22:01:28.056186+08:00
Errors in file /opt/oracle/diag/rdbms/stdb2/STDB2/trace/STDB2_pr00_3913.trc:
ORA-19906: recovery target incarnation changed during recovery
Recovery interrupted!
2021-09-26T22:01:29.960402+08:00
Stopping change tracking
2021-09-26T22:01:29.962316+08:00
Errors in file /opt/oracle/diag/rdbms/stdb2/STDB2/trace/STDB2_pr00_3913.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-26T22:01:30.588055+08:00
rfs (PID:20585): Selected LNO:4 for T-1.S-2 dbid 2778483057 branch 1084312848
2021-09-26T22:01:30.942431+08:00
Started logmerger process
2021-09-26T22:01:32.562040+08:00
PR00 (PID:20631): Managed Standby Recovery not using Real Time Apply
2021-09-26T22:01:34.537732+08:00
ARC2 (PID:3525): Archived Log entry 252 added for T-1.S-1 ID 0xaa3191f1 LAD:1
2021-09-26T22:01:34.537903+08:00
ARC2 (PID:3525): Archive log for T-1.S-1 available in 59 minute(s)
2021-09-26T22:01:34.915506+08:00
max_pdb is 5
2021-09-26T22:01:36.394057+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T22:01:38.128953+08:00
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 15238147
Stopping change tracking
2021-09-26T22:01:39.202866+08:00
PR00 (PID:20631): Media Recovery Waiting for B-1036108814.T-1.S-495
2021-09-26T22:01:55.182487+08:00
rfs (PID:20587): Opened log for T-1.S-495 dbid 2778483057 branch 1036108814
2021-09-26T22:01:57.676763+08:00
rfs (PID:20585): Selected LNO:5 for T-1.S-3 dbid 2778483057 branch 1084312848
2021-09-26T22:01:58.495693+08:00
ARC3 (PID:3527): Archived Log entry 253 added for T-1.S-2 ID 0xaa3191f1 LAD:1
2021-09-26T22:01:58.495890+08:00
ARC3 (PID:3527): Archive log for T-1.S-2 available in 59 minute(s)
2021-09-26T22:01:59.879131+08:00
rfs (PID:20587): Archived Log entry 254 added for B-1036108814.T-1.S-495 ID 0xa7521ccd LAD:3
2021-09-26T22:02:00.978032+08:00
PR00 (PID:20631): Media Recovery Delayed for 11 minute(s) T-1.S-495
2021-09-26T22:02:06.096910+08:00
rfs (PID:20950): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14753)
2021-09-26T22:02:06.097083+08:00
rfs (PID:20948): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14751)
2021-09-26T22:02:07.293676+08:00
rfs (PID:20587): Opened log for T-1.S-497 dbid 2778483057 branch 1036108814
2021-09-26T22:02:07.649778+08:00
rfs (PID:20950): Opened log for T-1.S-498 dbid 2778483057 branch 1036108814
2021-09-26T22:02:07.997282+08:00
rfs (PID:20948): Opened log for T-1.S-496 dbid 2778483057 branch 1036108814
2021-09-26T22:02:08.520189+08:00
rfs (PID:20587): Archived Log entry 255 added for B-1036108814.T-1.S-497 ID 0xa7521ccd LAD:3
2021-09-26T22:02:09.302061+08:00
rfs (PID:20950): Archived Log entry 256 added for B-1036108814.T-1.S-498 ID 0xa7521ccd LAD:3
2021-09-26T22:02:09.707710+08:00
rfs (PID:20948): Archived Log entry 257 added for B-1036108814.T-1.S-496 ID 0xa7521ccd LAD:3
2021-09-26T22:02:10.761235+08:00
rfs (PID:20587): Opened log for T-1.S-499 dbid 2778483057 branch 1036108814
2021-09-26T22:02:11.253039+08:00
rfs (PID:20587): Archived Log entry 258 added for B-1036108814.T-1.S-499 ID 0xa7521ccd LAD:3
2021-09-26T22:02:12.823920+08:00
rfs (PID:20948): Opened log for T-1.S-500 dbid 2778483057 branch 1036108814
2021-09-26T22:02:13.951243+08:00
rfs (PID:20948): Archived Log entry 259 added for B-1036108814.T-1.S-500 ID 0xa7521ccd LAD:3
2021-09-26T22:02:15.024761+08:00
rfs (PID:20587): Opened log for T-1.S-501 dbid 2778483057 branch 1036108814
2021-09-26T22:02:15.534976+08:00
rfs (PID:20950): Opened log for T-1.S-502 dbid 2778483057 branch 1036108814
2021-09-26T22:02:21.798580+08:00
rfs (PID:20950): Archived Log entry 260 added for B-1036108814.T-1.S-502 ID 0xa7521ccd LAD:3
2021-09-26T22:02:23.673255+08:00
rfs (PID:20587): Archived Log entry 261 added for B-1036108814.T-1.S-501 ID 0xa7521ccd LAD:3
2021-09-26T22:02:37.301066+08:00
rfs (PID:20585): Selected LNO:4 for T-1.S-4 dbid 2778483057 branch 1084312848
2021-09-26T22:02:37.452512+08:00
ARC0 (PID:3517): Archived Log entry 262 added for T-1.S-3 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:37.452615+08:00
ARC0 (PID:3517): Archive log for T-1.S-3 available in 59 minute(s)
2021-09-26T22:10:31.699175+08:00
rfs (PID:20585): Selected LNO:5 for T-1.S-5 dbid 2778483057 branch 1084312848
2021-09-26T22:10:31.774120+08:00
ARC1 (PID:3523): Archived Log entry 263 added for T-1.S-4 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:31.774245+08:00
ARC1 (PID:3523): Archive log for T-1.S-4 available in 59 minute(s)
2021-09-26T22:10:35.934765+08:00
rfs (PID:20585): Selected LNO:4 for T-1.S-6 dbid 2778483057 branch 1084312848
2021-09-26T22:10:35.938540+08:00
ARC2 (PID:3525): Archived Log entry 264 added for T-1.S-5 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:35.938646+08:00
ARC2 (PID:3525): Archive log for T-1.S-5 available in 60 minute(s)
2021-09-26T22:12:59.695487+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_495_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 19 minute(s) T-1.S-496
2021-09-26T22:21:12.441616+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_496_1036108814.dbf
2021-09-26T22:21:15.172035+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_497_1036108814.dbf
2021-09-26T22:21:16.215487+08:00
PR00 (PID:20631): Media Recovery Delayed for 20 minute(s) T-1.S-498
2021-09-26T22:22:10.156957+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_498_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 21 minute(s) T-1.S-499
2021-09-26T22:23:15.521168+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_499_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 22 minute(s) T-1.S-500
2021-09-26T22:24:17.854072+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_500_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 26 minute(s) T-1.S-501
2021-09-26T22:28:31.972725+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_502_1036108814.dbf
PR00 (PID:20631): Media Recovery Waiting for B-1036108814.T-1.S-503
2021-09-26T22:35:19.026115+08:00
rfs (PID:20585): Selected LNO:5 for T-1.S-7 dbid 2778483057 branch 1084312848
2021-09-26T22:35:19.052219+08:00
ARC3 (PID:3527): Archived Log entry 265 added for T-1.S-6 ID 0xaa3191f1 LAD:1
2021-09-26T22:35:19.052325+08:00
ARC3 (PID:3527): Archive log for T-1.S-6 available in 59 minute(s)



This has recognised the ResetLogs at 22:01 and has received the new Log Sequences (until Sequence#7) but still hasn't applied Sequence498  from the old Incarnation because that is not yet 1hour old [Sequence#497 has been applied to this Standby as at the time I view the alert log].  All  the new Incarnation Log Sequences# 1 to 6 and onwards will be applied when each is 1hour old.

Since STDB2, seems to have stopped applying ArchiveLogs afer Sequence#502, I stopped and restarted delayed recovery and re-registered ArchiveLogs for Sequence#503 to #505 (that had actually already been Received and Archived between 21:33 and 21:36 earlier).


oracle19c>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 23:07:38 2021
Version 19.12.0.0.0

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL> qui
SP2-0042: unknown command "qui" - rest of line ignored.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 23:11:34 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf';

Database altered.

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf';

Database altered.

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf';

Database altered.

SQL>


2021-09-26T23:07:57.810826+08:00
Managed Standby Recovery Canceled (STDB2)
Completed: alter database recover managed standby database cancel
2021-09-26T23:08:13.863811+08:00
alter database recover managed standby database using archived logfile disconnect from session
2021-09-26T23:08:13.865416+08:00
Attempt to start background Managed Standby Recovery process (STDB2)
Starting background process MRP0
2021-09-26T23:08:13.877996+08:00
MRP0 started with pid=36, OS id=14015
2021-09-26T23:08:13.879036+08:00
Background Managed Standby Recovery process started (STDB2)
2021-09-26T23:08:18.889440+08:00
Started logmerger process
2021-09-26T23:08:18.917894+08:00
PR00 (PID:14043): Managed Standby Recovery not using Real Time Apply
max_pdb is 5
2021-09-26T23:08:19.572566+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T23:08:19.748754+08:00
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 15238147
Stopping change tracking
2021-09-26T23:08:19.885274+08:00
Completed: alter database recover managed standby database using archived logfile disconnect from session
2021-09-26T23:08:19.969571+08:00
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-503
2021-09-26T23:12:02.832089+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf'
2021-09-26T23:12:02.832185+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:04.988846+08:00
.... (PID:15189): Resynchronizing from T-1-S-10 to T-1.S-503
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf'
2021-09-26T23:12:13.842691+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_503_1036108814.dbf
2021-09-26T23:12:15.812907+08:00
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-504
2021-09-26T23:12:22.647534+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf'
2021-09-26T23:12:22.647609+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:24.805303+08:00
.... (PID:15189): Resynchronizing from T-1-S-503 to T-1.S-504
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf'
2021-09-26T23:12:30.299323+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_504_1036108814.dbf
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-505
2021-09-26T23:12:36.167593+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf'
2021-09-26T23:12:36.167671+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:38.271665+08:00
.... (PID:15189): Resynchronizing from T-1-S-504 to T-1.S-505
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf'
2021-09-26T23:12:41.883099+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_505_1036108814.dbf
2021-09-26T23:12:43.883970+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_1_1084312848.dbf
2021-09-26T23:12:44.047740+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_2_1084312848.dbf
2021-09-26T23:12:44.473162+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_3_1084312848.dbf
2021-09-26T23:12:44.701811+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_4_1084312848.dbf
2021-09-26T23:12:52.961033+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_5_1084312848.dbf
PR00 (PID:14043): Media Recovery Delayed for 59 minute(s) T-1.S-6


This is a bug I've noticed in the past. If a Standby has Received and Archived a logfile but hasn't applied it when it should have (Sequence#503 to 505 should have been applied between 22:33 and 22:36 as they were received at 21:33 to 22:35-- you can see the earlier set of alert log entries that show that they were Received and Archived by this database instance), you might shutdown, startup and resume Recovery or simply stop and start Recovery.  If that doesn't work, re-register the ArchiveLogs with the "ALTER DATABASE REGISTER LOGFILE" command again and re-attempt Recovery.

Now, all 3 of my Standby databases are in sync with the Primary (ie. STDBYDB and STDBNEW are current while STDB2 is lagging by 1hour, as designed).

Thus it is possible to do a RESETLOGS on a Primary as long as you ensure that 
1.  The Primary had Archived the last Online Redo Log before Shutdown
2.  The Shutdown was Normal or Immediate
3.  All the Standbys have, at least, Received all the ArchiveLogs (if there was a delay in shipping ArchiveLogs, you can manually copy them to the Standbys and use the ALTER DATABASE REGISTER LOGFILE command at the Standbys)
4.  No actual recovery from Online Redo Logs {that were not shipped to the Standby} was required -- this is the case when the Shutdown is Normal or Immediate
Categories: DBA Blogs

ADG and DML Redirection -- with Multiple Standbys

Sat, 2021-09-04 09:55

 Building on my previous blog post on Active Data Guard with DML Redirection, here are a few more tests.

First, on the Primary :



22:03:50 SQL> select name, db_unique_name, database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB ORCLCDB PRIMARY

Elapsed: 00:00:00.02
22:04:08 SQL> alter pluggable database orclpdb1 open ;
alter pluggable database orclpdb1 open
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


Elapsed: 00:00:00.50
22:04:19 SQL>
22:05:02 SQL> alter session set container=ORCLPDB1;

Session altered.

Elapsed: 00:00:00.04
22:05:10 SQL> create table hemant.test_adg_dml as select * from dba_objects where 1=2;

Table created.

Elapsed: 00:00:01.16
22:05:31 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:09.29
22:06:03 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
22:06:12 SQL>


Now, one the first Standby as Active Data Guard and with DML Redirection

22:02:08 SQL> select name, db_unique_name, database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB STDBYDB PHYSICAL STANDBY

Elapsed: 00:00:00.00
22:07:24 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:01.01
22:07:34 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:34.66
22:08:23 SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

Elapsed: 00:00:10.76
22:08:39 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:07.04
22:08:48 SQL> connect hemant/hemant@stdbypdb1
Connected.
22:09:12 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

Elapsed: 00:00:00.00
22:09:28 SQL> select count(*) from hemant.test_adg_dml;

COUNT(*)
----------
73661

Elapsed: 00:00:01.60
22:09:48 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:10.71
22:10:13 SQL> select count(*) from hemant.test_adg_dml;

COUNT(*)
----------
147322

Elapsed: 00:00:00.01
22:10:24 SQL> commit;

Commit complete.

Elapsed: 00:00:00.09
22:10:27 SQL>


Now, on another Standby with ADG and DML Redirection :

22:11:52 SQL> select name, db_unique_name, database_role from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB STDBNEW PHYSICAL STANDBY

Elapsed: 00:00:00.03
22:11:53 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:06.01
22:12:06 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:32.27
22:12:42 SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

Elapsed: 00:00:14.89
22:12:59 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:06.06
22:13:08 SQL>
22:20:58 SQL> connect hemant/hemant@stdbnewpdb1
Connected.
22:21:04 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

Elapsed: 00:00:00.01
22:21:18 SQL> select count(*) from hemant.test_adg_dml;

COUNT(*)
----------
147322

Elapsed: 00:00:00.02
22:21:27 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:08.60
22:21:50 SQL> select count(*) from hemant.test_adg_dml;

COUNT(*)
----------
220983

Elapsed: 00:00:00.01
22:22:06 SQL> commit;

Commit complete.

Elapsed: 00:00:00.21
22:22:08 SQL>
22:22:43 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB STDBNEW PHYSICAL STANDBY READ ONLY WITH APPLY

Elapsed: 00:00:00.01
22:22:44 SQL>


So, this proves that we can run ADG and DML Redirection concurrently from multiple Standbys.

Let me test Locking.

I run a DML from STDBYDB and then try a similar DML from the Primary.


22:24:35 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ ONLY


Elapsed: 00:00:00.00
22:24:40 SQL> show user
USER is "HEMANT"
22:24:45 SQL> delete hemant.test_adg_dml;

220983 rows deleted.

Elapsed: 00:00:17.20
22:25:16 SQL>
--- no COMMIT has been issued yet !


-- now this is on the Primary !
22:26:18 SQL> select name, db_unique_name, database_role, open_mode from v$database
22:26:25 2 /

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB ORCLCDB PRIMARY READ WRITE

Elapsed: 00:00:00.39
22:26:27 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ WRITE


Elapsed: 00:00:00.12
22:26:33 SQL> delete hemant.test_adg_dml;


This goes into an indefinite WAIT on the Primary
--- from another session, I use utllockt to identify the Blocker and Waiter at the Primary
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135 None
391 Transaction Exclusive Exclusive 65541 5609

22:29:26 SQL> select s.sid, s.sql_id, sq.sql_text
22:30:47 2 from v$session s, v$sql sq
22:30:53 3 where s.sid in (135,391)
22:31:08 4 and s.sql_id=sq.sql_id
22:31:12 5 /

SID SQL_ID
---------- -------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
135 9utz9bdavk3bz
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"

391 4kmzmr764b4k6
delete hemant.test_adg_dml


22:31:13 SQL>
--- SID=135 is the remote session from the STDBYDB instance


To reconfirm, this, I deliberately, change the SQL Text and try again

--- this from STDBYDB
22:33:19 SQL> rollback;

Rollback complete.

Elapsed: 00:00:52.76
22:34:27 SQL>
22:34:51 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB STDBYDB PHYSICAL STANDBY READ ONLY WITH APPLY

Elapsed: 00:00:00.02
22:34:53 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ ONLY


Elapsed: 00:00:00.00
22:35:03 SQL>
22:35:50 SQL> delete /*+ Hint here from STDYBDB */ from hemant.test_adg_dml;

220983 rows deleted.

Elapsed: 00:00:06.78
22:36:27 SQL>

--- this from ORCLCDB
22:34:37 SQL> rollback;

Rollback complete.

Elapsed: 00:00:08.44
22:34:53 SQL>
22:36:41 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB ORCLCDB PRIMARY READ WRITE

Elapsed: 00:00:00.24
22:37:21 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ WRITE


Elapsed: 00:00:00.00
22:37:30 SQL> DELETE /*+ I am the Primary */ hemant.test_adg_dml target_table;


Again goes into an Indefnite WAIT at the Primary
-- now check using utllockt at the Primary
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135 None
391 Transaction Exclusive Exclusive 393247 7239


SQL> select s.sid, sq.sql_text
2 from v$session s, v$sql sq
3 where s.sid in (135, 391)
4 and s.sql_id = sq.sql_id
5 /

SID
----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
135
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"

391
DELETE /*+ I am the Primary */ hemant.test_adg_dml target_table


SQL>

---- apparently, when the Standby sends the SQL to the Primary it is rewritten as UPPER CASE
---- and the Invalid Hint that is a Comment is removed
---- nevertheless, this proves that SID=135 from the Standby is the Blocker and SID=391 on the Primary is the Waiter

So, this proves that DML Redirection from an ADG Standby does take locks and prevents concurrent DML against the same rows on the Primary (and, by extension, on any other ADG Standby)


Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.


Update :  If the Primary goes down / shuts down, an ADG with DML Redirection will show
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from ADGREDIRECT
at the next call 

Categories: DBA Blogs

Identifying Patches applied to an Oracle Installation and Database

Sun, 2021-08-29 09:30
"opatch" and "datapatch" are the methods to apply Oracle Patches in recent versions.

"opatch" applies updates to the library and binary files to the ORACLE_HOME at the OS level.

"datapatch" then applies the corresponding changes to the data dictionary in the database.  Note that if you have multiple databases running from the same ORACLE_HOME, you must run "datapatch" against each database.  Also, if you later create a new database [most of us use a template / script to do so], you must run "datapatch" against the new database as well.

The instructions for both "opatch" and "datapatch"  (including the pre-requisite checks) are included in the README file that accompanies each patch.


Here is a quick demo of the commands to identify patches that are applied.


oracle19c>$ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.25

OPatch succeeded.
oracle19c>
oracle19c>$ORACLE_HOME/OPatch/opatch lspatches
32876380;OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
oracle19c>
oracle19c>$ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, Oracle Corporation. All rights reserved.


Oracle Home : /opt/oracle/product/19c/dbhome_1
Central Inventory : /opt/oracle/oraInventory
from : /opt/oracle/product/19c/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.25
OUI version : 12.2.0.7.0
Log file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/opatch2021-08-29_21-20-33PM_1.log

Lsinventory Output file location : /opt/oracle/product/19c/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2021-08-29_21-20-33PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: ora19cs1
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 19c 19.0.0.0.0
There are 1 products installed in this Oracle Home.


Interim patches (3) :

Patch 32876380 : applied on Mon Aug 09 22:59:39 GMT+08:00 2021
Unique Patch ID: 24269510
Patch description: "OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)"
Created on 5 Jul 2021, 04:53:50 hrs UTC
Bugs fixed:
29445548, 29254623, 29540327, 29774362, 30134746, 30160625, 30534662
29512125, 29942275, 30855101, 31306261, 31359215, 30895577, 29224710
26716835, 31668872, 32165759, 32069696, 32032733, 30889443, 30674373
32167592, 32523206, 29415774, 28777073, 32124570, 31247838, 29540831
32892883, 31776121

Patch 32904851 : applied on Mon Aug 09 21:44:24 GMT+08:00 2021
Unique Patch ID: 24343243
Patch description: "Database Release Update : 19.12.0.0.210720 (32904851)"
Created on 20 Jul 2021, 09:21:24 hrs UTC
Bugs fixed:
7391838, 8460502, 8476681, 14570574, 14735102, 15931756, 16662822
16664572, 16750494, 17275499, 17395507, 17428816, 17468475, 17777718
18534283, 18697534, 19080742, 19138896, 19697993, 20007421, 20083476
20313356, 20319830, 20479545, 20867658, 20922160, 21119541, 21232786
21245711, 21374587, 21528318, 21629064, 21639146, 21888352, 21965541
22066547, 22252368, 22325312, 22387320, 22580355, 22725871, 22729345
22748979, 23020668, 23094775, 23125587, 23294761, 23296836, 23311885
23606241, 23645975, 23734075, 23763462, 24336782, 24356932, 24561942
24596874, 24669730, 24687075, 24833686, 24957575, 24971456, 25030027
25092651, 25093917, 25148135, 25404117, 25416731, 25560538, 25562258
25607406, 25607716, 25756945, 25792962, 25804387, 25804908, 25806201
...
... cut short a VERY LONG LIST of Bug Numbers
...
32677702, 32686850, 32697781, 32698569, 32700989, 32704765, 32711741
32712220, 32716726, 32718316, 32720458, 32725484, 32728984, 32738356
32739966, 32740503, 32754845, 32758096, 32765738, 32784393, 32784403
32786309, 32795712, 32810668, 32811069, 32811131, 32816003, 32817950
32818019, 32858446, 32874995, 32881853, 32889434, 32895105, 32900208
32902635, 32936961, 32941509, 32996071, 33034103, 33048277, 33127032
32490416

Patch 29585399 : applied on Thu Apr 18 15:21:33 GMT+08:00 2019
Unique Patch ID: 22840393
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
Bugs fixed:
27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
29413360, 29457319, 29465047



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

OPatch succeeded.
oracle19c>


The first command "opatch version" shows the current version
The second command "opatch lspatches" provides a high level listing of the patches that have been applied.
The third command "opatch lsinventory" provides a detailed listing of the patches that includes the dates when they were applied by the DBA, the dates they were actually created and released by Oracle Development / Support and each individual Bug# numbers fixed in the patches.  
Release Updates in 12.2 and higher are cumulative.  Here, "19.12.0.0.210720" indicates that I have applied the 19.12 Release Update of July 2021 ("2107" from the patch identifier)


Another method, using sql is the DBMS_QOPATCH package which has been implemented and substantially improved in recent releases.  (Oracle Support Document "How to Find PSU/One-off Patches is Applied Using QOPatch(DBMS_QOPATCH) (Doc ID 2169610.1)" is a good starting point :


SQL> set pages50000
SQL> set long 100000
SQL> spool dbms_qopatch_lsinventory.TXT
SQL> select dbms_qopatch.get_opatch_lsinventory from dual;
...
...
SQL> spool off
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>ls -l dbms_qopatch_lsinventory.TXT
-rw-r--r--. 1 oracle oinstall 237880 Aug 29 21:50 dbms_qopatch_lsinventory.TXT
oracle19c>
oracle19c>wc -l dbms_qopatch_lsinventory.TXT
1792 dbms_qopatch_lsinventory.TXT

dbms_qopatch.get_opatch_lsinventory


Since my output file from the dbms_qopatch.get_opatch_lsinventory is very long (1792 lines, 237KBytes, I've presented a screenshot of some of the lines f rom the file).  In addition to showing Bug# numbers, it also shows the Bug Titles (i.e. Descriptions) which the command-line opatch lsinventory doe not provide.


DBMS_QOPATCH is very useful.  You can query to check if a certain Patch has been installed (Patch 32904851 is the July 2021 Release Update Patch 19.12 that I installed on 09-Aug-2021).


SQL> set pages5000
SQL> set pages50000
SQL> set long 1000000
SQL> set pages50000
SQL> set long 1000000
SQL> select xmltransform(dbms_qopatch.is_patch_installed('32904851'),dbms_qopatch.get_opatch_xslt) from dual;

XMLTRANSFORM(DBMS_QOPATCH.IS_PATCH_INSTALLED('32904851'),DBMS_QOPATCH.GET_OPATCH_XSLT)
------------------------------------------------------------------------------------------------------------------------------------

Patch Information:
32904851: applied on 2021-08-09T21:44:24+08:00


SQL>
SQL>


"opatch" only lists patches that are applied to the ORACLE_HOME.  However, you must also confirm if the database data dictionary has also been updated -- which would be so if "datapatch" has been executed.  (side note : "datapatch" also has a number of command-line arguments which you might want to explore)


This information is in the dba_registry and dba_registry_sqlpatch  views in the database


SQL> set linesize 132
SQL> select * from dba_registry_history order by action_time;

ACTION_TIME ACTION
--------------------------------------------------------------------------- ------------------------------
NAMESPACE VERSION ID
------------------------------ ------------------------------ ----------
COMMENTS
------------------------------------------------------------------------------------------------------------------------------------
BUNDLE_SERIES
------------------------------
04-MAY-19 11.30.32.133846 PM RU_APPLY
SERVER 19.0.0.0.0
Patch applied on 19.3.0.0.0: Release_Update - 190410122720


09-AUG-21 10.13.42.105236 PM RU_APPLY
SERVER 19.0.0.0.0
Patch applied from 19.3.0.0.0 to 19.12.0.0.0: Release_Update - 210716141810


09-AUG-21 11.10.15.544636 PM jvmpsu.sql
SERVER 19.12.0.0.210720OJVMRU 0
RAN jvmpsu.sql


09-AUG-21 11.10.15.722597 PM APPLY
SERVER 19.12.0.0.210720OJVMRU 0
OJVM RU post-install


BOOTSTRAP
DATAPATCH 19
RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715



SQL>
SQL> select * from dba_registry_sqlpatch order by action_time;

INSTALL_ID PATCH_ID PATCH_UID PATCH_TYPE ACTION STATUS
---------- ---------- ---------- ---------- --------------- -------------------------
ACTION_TIME
---------------------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
LOGFILE
------------------------------------------------------------------------------------------------------------------------------------
RU_LOGFILE
------------------------------------------------------------------------------------------------------------------------------------
FLAGS
----------
PATCH_DESCRIPTOR
------------------------------------------------------------------------------------------------------------------------------------
PATCH_DIRECTORY
------------------------------------------------------------------------------------------------------------------------------------
SOURCE_VERSION SOURCE_BUILD_DESCRIPTION
--------------- --------------------------------------------------------------------------------
SOURCE_BUILD_TIMESTAMP TARGET_VERSION
--------------------------------------------------------------------------- ---------------
TARGET_BUILD_DESCRIPTION
--------------------------------------------------------------------------------
TARGET_BUILD_TIMESTAMP
---------------------------------------------------------------------------
1 29517242 22862832 RU APPLY SUCCESS
04-MAY-19 11.31.01.355942 PM
Database Release Update : 19.3.0.0.190416 (29517242)
/opt/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_ORCLCDB_CDBROOT_2019May04_23_23_03.log
/opt/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_ru_apply_ORCLCDB_CDBROOT_2019May04_23_22_59.log
N

sqlPatch ID="29517242" uniquePatchID=

19.1.0.0.0 Feature Release
19.3.0.0.0
Release_Update
10-APR-19 12.27.20.000000 PM



2 32904851 24343243 RU APPLY SUCCESS
09-AUG-21 10.35.37.696526 PM
Database Release Update : 19.12.0.0.210720 (32904851)
/opt/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_ORCLCDB_CDBROOT_2021Aug09_22_04_05.log
/opt/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_ru_apply_ORCLCDB_CDBROOT_2021Aug09_22_03_59.log
N

sqlPatch ID="32904851" uniquePatchID=

19.3.0.0.0 Release_Update
10-APR-19 12.27.20.000000 PM 19.12.0.0.0
Release_Update
16-JUL-21 02.18.10.000000 PM



3 32876380 24269510 INTERIM APPLY SUCCESS
09-AUG-21 11.12.07.815060 PM
OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)
/opt/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_ORCLCDB_CDBROOT_2021Aug09_23_06_42.log

NJ

sqlPatch ID="32876380" uniquePatchID=
504B03041400000008007A77E552F6AD657DF40100006C0400000C00000033323837363338302E786D6CB5534D73DA3014BCFB57BCEAD41EC01F044232B6339EE076
C804C81092999E3A8A2D374A6559
19.12.0.0.0 Release_Update
16-JUL-21 02.18.10.000000 PM 19.12.0.0.0
Release_Update
16-JUL-21 02.18.10.000000 PM


SQL>


The first query shows that "datapatch" was executed on 09-Aug-21 and the second query shows that it executed the changes for both Patches 32904851 (Database Release Update) and 32876380 (OJVM Release Update)


Categories: DBA Blogs

Restoring Standby Database after Failover

Sat, 2021-08-14 05:42

 In the previous post, I demonstrated how the Database Incarnation changed after a Failover was issued for a Standby Database.

Let's assume that Read-Write testing of the Standby has been done and now I need to restore and revert STDBYDB to the state of being a Standby for ORCLCDB


The Primary is still at Database Incarnation#=3  and is now at Sequence#=409



SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL> select incarnation#
2 from v$database_incarnation
3 where status = 'CURRENT'
4 /

INCARNATION#
------------
3

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 407
Next log sequence to archive 409
Current log sequence 409
SQL>


The former Standby STDBYDB has already diverged and is at Incarnation#=4 and its current Sequence#=5


SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select incarnation#
2 from v$database_incarnation
3 where status = 'CURRENT'
4 /

INCARNATION#
------------
4

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/STDBYDB
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>


I now delete all the datafiles STDBYDB and restore the the backup that I made before I executed the Failover (see the previous blog post)


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>cd
oracle19c>sh ./to_delete_STDBYDB.SH
oracle19c>
oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 14 17:49:50 2021
Version 19.12.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207955544 bytes

Fixed Size 9134168 bytes
Variable Size 486539264 bytes
Database Buffers 704643072 bytes
Redo Buffers 7639040 bytes

RMAN> restore controlfile from '/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_08_13/o1_mf_s_1080515319_jkf2tzhp_.bkp';

Starting restore at 14-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/oracle/oradata/STDBYDB/control01.ctl
output file name=/opt/oracle/oradata/STDBYDB/control02.ctl
Finished restore at 14-AUG-21

RMAN> quit


Recovery Manager complete.
oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 14 17:52:34 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database mount;

Database altered.

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select database_role, open_mode
2 from v$database
3 /

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 CURRENT 1036108814

SQL>


So, this restored Controlfile confirms that I have a Standby Database that is now back at Incarnation#=3.
I can now proceed to restore the database and I assume that I can reapply all the ArchiveLogs from the Primary which is also at Incarnation#=3


SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Aug 14 17:56:06 2021
Version 19.12.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)

RMAN> restore database;

Starting restore at 14-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/STDBYDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_08_13/o1_mf_nnndf_TAG20210813T230720_jkf2ob5r_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /opt/oracle/oradata/STDBYDB/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_08_13/o1_mf_nnndf_TAG20210813T230720_jkf2o9j4_.bkp
....
....
....

channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:36
Finished restore at 14-AUG-21

RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 14 18:01:50 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select database_role, open_mode
2 from v$database
3 /

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 CURRENT 1036108814

SQL>


Let my now try to Recover this Database as a Standby using ArchiveLogs from the PrimaryR (after " alter system set log_archive_dest_state_2='enable';" at the Primary)


SQL> select l.group#, l.bytes/1048576, l.status, f.member
2 from v$standby_log l, v$logfile f
3 where l.group#=f.group#
4 order by 1
5 /

GROUP# L.BYTES/1048576 STATUS
---------- --------------- ----------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------
4 200 ACTIVE
/opt/oracle/oradata/STDBYDB/stdbredo01.log

5 200 ACTIVE
/opt/oracle/oradata/STDBYDB/stdbredo02.dbf


SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL>
SQL> select l.group#, l.bytes/1048576, l.status, f.member
2 from v$standby_log l, v$logfile f
3 where l.group#=f.group#
4 order by 1
5 /

GROUP# L.BYTES/1048576 STATUS
---------- --------------- ----------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------
4 200 UNASSIGNED
/opt/oracle/oradata/STDBYDB/stdbredo01.log

5 200 UNASSIGNED
/opt/oracle/oradata/STDBYDB/stdbredo02.dbf


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

Database altered.

SQL>


STDBYDB takes a few minutes to get all the ArchiveLogs from Sequence#402 onwards and apply them


2021-08-14T18:16:46.242533+08:00
rfs (PID:24643): Selected LNO:5 for T-1.S-416 dbid 2778483057 branch 1036108814
2021-08-14T18:16:46.279518+08:00
ARC1 (PID:15987): Archived Log entry 19 added for T-1.S-415 ID 0xa7521ccd LAD:1
2021-08-14T18:16:46.408474+08:00
PR00 (PID:23234): Media Recovery Waiting for T-1.S-416 (in transit)
2021-08-14T18:16:46.420169+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 416 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.dbf


The Primary also now shows :


SQL> l
1 select dest_id, db_unique_name, status, type, archived_seq#, applied_seq#, synchronized, gap_status
2 from v$archive_dest_status
3 where dest_id in (1,2,3)
4* order by 1
SQL> /

DEST_ID DB_UNIQUE_NAME STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ# SYN GAP_STATUS
---------- ------------------------------ --------- ---------------- ------------- ------------ --- ------------------------
1 NONE VALID LOCAL 415 0 NO
2 STDBYDB VALID PHYSICAL 415 414 YES NO GAP
3 STDB2 VALID PHYSICAL 415 405 NO NO GAP

SQL>

I can also see the ArchiveLogs that were generated from STDBYDB :

oracle19c>pwd
/opt/oracle/archivelog/STDBYDB
oracle19c>ls -latr
total 196820
drwxr-xr-x. 6 oracle oinstall 63 Aug 11 14:25 ..
-rw-r-----. 1 oracle oinstall 269824 Aug 13 22:47 1_394_1036108814.dbf
-rw-r-----. 1 oracle oinstall 4147200 Aug 13 23:02 1_399_1036108814.dbf
-rw-r-----. 1 oracle oinstall 3584 Aug 13 23:02 1_400_1036108814.dbf
-rw-r-----. 1 oracle oinstall 24064 Aug 13 23:02 1_401_1036108814.dbf
-rw-r-----. 1 oracle oinstall 3072 Aug 13 23:03 1_402_1036108814.dbf
-rw-r-----. 1 oracle oinstall 1024 Aug 13 23:13 1_1_1080515513.dbf
-rw-r-----. 1 oracle oinstall 21302784 Aug 14 17:13 1_2_1080515513.dbf
-rw-r-----. 1 oracle oinstall 96683520 Aug 14 17:41 1_3_1080515513.dbf
-rw-r-----. 1 oracle oinstall 14848 Aug 14 17:42 1_4_1080515513.dbf
-rw-r-----. 1 oracle oinstall 45568 Aug 14 18:04 1_405_1036108814.dbf
-rw-r-----. 1 oracle oinstall 703488 Aug 14 18:04 1_404_1036108814.dbf
-rw-r-----. 1 oracle oinstall 486400 Aug 14 18:04 1_403_1036108814.dbf
-rw-r-----. 1 oracle oinstall 20830208 Aug 14 18:04 1_406_1036108814.dbf
-rw-r-----. 1 oracle oinstall 5378048 Aug 14 18:04 1_407_1036108814.dbf
-rw-r-----. 1 oracle oinstall 538112 Aug 14 18:04 1_408_1036108814.dbf
-rw-r-----. 1 oracle oinstall 4228096 Aug 14 18:04 1_409_1036108814.dbf
-rw-r-----. 1 oracle oinstall 4096 Aug 14 18:04 1_410_1036108814.dbf
-rw-r-----. 1 oracle oinstall 197120 Aug 14 18:07 1_411_1036108814.dbf
-rw-r-----. 1 oracle oinstall 176128 Aug 14 18:16 1_414_1036108814.dbf
-rw-r-----. 1 oracle oinstall 489984 Aug 14 18:16 1_412_1036108814.dbf
-rw-r-----. 1 oracle oinstall 91136 Aug 14 18:16 1_413_1036108814.dbf
-rw-r-----. 1 oracle oinstall 4096 Aug 14 18:16 1_415_1036108814.dbf
drwxr-xr-x. 2 oracle oinstall 4096 Aug 14 18:25 .
-rw-r-----. 1 oracle oinstall 45876224 Aug 14 18:25 1_416_1036108814.dbf
oracle19c>

Note how Sequence#1 to Sequence#4 were generated in the new Incarnation with Resetlogs ID 1080515513.  This was the Resetlogs ID assigned when STDBYDB was opened with Failover, thus a New Incarnation and effectively a Resetlogs. Sequence#403 onwards are from the Primary with Resetlogs ID 1036108814
(You can check the Resetlogs IDs shown in the previous blog post)
Categories: DBA Blogs

Failover and Database Incarnation

Fri, 2021-08-13 10:26
In previous blog posts, I have demonstrated : 

1.  Using Flashback Database for [destructive] testing at a Standby

2.  Using Snapshot Standby Database for [destructive] testing at a Standby



In this blog post, I will demonstrate the Database Incarnation feature when a Failover is executed.


This is the current status at the Primary :


SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL> archive log list

SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 401
Next log sequence to archive 403
Current log sequence 403
SQL>

SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 CURRENT 1036108814

SQL>
SQL> l
1 select dest_id, db_unique_name, status, type, archived_seq#, applied_seq#, synchronized, gap_status
2 from v$archive_dest_status
3 where dest_id in (1,2,3)
4* order by 1
SQL> /

DEST_ID DB_UNIQUE_NAME STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ# SYN GAP_STATUS
---------- -------------- --------- ---------------- ------------- ------------ --- ------------------------
1 NONE VALID LOCAL 402 0 NO
2 STDBYDB VALID PHYSICAL 402 401 YES NO GAP
3 STDB2 VALID PHYSICAL 402 384 NO NO GAP

SQL>


And this is the current status at the first Standby (STDBYDB)


From the alert log :

2021-08-13T23:03:00.998407+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 403 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 CURRENT 1036108814

SQL>


I now create a Backup of the Standby (STDBYDB) (for this test I will not be using Flashback Database or Snapshot Standby Database {see the first two blog posts listed at the beginning for demonstrations of both methods}) :


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 13 23:07:04 2021
Version 19.12.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)

RMAN> backup as compressed backupset database;

Starting backup at 13-AUG-21
Starting implicit crosscheck backup at 13-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=252 device type=DISK
Crosschecked 10 objects
Crosschecked 11 objects
Finished implicit crosscheck backup at 13-AUG-21

Starting implicit crosscheck copy at 13-AUG-21
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 13-AUG-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 13-AUG-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 13-AUG-21


....
....
....


Starting Control File and SPFILE Autobackup at 13-AUG-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_08_13/o1_mf_s_1080515319_jkf2tzhp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-AUG-21

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>



I now execute a FAILOVER command at STDBYDB :


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 13 23:11:08 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> alter database failover to STDBYDB;

Database altered.

SQL>
SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup open;
ORACLE instance started.

Total System Global Area 1207955544 bytes
Fixed Size 9134168 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL>
SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 PARENT 1036108814
4 14030903 13-AUG-21 23:11 CURRENT 1080515513

SQL>


Meanwhile, the Primary shows that it no longer communicates with STDBYDB


SQL> l
1 select dest_id, db_unique_name, status, type, archived_seq#, applied_seq#, synchronized, gap_status
2 from v$archive_dest_status
3 where dest_id in (1,2,3)
4* order by 1
SQL> /

DEST_ID DB_UNIQUE_NAME STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ# SYN GAP_STATUS
---------- -------------- --------- ---------------- ------------- ------------ --- ------------------------
1 NONE VALID LOCAL 403 0 NO
2 STDBYDB ERROR PHYSICAL 401 401 NO RESOLVABLE GAP
3 STDB2 VALID PHYSICAL 403 386 NO NO GAP

SQL>


from the alert log :
2021-08-13T23:02:58.137970+08:00
NET (PID:32207): Archived Log entry 956 added for T-1.S-401 ID 0xa7521ccd LAD:1
2021-08-13T23:03:00.379365+08:00
ALTER SYSTEM ARCHIVE LOG
2021-08-13T23:03:01.008064+08:00
Thread 1 advanced to log sequence 403 (LGWR switch), current SCN: 14029372
Current log# 1 seq# 403 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-08-13T23:03:01.143735+08:00
NET (PID:32207): Archived Log entry 959 added for T-1.S-402 ID 0xa7521ccd LAD:1
2021-08-13T23:11:46.487983+08:00
LGWR (PID:3724): Attempting LAD:2 network reconnect (3113)
LGWR (PID:3724): LAD:2 network reconnect abandoned
2021-08-13T23:11:46.488223+08:00
Errors in file /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_lgwr_3724.trc:
ORA-03113: end-of-file on communication channel
LGWR (PID:3724): Error 3113 for LNO:1 to 'STDBYDB'
2021-08-13T23:11:46.532708+08:00
LGWR (PID:3724): LAD:2 is UNSYNCHRONIZED
2021-08-13T23:11:48.149800+08:00
LGWR (PID:3724): Failed to archive LNO:1 T-1.S-403, error=3113
LGWR (PID:3724): Error 1041 disconnecting from LAD:2 standby host 'STDBYDB'
2021-08-13T23:11:48.300060+08:00
Thread 1 advanced to log sequence 404 (LGWR switch), current SCN: 14030909
Current log# 2 seq# 404 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
2021-08-13T23:11:49.005544+08:00
ARC1 (PID:3853): Archived Log entry 961 added for T-1.S-403 ID 0xa7521ccd LAD:1
2021-08-13T23:13:23.840728+08:00
rfs (PID:5505): krsr_rfs_atc: Identified database type as 'PRIMARY': Client is Foreground (PID:5166)
rfs (PID:5505): Database mount ID mismatch [0xa9f74b79:0xa9f7dc16] (2851556217:2851593238)
rfs (PID:5505): Not using real application clusters


So, now the STDBYDB database has diverged from the Primary at SCN#14030903 and no longer accepts Redo from the Primary.
The STDBYDB database is now at Incarnation#4


I can stop Redo Shipping from the Primary


SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL> select incarnation#, resetlogs_change#, to_char(resetlogs_time,'DD-MON-RR HH24:MI') resetlogs_time,
2 status, resetlogs_id
3 from v$database_incarnation
4 order by 1
5 /

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME STATUS RESETLOGS_ID
------------ ----------------- ------------------------ ------- ------------
1 1 17-APR-19 00:55 PARENT 1005785759
2 1920977 04-MAY-19 23:21 PARENT 1007421686
3 4797184 27-MAR-20 00:00 CURRENT 1036108814

SQL>
SQL> l
1 select dest_id, db_unique_name, status, type, archived_seq#, applied_seq#, synchronized, gap_status
2 from v$archive_dest_status
3 where dest_id in (1,2,3)
4* order by 1
SQL> /

DEST_ID DB_UNIQUE_NAME STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ# SYN GAP_STATUS
---------- -------------- --------- ---------------- ------------- ------------ --- ------------------------
1 NONE VALID LOCAL 403 0 NO
2 STDBYDB ERROR PHYSICAL 401 401 NO RESOLVABLE GAP
3 STDB2 VALID PHYSICAL 403 386 NO NO GAP

SQL>
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>

from the alert log
2021-08-13T23:21:58.120088+08:00
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;


Notice how v$archive_dest_status at the Primary now show that Sequence#401 is the last Archived and Applied at STDBYDB. Sequence#402 and subsequent ones are no longer relevant to STDBYDB.

You might note that the STDB2 which I have configured as a Lagging Standby (DEST_ID=3) is still receiving Redo from the Primary.  So, that Standby is still active.  (See here and here as to how I configured STDB2 as a second Standby that is lagging)


In the next Blog Post, I will show what happens if I attempt to restore the backup of STDBYDB and resync with the Primary after executing multiple changes (redo) in this database want to discard it, inspite of it now running at a different Incarnation# (Incarnation=4)
Categories: DBA Blogs

Opening a Lagging Standby Database (to verify data ?)

Sat, 2021-07-24 06:04

 As shown in my previous blog post, you can create a Standby Database that lags the Primary by not applying Redo immediately but "waiting" for a specified interval.  It continues to receive and  ArchiveLogs but simply applies each only after the "wait interval".


So, first, the status at the Primary:

oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:03:12 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select systimestamp, database_role, current_scn from v$database;

SYSTIMESTAMP DATABASE_ROLE CURRENT_SCN
-------------------------------------------------------------- ---------------- -----------
24-JUL-21 06.03.32.106863 PM +08:00 PRIMARY 13258062

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> select * from hemant.job_tracking_tbl order by 1;

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182
24-JUL-21 06.00.27.037797 PM 7 13257658

7 rows selected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 310
Next log sequence to archive 312
Current log sequence 312
SQL>


Now, the status at STDB2 (the Standby that is lagging with an enforced 60minutes delay

From the alert.log :
2021-07-24T17:51:15.716720+08:00
PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_303_1036108814.dbf
PR00 (PID:2924): Media Recovery Delayed for 59 minute(s) T-1.S-304
2021-07-24T17:57:26.299295+08:00
PR00 (PID:2924): Media Recovery Log /opt/oracle/archivelog/STDB21_304_1036108814.dbf
2021-07-24T17:57:44.580258+08:00
PR00 (PID:2924): Media Recovery Delayed for 60 minute(s) T-1.S-305
2021-07-24T18:00:32.550708+08:00
rfs (PID:3452): Archived Log entry 52 added for B-1036108814.T-1.S-311 ID 0xa7521ccd LAD:3
2021-07-24T18:00:33.444329+08:00
rfs (PID:3452): Selected LNO:4 for T-1.S-312 dbid 2778483057 branch 1036108814


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:05:53 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select systimestamp, database_role, open_mode, current_scn from v$database;

SYSTIMESTAMP DATABASE_ROLE OPEN_MODE CURRENT_SCN
--------------------------------------------------- ---------------- ------------ -----------
24-JUL-21 06.06.51.313616 PM +08:00 PHYSICAL STANDBY READ ONLY 13239390

SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> select * from hemant.job_tracking_tbl order by 1;

CURRENT_TIMESTAMP JOB_ID CURRENT_SCN
-------------------------------------------------------------- ---------- -----------
24-JUL-21 04.57.21.676949 PM 1 13239134

SQL>


The Primary database is at Log Sequence#312. This Standby has applied only Sequence#304. Let me resume Recovery for some more time and then check the Standby again.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:11:12 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>


Later ... from the STDB2 alert log :

2021-07-24T18:40:22.531574+08:00
PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_309_1036108814.dbf
PR00 (PID:29990): Media Recovery Delayed for 59 minute(s) T-1.S-310
2021-07-24T18:40:43.574486+08:00
rfs (PID:531): No SRLs available for T-1
2021-07-24T18:40:43.743506+08:00
rfs (PID:531): Opened log for T-1.S-317 dbid 2778483057 branch 1036108814
2021-07-24T18:40:43.762715+08:00
ARC3 (PID:29836): Archived Log entry 57 added for T-1.S-316 ID 0xa7521ccd LAD:1
2021-07-24T18:40:43.762785+08:00
ARC3 (PID:29836): Archive log for T-1.S-316 available in 60 minute(s)
2021-07-24T18:49:27.636427+08:00
PR00 (PID:29990): Media Recovery Log /opt/oracle/archivelog/STDB21_310_1036108814.dbf
PR00 (PID:29990): Media Recovery Delayed for 60 minute(s) T-1.S-311
2021-07-24T18:50:45.257290+08:00
rfs (PID:531): Archived Log entry 58 added for B-1036108814.T-1.S-317 ID 0xa7521ccd LAD:3
2021-07-24T18:50:46.045279+08:00
rfs (PID:531): Selected LNO:4 for T-1.S-318 dbid 2778483057 branch 1036108814


oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 24 18:51:27 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL> alter session set container=orclpdb1;

Session altered.

SQL>
SQL> select * from hemant.job_tracking_tbl order by 1;

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182

6 rows selected.

SQL>
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
24-JUL-21 06.53.31.159094 PM +08:00

SQL>


So, now at 18:47, STDB2 has applied Sequence#310 and the database now shows data that came through that ArchiveLog. Upto JOB_ID=6, JOB_END_TIME=05:50:23pm
The Primary has already progressed further.

SQL> l
1* select * from hemant.job_tracking_tbl order by 1
SQL> /

JOB_END_TIME JOB_ID DB_SCN
-------------------------------------------------------------- ---------- ----------
24-JUL-21 04.57.21.676949 PM 1 13239134
24-JUL-21 05.04.29.870877 PM 2 13241261
24-JUL-21 05.30.17.962275 PM 3 13246616
24-JUL-21 05.39.10.912969 PM 4 13247859
24-JUL-21 05.40.20.865467 PM 5 13248159
24-JUL-21 05.50.23.930352 PM 6 13252182
24-JUL-21 06.00.27.037797 PM 7 13257658
24-JUL-21 06.10.33.163203 PM 8 13259223
24-JUL-21 06.20.36.839944 PM 9 13261275
24-JUL-21 06.22.46.972310 PM 10 13261560
24-JUL-21 06.30.39.787880 PM 11 13262799
24-JUL-21 06.37.18.623659 PM 12 13263658
24-JUL-21 06.40.41.713016 PM 13 13264263
24-JUL-21 06.50.43.755835 PM 14 13265798

14 rows selected.

SQL>


So, the operative methods at the Standby are :
 For Recovery :
1.  alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session

To Open and Query :
1. alter database recover managed standby database CANCEL
2. alter database OPEN READ ONLY
3. alter pluggable database <pdbname>   OPEN READ ONLY

To resume Recovery :
1. shutdown immediate
2. startup mount
3. alter database recover managed standby database USING ARCHIVED LOGFILE disconnect from session

While the Primary must specify a DELAY value in the log_archive_dest_n parameter for this destination Standby




Categories: DBA Blogs

A Standby that lags the Primary by a deliberate Delay

Tue, 2021-07-20 06:24

 As I noted in my previous blog post, with multiple Standby databases, you can have one or more of them, lagging the Primary. This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.

Here is a quick demo.

At the Primary I have :

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 280
Next log sequence to archive 282
Current log sequence 282
SQL>


At the first Standby "STDBYDB", I have :

2021-07-20T17:50:05.870763+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-282 (in transit)
2021-07-20T17:50:06.354006+08:00
ARC3 (PID:2736): Archived Log entry 35 added for T-1.S-281 ID 0xa7521ccd LAD:1
2021-07-20T17:50:06.396543+08:00
rfs (PID:3263): Archival of T-1.S-281 complete
2021-07-20T17:50:06.527483+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-282 dbid 2778483057 branch 1036108814
2021-07-20T17:50:07.008298+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 282 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


At the second Standby "STDB2", I have :

2021-07-20T17:50:09.484608+08:00
PR00 (PID:2975): Media Recovery Waiting for T-1.S-282 (in transit)
2021-07-20T17:50:09.500278+08:00
rfs (PID:3216): Opened log for T-1.S-282 dbid 2778483057 branch 1036108814
2021-07-20T17:50:09.527462+08:00
ARC3 (PID:2867): Archived Log entry 22 added for T-1.S-281 ID 0xa7521ccd LAD:1


To introduce a delay in applying ArchiveLogs at STDBY, I specify the DELAY parameter at the Primary database  :

SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 DELAY=60 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';

System altered.

SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string SERVICE=STDB2 DELAY=60 ASYNC V
ALID_FOR=(ONLINE_LOGFILES,PRIM
ARY_ROLE) DB_UNIQUE_NAME=STDB2
log_archive_dest_30 string
log_archive_dest_31 string
SQL>


So, I have introduced a lag of 60minutes for STDB2.  Over the next 60minutes, the Primary will continue generating Redo and ArchiveLogs and both Standbys will be receiving them.  But STDB2 will apply them only after 60minutes.  

However, to enforce this, I must also cause the Standby to *not* use Real Time Apply, so I must change the RECOVER command at the Standby  Note, however, that this must be done on the Standby first !  The Standby must start it's recovery with "USING ARCHIVED LOGFILE" *before* the Primary sets a DELAY value for the target log_archive_dest_n

oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 20 18:14:26 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

SQL>


If I do not change the RECOVER command at the Standby to explicitly specify "USING ARCHIVED LOGFILE", it still defaults to Real Time Apply and ignores the DELAY specified by the Primary. I get the message in STDB2 alert log :

 rfs (PID:13712): WARN: Managed Standby Recovery started with REAL TIME APPLY
rfs (PID:13712): WARN: DELAY 60 minutes specified at primary ignored


I review the alert log files for all 3 databases about an hour later.

This is the first Standby (STDBYDB)

2021-07-20T19:10:57.161885+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-297 (in transit)
2021-07-20T19:10:57.324337+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-297 dbid 2778483057 branch 1036108814
2021-07-20T19:10:58.401720+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 297 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-07-20T19:14:39.910894+08:00
ARC3 (PID:2736): Archived Log entry 51 added for T-1.S-297 ID 0xa7521ccd LAD:1
2021-07-20T19:14:39.943728+08:00
rfs (PID:3263): Standby controlfile consistent with primary
2021-07-20T19:14:40.136187+08:00
rfs (PID:3263): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814
2021-07-20T19:14:40.136811+08:00
PR00 (PID:2912): Media Recovery Waiting for T-1.S-298 (in transit)
2021-07-20T19:14:41.180355+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 298 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
13123569

SQL>


And this is the Second Standby (STDB2) :

2021-07-20T19:10:58.180405+08:00
rfs (PID:21331): No SRLs available for T-1
2021-07-20T19:10:58.319036+08:00
ARC0 (PID:2857): Archived Log entry 37 added for T-1.S-296 ID 0xa7521ccd LAD:1
2021-07-20T19:10:58.319358+08:00
ARC0 (PID:2857): Archive log for T-1.S-296 available in 60 minute(s)
2021-07-20T19:10:58.320321+08:00
rfs (PID:21331): Opened log for T-1.S-297 dbid 2778483057 branch 1036108814
2021-07-20T19:14:40.363888+08:00
rfs (PID:21331): Archived Log entry 38 added for B-1036108814.T-1.S-297 ID 0xa7521ccd LAD:3
2021-07-20T19:14:40.782081+08:00
rfs (PID:21331): Selected LNO:4 for T-1.S-298 dbid 2778483057 branch 1036108814
2021-07-20T19:15:12.430015+08:00
PR00 (PID:26793): Media Recovery Log /opt/oracle/archivelog/STDB21_286_1036108814.dbf
PR00 (PID:26793): Media Recovery Delayed for 60 minute(s) T-1.S-287

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDB2
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
13108691

SQL>


So, while the first Standby (STDBYDB) has already applied and archived Sequence#297 and is currently applying Sequence#298 from the Standby logfile, the second Standby (STDB2) has archived Sequence#297 and received Sequence#298 but notifies that Sequence#286 is currently being applied and the apply Sequence#297 is delayed by 60minutes.
I can also run an SCN_TO_TIMESTAMP query in the Primary (this cannot be executed on a Standby that is not OPEN) :

SQL> select scn_to_timestamp(13123569) At_STDBYDB from dual;

AT_STDBYDB
---------------------------------------------------------------------------
20-JUL-21 07.13.39.000000000 PM

SQL> select scn_to_timestamp(13108691) At_STDB2 from dual;

AT_STDB2
---------------------------------------------------------------------------
20-JUL-21 06.15.07.000000000 PM

SQL>


This shows that STDB2 is lagging by about 60minutes
So, if any "bad action"  (deletion of data or dropping of objects) is detected at the Primary (and a Flashback option is not available), the Standby can be OPENed Read Only to view the data as it was 1hour ago.  
I'll show that option in my next blog post.



Categories: DBA Blogs

Creating [1 or more] Additional Standby Database[s]

Thu, 2021-07-15 10:15

 In my previous demos, I've shown 1 Standby (STDBYDB) for the Primary (ORCLCDB).

However, an organisation may choose to have additional Standby Databases.  

Note that each Standby Database must be licensed.  If the Primary is licensed by Processor count (say 32 processors), each Standby must also be licensed by Processor count, although the Standbys may be running on servers with fewer processors, thus needing smaller licensing than the Primary.

Why would an organisation have additional Standbys ?  Multiple reasons

1.  Having additional Disaster Recovery Data Centres.  Thus, with 2 DRCs in addition to the Production, there may be 2 Standby Databases.

2. Choosing to have a Standby that can be used to run Reporting Queries when it is OPENed READ ONLY for, say, 4hours a day.  [If the organisation has the Active Dataguard Licence, the Standby can be running Reporting Queries all 24hours]  Without the Active Dataguard Licence, Recovery has to be stopped at this Standby for those 4hours, though it will continue to receive and Archive Redo from the Primary.  Once the 4hour window is closed, Recovery can resumed and the Standby will apply all the "pending" Redo [from the ArchiveLogs that it has received] to "catch-up" to the Primary

3. To test D.R. scenarios, whereby a secondary Standby is opened for Read-Write operations, without impacting the actual Production and first Standby instance which continue to be in-sync throughoug the D.R. testing.  The Standby may be opened Read Write with a manually created Guaranteed Restore Point   OR   it may be opened as a Snapshot Standby

4. To have an environment that can be opened day-time hours for UAT while it is resynced (i.e. Redo Apply done) with the Primary every night.  This will require the Standby to have Flashback Restore Points created and reverted to each day.

5. To have a Standby that is lagging the Primary by, say, 30minutes or 1hour or 4hours deliberately.  This allows the organisation a database that can be quickly opened for data recovery in case someone makes a mistake and deletes data or drops tables/objects from the Primary and the delete/drop has already been replicated to the first Standby.

6. To run [additional] Database Backups at a Standby DRC.  I prefer that the Primary and each Standby be running local database backups.


I have built my Second Standby with these parameters :

initSTDB2.ora :

*.audit_file_dest='/opt/oracle/admin/STDB2/adump'
*.audit_sys_operations=false
*.audit_trail='none'
*.commit_logging='batch'
*.commit_wait='nowait'
*.compatible='19.0.0'
*.control_files='/opt/oracle/oradata/STDB2/control01.ctl','/opt/oracle/oradata/STDB2/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/opt/oracle/oradata'
*.db_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.db_name='ORCLCDB'
*.db_recovery_file_dest_size=10G
*.db_recovery_file_dest='/opt/oracle/FRA/STDB2'
*.db_unique_name='STDB2'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STDB2XDB)'
*.enable_pluggable_database=true
*.fal_server='ORCLCDB'
*.filesystemio_options='setall'
*.local_listener='LISTENER_STDB2'
*.log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/STDB2'
###*.log_archive_dest_2='SERVICE=ORCLCDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB'
*.log_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDB2'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=384m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1152m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


and listener.ora :
LISTENER_STDB2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1532))
)
)

SID_LIST_LISTENER_STDB2 =
(SID_LIST=
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
(SID_NAME = STDB2)
)
)

and the password file copied from the Primary -- although Oracle will copy the password file in recent versions, I still prefer to do it myself
oracle19c>pwd
/opt/oracle/product/19c/dbhome_1/dbs
oracle19c>ls orapw$ORACLE_SID
orapwSTDB2
oracle19c>


Note how I have disabled log_archive_dest_2.  In this case, this Standby will never be part of a Role Reversal done either by Switchover or Failover [where it could become a Primary in the D.R. Data Centre and ship Redo to the old Primary in the Production Data Centre]

oracle19c>lsnrctl start listener_STDB2

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-JUL-2021 22:07:14

Copyright (c) 1991, 2019, Oracle. All rights reserved.

Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1532)))
STATUS of the LISTENER
------------------------
Alias listener_STDB2
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 15-JUL-2021 22:07:14
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/ora19cs1/listener_stdb2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1532)))
Services Summary...
Service "STDB2" has 1 instance(s).
Instance "STDB2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle19c>
oracle19c>echo $ORACLE_SID
STDB2
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 15 22:21:47 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7639040 bytes
SQL>


Then, at the Primary :

SQL> show parameter log_archive_config

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(ORCLCDB, STDBYDB)
SQL> alter system set log_archive_config='DG_CONFIG=(ORCLCDB, STDBYDB, STDB2)'; -- note that this update is also done at the first Standy STDBYDB2

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=STDBYDB SYNC AFFIRM VA
LID_FOR=(ONLINE_LOGFILES,PRIMA
RY_ROLE) DB_UNIQUE_NAME=STDBYD
B
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL> show parameter log_archive_dest_3

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
SQL> alter system set log_archive_dest_3='SERVICE=STDB2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDB2';

System altered.

SQL>


Note that log_archive_dest_2 is set to SYNC AFFIRM but log_archive_dest_3 is set to ASYNC and [NOAFFIRM]. Because I can afford a slight lag (latency) for this Standby.  Maybe it is at a Data Centre that is much further away, increasing the latency.



Then, I copy the Primary to the new Standby using RMAN (see the previous example)

-- assuming that the tnsnames.ora at the Primary is updated to include "STDB2"

oracle19c>rman target sys/manager auxiliary sys/manager@STDB2

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jul 15 22:24:00 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)
connected to auxiliary database: ORCLCDB (not mounted)

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 15-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=256 device type=DISK
current log archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDB2' ;
}
executing Memory Script

Starting backup at 15-JUL-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=274 device type=DISK

....
....
....
....
....

Finished Duplicate Db at 15-JUL-21

RMAN>


After that I shutdown and restart the new Standby and enable Recovery

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>



Let me now verify that the Primary is shipping Redo to *2* Standbys

2021-07-15T22:49:55.328276+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:49:56.801388+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-272
LGWR (PID:6404): SRL selected for T-1.S-272 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-272. However, all SRLs are currently active
2021-07-15T22:49:57.005260+08:00
Thread 1 advanced to log sequence 272 (LGWR switch)
Current log# 2 seq# 272 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
2021-07-15T22:49:57.973514+08:00
NET (PID:28568): Archived Log entry 588 added for T-1.S-271 ID 0xa7521ccd LAD:1
2021-07-15T22:49:59.487773+08:00
TT03 (PID:8344): SRL selected for T-1.S-272 for LAD:3
2021-07-15T22:50:06.667866+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:50:07.669103+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-273
LGWR (PID:6404): SRL selected for T-1.S-273 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-273. However, all SRLs are currently active
2021-07-15T22:50:07.862847+08:00
Thread 1 advanced to log sequence 273 (LGWR switch)
Current log# 3 seq# 273 mem# 0: /opt/oracle/oradata/ORCLCDB/redo03.log
2021-07-15T22:50:08.191087+08:00
NET (PID:28568): Archived Log entry 592 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:52:42.492619+08:00
ALTER SYSTEM ARCHIVE LOG
2021-07-15T22:52:42.988590+08:00
LGWR (PID:6404): SRL selected to archive T-1.S-274
LGWR (PID:6404): SRL selected for T-1.S-274 for LAD:2
LGWR (PID:6404): SRL selected to archive T-1.S-274. However, all SRLs are currently active
2021-07-15T22:52:43.059342+08:00
Thread 1 advanced to log sequence 274 (LGWR switch)
Current log# 1 seq# 274 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-07-15T22:52:43.142797+08:00
NET (PID:28568): Archived Log entry 594 added for T-1.S-273 ID 0xa7521ccd LAD:1
2021-07-15T22:52:43.917398+08:00
TT03 (PID:8344): SRL selected for T-1.S-274 for LAD:3



and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>


Thus, we can see that the Primary is shipping Redo to both "LAD2" and "LAD3"

While the first Standby (STDBYDB) shows :

ARC2 (PID:23577): Archived Log entry 26 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:50:07.448944+08:00
rfs (PID:23634): Archival of T-1.S-272 complete
2021-07-15T22:50:07.533082+08:00
PR00 (PID:23765): Media Recovery Waiting for T-1.S-273 (in transit)
2021-07-15T22:50:07.668714+08:00
rfs (PID:23634): Selected LNO:4 for T-1.S-273 dbid 2778483057 branch 1036108814
2021-07-15T22:50:09.298541+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 273 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-07-15T22:52:42.921683+08:00
rfs (PID:23634): Standby controlfile consistent with primary
rfs (PID:23634): No SRLs available for T-1
rfs (PID:23634): Waiting for T-1.S-273 archival to complete
2021-07-15T22:52:42.929839+08:00
ARC0 (PID:23569): Archived Log entry 27 added for T-1.S-273 ID 0xa7521ccd LAD:1
2021-07-15T22:52:42.935444+08:00
rfs (PID:23634): Archival of T-1.S-273 complete
2021-07-15T22:52:42.988242+08:00
rfs (PID:23634): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814
2021-07-15T22:52:43.001151+08:00
PR00 (PID:23765): Media Recovery Waiting for T-1.S-274 (in transit)
2021-07-15T22:52:43.002924+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL>


And the new Standby (STDB2) shows :

Recovery of Online Redo Log: Thread 1 Group 4 Seq 272 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log
2021-07-15T22:50:08.467719+08:00
rfs (PID:23637): No SRLs available for T-1
2021-07-15T22:50:08.530396+08:00
PR00 (PID:23655): Media Recovery Waiting for T-1.S-273 (in transit)
2021-07-15T22:50:08.532241+08:00
rfs (PID:23637): Opened log for T-1.S-273 dbid 2778483057 branch 1036108814
2021-07-15T22:50:08.532340+08:00
ARC0 (PID:23450): Archived Log entry 13 added for T-1.S-272 ID 0xa7521ccd LAD:1
2021-07-15T22:52:43.299392+08:00
rfs (PID:23637): Archived Log entry 14 added for B-1036108814.T-1.S-273 ID 0xa7521ccd LAD:3
2021-07-15T22:52:43.917098+08:00
rfs (PID:23637): Selected LNO:4 for T-1.S-274 dbid 2778483057 branch 1036108814
2021-07-15T22:52:44.137685+08:00
PR00 (PID:23655): Media Recovery Log /opt/oracle/archivelog/STDB21_273_1036108814.dbf
PR00 (PID:23655): Media Recovery Waiting for T-1.S-274 (in transit)
2021-07-15T22:52:44.576855+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 274 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDB2/stdbredo01.log


and

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>


So, now the Primary is shipping Redo to 2 Standbys. The first Standby (STDBYDB) is in MAXIMUM AVAILABILITY mode, the second Standby (STDB2) is in MAXIMUM PERFORMANCE mode.
(For simplicity, I haven't created additional Standby Redo Logs, I have only Standby Redo Log at Primary and each of the Standbys --- it is group#4, filename "stdbredo01.log" in the listing above)

    

In the next blog post, I will explore how to have STDB2 deliberately lagging the Primary by 30minutes (use case 5 in the list at the beginning of this blog post)


Categories: DBA Blogs

Failover to a Standby with Data Loss

Sat, 2021-07-10 05:47

 One scenario I consider when testing a Standby Database -- actually testing a Disaster Recovery  site --  is to simulate complete loss of the Primary Database (at the Production site)

Previously, I have demonstrated two methods of "Destructive" Disaster Recovery site testing :

1. Being able to Switchover to the Standby (at a Disaster Recovery site), verifying that transactions created at the Disaster Recovery site do actually flow back to the Production site and, finally, reverting both databases to their pre-test image)

2. Opening a Standby Database as a Snapshot Standby, verify that it is writable and, finally, reverting it to being a "normal" Standby


But what if you want to simulate complete loss of the Production site (Primary database) -- signifying that you may have even lost some transactions, but needing to open the Standby for Read-Write operations ?  OR you actually encounter such a situation where the Primary goes down when the Standby is lagging ? 

On my Primary I had these configured for MAXIMUM AVAILABILITY (with SYNC and AFFIRM for log_archive_dest_2) :

SQL>alter system set log_archive_dest_2='SERVICE=STDBYDB SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDB';

System altered.

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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL>
SQL> alter database SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY ;

Database altered.

SQL> shutdown
ORA-01109: database not open


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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> select protection_mode, open_mode, database_role from v$database;

PROTECTION_MODE OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM AVAILABILITY READ WRITE PRIMARY

SQL>


This is information of the Primary Database (at the Production site)  when Primary and Standby are in sync :

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 235
Next log sequence to archive 237
Current log sequence 237
SQL>


And this is at the Standby Database (at the Disaster Recovery site) :
 
rfs (PID:4415): Changing standby controlfile to MAXIMUM AVAILABILITY mode
rfs (PID:4415): Changing standby controlfile to RESYNCHRONIZATION level
rfs (PID:4415): Standby controlfile consistent with primary

Recovery of Online Redo Log: Thread 1 Group 5 Seq 237 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.log



My Standby database / site goes down for a short while.  
So the Primary reports :

LGWR (PID:4280): Failed to archive LNO:1 T-1.S-238, error=3113
LGWR (PID:4280): Error 1041 disconnecting from LAD:2 standby host 'STDBYDB'
2021-07-10T17:58:05.264661+08:00
Thread 1 advanced to log sequence 239 (LGWR switch)
Current log# 2 seq# 239 mem# 0: /opt/oracle/oradata/ORCLCDB/redo02.log
2021-07-10T17:58:05.390312+08:00
ARC1 (PID:4345): Archived Log entry 524 added for T-1.S-238 ID 0xa7521ccd LAD:1


and sometime later

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 242
Next log sequence to archive 244
Current log sequence 244
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
12768459

SQL>


We now know that the Standby (last applying Online Redo Sequence#237 [it went on to 238 before the Instance/Server crashed]) is lagging behind the Primary (current Redo Log 244, SCN 12768459 .

Reminder : The configuration is in MAXIMUM AVAILABILITY mode (not MAXIMUM PROTECTION).

Now, my Primary goes down.

So the FAILOVER must be issued from the Standby when I bring it up.  Let my try that now

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> create restore point before_failover guarantee flashback database;

Restore point created.

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

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

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL>
SQL> alter database failover to STDBYDB;

Database altered.

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

PROTECTION_MODE OPEN_MODE DATABASE_ROLE
-------------------- -------------------- ----------------
MAXIMUM PERFORMANCE MOUNTED PRIMARY

SQL> alter database open;

Database altered.

SQL>


Let's see what the alert log on the Standby shows

ARC1 (PID:6205): FAL: Error 12541 connecting to ORCLCDB for fetching gap sequence

2021-07-10T18:04:34.507622+08:00
RVWR started with pid=45, OS id=11261
2021-07-10T18:04:38.704492+08:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 5430064 bytes in shared pool for flashback generation buffer
2021-07-10T18:04:39.802983+08:00
Created guaranteed restore point BEFORE_FAILOVER

2021-07-10T18:05:50.402988+08:00
alter database failover to STDBYDB
2021-07-10T18:05:50.403086+08:00
.... (PID:6240): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:6240): information. This will result in messages beingoutput to the alert log
.... (PID:6240): file with the prefix 'TMI: '. This is being enabled to make the timing of
.... (PID:6240): the various stages of the role transition available for diagnostic purposes.
.... (PID:6240): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2021-07-10 18:05:50.403742
Terminal Recovery requested in process 6240
TMI: adbdrv termRecovery BEGIN 2021-07-10 18:05:50.408509
2021-07-10T18:05:50.494984+08:00
Attempt to do a Terminal Recovery (STDBYDB)
TMI: adbdrv termRecovery END 2021-07-10 18:05:50.495053
2021-07-10T18:05:50.495642+08:00
Media Recovery Start: Managed Standby Recovery (STDBYDB)
Started logmerger process
2021-07-10T18:05:50.532463+08:00
PR00 (PID:11393): Managed Standby Recovery not using Real Time Apply
max_pdb is 5
2021-07-10T18:05:51.094491+08:00
Parallel Media Recovery started with 2 slaves
2021-07-10T18:05:51.233396+08:00
stopping change tracking
2021-07-10T18:05:51.583393+08:00
PR00 (PID:11393): Begin: SRL archival
PR00 (PID:11393): End: SRL archival
PR00 (PID:11393): Terminal Recovery timestamp is '07/10/2021 18:05:51'
PR00 (PID:11393): Terminal Recovery: applying standby redo logs.
PR00 (PID:11393): Terminal Recovery: thread 1 seq# 238 redo required
2021-07-10T18:05:51.682050+08:00
PR00 (PID:11393): Terminal Recovery:
2021-07-10T18:05:51.685886+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 238 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
Terminal Recovery finished with No-Data-Loss
2021-07-10T18:05:51.985957+08:00
Incomplete Recovery applied until change 12767732 time 07/10/2021 17:57:59
2021-07-10T18:05:52.074427+08:00
Media Recovery Complete (STDBYDB)
2021-07-10T18:05:52.655358+08:00
Terminal Recovery: successful completion
PR00 (PID:11393): Forcing ARSCN to IRSCN for TR SCN:0x0000000000c2d1f4
PR00 (PID:11393): Attempt to set limbo arscn SCN:0x0000000000c2d1f4 irscn SCN:0x0000000000c2d1f4
PR00 (PID:11393): Resetting standby activation ID 2807176397 (0xa7521ccd)
stopping change tracking
2021-07-10T18:05:52.873082+08:00
ALTER DATABASE SWITCHOVER TO PRIMARY (STDBYDB)
2021-07-10T18:05:52.873684+08:00
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-07-10 18:05:52.873693
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-07-10 18:05:52.873771
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-07-10 18:05:52.874148
Backup controlfile written to trace file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_ora_6240.trc
2021-07-10T18:05:53.571015+08:00
Standby terminal recovery start SCN: 12767670
RESETLOGS after complete recovery through change 12767732
NET (PID:6240): ORL pre-clearing operation disabled by switchover
Online log /opt/oracle/oradata/STDBYDB/redo01.log: Thread 1 Group 1 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo02.log: Thread 1 Group 2 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo03.log: Thread 1 Group 3 was previously cleared
2021-07-10T18:05:53.907884+08:00
Standby became primary SCN: 12767669
2021-07-10T18:05:53.908157+08:00
Setting recovery target incarnation to 4
2021-07-10T18:05:53.966494+08:00
NET (PID:6240): RT: Role transition work is not done
NET (PID:6240): The Time Management Interface (TMI) is being enabled for role transition
NET (PID:6240): information. This will result in messages beingoutput to the alert log
NET (PID:6240): file with the prefix 'TMI: '. This is being enabled to make the timing of
NET (PID:6240): the various stages of the role transition available for diagnostic purposes.
NET (PID:6240): This output will end when the role transition is complete.
NET (PID:6240): Redo network throttle feature is disabled at mount time
2021-07-10T18:05:54.087343+08:00
NET (PID:6240): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-07-10 18:05:54.088069
TMI: dbsdrv failover to target END 2021-07-10 18:05:54.088135
Failover completed with No-Data-Loss.
Completed: alter database failover to STDBYDB
2021-07-10T18:06:33.754882+08:00
alter database open
2021-07-10T18:06:33.755022+08:00
TMI: adbdrv open database BEGIN 2021-07-10 18:06:33.754898
Ping without log force is disabled:
instance mounted in exclusive mode.
2021-07-10T18:06:33.945688+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2021-07-10T18:06:33.945807+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
Endian type of dictionary set to little
2021-07-10T18:06:33.999216+08:00
Assigning activation ID 2848626420 (0xa9ca96f4)
2021-07-10T18:06:34.009104+08:00
LGWR (PID:6045): LAD:2 is UNSYNCHRONIZED
2021-07-10T18:06:34.503600+08:00
Thread 1 advanced to log sequence 2 (thread open)
2021-07-10T18:06:34.503635+08:00
ARC3 (PID:6209): Becoming the 'no SRL' ARCH
2021-07-10T18:06:34.503832+08:00
Redo log for group 2, sequence 2 is not located on DAX storage
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: /opt/oracle/oradata/STDBYDB/redo02.log
Successful open of redo thread 1
2021-07-10T18:06:34.521347+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2021-07-10T18:06:34.662361+08:00
TT03 (PID:11461): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2021-07-10T18:06:34.883461+08:00
ARC0 (PID:6199): Archived Log entry 259 added for T-1.S-1 ID 0xa9ca96f4 LAD:1
2021-07-10T18:06:35.784688+08:00
Undo initialization recovery: err:0 start: 1407469 end: 1407660 diff: 191 ms (0.2 seconds)
[6240] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 1407660 end: 1408339 diff: 679 ms (0.7 seconds)
Undo initialization finished serial:0 start:1407469 end:1408406 diff:937 ms (0.9 seconds)
Dictionary check beginning
2021-07-10T18:06:36.927538+08:00
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption for pdb 1..
Verifying file header compatibility for tablespace encryption completed for pdb 1
Database Characterset is AL32UTF8
No Resource Manager plan active
2021-07-10T18:06:39.177858+08:00


TT00 (PID:6201): Error 12541 received logging on to the standby
TT00 (PID:6201): Check whether the listener is up and running.
2021-07-10T18:06:39.185611+08:00
TT03 (PID:11461): Sleep 5 seconds and then try to clear SRLs in 3 time(s)
2021-07-10T18:06:39.733886+08:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 6240 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
2021-07-10T18:06:42.855357+08:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2021-07-10T18:06:43.050601+08:00
AQPC started with pid=50, OS id=11483
PDB$SEED(2):Autotune of undo retention is turned on.
2021-07-10T18:06:44.060956+08:00
PDB$SEED(2):Endian type of dictionary set to little
2021-07-10T18:06:44.314613+08:00
TT03 (PID:11461): Enabling archival of deferred physical standby SRLs
2021-07-10T18:06:44.532641+08:00
TT03 (PID:11461): Archived Log entry 260 added for T-1.S-238 ID 0xa7521ccd LAD:1
2021-07-10T18:06:44.681960+08:00
PDB$SEED(2):Undo initialization finished serial:0 start:1416557 end:1416557 diff:0 ms (0.0 seconds)
PDB$SEED(2):Pluggable database PDB$SEED dictionary check beginning
2021-07-10T18:06:48.986311+08:00
PDB$SEED(2):Pluggable Database PDB$SEED Dictionary check complete
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-07-10T18:07:04.127503+08:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-07-10T18:07:05.037256+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-07-10T18:07:06.501677+08:00
Starting background process CJQ0
2021-07-10T18:07:06.511870+08:00
CJQ0 started with pid=47, OS id=11538
Completed: alter database open


Note the messages 

 PR00 (PID:11393): Terminal Recovery:
2021-07-10T18:05:51.685886+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 238 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
Terminal Recovery finished with No-Data-Loss
2021-07-10T18:05:51.985957+08:00
Incomplete Recovery applied until change 12767732 time 07/10/2021 17:57:59
2021-07-10T18:05:52.074427+08:00
Media Recovery Complete (STDBYDB)
2021-07-10T18:05:52.655358+08:00
Terminal Recovery: successful completion

Standby terminal recovery start SCN: 12767670

Standby became primary SCN: 12767669

Failover completed with No-Data-Loss.
Completed: alter database failover to STDBYDB


The Standby completed Recovery to Sequence#238 and SCN 12767669 which are much lower then the last known ArchiveLog (Sequence#243) and SCN (something higher than 12768459).

I had expected to see an ORA-16472 error requiring the usage of the "ALTER DATABASE ACTIVATE STANDBY DATABASE" command.  Unfortunately, in this case, the Standby assumes that there are no transactions from the Primary and assumes that there is no Data Loss !

So, the next time you do a Failover, you have to be careful to identify whether there is actual Data Loss if you are unable to access the Primary database.

Now, at the Standby I can query

SQL> select scn_to_timestamp(12767669) from dual;

SCN_TO_TIMESTAMP(12767669)
---------------------------------------------------------------------------
10-JUL-21 05.48.25.000000000 PM

SQL>


which certainly tells me that the last transaction at the Standby is as of  05:48pm (approximately,  the SCN_TO_TIMESTAMP provides us an approximate time only), even though I startup the Standby at 06:04pm.  
Fortunately, when I can access the Primary and read the alert.log, I find that the Primary went down after 06:02pm, with the last ArchiveLog generated at 6:00pm


2021-07-10T18:00:12.509208+08:00
Thread 1 advanced to log sequence 244 (LGWR switch)
Current log# 1 seq# 244 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-07-10T18:00:12.634091+08:00
ARC2 (PID:4347): Archived Log entry 529 added for T-1.S-243 ID 0xa7521ccd LAD:1


So I can confirm that there IS Data Loss !

FYI , ORA-16472 is :


oracle19c>oerr ora 16472
16472, 0000, "failover failed due to data loss"
// *Cause: The database was in MaxAvailability or MaxProtection mode but
// terminal recovery finished with data loss.
// *Action: For a data loss failover, issue ALTER DATABASE ACTIVATE STANDBY
// DATABASE command.
oracle19c>


This signifies that a FAILOVER command has not succeeded and an ALTER DATABASE ACTIVATE STANDBY DATABASE must be issued.
Categories: DBA Blogs

Using a Snapshot Standby Database for [destructive] D.R. Testing

Tue, 2021-06-29 11:25

 In a previous blog post I have demonstrated how to use the Flashback Database feature to allow you to open a Standby Database for "destructive" testing (whereby you make changes that you want discarded later) and then revert all the changes with the FLASHBACK DATABASE command.

Another method is to open the existing Standby Database as a Snapshot Standby.

Essentially, the steps are :

1. Ensure that an FRA (instance parameters "db_recovery_file_dest" and "db_recovery_file_dest_size") is configured

2. If the Standby is in Recovery mode, stop recovery and confirm that it is only in MOUNT mode

3. Convert the Standby to a Snapshot Standby with "alter database convert to snapshot standby"

4. Open the Standby as a Read/Write database with "alter database open read write"

5. Begin Read/Write operations (i.e. what I call "destructive" testing which consists of updates to the database that you need discarded later so that it can resync with the Primary)

6. Continue using the Primary while the Standby is being tested.  The Standby will continue receiving Archive Logs from the Primary but will hold them and not apply them to the database

7. At the end of testing, revert to the MOUNT state and convert the Standby back to a [normal] Physical Standby with "alter database convert to physical standby"

8. Resume Recovery of the Standby database. You will see the Standby rollback all changes made at Step 5 and then apply all the Primary Archive Logs from Step 6 till it does a "catch-up" and is in sync with the Primary

Snapshot Standby actually uses the Flashback Database feature but you do *not* have to configure Flashback Database in either the Primary or Standby  -- note that any database change that prevents usage of Flashback Database will prevent you from converting the Snapshot Standby to a Physical Standby at Step 7.



Currently, the Standby is receiving and applying Redo :

PR00 (PID:25362): Media Recovery Waiting for T-1.S-24 (in transit)
2021-06-29T23:15:22.056684+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 24 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdredo04.log
2021-06-29T23:15:51.519366+08:00
ARC2 (PID:25135): Archived Log entry 6 added for T-1.S-24 ID 0xa998aceb LAD:1
2021-06-29T23:15:51.614140+08:00
rfs (PID:28617): Primary database is in MAXIMUM PERFORMANCE mode
2021-06-29T23:15:51.625100+08:00
PR00 (PID:25362): Media Recovery Waiting for T-1.S-25
2021-06-29T23:15:51.674786+08:00
rfs (PID:28617): Selected LNO:4 for T-1.S-25 dbid 2778483057 branch 1074297871
2021-06-29T23:15:52.671617+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 25 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdredo04.log


So, now, I chose to convert it to a Snapshot Standby.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/FRA/STDBYDB
db_recovery_file_dest_size big integer 10G
SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open read write;

Database altered.

SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>


This is what I see in the Standby alert log now ...

2021-06-29T23:18:07.359020+08:00
alter database recover managed standby database cancel
2021-06-29T23:18:07.360109+08:00
PR00 (PID:25362): MRP0: Background Media Recovery cancelled with status 16037
2021-06-29T23:18:07.360231+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_25362.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:25362): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2021-06-29T23:18:08.769438+08:00
Recovered data files to a consistent state at change 13251952
stopping change tracking
2021-06-29T23:18:08.770983+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_25362.trc:
ORA-16037: user requested cancel of managed recovery operation
2021-06-29T23:18:08.887583+08:00
Background Media Recovery process shutdown (STDBYDB)
2021-06-29T23:18:09.362417+08:00
Managed Standby Recovery Canceled (STDBYDB)
Completed: alter database recover managed standby database cancel
2021-06-29T23:18:21.398899+08:00alter database convert to snapshot standby
Starting background process RVWR
2021-06-29T23:18:21.820131+08:00
RVWR started with pid=45, OS id=29682
2021-06-29T23:18:24.330193+08:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 4664520 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_06/29/2021 23:18:21
.... (PID:29031): Killing 5 processes (PIDS:28412,28617,28424,28422,28426) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 29031
2021-06-29T23:18:25.156848+08:00
Process termination requested for pid 28617 [source = rdbms], [info = 2] [request issued by pid: 29031, uid: 54321]
2021-06-29T23:18:25.156966+08:00
Process termination requested for pid 28412 [source = rdbms], [info = 2] [request issued by pid: 29031, uid: 54321]
2021-06-29T23:18:25.156976+08:00
Process termination requested for pid 28424 [source = rdbms], [info = 2] [request issued by pid: 29031, uid: 54321]
2021-06-29T23:18:25.157075+08:00
Process termination requested for pid 28422 [source = rdbms], [info = 2] [request issued by pid: 29031, uid: 54321]
2021-06-29T23:18:25.157085+08:00
Process termination requested for pid 28426 [source = rdbms], [info = 2] [request issued by pid: 29031, uid: 54321]
2021-06-29T23:18:27.449097+08:00
.... (PID:29031): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]
.... (PID:29031): Begin: SRL archival
.... (PID:29031): End: SRL archival
2021-06-29T23:18:28.867461+08:00
RESETLOGS after incomplete recovery UNTIL CHANGE 13251952 time 06/29/2021 23:18:06
Resetting resetlogs activation ID 2845355243 (0xa998aceb)
Online log /opt/oracle/oradata/STDBYDB/redo01.log: Thread 1 Group 1 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo02.log: Thread 1 Group 2 was previously cleared
Online log /opt/oracle/oradata/STDBYDB/redo03.log: Thread 1 Group 3 was previously cleared
2021-06-29T23:18:30.336562+08:00
Standby became primary SCN: 13251950
2021-06-29T23:18:30.337002+08:00
Setting recovery target incarnation to 5
2021-06-29T23:18:31.503592+08:00
**********************************************************************
WARNING: The LOG_ARCHIVE_CONFIG parameter has NOT been defined but
remote Data Guard destinations have been configured. Oracle strongly
recommends that this parameter is set when using Data Guard as
described in the Data Guard manuals.
**********************************************************************
.... (PID:29031): Redo network throttle feature is disabled at mount time
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
2021-06-29T23:18:37.046934+08:00
alter database open read write
2021-06-29T23:18:37.175569+08:00
Ping without log force is disabled:
instance mounted in exclusive mode.
2021-06-29T23:18:37.305328+08:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2021-06-29T23:18:37.305440+08:00
Crash Recovery excluding pdb 4 which was cleanly closed.
Endian type of dictionary set to little
2021-06-29T23:18:37.380920+08:00
Assigning activation ID 2847610391 (0xa9bb1617)
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /opt/oracle/oradata/STDBYDB/redo01.log
Successful open of redo thread 1
2021-06-29T23:18:37.409372+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2021-06-29T23:18:37.410681+08:00
ARC2 (PID:25135): Becoming the 'no SRL' ARCH
2021-06-29T23:18:38.408371+08:00
Undo initialization recovery: err:0 start: 3634303 end: 3634511 diff: 208 ms (0.2 seconds)
[29031] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 3634511 end: 3635110 diff: 599 ms (0.6 seconds)
Undo initialization finished serial:0 start:3634303 end:3635201 diff:898 ms (0.9 seconds)
Dictionary check beginning
2021-06-29T23:18:39.390552+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/opt/oracle/oradata/STDBYDB/temp01.dbf'
2021-06-29T23:18:39.390607+08:00
File 201 not verified due to error ORA-01157
2021-06-29T23:18:39.457218+08:00
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption for pdb 1..
Verifying file header compatibility for tablespace encryption completed for pdb 1
2021-06-29T23:18:39.548199+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/opt/oracle/oradata/STDBYDB/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2021-06-29T23:18:39.549816+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/opt/oracle/oradata/STDBYDB/temp01.dbf'
2021-06-29T23:18:39.550371+08:00
File 201 not verified due to error ORA-01157
2021-06-29T23:18:39.587875+08:00
Re-creating tempfile /opt/oracle/oradata/STDBYDB/temp01.dbf
Database Characterset is AL32UTF8
2021-06-29T23:18:40.528004+08:00
No Resource Manager plan active
2021-06-29T23:18:41.713001+08:00
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
2021-06-29T23:18:42.808166+08:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 29031 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
2021-06-29T23:18:45.099661+08:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2021-06-29T23:18:45.252140+08:00
AQPC started with pid=44, OS id=29833
PDB$SEED(2):Autotune of undo retention is turned on.
2021-06-29T23:18:46.281751+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2021-06-29T23:18:46.283068+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01186: file 202 failed verification tests
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
2021-06-29T23:18:46.283122+08:00
File 202 not verified due to error ORA-01157
2021-06-29T23:18:46.299502+08:00
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:3642756 end:3642756 diff:0 ms (0.0 seconds)
PDB$SEED(2):Pluggable database PDB$SEED dictionary check beginning
PDB$SEED(2):Pluggable Database PDB$SEED Dictionary check complete
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-06-29T23:18:47.274699+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2021-06-29T23:18:47.275063+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01186: file 202 failed verification tests
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
2021-06-29T23:18:47.275116+08:00
File 202 not verified due to error ORA-01157
2021-06-29T23:18:47.278375+08:00
PDB$SEED(2):Re-creating tempfile /opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf
2021-06-29T23:18:53.913930+08:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-06-29T23:18:56.010088+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-06-29T23:18:56.556712+08:00
Starting background process CJQ0
2021-06-29T23:18:56.567311+08:00
CJQ0 started with pid=61, OS id=29890
Completed: alter database open read write
2021-06-29T23:18:56.718602+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_dbw0_24923.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2021-06-29T23:19:00.397276+08:00
Setting Resource Manager plan SCHEDULER[0x4D53]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager CDB plan DEFAULT_MAINTENANCE_PLAN via parameter
2021-06-29T23:19:01.355946+08:00


Ignore the error messages relating to the tempfile for pdbseed. For the Pluggable Database "ORCLPDB1", I have to actually OPEN it and then add the TEMPFILE

SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL>
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf' size 100M;

Tablespace altered.

SQL>


2021-06-29T23:31:16.350347+08:00
ORCLPDB1(3):alter pluggable database orclpdb1 open
ORCLPDB1(3):Autotune of undo retention is turned on.
2021-06-29T23:31:16.983648+08:00
ORCLPDB1(3):Endian type of dictionary set to little
2021-06-29T23:31:17.656064+08:00
ORCLPDB1(3):Undo initialization recovery: err:0 start: 4393577 end: 4393759 diff: 182 ms (0.2 seconds)
2021-06-29T23:31:18.148370+08:00
ORCLPDB1(3):[1962] Successfully onlined Undo Tablespace 2.
ORCLPDB1(3):Undo initialization online undo segments: err:0 start: 4393759 end: 4394252 diff: 493 ms (0.5 seconds)
ORCLPDB1(3):Undo initialization finished serial:0 start:4393577 end:4394295 diff:718 ms (0.7 seconds)
ORCLPDB1(3):Pluggable database ORCLPDB1 dictionary check beginning
ORCLPDB1(3):Dropping offline tempfile '/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf'
ORCLPDB1(3):Pluggable Database ORCLPDB1 Dictionary check complete
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
ORCLPDB1(3):*********************************************************************
ORCLPDB1(3):WARNING: The following temporary tablespaces in container(ORCLPDB1)
ORCLPDB1(3): contain no files.
ORCLPDB1(3): This condition can occur when a backup controlfile has
ORCLPDB1(3): been restored. It may be necessary to add files to these
ORCLPDB1(3): tablespaces. That can be done using the SQL statement:
ORCLPDB1(3):
ORCLPDB1(3): ALTER TABLESPACE tablespace_name ADD TEMPFILE
ORCLPDB1(3):
ORCLPDB1(3): Alternatively, if these temporary tablespaces are no longer
ORCLPDB1(3): needed, then they can be dropped.
ORCLPDB1(3): Empty temporary tablespace: TEMP
ORCLPDB1(3):*********************************************************************
2021-06-29T23:31:23.694969+08:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
ORCLPDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 1962 cid 3
2021-06-29T23:31:24.744735+08:00
Pluggable database ORCLPDB1 opened read write
ORCLPDB1(3):Completed: alter pluggable database orclpdb1 open
2021-06-29T23:36:08.893763+08:00
ORCLPDB1(3):alter tablespace temp add tempfile '/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf' size 100M
ORCLPDB1(3):Completed: alter tablespace temp add tempfile '/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf' size 100M


Now, my database with ORACLE_SID=STDBYDB is actually open in Read Write mode and I can execute transactions in it.

STDBYDB>sqlplus hemant/hemant@STDBYPDB1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 29 23:39:25 2021
Version 19.3.0.0.0

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

Last Successful login time: Fri Jun 25 2021 16:44:27 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table my_dummy_table as select * from dba_objects;

Table created.

SQL> insert into my_dummy_table select * from dba_objects;

72825 rows created.

SQL> commit;

Commit complete.

SQL>


Meanwhile, this Standby has been receiving Archive Logs from Sequence#26 onwards from the Primary but not applying them (not that the last one being applied before I converted this to a Snapshot Standby was Sequence#25 (if you scroll up this post and see the alert log entries):

2021-06-29T23:40:05.613462+08:00
rfs (PID:6610): Selected LNO:5 for T-1.S-26 dbid 2778483057 branch 1074297871
2021-06-29T23:40:06.191335+08:00
ARC3 (PID:25137): Archived Log entry 7 added for T-1.S-25 ID 0xa998aceb LAD:1
2021-06-29T23:41:31.588385+08:00
rfs (PID:6610): Selected LNO:4 for T-1.S-27 dbid 2778483057 branch 1074297871
2021-06-29T23:41:32.457688+08:00
ARC1 (PID:25133): Archived Log entry 8 added for T-1.S-26 ID 0xa998aceb LAD:1
2021-06-29T23:41:34.786546+08:00
rfs (PID:6610): Selected LNO:5 for T-1.S-28 dbid 2778483057 branch 1074297871
2021-06-29T23:41:34.972948+08:00
ARC2 (PID:25135): Archived Log entry 9 added for T-1.S-27 ID 0xa998aceb LAD:1
2021-06-29T23:41:38.291575+08:00
rfs (PID:6610): Selected LNO:4 for T-1.S-29 dbid 2778483057 branch 1074297871
2021-06-29T23:41:38.359875+08:00
ARC3 (PID:25137): Archived Log entry 10 added for T-1.S-28 ID 0xa998aceb LAD:1


After I am done with my testing (the "my_dummy_table" creation and insert statement simulate multiple transactions), I can revert this to a Physical Standby

STDBYDB>echo $ORACLE_SID
STDBYDB
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 29 23:48:50 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE

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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 436207616 bytes
Database Buffers 754974720 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

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

Database altered.

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

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL>


And if I check the alert log

2021-06-29T23:49:00.581987+08:00
Shutting down ORACLE instance (immediate) (OS id: 10178)
2021-06-29T23:49:02.106249+08:00
Stopping background process SMCO
2021-06-29T23:49:03.176041+08:00
Shutting down instance: further logons disabled
2021-06-29T23:49:03.419275+08:00
Stopping background process CJQ0
Stopping background process MMNL
2021-06-29T23:49:04.473142+08:00
Stopping background process MMON
2021-06-29T23:49:06.521607+08:00
alter pluggable database all close immediate
2021-06-29T23:49:06.659467+08:00
ORCLPDB1(3):JIT: pid 10178 requesting stop
ORCLPDB1(3):Buffer Cache flush deferred for PDB 3
2021-06-29T23:49:08.769129+08:00
Pluggable database ORCLPDB1 closed
Completed: alter pluggable database all close immediate
PDB$SEED(2):JIT: pid 10178 requesting stop
PDB$SEED(2):Buffer Cache flush deferred for PDB 2
License high water mark = 9
2021-06-29T23:49:09.947301+08:00
Dispatchers and shared servers shutdown

Data Pump shutdown on PDB: 1 in progress
2021-06-29T23:49:09.947665+08:00
Process termination requested for pid 6610 [source = rdbms], [info = 2] [request issued by pid: 10178, uid: 54321]
2021-06-29T23:49:09.947758+08:00
2021-06-29T23:49:09.947758+08:00
Process termination requested for pid 32278 [source = rdbms], [info = 2] [request issued by pid: 10178, uid: 54321]
2021-06-29T23:49:12.026734+08:00
ALTER DATABASE CLOSE NORMAL
2021-06-29T23:49:12.053755+08:00
Stopping Emon pool
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
2021-06-29T23:49:13.207695+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
Stopping Emon pool
stopping change tracking
2021-06-29T23:49:14.508126+08:00
Shutting down archive processes
2021-06-29T23:49:14.508360+08:00
TT00 (PID:25129): Gap Manager exiting
2021-06-29T23:49:15.508649+08:00
Archiving is disabled
2021-06-29T23:49:15.509020+08:00
ARC3 (PID:25137): ARCH shutting down
ARC3 (PID:25137): Archival stopped
2021-06-29T23:49:15.509302+08:00
ARC2 (PID:25135): ARCH shutting down
ARC2 (PID:25135): Archival stopped
2021-06-29T23:49:15.509646+08:00
ARC1 (PID:25133): ARCH shutting down
ARC1 (PID:25133): Archival stopped
2021-06-29T23:49:15.509894+08:00
ARC0 (PID:25127): ARCH shutting down
ARC0 (PID:25127): Archival stopped
2021-06-29T23:49:16.509551+08:00
Thread 1 closed at log sequence 1
Successful close of redo thread 1
2021-06-29T23:49:16.512464+08:00
Buffer Cache invalidation for all PDBs started
Buffer Cache invalidation for all PDBs complete
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
2021-06-29T23:49:17.721725+08:00
.... (PID:10178): Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
2021-06-29T23:49:18.723216+08:00
JIT: pid 10178 requesting stop
.... (PID:10178): Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
JIT: pid 10178 requesting stop
2021-06-29T23:49:18.753899+08:00
Stopping background process VKTM
2021-06-29T23:49:31.362905+08:00
Instance shutdown complete (OS id: 10178)
2021-06-29T23:49:40.001253+08:00
Starting ORACLE instance (normal) (OS id: 10479)
2021-06-29T23:49:40.008425+08:00
****************************************************
Sys-V shared memory will be used for creating SGA
****************************************************
2021-06-29T23:49:40.009279+08:00
**********************************************************************
2021-06-29T23:49:40.009331+08:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

2021-06-29T23:49:40.009453+08:00
Per process system memlock (soft) limit = 128G
2021-06-29T23:49:40.009507+08:00
....
.... removed normal alert log messages for instance startup
....
2021-06-29T23:49:42.110291+08:00
ALTER DATABASE MOUNT
2021-06-29T23:49:44.864639+08:00
Using default pga_aggregate_limit of 2048 MB
2021-06-29T23:49:46.335191+08:00
.... (PID:10659): Database role set to SNAPSHOT STANDBY [kcvfdb.c:8281]
**********************************************************************
WARNING: The LOG_ARCHIVE_CONFIG parameter has NOT been defined but
remote Data Guard destinations have been configured. Oracle strongly
recommends that this parameter is set when using Data Guard as
described in the Data Guard manuals.
**********************************************************************
.... (PID:10659): Redo network throttle feature is disabled at mount time
2021-06-29T23:49:46.670017+08:00
Successful mount of redo thread 1, with mount id 2847556374
2021-06-29T23:49:46.671968+08:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 8086776 bytes in shared pool for flashback generation buffer
Starting background process RVWR
2021-06-29T23:49:46.682551+08:00
RVWR started with pid=38, OS id=10680
2021-06-29T23:49:46.973026+08:00
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:10659): Using STANDBY_ARCHIVE_DEST parameter default value as /opt/oracle/archivelog/STDBYDB [krsd.c:18141]
.... (PID:10659): STARTING ARCH PROCESSES
Starting background process ARC0
2021-06-29T23:49:47.204833+08:00
TT00 (PID:10683): Gap Manager starting
2021-06-29T23:49:47.212662+08:00
ARC0 started with pid=40, OS id=10685
2021-06-29T23:49:47.223092+08:00
.... (PID:10659): ARC0: Archival started
.... (PID:10659): STARTING ARCH PROCESSES COMPLETE
2021-06-29T23:49:47.223272+08:00
ARC0 (PID:10685): Becoming a 'no FAL' ARCH
Completed: ALTER DATABASE MOUNT
ARC0 (PID:10685): Becoming the 'no SRL' ARCH
2021-06-29T23:49:48.204507+08:00
TMON (PID:10658): STARTING ARCH PROCESSES
Starting background process ARC1
Starting background process ARC2
2021-06-29T23:49:48.213579+08:00
ARC1 started with pid=41, OS id=10688
Starting background process ARC3
2021-06-29T23:49:48.223493+08:00
ARC2 started with pid=42, OS id=10690
TMON (PID:10658): ARC1: Archival started
TMON (PID:10658): ARC2: Archival started
2021-06-29T23:49:48.233487+08:00
ARC3 started with pid=43, OS id=10692
TMON (PID:10658): ARC3: Archival started
TMON (PID:10658): STARTING ARCH PROCESSES COMPLETE
2021-06-29T23:49:50.907722+08:00
rfs (PID:10708): Selected LNO:4 for T-1.S-29 dbid 2778483057 branch 1074297871
2021-06-29T23:49:56.957932+08:00
alter database convert to physical standby
2021-06-29T23:49:56.958049+08:00
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (STDBYDB)
2021-06-29T23:49:56.977719+08:00
.... (PID:10686): Killing 2 processes (PIDS:10706,10708) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 10686
2021-06-29T23:49:56.977988+08:00
Process termination requested for pid 10708 [source = rdbms], [info = 2] [request issued by pid: 10686, uid: 54321]
2021-06-29T23:49:56.978145+08:00
Process termination requested for pid 10706 [source = rdbms], [info = 2] [request issued by pid: 10686, uid: 54321]
2021-06-29T23:49:59.623973+08:00
Flashback Restore Start
2021-06-29T23:50:06.211660+08:00
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
2021-06-29T23:50:08.169218+08:00
Deleted Oracle managed file /opt/oracle/FRA/STDBYDB/STDBYDB/flashback/o1_mf_jfpgfxwn_.flb
Deleted Oracle managed file /opt/oracle/FRA/STDBYDB/STDBYDB/flashback/o1_mf_jfpgg215_.flb
Guaranteed restore point dropped
2021-06-29T23:50:08.204353+08:00
.... (PID:10686): Database role cleared from SNAPSHOT STANDBY [kcvs.c:8837]
Clearing standby activation ID 2847610391 (0xa9bb1617)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 27 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 28 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 29 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 30 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
.... (PID:10686): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8842]
.... (PID:10686): RT: Role transition work is not done
**********************************************************************
WARNING: The LOG_ARCHIVE_CONFIG parameter has NOT been defined but
remote Data Guard destinations have been configured. Oracle strongly
recommends that this parameter is set when using Data Guard as
described in the Data Guard manuals.
**********************************************************************
.... (PID:10686): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2021-06-29T23:50:08.445082+08:00
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: alter database convert to physical standby
2021-06-29T23:50:08.945385+08:00
rfs (PID:11113): Primary database is in MAXIMUM PERFORMANCE mode
2021-06-29T23:50:09.052749+08:00
rfs (PID:11113): Selected LNO:4 for T-1.S-29 dbid 2778483057 branch 1074297871
2021-06-29T23:50:28.597419+08:00
alter database recover managed standby database disconnect from session
2021-06-29T23:50:28.602993+08:00
Attempt to start background Managed Standby Recovery process (STDBYDB)
Starting background process MRP0
2021-06-29T23:50:28.613186+08:00
MRP0 started with pid=46, OS id=11197
2021-06-29T23:50:28.614021+08:00
Background Managed Standby Recovery process started (STDBYDB)
2021-06-29T23:50:33.639389+08:00
Started logmerger process
2021-06-29T23:50:33.675757+08:00
PR00 (PID:11203): Managed Standby Recovery starting Real Time Apply
max_pdb is 5
2021-06-29T23:50:33.879309+08:00
Parallel Media Recovery started with 2 slaves
2021-06-29T23:50:34.023681+08:00
stopping change tracking
2021-06-29T23:50:34.043787+08:00
TT02 (PID:11209): Waiting for all non-current ORLs to be archived
2021-06-29T23:50:34.043942+08:00
TT02 (PID:11209): All non-current ORLs have been archived
TT02 (PID:11209): Clearing ORL LNO:1 /opt/oracle/oradata/STDBYDB/redo01.log
Clearing online log 1 of thread 1 sequence number 1
2021-06-29T23:50:34.621214+08:00
Completed: alter database recover managed standby database disconnect from session
2021-06-29T23:50:35.522538+08:00
PR00 (PID:11203): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_25_1074297871.dbf
2021-06-29T23:50:43.531488+08:00
PR00 (PID:11203): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_26_1074297871.dbf
2021-06-29T23:50:45.201867+08:00
TT02 (PID:11209): Clearing ORL LNO:1 complete
2021-06-29T23:50:45.330739+08:00
PR00 (PID:11203): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_27_1074297871.dbf
2021-06-29T23:50:45.523635+08:00
PR00 (PID:11203): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_28_1074297871.dbf
PR00 (PID:11203): Media Recovery Waiting for T-1.S-29 (in transit)
2021-06-29T23:50:45.747920+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 29 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdredo04.log
2021-06-29T23:50:50.908827+08:00
TT02 (PID:11209): Waiting for all non-current ORLs to be archived
2021-06-29T23:50:50.908947+08:00
TT02 (PID:11209): All non-current ORLs have been archived
2021-06-29T23:57:47.333206+08:00
PR00 (PID:11203): Media Recovery Waiting for T-1.S-30
2021-06-29T23:57:47.338144+08:00
ARC3 (PID:10692): Archived Log entry 11 added for T-1.S-29 ID 0xa998aceb LAD:1
2021-06-29T23:57:47.351366+08:00
rfs (PID:14591): Primary database is in MAXIMUM PERFORMANCE mode
2021-06-29T23:57:47.397285+08:00
rfs (PID:14591): Selected LNO:4 for T-1.S-30 dbid 2778483057 branch 1074297871
2021-06-29T23:57:48.392151+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 30 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdredo04.log


We can see that it actually did a FLASHBACK DATABASE and DROP GUARANTEED RESTORE POINT(including deletion of Flashback Logs) between 2021-06-29T23:49:59.623973+08:00  and   2021-06-29T23:50:08.169218+08:00.
(This is, effectively, the same method that I demonstrated in the earlier blog post).  After converting to a Physical Standby it applied Archive Logs from Sequence#25 (which was incompletely applied as it was only in Real Time Apply mode at the first conversion to a Snapshot Standby) to Sequence#29 and even the new Sequence#30 being applied in Real Time Apply mode.

But what are the messages relating to datafiles 5, 6, 8, 27,28, 29, 30 ?  The first few are for PDBSEED -- which we know does not get updated and the last 4 are for a PDB called "RMANCAT" which I did *not* OPEN.  


SQL> l
1 select f.con_id, f.file#, p.name as pdb_name, f.name
2 from v$datafile f, v$pdbs p
3 where f.con_id=p.con_id
4 and f.file# in (5,6,8, 27, 28, 29, 30)
5* order by 1,2
SQL> /

CON_ID FILE# PDB_NAME NAME
---------- ---------- -------- --------------------------------------------------------------------------------
2 5 PDB$SEED /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
2 6 PDB$SEED /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
2 8 PDB$SEED /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
4 27 RMANCAT /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_syst
em_jckz3qym_.dbf

4 28 RMANCAT /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysa
ux_jckz3qy8_.dbf

4 29 RMANCAT /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undo
tbs1_jckz3r04_.dbf

4 30 RMANCAT /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman
_tbs_jckz3r09_.dbf


7 rows selected.

SQL>


Since these PDBs were *NOT* opened when the database was operating as a Snaphot Standby, they did not get updated.  I can actually ignore the message.  The datafiles are present but were simply not updated.  I do know that the datafiles are present and the RMANCAT PDB is being updated by operations on the Primary.

STDBYDB>cd /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile
STDBYDB>ls -l
total 860192
-rw-r-----. 1 oracle oinstall 104865792 Jun 30 00:14 o1_mf_rman_tbs_jckz3r09_.dbf
-rw-r-----. 1 oracle oinstall 377495552 Jun 30 00:14 o1_mf_sysaux_jckz3qy8_.dbf
-rw-r-----. 1 oracle oinstall 293609472 Jun 30 00:14 o1_mf_system_jckz3qym_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Jun 30 00:14 o1_mf_undotbs1_jckz3r04_.dbf
STDBYDB>


Also, I can ignore the messages relating to creation of Standby Redo Logs as I already have 3 Standby Redo Logs in the database.

SQL> l
1 select l.group#, l.status, f.member, l.bytes/1048576
2 from v$standby_log l, v$logfile f
3* where l.group#=f.group#
SQL> /

GROUP# STATUS MEMBER L.BYTES/1048576
---------- ---------- ------------------------------------------------ ---------------
4 UNASSIGNED /opt/oracle/oradata/STDBYDB/stdredo04.log 200
5 ACTIVE /opt/oracle/oradata/STDBYDB/stdredo05.log 200
6 UNASSIGNED /opt/oracle/oradata/STDBYDB/stdredo06.log 200

SQL> !ls -l /opt/oracle/oradata/STDBYDB/stdred*
-rw-r-----. 1 oracle oinstall 209715712 Jun 30 00:16 /opt/oracle/oradata/STDBYDB/stdredo04.log
-rw-r-----. 1 oracle oinstall 209715712 Jun 30 00:22 /opt/oracle/oradata/STDBYDB/stdredo05.log
-rw-r-----. 1 oracle oinstall 209715712 Jun 29 23:06 /opt/oracle/oradata/STDBYDB/stdredo06.log

SQL>



Finally : This was a demonstration of opening a Standby of a MultiTenant Database as a Snapshot, opening a selected PDB (ORCLPDB1) without having to open all the PDBs, executing transactions and then reverting the whole CDB back to a Physical Standby.


Categories: DBA Blogs

Restoring a Standby Database to the Primary

Thu, 2021-06-03 11:20

 When you lose the Primary database, you can restore a backup of the Standby to the Primary.


First, I start with a backup of the Standby :



SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cs1 MOUNTED 2778483057 ORCLCDB STANDBY PHYSICAL STANDBY 12445248

SQL> !echo $ORACLE_SID
STDBYDB

SQL>
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 3 22:29:59 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)

RMAN> backup as compressed backupset database;

Starting backup at 03-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
input datafile file number=00009 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00026 name=/opt/oracle/oradata/STDBYDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j36xw6vr_.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00028 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
input datafile file number=00027 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
input datafile file number=00029 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
input datafile file number=00030 name=/opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JUN-21
channel ORA_DISK_1: finished piece 1 at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp tag=TAG20210603T223042 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 03-JUN-21

Starting Control File and SPFILE Autobackup at 03-JUN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JUN-21

RMAN>


Let's say that some of the datafiles on my Primary are corrupt and I need to restore the Primary database from this Standby backup.  (Alternately, instead of restoring to the actual Primary, I might be planning to build a new Primary, maybe at a different Data Centre, from the Standby backup)
Let's assume that I had captured this information about the Primary before I lost the datafiles of the Primary  (I don't really need all this, I just need to know the Archive Log Sequence#s from the alert.log) :

SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 OPEN 2778483057 ORCLCDB CURRENT PRIMARY 12770765

SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 224
Next log sequence to archive 226
Current log sequence 226
SQL>
SQL> select sequence#, next_change#
2 from v$archived_log
3 where sequence# > 215
4 order by 1
5 /

SEQUENCE# NEXT_CHANGE#
---------- ------------
216 12439207
216 12439207
217 12545592
218 12645850
219 12746127
220 12760012
221 12760153
222 12760157
223 12760486
224 12770051
225 12770693

11 rows selected.

SQL>


Given that the Standby had SCN 12445248, after the RESTORE, I must RECOVER from SEQUENCE#217.
So I begin to restore the Standby backup which I've copied to the /var/tmp folder on the Primary / New Primary Server.  My ORACLE_SID here is ORCLCDB.


oracle19c>cd /var/tmp
oracle19c>tar xvf STDBYDB_bak.TAR
oracle19c>cd STDBYDB
oracle19c>pwd
/var/tmp/STDBYDB
oracle19c>ls -l
total 0
drwxr-x---. 3 oracle oinstall 23 May 22 2020 88129263B99F4BBDE0530100007F7BDF
drwxr-x---. 3 oracle oinstall 23 May 22 2020 8812E29864F47615E0530100007FA424
drwxr-x---. 5 oracle oinstall 60 Jun 3 22:33 autobackup
drwxr-x---. 5 oracle oinstall 60 Jun 3 22:30 backupset
drwxr-x---. 3 oracle oinstall 23 Apr 21 22:38 C07D1F2BA98C23D0E0530100007F7D34
drwxr-x---. 2 oracle oinstall 6 Mar 27 2020 flashback
drwxr-x---. 2 oracle oinstall 6 Mar 26 2020 onlinelog
oracle19c>
oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 3 23:11:01 2021
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207955552 bytes

Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes

RMAN> restore PRIMARY controlfile from
2> '/var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp';

Starting restore at 03-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/ORCLCDB/control01.ctl
output file name=/opt/oracle/oradata/ORCLCDB/control02.ctl
Finished restore at 03-JUN-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>


I now have the Standby Controlfile restored as a Primary Controlfile.
Next, I must remove information about unavailable backups and catalog only the backups that I have copied from the Standby.


RMAN> crosscheck backup;

Starting implicit crosscheck backup at 03-JUN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 03-JUN-21

Starting implicit crosscheck copy at 03-JUN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-JUN-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp RECID=21 STAMP=1065202775
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp RECID=22 STAMP=1065202831
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp RECID=23 STAMP=1065202866
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp RECID=24 STAMP=1065202903
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp RECID=25 STAMP=1065203298
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp RECID=26 STAMP=1065203397
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp RECID=27 STAMP=1070491024
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp RECID=28 STAMP=1070491023
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp RECID=29 STAMP=1070491063
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp RECID=30 STAMP=1070491067
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp RECID=31 STAMP=1070491092
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp RECID=32 STAMP=1070491093
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp RECID=33 STAMP=1070491119
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp RECID=34 STAMP=1070491118
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp RECID=35 STAMP=1070491152
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp RECID=36 STAMP=1074292243
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp RECID=37 STAMP=1074292298
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp RECID=38 STAMP=1074292334
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp RECID=39 STAMP=1074292359
Crosschecked 19 objects


RMAN>

RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
21 21 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp
22 22 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp
23 23 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp
24 24 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp
25 25 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp
26 26 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp
27 27 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp
28 28 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
29 29 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp
30 30 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp
31 31 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp
32 32 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp
33 33 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp
34 34 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp
35 35 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp
36 36 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
37 37 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
38 38 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
39 39 1 1 EXPIRED DISK /opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36yyqqo_.bkp RECID=21 STAMP=1065202775
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z0h1w_.bkp RECID=22 STAMP=1065202831
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_02_22/o1_mf_nnndf_TAG20210222T173935_j36z1lx8_.bkp RECID=23 STAMP=1065202866
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065202724_j36z2q4m_.bkp RECID=24 STAMP=1065202903
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_02_22/o1_mf_annnn_TAG20210222T174817_j36zh2k1_.bkp RECID=25 STAMP=1065203298
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_02_22/o1_mf_s_1065203261_j36zl5fz_.bkp RECID=26 STAMP=1065203397
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp RECID=27 STAMP=1070491024
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp RECID=28 STAMP=1070491023
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5qmn_.bkp RECID=29 STAMP=1070491063
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g5v6j_.bkp RECID=30 STAMP=1070491067
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6nhq_.bkp RECID=31 STAMP=1070491092
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g6p4q_.bkp RECID=32 STAMP=1070491093
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7j4f_.bkp RECID=33 STAMP=1070491119
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g7gj2_.bkp RECID=34 STAMP=1070491118
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp RECID=35 STAMP=1070491152
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp RECID=36 STAMP=1074292243
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp RECID=37 STAMP=1074292298
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp RECID=38 STAMP=1074292334
deleted backup piece
backup piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp RECID=39 STAMP=1074292359
Deleted 19 EXPIRED objects


RMAN>


RMAN> catalog start with '/var/tmp/STDBYDB';

searching for all files that match the pattern /var/tmp/STDBYDB

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/STDBYDB_bak.TAR
File Name: /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
File Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
File Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
File Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
File Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
File Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
File Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
File Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
File Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp

List of Files Which Were Not Cataloged
=======================================
File Name: /var/tmp/STDBYDB_bak.TAR
RMAN-07517: Reason: The file header is corrupted

RMAN>

RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
40 Full 119.55M DISK 00:00:20 03-JUN-21
BP Key: 40 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
List of Datafiles in backup set 40
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
6 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
8 Full 4931679 20-MAY-20 NO /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Full 150.14M DISK 00:00:32 03-JUN-21
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
List of Datafiles in backup set 41
Container ID: 3, PDB Name: ORCLPDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 12441929 01-JUN-21 12444076 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
10 Full 12441973 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
11 Full 12442030 01-JUN-21 12444077 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
12 Full 12442056 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
26 Full 12442098 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j36xw6vr_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Full 17.95M DISK 00:00:02 03-JUN-21
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: TAG20210603T223304
Piece Name: /var/tmp/STDBYDB/autobackup/2021_06_03/o1_mf_s_1074102643_jckt126x_.bkp
SPFILE Included: Modification time: 03-JUN-21
SPFILE db_unique_name: STDBYDB
Standby Control File Included: Ckp SCN: 12445249 Ckp time: 01-JUN-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Full 318.54M DISK 00:00:45 03-JUN-21
BP Key: 43 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 12441752 01-JUN-21 12444640 NO /opt/oracle/oradata/STDBYDB/system01.dbf
3 Full 12441842 01-JUN-21 12445226 NO /opt/oracle/oradata/STDBYDB/sysaux01.dbf
4 Full 12441901 01-JUN-21 12445227 NO /opt/oracle/oradata/STDBYDB/undotbs01.dbf
7 Full 12441924 01-JUN-21 NO /opt/oracle/oradata/STDBYDB/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
44 Full 121.72M DISK 00:00:22 03-JUN-21
BP Key: 44 Status: AVAILABLE Compressed: YES Tag: TAG20210603T223042
Piece Name: /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
List of Datafiles in backup set 44
Container ID: 4, PDB Name: RMANCAT
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
27 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
28 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
29 Full 11082204 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
30 Full 11082204 21-APR-21 NO /opt/oracle/oradata/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf

RMAN>

RMAN> quit


Recovery Manager complete.
oracle19c>


So I have now CATALOGed all the backups that I copied over from the Standby server.
Before I proceed with the RESTORE, I verify the "database" information in the controlfile :


oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 3 23:37:54 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col host_name format a16
SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 MOUNTED 2778483057 ORCLCDB BACKUP PRIMARY 0

SQL>
SQL> show parameter db_create

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL> create pfile from spfile;

File created.

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> --- at this point modify the created pfile to set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT
SQL> !tail -2 $ORACLE_HOME/dbs/initORCLCDB.ora
*.db_file_name_convert='STDBYDB','ORCLCDB'
*.log_file_name_convert='STDBYDB','ORCLCDB'

SQL>
SQL> startup mount pfile='$ORACLE_HOME/dbs/initORCLCDB.ora';
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> show parameter file_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string STDBYDB, ORCLCDB
log_file_name_convert string STDBYDB, ORCLCDB
pdb_file_name_convert string
SQL>



This shows that I have the correct database controlfile based on the DBID and NAME and that database will now be a PRIMARY, not a Standby.
I cannot check the SCN because the database is not OPEN (in fact, it hasn't been RESTOREd yet !)
However, I had to set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT because the file names in the control file had STDBYDB in the path and db_create_file_dest would not overwrite that.

I can now proceed to RESTORE database :
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 3 23:58:05 2021
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057, not open)

RMAN> restore database;

Starting restore at 03-JUN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckswmk4_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckt07r9_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: restoring datafile 00026 to /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_jcky1j3g_.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jcksybw9_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00027 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf
channel ORA_DISK_1: restoring datafile 00028 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf
channel ORA_DISK_1: restoring datafile 00029 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf
channel ORA_DISK_1: restoring datafile 00030 to /opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f1p8x_.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp
channel ORA_DISK_1: piece handle=/var/tmp/STDBYDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2021_06_03/o1_mf_nnndf_TAG20210603T223042_jckszgbr_.bkp tag=TAG20210603T223042
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 04-JUN-21

RMAN>
RMAN> quit


Recovery Manager complete.


I can now proceed to RECOVER the database. Note how I had identified that I would need the ArchiveLogs from SEQUENCE#217 onwards.  Here, either I already have the ArchiveLogs on-disk (if I am restoring the database to the Primary)  OR have copied them from the Old Primary server to the New Primary Server (if I am building the database with a RESTORE on a new Server)
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 4 00:00:48 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12441752 generated at 06/01/2021 17:33:06 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_217_1036108814.dbf
ORA-00280: change 12441752 for thread 1 is in sequence #217


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12545592 generated at 06/03/2021 14:12:45 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_218_1036108814.dbf
ORA-00280: change 12545592 for thread 1 is in sequence #218
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_217_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12645850 generated at 06/03/2021 14:19:21 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_219_1036108814.dbf
ORA-00280: change 12645850 for thread 1 is in sequence #219
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_218_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12746127 generated at 06/03/2021 14:21:22 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_220_1036108814.dbf
ORA-00280: change 12746127 for thread 1 is in sequence #220
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_219_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760012 generated at 06/03/2021 15:04:16 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_221_1036108814.dbf
ORA-00280: change 12760012 for thread 1 is in sequence #221
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_220_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760153 generated at 06/03/2021 15:04:42 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_222_1036108814.dbf
ORA-00280: change 12760153 for thread 1 is in sequence #222
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_221_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760157 generated at 06/03/2021 15:04:43 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_223_1036108814.dbf
ORA-00280: change 12760157 for thread 1 is in sequence #223
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_222_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12760486 generated at 06/03/2021 22:43:35 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_224_1036108814.dbf
ORA-00280: change 12760486 for thread 1 is in sequence #224
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_223_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12770051 generated at 06/03/2021 22:45:50 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_225_1036108814.dbf
ORA-00280: change 12770051 for thread 1 is in sequence #225
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_224_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 12770693 generated at 06/03/2021 22:45:55 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_226_1036108814.dbf
ORA-00280: change 12770693 for thread 1 is in sequence #226
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_225_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/opt/oracle/archivelog/ORCLCDB/1_226_1036108814.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select
2 i.host_name, i.status,
3 d.dbid, d.name, d.controlfile_type, d.database_role, d.current_scn
4 from v$instance i, v$database d
5 /

HOST_NAME STATUS DBID NAME CONTROL DATABASE_ROLE CURRENT_SCN
---------------- ------------ ---------- --------- ------- ---------------- -----------
ora19cP1 OPEN 2778483057 ORCLCDB CURRENT PRIMARY 12773006

SQL>
SQL> select name from v$datafile order by 1;

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_jckz2yvk_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_jckz3r09_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_jckz3qy8_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_jckz3qym_.dbf
/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_jckz3r04_.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf

16 rows selected.

SQL>
SQL> create spfile from pfile;

File created.

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

Total System Global Area 1207955552 bytes
Fixed Size 9134176 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/19c/dbhome
_1/dbs/spfileORCLCDB.ora
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archivelog/ORCLCDB
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> alter system archive log current;

System altered.

SQL>


(Note : I removed the "<" and ">" signs around the "RET" to preservve HTML formatting)

If I had the Online Redo Logs still available on-disk, I could have done a COMPLETE Recovery of the INCOMPLETE Recovery until SEQUENCE#225.  Transactions in SEQUENCE#226 are lost because they were in the Online Redo Log that hadn't been Archived out at the Primary.

Earlier, my Primary was at SCN 12770765,  now (with the INCOMPLETE Recovery) it has been recovered to some transaction at around SCN 12773005 or thereabouts (because the OPEN RESETLOGS also increments the SCN).


Thus, I have restored from a Standby backup as of SCN 12445248 on server ora19cs1, where the ORACLE_SID and file names were STDBYDB  to SCN 12773005 as a new Primary on server ora19cP1 where the ORACLE_SID and file names are ORCLCDB

Of course, since this was an INCOMPLETE Recovery and I have created a new Incarnation of the Primary, I have to FLASHBACK the Standby to a Lower SCN  OR  Rebuild the Standby.
Had I been able to do a COMPLETE Recovery, I could have simply resumed DataGuard between the Primary and the Standby.

Note :  If the Restored Database is no longer to be a Primary in a DataGuard configuration, you can unset LOG_ARCHIVE_DEST_x  (where x is 2 or greater) and drop any Standby Log files currently defined.
Categories: DBA Blogs

Performance Hub for On-Premises Database

Sun, 2021-05-30 04:48

Previously, I have demonstrated the Performance Hub on an Oracle Autonomous Transaction Processing database on the Oracle Cloud.

 However, you can use the DBMS_PERF package for a similar report on your On-Premises database.

The code to generate such a report is (you can connect explicitly to the target PDB to generate the Performance Hub report) :

set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool DBMS_PERF_REPORT.html
select dbms_perf.report_perfhub(is_realtime=>1,type=>'active') from dual;
-- is_realtime 1 and active shows the report for the last 1hour
-- for more options see the documentation on DBMS_PERF
spool off
-- then edit the html file to remove the first line and the spool off command if necessary


I have generated a similar report for the database in my On-Premises VM.


See the 19c documentation on DBMS_PERF for more details.


Note :   The window is only 5minutes because the default selected_start_time parameter is 5minutes before the current time (i.e. the time when the report is generated)


Categories: DBA Blogs

A DDL Trigger ... and writing to the alert log file

Thu, 2021-05-27 08:42

 Below is sample code for a DDL Trigger that writes to the alert log when a specific condition is encountered.



SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb1;

Session altered.

SQL> @Drop_Table_not_permitted.sql
SQL> create or replace trigger DROP_TABLE_NOT_PERMITTED
2 after ddl
3 on database
4 begin
5 if (ora_sysevent='DROP' and ora_dict_obj_type = 'TABLE')
6 then
7 --- code for INSERT into an audit log table
8 --- INSERT INTO ....
9 ---
10 --- code below is to write to the alert log file
11 --- dbms_log is undocumented but available since 12c or 11.2.0.4
12 --- see https://jonathanlewis.wordpress.com/2018/10/12/dbms_log/
13 --- dbms_log.ksdwrt is the same as dbms_system.ksdwrt
14 --- see https://hemantoracledba.blogspot.com/2008/11/database-event-trigger-and-sysoper.html
15 dbms_log.ksdwrt(2,' Warning : ' || sys_context('USERENV','SESSION_USER')
16 || ' tried to drop ' || ora_dict_obj_name
17 || ' in SID ' || sys_context('USERENV','SID')
18 || ', AUDSID ' || sys_context('USERENV','SESSIONID')
19 || ' and service name ' || sys_context('USERENV','SERVICE_NAME'));
20 raise_application_error(-20001,'You are not permitted to Drop Tables');
21 end if;
22 end;
23 /

Trigger created.

SQL>


When I connect to the PDB and attempt to execute a DROP TABLE ...

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc dummy_tab
Name Null? Type
-------------------------------------------------------------- -------- ------------------------
ID_COL NUMBER

SQL> drop table dummy_tab;
drop table dummy_tab
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.DROP_TABLE_NOT_PERMITTED'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not permitted to Drop Tables
ORA-06512: at line 17


SQL>


The alert log shows this entry :

2021-05-27T21:38:11.859746+08:00
ORCLPDB1(3): Warning : HEMANT tried to drop DUMMY_TAB in SID 272, AUDSID 1061583 and service name orclpdb1


You could use more information from SYS_CONTEXT ( I have retrieved only some pieces of information in my PL/SQL Trigger code above) and populate an Audit Log table with more information.

Comment : The "2" as the first parameter to dbms_log.ksdwrt references the instance's alert log. "1" would reference the session's trace file.  "3" would reference both files.
Categories: DBA Blogs

Pages