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: 10 hours 46 min ago

SCN Synchronization when using a Database Link

Wed, 2022-09-07 09:36

 In my previous blog post about querying across a Database Link, I had stated "when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN "


Here is a quick demonstration :


-- First I verify the current SCN in two databases "ORCLCDB" and "CDB2"
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.10.49.643026 PM +08:00 19617903

ORCLCDB SQL>

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.10.52.378969 PM +08:00 18562711

CDB2 SQL>
-- there is a wide gap between the two -- with ORCLCDB being ahead by approximately 1million
============================================================================================



-- now I connect a client to a PDB in ORCLCDB
-- and run a SELECT query that queries across a DBLink a table in a PDB in CDB2
-- this query is NOT a user transaction, only a SELECT statement
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> set time on
22:11:33 SQL>
22:11:48 SQL> select count(*) from tmp_data@pdbtmp_data;

COUNT(*)
----------
73181

22:12:05 SQL>


-- Now I check the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.12.19.955477 PM +08:00 19620089

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.12.23.499409 PM +08:00 19620089

CDB2 SQL>
-- I can see that CDB2's SCN has "mysteriously" caught up with ORCLCDB
-- in fact they are exactly the same
-- in the real world there would still be some difference by the time I query again
-- because other transactions in either or both the databases would have incremented the SCN(s)
================================================================================================



-- now I disconnect from the client
-- this *closes* the database link
22:12:58 SQL> disconnect
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

-- and reconnect to the client
22:13:04 SQL> connect hemant/hemant@orclpdb1
Connected.
22:13:14 SQL>

-- I compare the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.13.22.738662 PM +08:00 19620223

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.13.30.874874 PM +08:00 19620229

CDB2 SQL>
-- the gap is very small
-- in fact some internal operations in CDB2 have slightly incremented it ahead
===============================================================================



-- I run a PLSQL procedure in my client that does 1million COMMITs in the PDB
-- to increase the SCN only in ORCLCDB
22:14:06 SQL> execute onemillioncommits;

PL/SQL procedure successfully completed.

22:15:03 SQL>
22:15:07 SQL>


-- I now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.15.21.713518 PM +08:00 20654338

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.15.23.779855 PM +08:00 19620296

CDB2 SQL>
-- and find that there is again a huge gap between the two databases, with ORCLCDB ahead again
==============================================================================================



-- I query across the Database Link again from my client session
22:15:46 SQL> select count(*) from tmp_data@pdbtmp_data;

COUNT(*)
----------
73181

22:15:50 SQL>


-- and now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;
NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.16.02.362146 PM +08:00 20654373

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.16.14.122201 PM +08:00 20654453

CDB2 SQL>
-- now the gap is very small
-- some other operations in ORCLCDB have already incremented the SCN after the query
====================================================================================


So, simply running a SELECT statement (not even a User-Initiated COMMIT causes the two databases to synchronise SCNs.  The database with the lower SCN "inherits" the higher SCN.

So, the next time you see database SCNs mysteriously jump (e.g. in V$DATABASE.CURRENT_SCN  or in V$ARCHIVED_LOG.FIRST_CHANGE#  or in V$DATAFILE.CHECKPOINT_SCN) very significantly, check to see if you have queries to/from the database that use one or more Database Links.

Categories: DBA Blogs

Querying across a Database Link

Tue, 2022-08-23 10:17

 In this demonstration, assume that I have a local table called "TARGET_DATA" with a list of Countries and Products.  A new company is being acquired and that organisations's product list has been loaded into a table called TMP_DATA in a different database PDBTMP.

So, I run a MINUS query to find the local products that are not in the new company's product list -- but this query is run across a Database Link.

Here is how I create the Database Link, run the MINUS query and then obtain a tkprof of the trace file generated for the query.


SQL> create database link pdbtmp_data
2 connect to data_load identified by data_load using 'pdbtmp';

Database link created.

SQL>
SQL> alter session set tracefile_identifier='MY_DBLINK_QRY';

Session altered.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL>
SQL> exec dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from target_data
2 minus
3 select * from tmp_data@pdbtmp_data
4 /

...
...

600 rows selected.

SQL> select 'x' from dual;

'
-
x

SQL> show array
arraysize 15
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>


-- tkprof of the trace file

select * from target_data
minus
select * from tmp_data@pdbtmp_data

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.24 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 41 0.17 0.30 453 455 0 600
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.19 0.55 453 455 1 600

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
600 600 600 MINUS (cr=455 pr=453 pw=0 time=306868 us starts=1)
71682 71682 71682 SORT UNIQUE (cr=455 pr=453 pw=0 time=142899 us starts=1 cost=872 size=2876679 card=73761)
73761 73761 73761 TABLE ACCESS FULL TARGET_DATA (cr=455 pr=453 pw=0 time=80877 us starts=1 cost=130 size=2876679 card=73761)
71143 71143 71143 SORT UNIQUE (cr=0 pr=0 pw=0 time=148559 us starts=1 cost=831 size=2927240 card=73181)
73181 73181 73181 REMOTE TMP_DATA (cr=0 pr=0 pw=0 time=96385 us starts=1 cost=80 size=2927240 card=73181)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 135 0.00 0.00
SQL*Net message to client 41 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 21 0.00 0.04
SQL*Net message to dblink 4 0.00 0.00
SQL*Net message from dblink 4 0.00 0.00
SQL*Net more data from dblink 352 0.01 0.05
SQL*Net message from client 41 8.84 8.89
********************************************************************************
SQL ID: 04vfkrajpkrnj Plan Hash: 1388734953

select 'x'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 11.31 11.31



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


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.24 0 0 1 0
Execute 3 0.01 0.01 0 676 0 1
Fetch 43 0.17 0.30 453 455 0 601
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48 0.20 0.56 453 1131 1 602

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 137 0.00 0.00
SQL*Net message to client 44 0.00 0.00
SQL*Net message from client 44 16.85 37.06
db file sequential read 1 0.00 0.00
single-task message 1 0.02 0.02
SQL*Net message from dblink 15 0.05 0.15
SQL*Net message to dblink 14 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file scattered read 21 0.00 0.04
SQL*Net more data from dblink 352 0.01 0.05




The query returns 600 rows but with an ARRAYSIZE of 15 for the sqlplus session, it results in 40 round trips (SQL*Net message to/from client)  (plus 1 additional for the parse) resulting a total of 41 SQL*Net messages.
However, the 73,181 rows from the remote database (PDBTMP) appear as 4 DBLink messages (SQL*Net message to/from dblink) and 352 "more data from dblink" waits.  So, there were many more network packets from PDBTMP to my local database.
But the total count of dblink messages is 15 (from dblink) and 14 (to dblink) -- an excess of about 11 over the initial 4.  Which means that there were some additional "chatty" interaction between my local database session and the one created in PDBTMP.


I also trace the session that is created by the Database Link operation in PDBTMP.  The tkprof for the trace file of that session is below :

SQL ID: bv2zwsyu4hq3k Plan Hash: 0

SELECT /*+ FULL(P) +*/ *
FROM
"TMP_DATA" P


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.05 4 101 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.04 0.05 4 101 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.14 0.14
********************************************************************************
SQL ID: 33v4pbtw6zjxt Plan Hash: 3633341221

SELECT "COUNTRY","PRODUCT_NAME"
FROM
"TMP_DATA" "TMP_DATA"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.05 0.08 450 455 0 73181
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.05 0.08 450 455 0 73181

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
73181 73181 73181 TABLE ACCESS FULL TMP_DATA (cr=455 pr=450 pw=0 time=66315 us starts=1 cost=124 size=2927240 card=73181)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 19 0.00 0.03
SQL*Net message from client 4 20.26 20.31
SQL*Net more data to client 352 0.00 0.00



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----------- ---------- ---------- ----------
Parse 2 0.04 0.05 4 101 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.05 0.08 450 455 0 73181
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.09 0.13 454 556 0 73181

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 11 0.00 0.00
SQL*Net message from client 11 20.26 20.47
db file sequential read 1 0.00 0.00
db file scattered read 19 0.00 0.03
SQL*Net more data to client 352 0.00 0.00




Note how the "select * from tmp_data" portion passed to PDBTMP is rewritten as 
SELECT /*+ FULL(P) +*/ *
FROM
"TMP_DATA" P
- and then as
SELECT "COUNTRY","PRODUCT_NAME"
FROM
"TMP_DATA" "TMP_DATA"

The remote database also shows that it sent 4 messages to/from "client" (in this case the client was my database session in my local database, not the sqlplus program) and 352 "more data" packets.  These numbers match what we see in the local trace file.
However, this also shows an additional 11 messages to/from "client".
There are some additional lines in the raw trace file which indicate what these overheads are.

One of them is for the logon trigger in PDBTMP that I have created to ensure that the DBLink connection generates a trace file.
The others are for recursive parse and other calls in PDBTMP (where parse success is sent to my "client" session in my local database).  Calls like :
select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1,  'NONE', 1, 'SESSION', 2, 'SELECT',  4, 'TRANSACTION',  8, 'AUTO' , 32,  1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1

insert into link_logons$(logon_time , source_id) VALUES ( SYSTIMESTAMP AT TIME ZONE 'UTC', :srcid )

XCTEND rlbk=0, rd_only=0, tim=....

XCTEND rlbk=1, rd_only=1, tim=...

XCTEND rlbk=0, rd_only=1, tim=...


So, a Database Link query has overheads. Also, when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN  A distributed query is effectively a *transaction* which requires implicit COMMIT/ROLLBACK.

(also notice the "insert into link_logon$"  which seems to be an audit implementation in the remote database PDBTMP).


Bonus : This is the ON LOGON TRIGGER in PDTMP which causes it to write to a Trace File :


SQL> select dbms_metadata.get_ddl('TRIGGER','DATA_LOAD_LOGON_TRIG') from dual;

DBMS_METADATA.GET_DDL('TRIGGER','DATA_LOAD_LOGON_TRIG')
--------------------------------------------------------------------------------

CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG"
after logon on database
WHEN (user = 'DATA_LOAD') begin
execute immediate 'alter session set tracefile_identifier=''DATA_LOAD''';
execute immediate 'alter session set statistics_level=''ALL''';
dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);
end;
ALTER TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG" ENABLE


Such a trigger is useful when you want to trace every logon by a particular user but don't know when the logon will occur.
Categories: DBA Blogs

Direct Path Inserts and Locks

Sun, 2022-08-14 04:40

 In my previous post I had demonstrated how a Direct Path Insert in one session blocks another Direct Path Insert from another session, except when the two sessions explicitly name separate target Partitions.

Here, I will lock at how Oracle creates Locks for such operations.  In the output below, the SQLPrompt is set to either 'Sesn_1' or 'Sesn_2' or 'SYSTEM' (the DBA session) to indicate which session is executing the SQL statement.

First, running the Direct Path Insert without specifying the target Partition name.



SYSTEM>l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name in ('MY_PART_TABLE')
5 and object_type in ('TABLE','TABLE PARTITION')
6* order by 1, 2 nulls first, 4
SYSTEM>/

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
MY_PART_TABLE TABLE 81817
MY_PART_TABLE P_100 TABLE PARTITION 81818 81827
MY_PART_TABLE P_200 TABLE PARTITION 81819 81828
MY_PART_TABLE P_300 TABLE PARTITION 81820 81820

SYSTEM>
SYSTEM>truncate table hemant.my_part_table;

Table truncated.

SYSTEM>
SYSTEM>select count(*) from v$locked_object;

COUNT(*)
----------
0

SYSTEM>



Sesn_1>select distinct sid from v$mystat;

SID
----------
138

Sesn_1>
Sesn_1>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>select distinct sid from v$mystat;

SID
----------
384

Sesn_2>
Sesn_2>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/
---------- Sesn_2 is now in a Wait



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
enq: TM - contention

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 6
384 81817 0

SYSTEM>
---------- Object ID 81817 is the Table itself, locked by Session 138 -- Sesn_1
SYSTEM>select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4 order by 1
5 /

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 TM 81817 0 6 0 1
138 AE 134 4057974068 4 0 0
138 TX 393246 7880 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 0 6 0

SYSTEM>
---------- Session 138 has TM Lock Mode6 on Object 81817 -- the Table itself
---------- Session 384 (Sesn_2, Waiting) is requesting a TM Lock Mode6



So, it is clear that Session 1 (SID 138) had a TM Lock (Mode->6) on the Table, blocking Session 2 (384) (which is requesting the same Mode->6 lock) when the Direct Path Insert specified the Table name alone.
The TX lock by SID 138  is the Transaction Row Exclusive Lock.  What is blocking Session 2 (SID 384) is that it is requesting a Mode->6 lock on the same Object (the Table) as already held by Session 1 (SID 138)

Then, when I issue a Rollback (or Commit) from Sesn_1, the Insert by Sesn_2 goes through.

Before this next test, I rollback both the Inserts

Next, with the target Partition named



Sesn_1>l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/

50 rows created.

Sesn_2>



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
SQL*Net message from client

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 3
138 81818 6
384 81817 3
384 81819 6

SYSTEM>
SYSTEM>l
1 select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4* order by 1,2,3
SYSTEM>/

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 AE 134 4057974068 4 0 0
138 TM 81817 0 3 0 0
138 TM 81818 0 6 0 0
138 TX 65540 6296 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 3 0 0
384 TM 81819 0 6 0 0
384 TX 458781 6211 6 0 0

8 rows selected.

SYSTEM>
---------- Both sessions SIDs 138 (Sesn_1) and 384 (Sesn_2) have the Table Object (81817) locked in Mode3, not Mode6
---------- But the Partitions (Objects 81818 and 81819) are locked in Mode6
---------- Neither is SID 138 Blocking any other session nor is SID 384 being blocked


Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6  by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.

Thus, as the Table itself is not locked in Mode->6, Sesn_2 is allowed a Direct Path Insert into another Partition -- only so as long as it explicitly names the Target Partition. (If Sesn_2 attempts to do a Direct Path Insert without naming a Target Partition, it will, again begin waiting on Sesn_1 without the REQUEST being evident in v$lock)

Mode 6 is Lock Table in Exclusive Mode in the first case.
In the second case, the Table is locked in Mode 3 which is Row-Exclusive, not Table level.

The "TX" is a Transaction Enqueue 

The "AE" Lock type is "Edition Enqueue" --- which we are not concerned with in this test, as it relates to Editioning (which I am not currently using) and will appear even if Editioning is not being used.


Categories: DBA Blogs

Direct Path Insert into a Partitioned Table

Fri, 2022-08-12 09:45

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

Thus :


--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
1 create table my_part_table (id_col number, data_col varchar2(25))
2 partition by range (id_col)
3 (partition p_100 values less than (101),
4 partition p_200 values less than (201),
5 partition p_300 values less than (301)
6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 : where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK


Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :


--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 : inserting into the second Parition p_200 explicitlly named
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see rows inserted by Session 1 and vice-versa, but not their own rows
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

COUNT(*)
----------
100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

COUNT(*)
----------
50

SQL> select count(*) from my_part_table partition (p_200);

COUNT(*)
----------
50

SQL>


Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.




Categories: DBA Blogs

The format of the ROWID

Sun, 2022-08-07 04:31

 A ROWID is a method of identifying the physical location of a row in an Oracle Database.  An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.

A ROWID (called an Extended ROWID) consists of 4 components :

    -    DataObject Number

    -    DataFile Number Relative to the Tablespace

    -    DataBlock Number (within the DataFile)

    -    RowNumber within the DataBlock


A Partitioned Table actually consists of multiple segments.  Each segment has a different DataObject Number.

Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :


The Normal Table :



SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;

Table created.

SQL>
SQL> insert into NONPARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into NONPARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from NONPARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA 1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81652 32 147
2 81652 32 151

SQL>


Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block.  Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652).  Is this really the Object ID ?



The Partitioned Table :


SQL> l
1 create table PARTITIONED (id_col number, data_col varchar2(1000))
2 partition by range (id_col)
3 (
4 partition P_1 values less than (2) tablespace HEMANT_DATA,
5 partition P_2 values less than (3) tablespace HEMANT_DATA,
6 partition P_3 values less than (4) tablespace HEMANT_DATA,
7 partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
8 )
9* tablespace HEMANT_DATA
SQL> /

Table created.

SQL>
SQL> insert into PARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into PARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from PARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA 1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from PARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81659 31 631
2 81660 31 1655

SQL>


In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.
(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)

In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the  Segment, not the Object Number of the Table.

Thus, to verify the Segments of the two tables, I can query and check :


SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81652
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>



Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.  
But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.
The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID  (81659 and 81660).



We know that when we rebuild a Table, the ROWID changes.  But this is actually because a new Segment is allocated.  

Thus, if I were to do a MOVE of the "Normal" Table :



SQL> alter table NONPARTITIONED move;

Table altered.

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81663 32 155
2 81663 32 156

SQL>
SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81663
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>


Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.



For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald




Categories: DBA Blogs

Index Statistic NUM_ROWS excludes Rows with NULL value

Sun, 2022-06-12 05:03

 A quick demonstration of the difference between the Table Statistic NUM_ROWS and the Index Statistic NUM_ROWS



SQL> desc customers
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
CUST_ID NOT NULL NUMBER
CUST_NAME VARCHAR2(128)
CUST_CITY VARCHAR2(128)
CUST_START_DATE DATE

SQL> select index_name, column_position, column_name
2 from user_ind_columns
3 where table_name = 'CUSTOMERS'
4 order by 1,2
5 /

INDEX_NAME COLUMN_POSITION COLUMN_NAME
---------------- --------------- ----------------
CUST_CITY_NDX 1 CUST_CITY
CUST_ID_NDX 1 CUST_ID

SQL>
SQL> select count(*) from customers;

COUNT(*)
----------
1299647

SQL>
SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL>
SQL> select last_analyzed, sample_size, num_rows
2 from user_tables
3 where table_name = 'CUSTOMERS'
4 /

LAST_ANAL SAMPLE_SIZE NUM_ROWS
--------- ----------- ----------
12-JUN-22 1299647 1299647

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>
SQL> select count(*)
2 from customers
3 where cust_city is null
4 /

COUNT(*)
----------
119999

SQL>
SQL> select 1299647-1179648 from dual;

1299647-1179648
---------------
119999

SQL>
SQL> create index cust_id_city_ndx
2 on customers(cust_id, cust_city)
3 /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_CITY_NDX 12-JUN-22 1299647 1299647
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>
SQL> drop index cust_id_city_ndx;

Index dropped.

SQL>
SQL> create index cust_city_id_ndx
2 on customers(cust_city, cust_id)
3 /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_ID_NDX 12-JUN-22 1299647 1299647
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>


In recent versions, a CREATE INDEX implicitly includes a Gather Stats call on the new Index by default, so the two new Indexes also had updated statistics.

The CUST_CITY column has 119999 rows with NULLs.  So, the Statistics on the Index CUST_CITY_NDX on this column did not include these new rows.

The two new Indexes that I created (CUST_ID_CITY_NDX and CUST_CITY_ID_NDX) were composite indexes where at least one column (CUST_ID - which is a Primary Key) is a NOT NULL.
Therefore, Statistics on these Indexes did include all the rows as, for every row in the Table, at least 1 column had a Not NULL value.

Categories: DBA Blogs

PDB Recovery Isolation in 21c

Sun, 2022-05-08 10:14

 In my previous blog post, where I demonstrated PDB Flashback, I had alluded to the "PDB Recovery Isolation" feature of 21c.

Here is a demonstration.

On the Primary, I issue only two commands to create a new PDB from an *existing, active* PDB (a PDB that is in Read-Write mode and is not  PDB$SEED) :  The source PDBHKC is active and you will notice the Archive Log Sequence# being incremented significantly as transactions are in-flight and continuing while PDBKC is being CREATEd and OPENed.


SQL> create pluggable database pdbnew from pdbhkc;

Pluggable database created.

SQL> alter pluggable database pdbnew open;

Pluggable database altered.

SQL> quit


These are the relevant Primary Database Alert entries (not every entry) :

2022-05-08T22:32:56.582810+08:00
create pluggable database pdbnew from pdbhkc

2022-05-08T22:33:02.783531+08:00
PDBNEW(6):Endian type of dictionary set to little

2022-05-08T22:33:03.103173+08:00
****************************************************************
Pluggable Database PDBNEW with pdb id - 6 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x000000000000011d
****************************************************************
PDBNEW(6):Media Recovery Start
2022-05-08T22:33:03.293420+08:00
PDBNEW(6):Serial Media Recovery started
PDBNEW(6):max_pdb is 6
2022-05-08T22:33:03.393288+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 1 Seq 73 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1/redo01.log
2022-05-08T22:33:03.541053+08:00
Thread 1 advanced to log sequence 75 (LGWR switch), current SCN: 4931897
Current log# 3 seq# 75 mem# 0: /u02/oradata/DB213H1/redo03.log
2022-05-08T22:33:03.541508+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 2 Seq 74 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1/redo02.log
2022-05-08T22:33:03.598468+08:00
PDBNEW(6):Incomplete Recovery applied until change 4931866 time 05/08/2022 22:33:02
PDBNEW(6):NET (PID:16256): Media Recovery Complete [dbsdrv.c:15048]
2022-05-08T22:33:03.615889+08:00
NET (PID:16388): Archived Log entry 119 added for B-1102494684.T-1.S-74 ID 0x7fbfe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:03.786205+08:00
PDBNEW(6):Pluggable database PDBNEW pseudo opening
PDBNEW(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBNEW(6):Autotune of undo retention is turned on.
PDBNEW(6):Undo initialization recovery: Parallel FPTR failed: start:15704441 end:15704446 diff:5 ms (0.0 seconds)
PDBNEW(6):Undo initialization recovery: err:0 start: 15704439 end: 15704511 diff: 72 ms (0.1 seconds)
PDBNEW(6):[16256] Successfully onlined Undo Tablespace 2.
PDBNEW(6):Undo initialization online undo segments: err:0 start: 15704512 end: 15704530 diff: 18 ms (0.0 seconds)
PDBNEW(6):Undo initialization finished serial:0 start:15704439 end:15704538 diff:99 ms (0.1 seconds)
PDBNEW(6):Database Characterset for PDBNEW is AL32UTF8
PDBNEW(6):Pluggable database PDBNEW pseudo closing
PDBNEW(6):JIT: pid 16256 requesting stop
PDBNEW(6):Closing sequence subsystem (15704602605).
PDBNEW(6):Buffer Cache flush started: 6
PDBNEW(6):Buffer Cache flush finished: 6
Completed: create pluggable database pdbnew from pdbhkc


2022-05-08T22:33:18.209272+08:00
alter pluggable database pdbnew open
2022-05-08T22:33:18.210926+08:00
PDBNEW(6):Pluggable database PDBNEW opening in read write
PDBNEW(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBNEW(6):Autotune of undo retention is turned on.
PDBNEW(6):Endian type of dictionary set to little
PDBNEW(6):Undo initialization recovery: Parallel FPTR complete: start:15718778 end:15718780 diff:2 ms (0.0 seconds)
PDBNEW(6):Undo initialization recovery: err:0 start: 15718778 end: 15718780 diff: 2 ms (0.0 seconds)
PDBNEW(6):[16256] Successfully onlined Undo Tablespace 2.
PDBNEW(6):Undo initialization online undo segments: err:0 start: 15718780 end: 15718873 diff: 93 ms (0.1 seconds)
PDBNEW(6):Undo initialization finished serial:0 start:15718778 end:15718875 diff:97 ms (0.1 seconds)
PDBNEW(6):Database Characterset for PDBNEW is AL32UTF8
PDBNEW(6):Deleting old file#13 from file$
PDBNEW(6):Deleting old file#14 from file$
PDBNEW(6):Deleting old file#15 from file$
PDBNEW(6):Adding new file#22 to file$(old file#13). fopr-1, newblks-35840, oldblks-19200
PDBNEW(6):Adding new file#23 to file$(old file#14). fopr-1, newblks-47360, oldblks-15360
PDBNEW(6):Adding new file#24 to file$(old file#15). fopr-1, newblks-12800, oldblks-12800
PDBNEW(6):Successfully created internal service PDBNEW at open
****************************************************************
Post plug operations are now complete.
Pluggable database PDBNEW with pdb id - 6 is now marked as NEW.
****************************************************************
PDBNEW(6):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)


2022-05-08T22:33:21.760166+08:00
Thread 1 advanced to log sequence 81 (LGWR switch), current SCN: 4933127
Current log# 3 seq# 81 mem# 0: /u02/oradata/DB213H1/redo03.log


2022-05-08T22:33:23.162867+08:00
PDBNEW(6):Opening pdb with no Resource Manager plan active
Pluggable database PDBNEW opened read write
Completed: alter pluggable database pdbnew open


(The "Deleting old file#" and "Adding new file#" entries are simply about updating the controlfile)
Note that Log Sequence# has moved from 72 to 81 during the course of the CREATE and OPEN of the new PDB.


These are the Standby Alert Log entries :


2022-05-08T22:32:57.237583+08:00
ARC0 (PID:1354): Archived Log entry 55 added for B-1102494684.T-1.S-73 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:32:57.237902+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-74 [krsr.c:18143]
2022-05-08T22:32:57.386776+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-74 (in transit) [krsm.c:6185]
2022-05-08T22:32:57.388789+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 74 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:02.900342+08:00
Recovery created pluggable database PDBNEW
PDBNEW(6):Tablespace-SYSTEM during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #22 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho4s6r_.dbf'.
PDBNEW(6):Tablespace-SYSAUX during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #23 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho4s6s_.dbf'.
PDBNEW(6):Tablespace-UNDOTBS1 during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #24 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho4s6t_.dbf'.
PDBNEW(6):Tablespace-TEMP during PDB create offlined since source is in r/w mode or this is a refresh clone
2022-05-08T22:33:03.709388+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-75 [krsr.c:18143]
2022-05-08T22:33:03.724813+08:00
ARC2 (PID:1362): Archived Log entry 56 added for B-1102494684.T-1.S-74 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:04.051241+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-75 (in transit) [krsm.c:6185]
2022-05-08T22:33:04.052945+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 75 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_4_k5yt8y5f_.log
2022-05-08T22:33:07.786948+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-76 [krsr.c:18143]
2022-05-08T22:33:07.811804+08:00
ARC0 (PID:1354): Archived Log entry 57 added for B-1102494684.T-1.S-75 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:07.926136+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-76 (in transit) [krsm.c:6185]
2022-05-08T22:33:07.927698+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 76 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:08.525489+08:00
PDBNEW(6):Full restore complete of datafile 24 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho53ny_.dbf. Elapsed time: 0:00:01
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3928601
PDBNEW(6):Datafile #24 has been copied to the standby.
2022-05-08T22:33:11.424337+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-77 [krsr.c:18143]
2022-05-08T22:33:11.492712+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-77 (in transit) [krsm.c:6185]
2022-05-08T22:33:11.494117+08:00
ARC2 (PID:1362): Archived Log entry 58 added for B-1102494684.T-1.S-76 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:11.552303+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 77 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_4_k5yt8y5f_.log
2022-05-08T22:33:12.363978+08:00
PDBNEW(6):Full restore complete of datafile 23 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho54o6_.dbf. Elapsed time: 0:00:04
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3862144
PDBNEW(6):Datafile #23 has been copied to the standby.
2022-05-08T22:33:14.572554+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-78 [krsr.c:18143]
2022-05-08T22:33:14.572781+08:00
Trying to expand controlfile section 11 for Oracle Managed Files
Expanded controlfile section 11 from 56 to 112 records
Requested to grow by 56 records; added 2 blocks of records
2022-05-08T22:33:14.585789+08:00
ARC0 (PID:1354): Archived Log entry 59 added for B-1102494684.T-1.S-77 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:14.650210+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-78 (in transit) [krsm.c:6185]
2022-05-08T22:33:14.698805+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:15.936076+08:00
PDBNEW(6):Full restore complete of datafile 22 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho58jz_.dbf. Elapsed time: 0:00:03
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3860354
PDBNEW(6):Datafile #22 has been copied to the standby.
2022-05-08T22:33:15.948328+08:00
PDBNEW(6):PDB Side Media Recovery started for pdbid(6)
PDBNEW(6):.... (PID:12667): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBNEW(6):max_pdb is 6
2022-05-08T22:33:16.031099+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_75_k7ho53pg_.arc
2022-05-08T22:33:16.045816+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_76_k7ho57fz_.arc
2022-05-08T22:33:16.076227+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_77_k7ho5bgp_.arc
PDBNEW(6):.... (PID:12667): Media Recovery Waiting for T-1.S-78 (in transit) [krsm.c:6185]
2022-05-08T22:33:16.106119+08:00
PDBNEW(6):Media Recovery of Online Log [Thread=1, Seq=78]
2022-05-08T22:33:16.106586+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
PDBNEW(6):PSR[6]:influxScn=4932081,nxtCkptScn=4932081,minScn=4932004,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843
PDBNEW(6):The merge request has been submitted by side recovery for pdbid 6
2022-05-08T22:33:16.114934+08:00
PR00 (PID:1438): Background Media Recovery cancelled with status 16037 [krd.c:27039]
PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions
2022-05-08T22:33:16.119431+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:1438): Managed Recovery not using Real Time Apply [krsm.c:15876]
Recovery interrupted!
Recovered data files to a consistent state at change 4932082
Stopping change tracking
2022-05-08T22:33:16.347544+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-05-08T22:33:17.205906+08:00
PDBNEW(6):.... (PID:12667): Side Recovery Complete [krds.c:1584]
2022-05-08T22:33:17.250805+08:00
ARC3 (PID:1364): Archived Log entry 60 added for B-1102494684.T-1.S-78 ID 0x7fd0e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:17.251185+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-79 [krsr.c:18143]
2022-05-08T22:33:20.411643+08:00
ARC1 (PID:1360): Archived Log entry 61 added for B-1102494684.T-1.S-79 ID 0x7fd1e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:20.411942+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-80 [krsr.c:18143]
2022-05-08T22:33:21.944250+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-81 [krsr.c:18143]
2022-05-08T22:33:21.944465+08:00
ARC2 (PID:1362): Archived Log entry 62 added for B-1102494684.T-1.S-80 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]


I have extracted below the important entries on the Standby that show the behaviour of PDB Recovery Isolation --- that Oracle at the Standby handles Recovery of the new PDB separately



2022-05-08T22:33:02.900342+08:00
Recovery created pluggable database PDBNEW
PDBNEW(6):Tablespace-SYSTEM during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #22 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho4s6r_.dbf'.
PDBNEW(6):Tablespace-SYSAUX during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #23 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho4s6s_.dbf'.
PDBNEW(6):Tablespace-UNDOTBS1 during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #24 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho4s6t_.dbf'.
PDBNEW(6):Tablespace-TEMP during PDB create offlined since source is in r/w mode or this is a refresh clone

PDBNEW(6):PDB Side Media Recovery started for pdbid(6)
PDBNEW(6):.... (PID:12667): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBNEW(6):max_pdb is 6

PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
PDBNEW(6):PSR[6]:influxScn=4932081,nxtCkptScn=4932081,minScn=4932004,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843
PDBNEW(6):The merge request has been submitted by side recovery for pdbid 6
2022-05-08T22:33:16.114934+08:00
PR00 (PID:1438): Background Media Recovery cancelled with status 16037 [krd.c:27039]
PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions
2022-05-08T22:33:16.119431+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:1438): Managed Recovery not using Real Time Apply [krsm.c:15876]
Recovery interrupted!
Recovered data files to a consistent state at change 4932082
Stopping change tracking
2022-05-08T22:33:16.347544+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-05-08T22:33:17.205906+08:00
PDBNEW(6):.... (PID:12667): Side Recovery Complete [krds.c:1584]

2022-05-08T22:33:17.250805+08:00
ARC3 (PID:1364): Archived Log entry 60 added for B-1102494684.T-1.S-78 ID 0x7fd0e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:17.251185+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-79 [krsr.c:18143]
2022-05-08T22:33:20.411643+08:00
ARC1 (PID:1360): Archived Log entry 61 added for B-1102494684.T-1.S-79 ID 0x7fd1e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:20.411942+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-80 [krsr.c:18143]
2022-05-08T22:33:21.944250+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-81 [krsr.c:18143]
2022-05-08T22:33:21.944465+08:00
ARC2 (PID:1362): Archived Log entry 62 added for B-1102494684.T-1.S-80 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:37.537823+08:00
all data files of pdbid 6 are brought online.
Started logmerger process
2022-05-08T22:33:37.592170+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:12697): Managed Recovery starting Real Time Apply [krsm.c:15865]
max_pdb is 6
2022-05-08T22:33:37.736778+08:00
Parallel Media Recovery started with 2 slaves
2022-05-08T22:33:37.858641+08:00
Stopping change tracking
2022-05-08T22:33:37.914220+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_78_k7ho5f5y_.arc [krd.c:9408]
2022-05-08T22:33:38.024387+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_79_k7ho5jc9_.arc [krd.c:9408]
2022-05-08T22:33:38.548017+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_80_k7ho5kvg_.arc [krd.c:9408]
2022-05-08T22:33:38.764339+08:00
PR00 (PID:12697): Media Recovery Waiting for T-1.S-81 (in transit) [krsm.c:6185]
2022-05-08T22:33:38.764667+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 81 Reading mem 0




First, it identifies that the Source PDB is in Read-Write mode.
Then it begins a "Side Media Recovery" for the new PDB PDBNEW(6).
Later, it temporarily halts recovery of the full Standby CDB to merge the  "Side Recovery".
Finally, it resumes "normal" Recovery of the Standby from ArchiveLog Sequence 78 onwards.


Categories: DBA Blogs

PDB Flashback in 21c

Tue, 2022-05-03 02:24

 Oracle 21c allows you to Flashback a Pluggable Database to a past point-in-time / SCN.


Here is a quick demo  (also with alert log messages from the Primary and Standby) :


On the Primary :


SQL> create pluggable database pdbhkc admin user hemant identified by hemant;

Pluggable database created.

SQL> alter pluggable database pdbhkc open;

Pluggable database altered.

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3688309

SQL>
SQL> alter session set container=PDBHKC;

Session altered.

SQL> grant dba to hemant; -- grant is done *after* SCN 3688309

Grant succeeded.

SQL> connect hemant/hemant@pdbhkc
Connected.
SQL>
SQL> create table obj_list as select * from user_objects;

Table created.

SQL> select count(*) from obj_list;

COUNT(*)
----------
0

SQL>
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3688878

SQL>
SQL> insert into obj_list select * from user_objects;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into obj_list select * from obj_list;

1 row created.

SQL> commit;

Commit complete.

SQL> insert into obj_list select * from obj_list;

2 rows created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3689045

SQL>
SQL> quit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


Now I will attempt a FLASHBACK to SCN 3688309

[oracle@primary admin]$ rman target /

Recovery Manager: Release 21.0.0.0.0 - Production on Tue May 3 14:18:15 2022
Version 21.3.0.0.0

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

connected to target database: DB213H1 (DBID=3870017436)

RMAN> alter pluggable database pdbhkc close;

Statement processed

RMAN>
RMAN> flashback pluggable database pdbhkc to scn 3688309;

Starting flashback at 03-MAY-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 03-MAY-22

RMAN>
RMAN> alter pluggable database pdbhkc open resetlogs;

Statement processed

RMAN>
RMAN> quit


Recovery Manager complete.
[oracle@primary admin]$ sqlplus hemant/hemant@pdbhkc

SQL*Plus: Release 21.0.0.0.0 - Production on Tue May 3 14:20:01 2022
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> select count(*) from user_objects;

COUNT(*)
----------
0

SQL>
SQL> select current_scn from v$database; -- this fails because DBA grant is not yet present
select current_scn from v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect / as sysdba
Connected.
SQL> alter session set container=PDBHKC;

Session altered.

SQL> grant dba to hemant;

Grant succeeded.

SQL> connect hemant/hemant@pdbhkc;
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
3691628

SQL>
SQL> connect / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

SQL>
SQL> col name format a8
SQL> get pdb_incarnations.sql
1 select pi.con_id, p.name, pi.pdb_incarnation#,
2 pi.incarnation_scn, pi.prior_pdb_incarnation#, pi.status
3 from v$pdb_incarnation pi, v$pdbs p
4 where pi.con_id=p.con_id
5* order by 1,2,3,4
SQL> /

CON_ID NAME PDB_INCARNATION# INCARNATION_SCN PRIOR_PDB_INCARNATION# STATUS
---------- -------- ---------------- --------------- ---------------------------------------- -------
2 PDB$SEED 0 1 PARENT
2 PDB$SEED 0 2601843 0 CURRENT
3 PDB1 0 1 PARENT
3 PDB1 0 2601843 0 CURRENT
4 PDBHKC 0 2601843 PARENT
4 PDBHKC 1 3688310 0 CURRENT

6 rows selected.

SQL>


Note how the "GRANT DBA" was after the Flashback SCN and had to be regranted. After the FLASHBACK, the custom table "obj_list" does not exist.


Primary Alert Log Entries :

Primary Instance alert log :
==============================
2022-05-03T14:18:28.808263+08:00
alter pluggable database pdbhkc close
2022-05-03T14:18:28.815690+08:00
PDBHKC(4):Pluggable database PDBHKC closing
PDBHKC(4):JIT: pid 7263 requesting stop
PDBHKC(4):Closing sequence subsystem (5915010500).
PDBHKC(4):Buffer Cache flush started: 4
PDBHKC(4):Buffer Cache flush finished: 4
Pluggable database PDBHKC closed
Completed: alter pluggable database pdbhkc close
2022-05-03T14:19:12.776065+08:00
RMAN flashback PDB to scn 3688309
Flashback Restore Start
Restore Flashback Pluggable Database PDBHKC (4) until change 3686342
Flashback Restore Complete
Flashback Media Recovery Start
2022-05-03T14:19:13.675674+08:00
Serial Media Recovery started
max_pdb is 4
2022-05-03T14:19:13.769641+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 28 Reading mem 0
Mem# 0: /u02/oradata/DB213H1/redo01.log
2022-05-03T14:19:13.970865+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 29 Reading mem 0
Mem# 0: /u02/oradata/DB213H1/redo02.log
2022-05-03T14:19:14.621235+08:00
Incomplete Recovery applied until change 3688310 time 05/03/2022 14:12:57
Flashback Media Recovery Complete
Flashback Pluggable Database PDBHKC (4) recovered until change 3688310
Completed: RMAN flashback PDB to scn 3688309
2022-05-03T14:19:42.382165+08:00
alter pluggable database pdbhkc open resetlogs
2022-05-03T14:19:42.461760+08:00
Online datafile 15
Online datafile 14
Online datafile 13
PDBHKC(4):Pluggable database PDBHKC pseudo opening
PDBHKC(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBHKC(4):Autotune of undo retention is turned on.
PDBHKC(4):Endian type of dictionary set to little
PDBHKC(4):Undo initialization recovery: Parallel FPTR failed: start:5988531 end:5988533 diff:2 ms (0.0 seconds)
PDBHKC(4):Undo initialization recovery: err:0 start: 5988531 end: 5988547 diff: 16 ms (0.0 seconds)
PDBHKC(4):[7263] Successfully onlined Undo Tablespace 2.
PDBHKC(4):Undo initialization online undo segments: err:0 start: 5988547 end: 5988555 diff: 8 ms (0.0 seconds)
PDBHKC(4):Undo initialization finished serial:0 start:5988531 end:5988556 diff:25 ms (0.0 seconds)
PDBHKC(4):Database Characterset for PDBHKC is AL32UTF8
PDBHKC(4):Pluggable database PDBHKC pseudo closing
PDBHKC(4):JIT: pid 7263 requesting stop
PDBHKC(4):Closing sequence subsystem (5988605782).
PDBHKC(4):Buffer Cache flush started: 4
PDBHKC(4):Buffer Cache flush finished: 4
PDBHKC(4):Pluggable database PDBHKC opening in read write
PDBHKC(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBHKC(4):Autotune of undo retention is turned on.
PDBHKC(4):Endian type of dictionary set to little
PDBHKC(4):Undo initialization recovery: Parallel FPTR complete: start:5989023 end:5989027 diff:4 ms (0.0 seconds)
PDBHKC(4):Undo initialization recovery: err:0 start: 5989023 end: 5989027 diff: 4 ms (0.0 seconds)
PDBHKC(4):[7263] Successfully onlined Undo Tablespace 2.
PDBHKC(4):Undo initialization online undo segments: err:0 start: 5989027 end: 5989188 diff: 161 ms (0.2 seconds)
PDBHKC(4):Undo initialization finished serial:0 start:5989023 end:5989194 diff:171 ms (0.2 seconds)
PDBHKC(4):Pluggable database PDBHKC dictionary check beginning
2022-05-03T14:19:43.379867+08:00
PDBHKC(4):Pluggable Database PDBHKC Dictionary check complete
PDBHKC(4):Database Characterset for PDBHKC is AL32UTF8
2022-05-03T14:19:43.935738+08:00
PDBHKC(4):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2022-05-03T14:19:44.385306+08:00
PDBHKC(4):Opening pdb with no Resource Manager plan active
2022-05-03T14:19:44.967205+08:00
Control autobackup written to DISK device

handle '/u01/app/oracle/DB213H1/autobackup/2022_05_03/o1_mf_s_1103725184_k71ld0sw_.bkp'

Pluggable database PDBHKC closed
Completed: alter pluggable database pdbhkc open resetlogs
2022-05-03T14:21:30.693825+08:00
2022-05-03T14:21:30.693825+08:00
PDBHKC(4):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P328 (2864) VALUES LESS THAN (TIMESTAMP' 2022-05-04 00:00:00')
2022-05-03T14:23:50.678383+08:00
ALTER SYSTEM ARCHIVE LOG
2022-05-03T14:23:50.737171+08:00
Thread 1 advanced to log sequence 30 (LGWR switch), current SCN: 3691895
Current log# 3 seq# 30 mem# 0: /u02/oradata/DB213H1/redo03.log
2022-05-03T14:23:50.840999+08:00
Deleted Oracle managed file /u01/app/oracle/DB213H1/archivelog/2022_04_20/o1_mf_1_9_k5yopcl1_.arc
2022-05-03T14:23:50.843496+08:00
NET (PID:7571): Archived Log entry 29 added for B-1102494684.T-1.S-29 ID 0x7f6ee6abae9c LAD:1 [krse.c:4933]
2022-05-03T14:23:50.879882+08:00
Deleted Oracle managed file /u01/app/oracle/DB213H1/archivelog/2022_04_20/o1_mf_1_10_k5yos1cc_.arc


Note the messages 
"RMAN flashback PDB to scn 3688309" 
"Restore Flashback Pluggable Database PDBHKC (4) until change 3686342" 
"Incomplete Recovery applied until change 3688310" 
"Completed: RMAN flashback PDB to scn 3688309"



Standby Alert Log Entries :

Standby Instance alert log :
============================
2022-05-03T14:19:42.855709+08:00
PDBHKC(4):PDB Side Media Recovery started for pdbid(4)
PDBHKC(4):.... (PID:6103): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBHKC(4):max_pdb is 4
PDBHKC(4):Datafile 15 (ckpscn 3690771) is orphaned on PDB incarnation#=1
PDBHKC(4):Datafile 14 (ckpscn 3690771) is orphaned on PDB incarnation#=1
PDBHKC(4):Datafile 13 (ckpscn 3690771) is orphaned on PDB incarnation#=1
PDBHKC(4):PSR[4]: the incarnation of data files don't match, so we are trying to fetch data files again from the primary database.
2022-05-03T14:19:42.962411+08:00
PDBHKC(4):Media Recovery failed with error 65138
PDBHKC(4):ORA-65138 is signaled while preparing the media recovery.
2022-05-03T14:19:43.506242+08:00
PDBHKC(4):Applying tablespace dictionary check redo for tablespace #0
PDBHKC(4):Applying tablespace dictionary check redo for tablespace #1
PDBHKC(4):Applying tablespace dictionary check redo for tablespace #2
PDBHKC(4):Applying tablespace dictionary check redo for tablespace #3
PDBHKC(4):Applying tablespace dictionary check redo for tablespace #4
PDBHKC(4):Applying datafile dictionary check redo for datafile #13
PDBHKC(4):Applying datafile dictionary check redo for datafile #14
PDBHKC(4):Applying datafile dictionary check redo for datafile #15
2022-05-03T14:19:45.892494+08:00
PDBHKC(4):Full restore complete of datafile 15 /u02/oradata/DB213H1_STDBY/DE155F0301020A30E0538338A8C07AFA/datafile/o1_mf_undotbs1_k71kvzq4_.dbf. Elapsed time: 0:00:00
PDBHKC(4): checkpoint is 3691257
PDBHKC(4): last deallocation scn is 3
PDBHKC(4):Datafile #15 has been copied to the standby.
2022-05-03T14:19:49.845284+08:00
PDBHKC(4):Full restore complete of datafile 14 /u02/oradata/DB213H1_STDBY/DE155F0301020A30E0538338A8C07AFA/datafile/o1_mf_sysaux_k71kvzq3_.dbf. Elapsed time: 0:00:03
PDBHKC(4): checkpoint is 3691259
PDBHKC(4): last deallocation scn is 1071312
PDBHKC(4):Datafile #14 has been copied to the standby.
2022-05-03T14:19:53.340847+08:00
PDBHKC(4):Full restore complete of datafile 13 /u02/oradata/DB213H1_STDBY/DE155F0301020A30E0538338A8C07AFA/datafile/o1_mf_system_k71kvzpm_.dbf. Elapsed time: 0:00:03
PDBHKC(4): checkpoint is 3691268
PDBHKC(4): last deallocation scn is 2645444
PDBHKC(4):Datafile #13 has been copied to the standby.
2022-05-03T14:19:53.354185+08:00
PDBHKC(4):PDB Side Media Recovery started for pdbid(4)
PDBHKC(4):.... (PID:6103): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBHKC(4):max_pdb is 4
PDBHKC(4):.... (PID:6103): Media Recovery Waiting for T-1.S-29 (in transit) [krsm.c:6185]
2022-05-03T14:19:53.419125+08:00
PDBHKC(4):Media Recovery of Online Log [Thread=1, Seq=29]
2022-05-03T14:19:53.419332+08:00
PDBHKC(4):Recovery of Online Redo Log: Thread 1 Group 5 Seq 29 Reading mem 0
PDBHKC(4): Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
PDBHKC(4):PSR[4]:influxScn=3691275,nxtCkptScn=3691275,minScn=3691268,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843
PDBHKC(4):The merge request has been submitted by side recovery for pdbid 4
2022-05-03T14:19:53.462873+08:00
PR00 (PID:1896): Background Media Recovery cancelled with status 16037 [krd.c:27039]
PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions
2022-05-03T14:19:53.466115+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1896.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:1896): Managed Recovery not using Real Time Apply [krsm.c:15876]
Recovery interrupted!
Recovered data files to a consistent state at change 3691276
Stopping change tracking
2022-05-03T14:19:53.679424+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1896.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-05-03T14:19:54.506794+08:00
PDBHKC(4):.... (PID:6103): Side Recovery Complete [krds.c:1584]
2022-05-03T14:20:14.680123+08:00
all data files of pdbid 4 are brought online.
Started logmerger process
2022-05-03T14:20:14.739054+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:6144): Managed Recovery starting Real Time Apply [krsm.c:15865]
max_pdb is 4
2022-05-03T14:20:14.902709+08:00
Parallel Media Recovery started with 4 slaves
2022-05-03T14:20:15.022317+08:00
Stopping change tracking
PR00 (PID:6144): Media Recovery Waiting for T-1.S-29 (in transit) [krsm.c:6185]
2022-05-03T14:20:15.207572+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 29 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-03T14:23:50.977661+08:00
rfs (PID:2029): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-30 [krsr.c:18143]
2022-05-03T14:23:50.978472+08:00
ARC1 (PID:1786): Archived Log entry 11 added for B-1102494684.T-1.S-29 ID 0x7f21e6abae9c LAD:1 [krse.c:4933]
2022-05-03T14:23:51.046002+08:00
PR00 (PID:6144): Media Recovery Waiting for T-1.S-30 (in transit) [krsm.c:6185]
2022-05-03T14:23:51.046688+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 30 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_4_k5yt8y5f_.log


Note the messages 
"PDBHKC(4):PSR[4]: the incarnation of data files don't match, so we are trying to fetch data files again from the primary database."
"PDBHKC(4):Media Recovery failed with error 65138"
"PDBHKC(4):ORA-65138 is signaled while preparing the media recovery."
"PDBHKC(4):Full restore complete of datafile ..<fileid>" for each datafile in the PDB
"PDBHKC(4):Datafile #<fileid> has been copied to the standby." for each datafile in the PDB
"PDBHKC(4):PDB Side Media Recovery started for pdbid(4)"
"PDBHKC(4):.... (PID:6103): Managed Recovery starting Real Time Apply [krsm.c:15865]"
"PDBHKC(4):PSR[4]:influxScn=3691275,nxtCkptScn=3691275,minScn=3691268,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843"
"PDBHKC(4):The merge request has been submitted by side recovery for pdbid 4"
"PR00 (PID:1896): Background Media Recovery cancelled with status 16037 [krd.c:27039]"
"PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions"
"ORA-16037: user requested cancel of managed recovery operation"
"PR00 (PID:1896): Managed Recovery not using Real Time Apply [krsm.c:15876]"
"PDBHKC(4):.... (PID:6103): Side Recovery Complete [krds.c:1584]"
"all data files of pdbid 4 are brought online."
" Started logmerger process" 
"PR00 (PID:6144): Managed Recovery starting Real Time Apply [krsm.c:15865]"

These messages would relate to the new 12c feature "PDB Recovery Isolation" whereby Oracle DataGuard detects that one PDB has to be recovered separately -- i.e. using a separate background session.  (this seems to be called "side recovery" in the messages)

Here are the ORA error descriptions :


65138, 00000, "Data file %s of pluggable database %s belongs to an orphan PDB incarnation."
// *Cause: Either the specified data file was restored from a backup that was
// taken during a period of time that was discarded by
// a RESETLOGS operation, or Oracle could not identify which pluggable
// database incarnation the file belongs to. One would also encounter
// this error if a pluggable database (PDB) flashback or restore was
// performed to an orphan PDB incarnation followed by complete
// recovery of the PDB. The alert log contains more information.
// *Action: Restore a backup of this file that belonged to either the current
// or a prior incarnation of the pluggable database. If you are using
// RMAN to restore, RMAN will automatically select a correct backup.
// If you are using Flashback, then perform flashback to a point
// prior to the start of the orphan incarnation.


16037, 00000, "user requested cancel of managed recovery operation"
// *Cause: The managed standby database recovery operation has been
// canceled per user request.
// *Action: No action is required.
//

16037 is a standard message because Oracle stops media recovery for a while.

However, this behaviour indicates that Oracle actually attempts to copy the entire PDB from the Primary to the Standby.  It would be interesting to see how it works with an available backup of the PDB at the Standby -- particularly so if the PDB is very large and copy-over-the-network would take time.

Categories: DBA Blogs

A Fresh Look at AWR Reports --- 4 : SQL*Net Waits

Fri, 2022-04-15 06:18

 After the first three posts on CPU Usage, Redo Size and I/O Profile, I present my interpretation of SQL*Net waits.

From the same AWR Report as the first three reviews, I have extracted the specific SQL*Net waits from the Foreground Wait Events table:

SQL*Net Waits from the Foreground Waits Table

"SQL*Net more data from client" waits occur when the server process hasn't received the required data (e.g. an INSERT statement data) within 1 network packet and has to wait to receive more more.  "SQL*Net more data to client" is the reverse -- the client has requested, say a large row or dataset that doesn't fit into 1 network packet.  The network packet size is determined essentially by two parameters "Session Data Unit" (aka "SDU") and "Transport Data Unit" (aka "TDU").  When sending rows out of the server, it can automatically also do compression also. ARRAYSIZE (or Fetch Size, depending on your client code, in java) can also achieve some compression. TDU is obsolete in all recent versions but SDU can still be tuned.  For guidance on tuning SDU,  section 14.2 of the Tuning Guide.

"SQL*Net message to client" doesn't really show time taken to send the message (e.g. sql select statement result or execution of commit) to the client.  It is actually the time taken by the Server Process to put the results on to the Network Layer on the DB Server.  Beyond that, the Oracle Server Process does *not* how long it takes for the data to reach the client (whether a user desktop or an application server / middle-tier).  The actual time would depend on network latency and bandwidth.  So, you can interpret the number of occurrences of the Wait Event but NOT the time.

"SQL*Net break/reset" would be presented when an error occurs or an unhandled exception is raised. A high number of such waits indicate repeated occurrences of errors in SQL calls.

"SQL*Net message from client" may or may not be a problem (and that is why Oracle does not include it in % DB time).  There are different manifestations of this event :

1. The database server has sent a row or a set of rows (depending on arraysize or fetchsize) and is waiting for the client / application server to acknowledge and request the next set of rows.  The client could be a user who is running, say, SQL*Developer, and has seen the first 50 rows of a 1000 row result set but has walked away from his desk to get some coffee and has not yet requested the next set of 50 rows. (Yes, it is true that SQL*Developer and most clients do NOT fetch and present all the rows to the user immediately, this is is done in batches -- the parameter is configurable in most client or java programs).  The user may come back 5minutes or 30minutes later and ask for the next 50 rows.  Till then, the Oracle Server Process is "waiting" on this event.

2. It could also be an application server / middle tier that is formatting / sorting the rows or is writing the results to disk or is joining this set of rows with another data set (even from another source -- e.g. an ODBC connection, an Excel file etc) and is taking some milliseconds to do so.  Till then, the Oracle Server Process may be "waiting" on this event.

3. The database server has sent the results of a query (or success of execution of a DML) to the client / application server and is waiting for the next SQL call.  The next SQL call could be a COMMIT (or ROLLBACK) or any INSERT/UPDATE/DML statement.  It may come 1second later or it may come 30minutes later or it may come 10hours later.  As long as the client / application server has maintained a session on the Database, the Oracle Server Process will be "waiting" on this event.



Other than the above set of "Wait" Events relating to a client / application server, there may occur exactly the same Events relating to a Database Link. :  "SQL*Net more data from/to dblink",  "SQL*Net message from/to dblink" and SQL*Net break/reset to dblink".  They don't appear in my AWR because my example load does not include Database Links to / from other databases.  But you need to interpret them as well.  For example, if you have Database Links for periodic refresh's of Materialized Views between Databases, you may well see such "SQL*Net message" and "SQL*Net more data" waits relating to "dblink"s.


Categories: DBA Blogs

A Fresh Look at AWR Reports -- 3 : I/O Profile

Sat, 2022-04-02 03:56

 After viewing CPU Usage and Redo Size statistics, here's a look at the I/O Profile.

Here's the header which indicates that this AWR report is for duration of 2hours on 19c on Windows :

AWR Header


Instead of the Load Profile, this time I will skip directly to the I/O Profile section :

I/O Profile

In the above table, "Database" seems to include both "Datafile" and "Tempfile".

Also, for some co-relation, the IOStat section :

IOStat by Filetype Summary

As I've already mentioned in my previous 2 posts, the load I that I generated was more I/O Intensive than CPU Intensive.
Here we can see a break-down of the I/O Profile.  I am more interested in the *Writes*.

From the previous post, we've already seen that 20GB of writes were for Duplexed Online Redo Logs and 10GB for ArchiveLogs.
But what about the rate per second ?

Online Redo Log file writes were 2.7MB/second.

Datafile + Tempfile writes were 3.3MB/second.  Note that Tempfile I/O was higher than Datafile I/O.

Although Datafile requests per second were higher, Datafile writes by DBWR may well be in smaller batches (i.e. fewer blocks at each write) --- more I/O calls (85.59), less total written bytes (1.388M).  This is in the nature of DBWR behaviour as DBWR periodically "collects" dirty buffers to flush to disk.

Writes to Tempfile for Sort/GroupBy operations (10.59 for 1.89M) can be more blocks per call.  This is because such operations can consist of large number of blocks in each sesssion. The writes are from PGA, not the Buffer Cache in the SGA.

So, what could have been happening here, that Tempfile writes were so high, relative to Datafile writes ?  Sort Operations, Group By Queries, Create / Rebuild Index operations.

Note : Create / Rebuild operations are unlikely to be all visible in the SQL Statements section of the AWR report because, as DDL statements, they get invalidated and aged out of the Shared Pool quickly before each AWR Snapshot run.  Yet, their I/O profile is visible in the IO Sections of the AWR Report).
Create/Rebuild Index operations do not generate Redo for their Tempfile writes but *do* generate Redo for their Datafile writes if they are not executed as NoLogging operations.









Categories: DBA Blogs

A Fresh Look at AWR Reports -- 2 : Redo Size

Wed, 2022-03-23 09:39

 In my previous blog post, I showed how to interpret CPU Usage from an AWR report.

Here, I take the same 2hour AWR report and help interpret Redo Size -- ie. volume of Redo Generated and Written to Disk.

Here's the header which indicates that this AWR report is for duration of 2hours on 19c on Windows :

AWR Header


These are the first few lines of the Load Profile :

First few lines of Load Profile

This indicates that the Redo Size (total Redo Generated) is just about 10GB  (multiply Redo Size bytes per Second by the total elapsed seconds (120.71) minutes)

I can confirm that from the Key Instance Activity Stats section :

Instance Activity Statistics

So, did my database write about 10GB (about 9.8GB actually, if you divide by 1024s) of Redo to disk ?   Is that true ?

Let me go to the IOStat(s) section.  (Please ignore the "average service time" in this table because I've run this test on a PC with a standard Hard-Disk -- not an Enterprise SAN !)

IO Statistics by FileType

What do I see here ?  The total volume written to Log Files (i.e. Online Redo Logs) is 19GB !

Why is that so ?

Because, in my database, every Redo Log Group is duplexed -- it has 2 members.  So, Redo of approximately 9.8GB actually translates to 19GB (or close to 20GB) of Physical Writes to Online Redo Log files.

Note that there are 10GB of Reads from  the Online Redo Log files.  That is the Log Archiver Process(es) writing out the Online Redo Logs to the Archive Logs -- which you can see is 10GB of Writes to Archive Log !

Thus, the initial "Redo Size" of 10GB is actually 30GB of Physical Writes to Disk (20GB for Duplexed Online Redo Logs  PLUS 10GB of Archive Logs).

If I had defined an additional ArchiveLogDestination on my server (as I have done in previous implementations where dedicated disks or NFS mount points were assigned), there would have been another 10GB of Physical Writes.

For every Standby Database (note how in previous blog posts here and here , I have demonstrated 3 separate Standby Databases for the Primary Database), 10GB of Redo has to be transferred over the Network and then written to the Standby Redo Logs at each Standby Site/Server.

So, my 10GB of Redo could effectively translate to anywhere from 30GB to 70GB of Physical Write of Redo  Plus 10GB to 30GB of transfer over the network !!

When you look at the SAN or underlying storage, you must consider how all those writes get through.  Does the SAN have enough Write-Through Cache ?  Are those Redo Logs and the one or more ArchiveLogDestination(s) on the Primary Server  actually on separate Disks  -- you might have separate FileSystem MountPoints or ASM DiskGroups but how do they translate down to the Disks in the SAN ?


Categories: DBA Blogs

A Fresh Look at AWR Reports -- 1 : CPU Usage

Sun, 2022-03-20 09:57

 I have covered AWR Reports (from 10g to 12c, including MultiTenant) in the past.  You could search for "AWR" in the "Search this Blog" in the panel on the right or by specifying :   
site://hemantoracledba.blogspot.com AWR
in the Google Search Window

In this post I present a comparison between OS and Oracle CPU Statistics.

AWR Header

So, I have an AWR Report for 19c on Windows for a duration of 120minutes with 12 CPUs.  That is a total of 86,400 available CPU seconds (120minutes X 12 CPUs X 60seconds_per_minute)



Load Profile


The Load Profile shows 0.4 Oracle CPU seconds per Elapsed Seconds.  This means that, on average, I am consuming less than 1 CPU.


Listing of Waits and Wait Classes

Oracle Database CPU time is 13.5% of total Oracle Time.  This is 1,918 seconds.
Note very carefully that this does NOT mean that the Oracle Database Processes account for 13.5% of Server CPU Usage.



O.S. and Oracle Instance CPU
This shows that the Server reports 24% CPU Usage but Oracle used only 2.4% of the total CPU available (approximately 2.4% of 86,400seconds is 2,073seconds close to Oracle's DB CPU figure)
This also translates about 9% of the total actual CPU "busyness" on the Server.

So, if Oracle consumed only 9% of the actual CPU Usage on the server, I would need to look for other processes on the same server that would have been consuming CPU.  

This could be other Oracle Database Instances (my report is for database instance "CDB1" -- was there / were there one or more other Database Instances active on the same server ?).  This could also be non-Oracle programs, Application Server software,  other system / monitoring tools running (I've seen monitoring tools alone consuming 20% of the CPU on a server !).

If this server is hosting only this 1 Database Instance (CDB1), it is excessively provisioned (12 CPUs) for the actual load.  Of course, the load that I have generated here is I/O intensive, but it means that I could have actually reduced the number of CPUs to, say, 4 CPUs and paid much less in Hardware, Oracle Licensing and Oracle Support costs.  (4 CPUs would have allowed for the OS and the other non-Oracle processes that seem to be accounting for 24% user-time of 12 CPUs).




Categories: DBA Blogs

SQL Execution Statistics -- 4 : Adaptive Plans

Sat, 2022-03-12 08:45

In the previous 3 blog posts, I have shown how you can use the GATHER_PLAN_STATISTICS Hint or the STATISTICS_LEVEL='ALL' to get sql execution statistics.

This may work with Adaptive Plans as well.



SQL> select /*+ gather_plan_statistics */ count(*)
2 from tables_list t
3 where (owner, table_name)
4 not in
5 (
6 select owner, table_name
7 from indexes_list i
8 )
9 /

COUNT(*)
----------
593

SQL> select * from table(dbms_xplan.display_cursor('',format=>'ALLSTATS LAST +COST +ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g6q8m94krdvz7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from tables_list t where
(owner, table_name) not in ( select owner, table_name from
indexes_list i )

Plan hash value: 2460844421

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1 |00:00:00.01 | 37 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 37 | | | |
| * 2 | HASH JOIN ANTI | | 1 | 22 | 11 (0)| 593 |00:00:00.01 | 37 | 1335K| 1335K| 1376K (0)|
|- 3 | NESTED LOOPS ANTI | | 1 | 22 | 11 (0)| 2202 |00:00:00.01 | 17 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 2202 |00:00:00.01 | 17 | | | |
| 5 | INDEX FAST FULL SCAN| TABLES_LIST_NDX | 1 | 2202 | 5 (0)| 2202 |00:00:00.01 | 17 | | | |
|- * 6 | INDEX RANGE SCAN | INDEXES_LIST_NDX_1 | 0 | 2918 | 6 (0)| 0 |00:00:00.01 | 0 | | | |
| 7 | INDEX FAST FULL SCAN | INDEXES_LIST_NDX_1 | 1 | 2918 | 6 (0)| 2918 |00:00:00.01 | 20 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
6 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)


31 rows selected.

SQL>
SQL> alter session set statistics_level='ALL';

Session altered.

SQL> select count(*)
2 from tables_list t
3 where (owner, table_name)
4 not in
5 (
6 select owner, table_name
7 from indexes_list i
8 )
9 /

COUNT(*)
----------
593

SQL> select * from table(dbms_xplan.display_cursor('',format=>'ALLSTATS LAST +COST +ADAPTIVE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c1fdcdxhvjrat, child number 0
-------------------------------------
select count(*) from tables_list t where (owner, table_name) not in (
select owner, table_name from indexes_list i )

Plan hash value: 2460844421

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1 |00:00:00.01 | 37 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 37 | | | |
| * 2 | HASH JOIN ANTI | | 1 | 22 | 11 (0)| 593 |00:00:00.01 | 37 | 1335K| 1335K| 1381K (0)|
|- 3 | NESTED LOOPS ANTI | | 1 | 22 | 11 (0)| 2202 |00:00:00.01 | 17 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | | 2202 |00:00:00.01 | 17 | | | |
| 5 | INDEX FAST FULL SCAN| TABLES_LIST_NDX | 1 | 2202 | 5 (0)| 2202 |00:00:00.01 | 17 | | | |
|- * 6 | INDEX RANGE SCAN | INDEXES_LIST_NDX_1 | 0 | 2918 | 6 (0)| 0 |00:00:00.01 | 0 | | | |
| 7 | INDEX FAST FULL SCAN | INDEXES_LIST_NDX_1 | 1 | 2918 | 6 (0)| 2918 |00:00:00.01 | 20 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")
6 - access("OWNER"="OWNER" AND "TABLE_NAME"="TABLE_NAME")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)


30 rows selected.

SQL>


ote : The two SQL statements, although being the same, have different SQL_IDs because of the presence / absence of the GATHER_PLAN_STATISTICS Hint.

Categories: DBA Blogs

SQL Execution Statistics -- 3 : Using STATISTICS_LEVEL

Sun, 2022-03-06 09:11

 In my previous posts here and here, I have shown how to use the GATHER_PLAN_STATISTICS Hint to collect additional information.  But what if you can't modify the SELECT query statement ?

You could set STATISTICS_LEVEL='ALL' at the session, run the query and then reset STATISTICS_LEVEL to the default  'TYPICAL'



SQL> alter session set tracefile_identifier='H2';

Session altered.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL> exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE); -- just to get the SQL_ID in the trace file

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 d.department_id, d.department_name, e.first_name, e.last_name
3 from hr.departments d join hr.employees e
4 on d.department_id=e.department_id
5 order by 1,2,4,3
6 /
....
....
....
106 rows selected.

SQL>
SQL> exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE);

PL/SQL procedure successfully completed.

SQL>
QL> select * from table(dbms_xplan.display_cursor('4aa2k5b98ybfr',format=>'ALLSTATS LAST')); -- SQL_ID identified from the trace file

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4aa2k5b98ybfr, child number 0
-------------------------------------
select d.department_id, d.department_name, e.first_name, e.last_name
from hr.departments d join hr.employees e on
d.department_id=e.department_id order by 1,2,4,3

Plan hash value: 2209899241

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.10 | 10 | 14 | | | |
| 1 | SORT ORDER BY | | 1 | 106 | 106 |00:00:00.10 | 10 | 14 | 11264 | 11264 |10240 (0)|
| 2 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.10 | 10 | 14 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 12 |00:00:00.03 | 2 | 2 | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 12 |00:00:00.03 | 1 | 1 | | | |
|* 5 | SORT JOIN | | 12 | 107 | 106 |00:00:00.06 | 8 | 12 | 9216 | 9216 | 8192 (0)|
| 6 | VIEW | index$_join$_002 | 1 | 107 | 106 |00:00:00.06 | 8 | 12 | | | |
|* 7 | HASH JOIN | | 1 | | 106 |00:00:00.06 | 8 | 12 | 1610K| 1610K| 1658K (0)|
| 8 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 1 | 107 | 106 |00:00:00.06 | 4 | 6 | | | |
| 9 | INDEX FAST FULL SCAN | EMP_NAME_IX | 1 | 107 | 107 |00:00:00.01 | 4 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
filter("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
7 - access(ROWID=ROWID)


30 rows selected.

SQL>


Thus, I was able to get similar execution statistics without the Hint if I set STATISTICS_LEVEL='ALL'.

(Note : The top level operation here is a MERGE JOIN while that in the first example of the first post of this series on 27-January-22 was a HASH JOIN. The result here is 106 rows, not the 108 rows in the first example. The first example was in an HR schema in another database -- e.g. you would see DEPARTMENT_ID=280 'New Department' and Employee 'HEMANT CHITALE' in that listing.  These are two different HR schemas where I have modified some of the data.  Thus, you'll see a difference in the Execution Plan as well between the first example and this one. although the query is similar -- because the databases, patch levels,  possibly optimizer parameters, schema and data are different.)

Even a slight difference in data or parameters or patch levels can result in different execution plans !







Categories: DBA Blogs

SQL Execution Statistics -- 2 : SQL_ID from a Tracefile

Tue, 2022-02-22 08:45

In my previous blog post, I have demonstrated sql execution statistics for the last SQL statement of the current session.

However, you can trace SQL execution in the current session or in any other session and then review the information from the tracefile.

(For different methods of SQL Tracing see this series of blog posts

For example, in an SQLPlus session :



SQL> alter session set tracefile_identifier='CUSTOMER_COUNT';

Session altered.

SQL>
SQL> select *
2 from v$diag_info
3 where name = 'Default Trace File';

INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
---------------------------------------------------------------------------------------
CON_ID
----------
1 Default Trace File
/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_9416_CUSTOMER_COUNT.trc
0


SQL>
SQL> exec DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE);

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics */ count(*) from tpcc.orders;

COUNT(*)
----------
303697

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

From the tracefile :
PARSING IN CURSOR #139722640816616 len=62 dep=0 uid=106 oct=3 lid=106 tim=20489021058 hv=3158106211 ad='777865e0' sqlid='du49mwuy3ts33'
select /*+ gather_plan_statistics */ count(*) from tpcc.orders
END OF STMT
PARSE #139722640816616:c=10950,e=11447,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=630573765,tim=20489021057
EXEC #139722640816616:c=52,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=630573765,tim=20489021253
FETCH #139722640816616:c=78319,e=464572,p=1504,cr=1508,cu=0,mis=0,r=1,dep=0,og=1,plh=630573765,tim=20489485885
STAT #139722640816616 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1508 pr=1504 pw=0 str=1 time=464570 us)'
STAT #139722640816616 id=2 cnt=303697 pid=1 pos=1 obj=80213 op='TABLE ACCESS FULL ORDERS (cr=1508 pr=1504 pw=0 str=1 time=566811 us cost=411 size=0 card=303697)'
FETCH #139722640816616:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=630573765,tim=20489486318


From a tkprof of the tracefile :
SQL ID: du49mwuy3ts33 Plan Hash: 630573765

select /*+ gather_plan_statistics */ count(*)
from
tpcc.orders


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.46 1504 1508 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.08 0.47 1504 1508 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1508 pr=1504 pw=0 time=464570 us starts=1)
303697 303697 303697 TABLE ACCESS FULL ORDERS (cr=1508 pr=1504 pw=0 time=566811 us starts=1 cost=411 size=0 card=303697)


SQL> select * from table(dbms_xplan.display_cursor('du49mwuy3ts33',format=>'ALLSTATS LAST +COST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID du49mwuy3ts33, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from tpcc.orders

Plan hash value: 630573765

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 411 (100)| 1 |00:00:00.38 | 1506 | 1505 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.38 | 1506 | 1505 |
| 2 | TABLE ACCESS FULL| ORDERS | 1 | 303K| 411 (1)| 303K|00:00:00.07 | 1506 | 1505 |
-------------------------------------------------------------------------------------------------------------


14 rows selected.

SQL>


From the tracefile, I could identify the SQL_ID (du49mwuy3ts33) and then run dbms_xplan.display_cursor for that SQL_ID.  

There is a slight discrepancy between the tracefile and the dbms_xplan.display_cursor output on the Actual Time and Buffers ("cr" in the tracefile) and Physical Reads ("pr" in the tracefile).  Similarly, you might notice a discrepancy in the time reported in the raw trace file STAT lines for operation Ids 2 and 1.  When the timing is in microseconds, some discrepancies may arise.
You must also note that gather_plan_statistics itself introduces an overhead on execution time for SQL statements that run in microseconds/milliseconds.


(For an explanation of reading the tracefile, see this post on the details and and this post on the summary)

Categories: DBA Blogs

SQL Execution Statistics -- 1 : A Quick Introduction

Thu, 2022-01-27 08:42

 A couple of quick examples of how to obtain SQL Execution Statistics using the GATHER_PLAN_STATISTICS Hint


Here's how you get the Statisitics for an SQL executed from the same session.
SQL> Run query with /*+ gather_plan_statistics */ hint
SQL> Get execution statistics with dbms_xplan.display_cursor(FORMAT=>'ALLSTATS LAST');

For an SQL from another session, you will have to pass the SQL_ID (and, if necessary, the CHILD_NUMBER) as a parameter(s) to the DISPLAY_CURSOR function.

I present a query on DEPARTMENTS and EMPLOYEES with a three different execution plans.  The first is the one automatically generated by the Optimizer and using a Hash Join.  The next two use Nested Loop(s) but differently based on how I specify the LEADING and USE_NL Hints.


The first one using a HASH JOIN :


SQL> select /*+ gather_plan_statistics   */
2 d.department_id, d.department_name, e.first_name, e.last_name
3 from hr.departments d join hr.employees e
4 on d.department_id=e.department_id
5 order by 1,2,4,3
6 /

DEPARTMENT_ID DEPARTMENT_NAME FIRST_NAME LAST_NAME
------------- ------------------------------ -------------------- -------------------------
10 Administration Jennifer Whalen
20 Marketing Pat Fay
20 Marketing Michael Hartstein
30 Purchasing Shelli Baida
30 Purchasing Karen Colmenares
30 Purchasing Guy Himuro
30 Purchasing Alexander Khoo
30 Purchasing Den Raphaely
30 Purchasing Sigal Tobias
40 Human Resources Susan Mavris
50 Shipping Mozhe Atkinson
....
.... Intermediate rows deleted from the output presented here
....
100 Finance Ismael Sciarra
100 Finance Jose Manuel Urman
110 Accounting William Gietz
110 Accounting Shelley Higgins
280 New Department Hemant Chitale

107 rows selected.

SQL>
SQL> @show_last_sql_statistics.sql
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))
2 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 59vduj8s28fhn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ d.department_id,
d.department_name, e.first_name, e.last_name from hr.departments d join
hr.employees e on d.department_id=e.department_id order by 1,2,4,3

Plan hash value: 207737665

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 14 | | | |
| 1 | SORT ORDER BY | | 1 | 107 | 107 |00:00:00.01 | 14 | 18432 | 18432 |16384 (0)|
|* 2 | HASH JOIN | | 1 | 107 | 107 |00:00:00.01 | 14 | 1572K| 1572K| 1652K (0)|
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 29 | 29 |00:00:00.01 | 6 | | | |
| 4 | VIEW | index$_join$_002 | 1 | 108 | 107 |00:00:00.01 | 8 | | | |
|* 5 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 8 | 1610K| 1610K| 1681K (0)|
| 6 | INDEX FAST FULL SCAN| EMP_DEPARTMENT_IX | 1 | 108 | 107 |00:00:00.01 | 4 | | | |
| 7 | INDEX FAST FULL SCAN| EMP_NAME_IX | 1 | 108 | 108 |00:00:00.01 | 4 | | | |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
5 - access(ROWID=ROWID)


27 rows selected.

SQL> SQL>




The second one with a simple NESTED LOOP using  DEPARTMENTS as the "Outer" table :

SQL> select /*+ gather_plan_statistics  leading(d) use_nl(e) */
2 d.department_id, d.department_name, e.first_name, e.last_name
3 from hr.departments d join hr.employees e
4 on d.department_id=e.department_id
5 order by 1,2,4,3
6 /

DEPARTMENT_ID DEPARTMENT_NAME FIRST_NAME LAST_NAME
------------- ------------------------------ -------------------- -------------------------
10 Administration Jennifer Whalen
20 Marketing Pat Fay
20 Marketing Michael Hartstein
30 Purchasing Shelli Baida
30 Purchasing Karen Colmenares
30 Purchasing Guy Himuro
30 Purchasing Alexander Khoo
30 Purchasing Den Raphaely
30 Purchasing Sigal Tobias
40 Human Resources Susan Mavris
50 Shipping Mozhe Atkinson
....
.... Intermediate rows deleted from the output presented here
....
100 Finance Ismael Sciarra
100 Finance Jose Manuel Urman
110 Accounting William Gietz
110 Accounting Shelley Higgins
280 New Department Hemant Chitale

107 rows selected.


SQL>
SQL> @show_last_sql_statistics.sql
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))
2 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8q08zbr367cgg, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(d) use_nl(e) */
d.department_id, d.department_name, e.first_name, e.last_name from
hr.departments d join hr.employees e on d.department_id=e.department_id
order by 1,2,4,3

Plan hash value: 2178791499

----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 19 | | | |
| 1 | SORT ORDER BY | | 1 | 107 | 107 |00:00:00.01 | 19 | 22528 | 22528 |20480 (0)|
| 2 | NESTED LOOPS | | 1 | 107 | 107 |00:00:00.01 | 19 | | | |
| 3 | NESTED LOOPS | | 1 | 261 | 107 |00:00:00.01 | 9 | | | |
| 4 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 29 | 29 |00:00:00.01 | 6 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 29 | 9 | 107 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 4 | 107 |00:00:00.01 | 10 | | | |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")


26 rows selected.

SQL> SQL>


And the third, more complicated, Execution Plan for a slightly different SQL :

SQL> select /*+ gather_plan_statistics  leading(e) use_nl(d) */
2 d.department_id, d.department_name, e.first_name, e.last_name
3 from hr.departments d join hr.employees e
4 on d.department_id=e.department_id
5 order by 1,2,4,3
6 /

DEPARTMENT_ID DEPARTMENT_NAME FIRST_NAME LAST_NAME
------------- ------------------------------ -------------------- -------------------------
10 Administration Jennifer Whalen
20 Marketing Pat Fay
20 Marketing Michael Hartstein
30 Purchasing Shelli Baida
30 Purchasing Karen Colmenares
30 Purchasing Guy Himuro
30 Purchasing Alexander Khoo
30 Purchasing Den Raphaely
30 Purchasing Sigal Tobias
40 Human Resources Susan Mavris
50 Shipping Mozhe Atkinson
....
.... Intermediate rows deleted from the output presented here
....
100 Finance Ismael Sciarra
100 Finance Jose Manuel Urman
110 Accounting William Gietz
110 Accounting Shelley Higgins
280 New Department Hemant Chitale

107 rows selected.

SQL>
SQL> @show_last_sql_statistics.sql
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))
2 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2kbc7rcpjcu0w, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(e) use_nl(d) */
d.department_id, d.department_name, e.first_name, e.last_name from
hr.departments d join hr.employees e on d.department_id=e.department_id
order by 1,2,4,3

Plan hash value: 2010582952

----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 119 | | | |
| 1 | SORT ORDER BY | | 1 | 107 | 107 |00:00:00.01 | 119 | 18432 | 18432 |16384 (0)|
| 2 | NESTED LOOPS | | 1 | 107 | 107 |00:00:00.01 | 119 | | | |
| 3 | NESTED LOOPS | | 1 | 108 | 107 |00:00:00.01 | 12 | | | |
| 4 | VIEW | index$_join$_002 | 1 | 108 | 107 |00:00:00.01 | 8 | | | |
|* 5 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 8 | 1610K| 1610K| 1539K (0)|
| 6 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 1 | 108 | 107 |00:00:00.01 | 4 | | | |
| 7 | INDEX FAST FULL SCAN | EMP_NAME_IX | 1 | 108 | 108 |00:00:00.01 | 4 | | | |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK | 107 | 1 | 107 |00:00:00.01 | 4 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 107 | 1 | 107 |00:00:00.01 | 107 | | | |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(ROWID=ROWID)
8 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")


30 rows selected.

SQL> SQL>


All three executions return exactly the same Result Set.  What are different are the Execution Plans and the Logical Reads.
The first Execution Plan (using a HASH JOIN) required 14 Logical Reads (Buffer Gets).
Note how the Second Execution required 19 Logical Reads  while the Third Execution required 119 Logical Reads  having queried the DEPARTMENTS Table via the Rows from the Nested Loop driven by the intermdiate "view" index$_join$_001  107 times (reading 1 Block each time).  

Since these are extremely small tables, the Actual Time is less than 1second but what matters in the comparison is the number of Logical Reads

("Starts" is the number of times that particular Step (Id) was actually executed, 
"E-Rows" is the Estimated Rows for the Execution Plan,
"A-Rows" is the *Actual* number of Rows retrieved at Runtime,
"A-Time" is the *Actual* Time in Hours:Minutes:Seconds,
"0mem" is the "Estimated Optimal Memory Usage", 
"1mem" is the Estimated 1-pass (one write to temp) Memory Usage 
"Used-Mem" is the Actual Memory Used)


SQL> select table_name, blocks, num_rows
2 from dba_tables
3 where owner = 'HR'
4 and table_name in ('DEPARTMENTS','EMPLOYEES')
5 order by 1
6 /

TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
DEPARTMENTS 5 29
EMPLOYEES 5 108

SQL>


Given the sizes of the two tables, I am sure that all the blocks are cached in the Buffer Cache, so I am not interested in Physical I/O statistics and Wait Times.  The Logical Reads consume CPU time.

This is an example of how to read SQL Execution Statistics.  

You may extrapolate to tables with thousands of blocks and tens of thousands of rows.  

Also, consider Memory Usage for the operations when you may have dozens of hundreds of sessions concurrently doing HASH JOINs and SORTs and being limited by PGA_AGGREGATE_TARGET.  Some Memory operations may overflow to the TEMP Tablespace, noticeably increasing total execution time.

Categories: DBA Blogs

Refreshable Clone PDB -- 4 Converting it to a Read Write PDB

Sun, 2022-01-23 01:51

 As demonstrated in my first post on Refreshable Clone PDBs, the Clone PDB can be opened in only READ ONLY mode.  This is akin to a Basic Materialized View or a Read Only Standby Database, either of which is updated from the source table(s) / database.

Such a Refreshable Clone PDB is useful as a Reporting Database where you can run queries / extracts -- with data AS OF the last Refresh -- without putting load on the running Production (Transactional, Read Write Database).

But if you want to (finally) open the Clone PDB in Read Write mode (and severe any links with the source, thus preventing further Refresh's) you can do so by changing the Refresh Mode.



SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;

COUNT(*)
----------
73645

SQL> connect / as sysdba
Connected.

SQL> alter pluggable database ro_pdb close; -- close it so that I can do a REFRESH

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb refresh; -- execute the REFRES

Pluggable database altered.

SQL> alter pluggable database ro_pdb open ; -- attempt to OPEN, defaulting to READ WRITE mode
alter pluggable database ro_pdb open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL> alter pluggable database ro_pdb open read write;
alter pluggable database ro_pdb open read write
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL>
SQL> alter pluggable database ro_pdb open read only; -- open READ ONLY

Pluggable database altered.

SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects; -- verify that the database has been refreshed (new rows visible in the source table)

COUNT(*)
----------
83645

SQL>
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database ro_pdb close; -- close it again

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb refresh mode none ; -- ***DISABLE FURTHER REFRESH's***

Pluggable database altered.

SQL>
SQL> alter pluggable database ro_pdb open read write; -- open in READ WRITE mode now !!

Pluggable database altered.

SQL>
SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;

COUNT(*)
----------
83645

SQL> delete list_of_objects where owner = 'HEMANT'; -- proof that the database is now WRITABLE

32 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL> alter pluggable database ro_pdb close;

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh; -- check if it can be REFRESH'ed from the source
alter pluggable database ro_pdb refresh
*
ERROR at line 1:
ORA-65261: pluggable database RO_PDB not enabled for refresh


SQL>


Thus, to enable the PDB to be WRITABLE, REFRESH has to be disabled. 
 Once REFRESH is disabled, the PDB can no longer be refreshed from the source. It has diverged from the source.

These are the alert log messages when REFRESH is disabled :


2022-01-23T15:35:55.766486+08:00
alter pluggable database ro_pdb refresh mode none
2022-01-23T15:35:55.846041+08:00
RO_PDB(6):Pluggable database RO_PDB pseudo opening
RO_PDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
RO_PDB(6):Autotune of undo retention is turned on.
RO_PDB(6):Endian type of dictionary set to little
RO_PDB(6):Undo initialization recovery: Parallel FPTR failed: start:1511350 end:1511367 diff:17 ms (0.0 seconds)
RO_PDB(6):Undo initialization recovery: err:0 start: 1511329 end: 1511464 diff: 135 ms (0.1 seconds)
RO_PDB(6):[6305] Successfully onlined Undo Tablespace 2.
RO_PDB(6):Undo initialization online undo segments: err:0 start: 1511465 end: 1511507 diff: 42 ms (0.0 seconds)
RO_PDB(6):Undo initialization finished serial:0 start:1511329 end:1511509 diff:180 ms (0.2 seconds)
RO_PDB(6):Database Characterset for RO_PDB is AL32UTF8
2022-01-23T15:35:57.144146+08:00
RO_PDB(6):Pluggable database RO_PDB pseudo closing
RO_PDB(6):JIT: pid 6305 requesting stop
RO_PDB(6):Closing sequence subsystem (1512195729).
RO_PDB(6):Buffer Cache flush started: 6
RO_PDB(6):Buffer Cache flush finished: 6
Completed: alter pluggable database ro_pdb refresh mode none


And these are the messages when it is OPENed in READ WRITE mode :



2022-01-23T15:36:56.085938+08:00
alter pluggable database ro_pdb open read write
2022-01-23T15:36:56.088166+08:00
RO_PDB(6):Pluggable database RO_PDB opening in read write
RO_PDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
RO_PDB(6):Autotune of undo retention is turned on.
RO_PDB(6):Endian type of dictionary set to little
RO_PDB(6):Undo initialization recovery: Parallel FPTR complete: start:1571711 end:1571732 diff:21 ms (0.0 seconds)
RO_PDB(6):Undo initialization recovery: err:0 start: 1571710 end: 1571732 diff: 22 ms (0.0 seconds)
2022-01-23T15:36:58.249919+08:00
RO_PDB(6):[6305] Successfully onlined Undo Tablespace 2.
RO_PDB(6):Undo initialization online undo segments: err:0 start: 1571732 end: 1573115 diff: 1383 ms (1.4 seconds)
RO_PDB(6):Undo initialization finished serial:0 start:1571710 end:1573161 diff:1451 ms (1.5 seconds)
RO_PDB(6):Deleting old file#9 from file$
RO_PDB(6):Deleting old file#10 from file$
RO_PDB(6):Deleting old file#11 from file$
RO_PDB(6):Deleting old file#12 from file$
RO_PDB(6):Deleting old file#26 from file$
RO_PDB(6):Deleting old file#31 from file$
RO_PDB(6):Deleting old file#32 from file$
RO_PDB(6):Deleting old file#33 from file$
RO_PDB(6):Deleting old file#34 from file$
RO_PDB(6):Adding new file#50 to file$(old file#9). fopr-1, newblks-48640, oldblks-19200
RO_PDB(6):Adding new file#51 to file$(old file#10). fopr-1, newblks-61440, oldblks-15360
RO_PDB(6):Adding new file#52 to file$(old file#11). fopr-1, newblks-58240, oldblks-12800
RO_PDB(6):Adding new file#53 to file$(old file#12). fopr-1, newblks-46880, oldblks-640
RO_PDB(6):Adding new file#54 to file$(old file#26). fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#56 to file$(old file#31). fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#55 to file$(old file#32). fopr-1, newblks-12800, oldblks-12800
RO_PDB(6):Adding new file#57 to file$(old file#34). fopr-1, newblks-115200, oldblks-12800
RO_PDB(6):Successfully created internal service RO_PDB at open
2022-01-23T15:36:59.694482+08:00
****************************************************************
Post plug operations are now complete.
Pluggable database RO_PDB with pdb id - 6 is now marked as NEW.
****************************************************************
RO_PDB(6):Pluggable database RO_PDB dictionary check beginning
RO_PDB(6):Pluggable Database RO_PDB Dictionary check complete
RO_PDB(6):Database Characterset for RO_PDB is AL32UTF8
2022-01-23T15:37:05.752451+08:00
RO_PDB(6):JIT: pid 6305 requesting full stop
2022-01-23T15:37:11.910855+08:00
RO_PDB(6):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2022-01-23T15:37:20.655852+08:00
RO_PDB(6):Opening pdb with no Resource Manager plan active
RO_PDB(6):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 6305 cid 6
2022-01-23T15:37:22.578807+08:00
Pluggable database RO_PDB opened read write
Completed: alter pluggable database ro_pdb open read write


Note : To understand FILE# values, see my previous post on Datafile Names for Refresh Clone PDB.
(Some of the FILE# values have changed since that post because I have added a new tablespace in the source database and recreated RO_PDB as a Refreshable Clone PDB since that post).

Categories: DBA Blogs

The Contents of the Database Controlfile -- 3 : Recreating the Controlfile

Thu, 2022-01-20 03:57

 In my previous posts, I have shown the Logical Structure and the Physical Contents of the Controlfile.  It is clear that some of the sections (e.g. for "ARCHIVED LOG" entries) have many slots (or "records") not all of which are currently in use.

What happens if you recreate the controlfile ?  Since my first post, I have generated some more Backup Pieces and ArchiveLogs (and purged ArchiveLogs) so the RECORDS_UESD or INDEXes of "BACKUP DATAFILE", "BACKUP PIECE" and "ARCHIVED LOG" in this listing are different from that first listing.



SQL> select *
2 from v$controlfile_record_section
3 order by type
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ACM OPERATION 104 64 11 0 0 0 0
ARCHIVED LOG 584 383 383 198 197 1444 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 210 1 210 210 0
BACKUP PIECE 780 1006 160 1 160 160 0
BACKUP REDOLOG 76 215 215 51 50 265 0
BACKUP SET 96 1022 149 1 149 149 0
BACKUP SPFILE 124 131 33 1 33 33 0
CKPT PROGRESS 8180 11 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DATABASE 316 1 1 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
DATABASE INCARNATION 56 292 4 1 4 4 0
DATAFILE 520 1024 34 0 0 1532 0
DATAFILE COPY 736 1000 3 1 3 3 0
DATAFILE HISTORY 568 57 0 0 0 0 0
DELETED OBJECT 20 818 818 14 13 831 0
FILENAME 524 4146 29 0 0 0 0
FLASHBACK LOG 84 2048 2 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
GUARANTEED RESTORE POINT 256 2048 1 0 0 2 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
LOG HISTORY 56 292 292 54 53 637 0
MTTR 100 8 1 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
OFFLINE RANGE 200 1063 696 1 696 696 0
PDB RECORD 780 10 5 0 0 26 0
PDBINC RECORD 144 113 0 0 0 0 0
PROXY COPY 928 1004 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
REDO LOG 72 16 4 0 0 15 0
REDO THREAD 256 8 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 6 0 0 0 0
RESTORE POINT 256 2108 0 0 0 0 0
RMAN CONFIGURATION 1108 50 3 0 0 7 0
RMAN STATUS 116 141 141 98 97 379 0
STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
TABLESPACE 180 1024 22 0 0 75 0
TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
TEMPORARY FILENAME 56 1024 7 0 0 36 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0

42 rows selected.

SQL>
SQL> alter database backup controlfile to trace as '/home/oracle/controlfile_creation.txt';

Database altered.

SQL>

SQL> !cat /home/oracle/controlfile_creation.txt
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="ORCLCDB"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/ORCLCDB/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/ORCLCDB/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/ORCLCDB/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/opt/oracle/oradata/ORCLCDB/stdbredo01.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/opt/oracle/oradata/ORCLCDB/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/users01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f07n8_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf'
CHARACTER SET AL32UTF8
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/temp01.dbf'
SIZE 138412032 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "PDB$SEED";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
SIZE 76546048 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "ORCLPDB1";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf'
SIZE 135266304 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp02.dbf'
SIZE 52428800 REUSE AUTOEXTEND OFF;
ALTER SESSION SET CONTAINER = "RMANCAT";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_temp_j80dl97t_.dbf'
SIZE 77594624 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "CDB$ROOT";
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/opt/oracle/oradata/ORCLCDB/stdbredo01.log'
SIZE 200M BLOCKSIZE 512 REUSE;
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/ORCLCDB/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/ORCLCDB/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/opt/oracle/oradata/ORCLCDB/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/opt/oracle/oradata/ORCLCDB/stdbredo01.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/opt/oracle/oradata/ORCLCDB/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/users01.dbf',
'/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf',
'/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf',
'/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f07n8_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf',
'/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf'
CHARACTER SET AL32UTF8
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO BACKED UP 1 TIMES TO DISK');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Open all the PDBs.
ALTER PLUGGABLE DATABASE ALL OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/temp01.dbf'
SIZE 138412032 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "PDB$SEED";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
SIZE 76546048 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "ORCLPDB1";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf'
SIZE 135266304 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp02.dbf'
SIZE 52428800 REUSE AUTOEXTEND OFF;
ALTER SESSION SET CONTAINER = "RMANCAT";
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_temp_j80dl97t_.dbf'
SIZE 77594624 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER SESSION SET CONTAINER = "CDB$ROOT";
-- End of tempfile additions.
--
--
--
----------------------------------------------------------
-- The following script can be used on the standby database
-- to re-populate entries for a standby controlfile created
-- on the primary and copied to the standby site.
----------------------------------------------------------
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/opt/oracle/oradata/ORCLCDB/stdbredo01.log'
SIZE 200M BLOCKSIZE 512 REUSE;

SQL>


The trace that is generated with the "alter database backup controlfile to trace ..." command actually contains the SQL statements that can be used to recreate the controlfile.  There are a number of useful comments also added.
Essentially, there are two sets of SQL commands.  

The first set, titled "Set #1" is for the NORESETLOGS scenario where all the Online Redo Log files are available.
The second set, titled "Set #2" is for a RESETLOGS scenario where Incomplete Recovery is required because not all the Online Redo Log files are available.

In my case, I have all my Online Redo Log files so I do not lose any data and I can use the commands from Set #1.

Recreating the Controlfile :


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

SQL> @Recreate_Controlfile.SQL
SQL> spool Recreate_Controlfile
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1207955544 bytes
Fixed Size 9134168 bytes
Variable Size 671088640 bytes
Database Buffers 520093696 bytes
Redo Buffers 7639040 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/opt/oracle/oradata/ORCLCDB/redo01.log' SIZE 200M BLOCKSIZE 512,
9 GROUP 2 '/opt/oracle/oradata/ORCLCDB/redo02.log' SIZE 200M BLOCKSIZE 512,
10 GROUP 3 '/opt/oracle/oradata/ORCLCDB/redo03.log' SIZE 200M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 -- GROUP 4 '/opt/oracle/oradata/ORCLCDB/stdbredo01.log' SIZE 200M BLOCKSIZE 512
13 DATAFILE
14 '/opt/oracle/oradata/ORCLCDB/system01.dbf',
15 '/opt/oracle/oradata/ORCLCDB/sysaux01.dbf',
16 '/opt/oracle/oradata/ORCLCDB/undotbs01.dbf',
17 '/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf',
18 '/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf',
19 '/opt/oracle/oradata/ORCLCDB/users01.dbf',
20 '/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf',
21 '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf',
22 '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf',
23 '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf',
24 '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf',
25 '/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf',
26 '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_system_j80dl8qy_.dbf',
27 '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_sysaux_j80dl94j_.dbf',
28 '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_undotbs1_j80dl96d_.dbf',
29 '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_rman_tbs_j80f07n8_.dbf',
30 '/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf',
31 '/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf',
32 '/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf'
33 CHARACTER SET AL32UTF8
34 ;

Control file created.

SQL>
SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO BACKED UP 1 TIMES TO DISK');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');

PL/SQL procedure successfully completed.

SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> -- I will be doing a SHUTDOWN IMMEDIATE so I do not need a RECOVER DATABASE
SQL> -- RECOVER DATABASE
SQL>
SQL> -- All logs need archiving and a log switch is needed.
SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL>
SQL> -- Database can now be opened normally.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
SQL> -- Open all the PDBs.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL>
SQL> -- Commands to add tempfiles to temporary tablespaces.
SQL> -- Online tempfiles have complete space information.
SQL> -- Other tempfiles may require adjustment.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/temp01.dbf'
2 SIZE 138412032 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = "PDB$SEED";

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf'
2 SIZE 76546048 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = "ORCLPDB1";

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf'
2 SIZE 135266304 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp02.dbf'
2 SIZE 52428800 REUSE AUTOEXTEND OFF;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = "RMANCAT";

Session altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/datafile/o1_mf_temp_j80dl97t_.dbf'
2 SIZE 77594624 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

SQL> ALTER SESSION SET CONTAINER = "CDB$ROOT";

Session altered.

SQL> -- End of tempfile additions.
SQL>
SQL> spool off
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 71
Next log sequence to archive 73
Current log sequence 73
SQL>


The ARCHIVE LOG LIST output shows that I did NOT have to do a RESETLOGS. The CREATE CONTROLFILE and ALTER DATABASE OPEN commands succeeded.

What do I now see in the Logical Entries in the Controlfile ?


SQL> select *
2 from v$controlfile_record_section
3 order by type
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ACM OPERATION 104 64 11 0 0 0 0
ARCHIVED LOG 584 308 3 1 3 3 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 0 0 0 0 0
BACKUP PIECE 780 1006 0 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0 0
BACKUP SET 96 1022 0 0 0 0 0
BACKUP SPFILE 124 131 0 0 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DATABASE 316 1 1 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
DATABASE INCARNATION 56 292 2 1 2 2 0
DATAFILE 520 1024 34 0 0 39 0
DATAFILE COPY 736 1000 0 0 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0 0
FILENAME 524 4146 27 0 0 0 0
FLASHBACK LOG 84 2048 0 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
LOG HISTORY 56 292 1 1 1 1 0
MTTR 100 8 1 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
OFFLINE RANGE 200 1063 12 1 12 12 0
PDB RECORD 780 10 4 0 0 6 0
PDBINC RECORD 144 113 0 0 0 0 0
PROXY COPY 928 1004 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
REDO LOG 72 16 3 0 0 0 0
REDO THREAD 256 8 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0
RESTORE POINT 256 2108 0 0 0 0 0
RMAN CONFIGURATION 1108 50 3 0 0 3 0
RMAN STATUS 116 141 0 0 0 0 0
STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
TABLESPACE 180 1024 22 0 0 7 0
TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
TEMPORARY FILENAME 56 1024 5 0 0 8 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0

42 rows selected.

SQL>


Recreating the Controlfile resulted in loss of information on all ArchivedLogs and RMAN Backups !
Compare the RECORDS_USED and INDEXes for "ARCHIVED LOG" and "BACKUP %" entries against the listing obtained before recreated the Controlfile.  (A few ArchivedLogs were generated by the "ALTER SYSTEM ARCHIVE LOG ALL" command in the script itself).

Can I add that information back ?


RMAN> catalog start with '/opt/oracle/FRA/ORCLCDB';

using target database control file instead of recovery catalog
searching for all files that match the pattern /opt/oracle/FRA/ORCLCDB

List of Files Unknown to the Database
=====================================
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143716_jy7pz5dd_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143804_jy7q1xp1_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094145522_jy7rqmov_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469527_jyko4r0n_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469675_jyko9dj3_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469696_jykob1pp_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094477941_jykxcrnx_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094481585_jyl0xoq0_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko09yl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko0b3k_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko6m5w_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko7djl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko98yc_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_ncnnf_TAG20220120T112134_jyko9zkl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T133854_jykxch2m_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T133854_jykxchwb_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q04z2_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q08kj_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko1qll_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko20ty_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/88129263B99F4BBDE0530100007F7BDF/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko3xgo_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/88129263B99F4BBDE0530100007F7BDF/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko3y0y_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko33fq_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko33wn_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_60_jyl0zmoz_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_61_jyl10qd1_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_62_jyl11rbq_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_63_jyl12p73_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_64_jyl13l2w_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_65_jyl14s7l_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_66_jyl1qrjm_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_67_jyl81z9z_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_68_jyl861ll_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_69_jyl8l6mo_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_70_jyl8p3d5_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_71_jyl8szdf_.arc

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

List of Cataloged Files
=======================
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143716_jy7pz5dd_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143804_jy7q1xp1_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094145522_jy7rqmov_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469527_jyko4r0n_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469675_jyko9dj3_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094469696_jykob1pp_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094477941_jykxcrnx_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_20/o1_mf_s_1094481585_jyl0xoq0_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko09yl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko0b3k_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko6m5w_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko7djl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T111944_jyko98yc_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_ncnnf_TAG20220120T112134_jyko9zkl_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T133854_jykxch2m_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2022_01_20/o1_mf_annnn_TAG20220120T133854_jykxchwb_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q04z2_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q08kj_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko1qll_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko20ty_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/88129263B99F4BBDE0530100007F7BDF/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko3xgo_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/88129263B99F4BBDE0530100007F7BDF/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko3y0y_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko33fq_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/C07D1F2BA98C23D0E0530100007F7D34/backupset/2022_01_20/o1_mf_nnndf_TAG20220120T111624_jyko33wn_.bkp
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_60_jyl0zmoz_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_61_jyl10qd1_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_62_jyl11rbq_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_63_jyl12p73_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_64_jyl13l2w_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_65_jyl14s7l_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_66_jyl1qrjm_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_67_jyl81z9z_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_68_jyl861ll_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_69_jyl8l6mo_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_70_jyl8p3d5_.arc
File Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_71_jyl8szdf_.arc

RMAN>
RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 26-SEP-21 1 1 NO TAG20210926T213259
2 B F A DISK 16-JAN-22 1 1 NO TAG20220116T164836
3 B F A DISK 16-JAN-22 1 1 NO TAG20220116T165004
4 B F A DISK 16-JAN-22 1 1 NO TAG20220116T171842
5 B F A DISK 20-JAN-22 1 1 NO TAG20220120T111847
6 B F A DISK 20-JAN-22 1 1 NO TAG20220120T112115
7 B F A DISK 20-JAN-22 1 1 NO TAG20220120T112136
8 B F A DISK 20-JAN-22 1 1 NO TAG20220120T133901
9 B F A DISK 20-JAN-22 1 1 NO TAG20220120T143945
10 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
11 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
12 B A A DISK 20-JAN-22 1 1 YES TAG20220120T111944
13 B A A DISK 20-JAN-22 1 1 YES TAG20220120T111944
14 B A A DISK 20-JAN-22 1 1 YES TAG20220120T111944
15 B F A DISK 20-JAN-22 1 1 NO TAG20220120T112134
16 B A A DISK 20-JAN-22 1 1 YES TAG20220120T133854
17 B A A DISK 20-JAN-22 1 1 YES TAG20220120T133854
18 B F A DISK 16-JAN-22 1 1 NO TAG20220116T164908
19 B F A DISK 16-JAN-22 1 1 NO TAG20220116T164908
20 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
21 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
22 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
23 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
24 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624
25 B F A DISK 20-JAN-22 1 1 YES TAG20220120T111624

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
4 1 60 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_60_jyl0zmoz_.arc

5 1 61 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_61_jyl10qd1_.arc

6 1 62 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_62_jyl11rbq_.arc

7 1 63 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_63_jyl12p73_.arc

8 1 64 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_64_jyl13l2w_.arc

9 1 65 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_65_jyl14s7l_.arc

10 1 66 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_66_jyl1qrjm_.arc

11 1 67 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_67_jyl81z9z_.arc

12 1 68 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_68_jyl861ll_.arc

13 1 69 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_69_jyl8l6mo_.arc

14 1 70 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_70_jyl8p3d5_.arc

1 1 70 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_70_jylbnvgo_.arc

15 1 71 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_71_jyl8szdf_.arc

2 1 71 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_71_jylbo521_.arc

3 1 72 A 20-JAN-22
Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2022_01_20/o1_mf_1_72_jylbofq3_.arc


RMAN>


Yes ! RMAN can "add the information" back into the Controlfile  using the CATALOG command  (also see this earlier post on the CATALOG command)-- as long as you know where the Backups and ArchiveLogs ARE ! (Another good reason to use a single FRA location !)


SQL> select *
2 from v$controlfile_record_section
3 order by type
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ACM OPERATION 104 64 11 0 0 0 0
ARCHIVED LOG 584 308 15 1 15 15 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 37 1 37 37 0
BACKUP PIECE 780 1006 26 1 26 26 0
BACKUP REDOLOG 76 215 112 1 112 112 0
BACKUP SET 96 1022 26 1 26 26 0
BACKUP SPFILE 124 131 10 1 10 10 0
CKPT PROGRESS 8180 11 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DATABASE 316 1 1 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
DATABASE INCARNATION 56 292 2 1 2 2 0
DATAFILE 520 1024 34 0 0 39 0
DATAFILE COPY 736 1000 0 0 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0 0
FILENAME 524 4146 28 0 0 0 0
FLASHBACK LOG 84 2048 0 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
LOG HISTORY 56 292 1 1 1 1 0
MTTR 100 8 1 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
OFFLINE RANGE 200 1063 12 1 12 12 0
PDB RECORD 780 10 4 0 0 6 0
PDBINC RECORD 144 113 0 0 0 0 0
PROXY COPY 928 1004 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
REDO LOG 72 16 3 0 0 0 0
REDO THREAD 256 8 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 8 0 0 0 0
RESTORE POINT 256 2108 0 0 0 0 0
RMAN CONFIGURATION 1108 50 3 0 0 3 0
RMAN STATUS 116 141 3 1 3 3 0
STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
TABLESPACE 180 1024 22 0 0 7 0
TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
TEMPORARY FILENAME 56 1024 5 0 0 8 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0

42 rows selected.

SQL>
SQL> select dest_id, standby_dest, deleted, status, count(*)
2 from v$archived_log
3 group by dest_id, standby_dest, deleted, status
4 order by 1,2,3,4
5 /

DEST_ID STA DEL S COUNT(*)
---------- --- --- - ----------
1 NO NO A 15

SQL>


The RECORDS_USED for ARCHIVED LOG" and "BACKUP DATAFILE" and "BACKUP PIECE" are now properly reset.
I would NOT advice that you periodically recreate your Controlfile.  

But this is an available method to "reset" the controlfile. I could also take this opportunity to increase controlled-values like MAXLOGFILES, MAXMEMBERS and MAXDATAFILES (or even MAXINSTANCES !).


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

SQL> @Recreate_Controlfile.SQL
SQL> spool Recreate_Controlfile
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 1207955544 bytes
Fixed Size 9134168 bytes
Variable Size 671088640 bytes
Database Buffers 520093696 bytes
Redo Buffers 7639040 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLCDB" NORESETLOGS FORCE LOGGING ARCHIVELOG
2 MAXLOGFILES 24
3 MAXLOGMEMBERS 4
4 MAXDATAFILES 2048
5 MAXINSTANCES 8
6 MAXLOGHISTORY 512
7 LOGFILE
8 GROUP 1 '/opt/oracle/oradata/ORCLCDB/redo01.log' SIZE 200M BLOCKSIZE 512,
9 GROUP 2 '/opt/oracle/oradata/ORCLCDB/redo02.log' SIZE 200M BLOCKSIZE 512,
10 GROUP 3 '/opt/oracle/oradata/ORCLCDB/redo03.log' SIZE 200M BLOCKSIZE 512
....
....
....


RMAN> catalog start with '/opt/oracle/FRA/ORCLCDB';
....
....
....



SQL> select *
2 from v$controlfile_record_section
3 order by type
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ACM OPERATION 104 64 11 0 0 0 0
ARCHIVED LOG 584 532 18 1 18 18 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 37 1 37 37 0
BACKUP PIECE 780 1006 26 1 26 26 0
BACKUP REDOLOG 76 215 112 1 112 112 0
BACKUP SET 96 1022 26 1 26 26 0
BACKUP SPFILE 124 131 10 1 10 10 0
CKPT PROGRESS 8180 11 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DATABASE 316 1 1 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
DATABASE INCARNATION 56 292 2 1 2 2 0
DATAFILE 520 2048 34 0 0 39 0
DATAFILE COPY 736 1000 0 0 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0 0
FILENAME 524 6242 27 0 0 0 0
FLASHBACK LOG 84 2048 0 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
LOG HISTORY 56 584 1 1 1 1 0
MTTR 100 8 1 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
OFFLINE RANGE 200 1063 12 1 12 12 0
PDB RECORD 780 10 4 0 0 6 0
PDBINC RECORD 144 113 0 0 0 0 0
PROXY COPY 928 1004 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
REDO LOG 72 24 3 0 0 0 0
REDO THREAD 256 8 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 9 0 0 0 0
RESTORE POINT 256 2108 0 0 0 0 0
RMAN CONFIGURATION 1108 50 3 0 0 3 0
RMAN STATUS 116 141 1 1 1 1 0
STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
TABLESPACE 180 2048 22 0 0 7 0
TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
TEMPORARY FILENAME 56 2048 5 0 0 8 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0

42 rows selected.

SQL>


Note how I have increased the "DATAFILE" RECORDS_TOTAL for "DATAFILE" from 1024 to 2048 and for "REDO LOG" from 16 to 24 and "LOGHISTORY" from 292 to 584.


Categories: DBA Blogs

The contents of the Database Controlfile -- 2 : Physical Structure

Tue, 2022-01-18 08:41

 In my previous post, I had demonstrated how you can view the "count of records" in different sections of the controlfile.  However, you cannot actually view the data in those records.

You can, however, do a Physical Dump of the controlfile :



QL> select name, block_size*file_size_blks/1024
2 from v$controlfile;

NAME
------------------------------------------------------------------------------------------------------------------------------------
BLOCK_SIZE*FILE_SIZE_BLKS/1024
------------------------------
/opt/oracle/oradata/ORCLCDB/control01.ctl
18688

/opt/oracle/oradata/ORCLCDB/control02.ctl
18688


SQL> !ls -l /opt/oracle/oradata/ORCLCDB/control01.ctl
-rw-r-----. 1 oracle oinstall 19152896 Jan 18 22:15 /opt/oracle/oradata/ORCLCDB/control01.ctl

SQL> !bc
bc 1.06.95
Copyright 1991-1994, 1997, 1998, 2000, 2004, 2006 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'.
19152896/1024
18704
quit

SQL>
SQL> alter session set tracefile_identifier='my_cntrlfile_dump';

Session altered.

SQL>
SQL> select *
2 from v$diag_info
3 where name = 'Default Trace File'
4 /

INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1 Default Trace File
/opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_3207_my_cntrlfile_dump.trc
0


SQL>
SQL> alter session set events 'immediate trace name controlf level 10';

Session altered.

SQL> !ls -l /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_3207_my_cntrlfile_dump.trc
-rw-r-----. 1 oracle oinstall 1449760 Jan 18 22:17 /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_3207_my_cntrlfile_dump.trc

SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>view /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_3207_my_cntrlfile_dump.trc


The "alter session set events 'immediate trace name controlf level 10'" command does a Physical Dump of the trace file.

I can't reproduce the whole file here, but am presenting the interesting bits.


DUMP OF CONTROL FILES, Seq # 1036136918 = 0x3dc231d6
V10 STYLE FILE HEADER:
Compatibility Vsn = 318767104=0x13000000
Db ID=2778483057=0xa59c4971, Db Name='ORCLCDB'
Activation ID=0=0x0
Control Seq=1036136918=0x3dc231d6, File size=1168=0x490
File Number=0, Blksiz=16384, File Type=1 CONTROL
Logical block number 1 (header block)
Dump of memory from 0x00007F02655C9000 to 0x00007F02655CD000
7F02655C9000 0000C215 00000001 00000000 04010000 [................]
7F02655C9010 0000791A 00000000 13000000 A59C4971 [.y..........qI..]
7F02655C9020 4C43524F 00424443 3DC231D6 00000490 [ORCLCDB..1.=....]
7F02655C9030 00004000 00010000 00000000 00000000 [.@..............]
7F02655C9040 32474154 30313230 54363239 33333132 [TAG20210926T2133]
7F02655C9050 00003230 00000000 00000000 00000000 [02..............]
7F02655C9060 AACA5B83 3C0C08F2 01025B25 00008000 [.[.....<%[......]
7F02655C9070 413A3EE4 00000000 00000000 00000000 [.>:A............]
7F02655C9080 00000000 00000000 00000000 00000000 [................]
Repeat 7 times
7F02655C9100 00000000 00000000 00000008 00000008 [................]
7F02655C9110 00000008 00000000 00000000 00000000 [................]
7F02655C9120 00000001 00000000 00000000 00000000 [................]
7F02655C9130 00000000 00000000 00000000 00000000 [................]
Repeat 1003 times
7F02655CCFF0 00000000 00000000 00000000 00001501 [................]
... notihing interesting
... except that, funnily, some RMAN Backup TAG is visible !


Logical block number 44
Dump of memory from 0x00007F02655AD000 to 0x00007F02655B1000
7F02655AD000 0000C215 00000059 3DC230EF 0401FFFF [....Y....0.=....]
7F02655AD010 0000461E 00030003 00000000 6F2F0000 [.F............/o]
7F02655AD020 6F2F7470 6C636172 726F2F65 74616461 [pt/oracle/oradat]
7F02655AD030 524F2F61 44434C43 65722F42 33306F64 [a/ORCLCDB/redo03]
7F02655AD040 676F6C2E 00000000 00000000 00000000 [.log............]
7F02655AD050 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AD220 00020003 00000000 6F2F0000 6F2F7470 [........../opt/o]
7F02655AD230 6C636172 726F2F65 74616461 524F2F61 [racle/oradata/OR]
7F02655AD240 44434C43 65722F42 32306F64 676F6C2E [CLCDB/redo02.log]
7F02655AD250 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AD420 00000000 00000000 00000000 00010003 [................]
7F02655AD430 00000000 6F2F0000 6F2F7470 6C636172 [....../opt/oracl]
7F02655AD440 726F2F65 74616461 524F2F61 44434C43 [e/oradata/ORCLCD]
7F02655AD450 65722F42 31306F64 676F6C2E 00000000 [B/redo01.log....]
7F02655AD460 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AD630 00000000 00000000 00070004 00000000 [................]
7F02655AD640 6F2F0000 6F2F7470 6C636172 726F2F65 [../opt/oracle/or]
7F02655AD650 74616461 524F2F61 44434C43 73752F42 [adata/ORCLCDB/us]
7F02655AD660 30737265 62642E31 00000066 00000000 [ers01.dbf.......]
7F02655AD670 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AD840 00000000 00040004 00000000 6F2F0000 [............../o]
7F02655AD850 6F2F7470 6C636172 726F2F65 74616461 [pt/oracle/oradat]
7F02655AD860 524F2F61 44434C43 6E752F42 62746F64 [a/ORCLCDB/undotb]
7F02655AD870 2E313073 00666264 00000000 00000000 [s01.dbf.........]
7F02655AD880 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655ADA50 00010004 00000000 6F2F0000 6F2F7470 [........../opt/o]
7F02655ADA60 6C636172 726F2F65 74616461 524F2F61 [racle/oradata/OR]
7F02655ADA70 44434C43 79732F42 6D657473 642E3130 [CLCDB/system01.d]
7F02655ADA80 00006662 00000000 00000000 00000000 [bf..............]
7F02655ADA90 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
7F02655ADC50 00000000 00000000 00000000 00030004 [................]
7F02655ADC60 00000000 6F2F0000 6F2F7470 6C636172 [....../opt/oracl]
7F02655ADC70 726F2F65 74616461 524F2F61 44434C43 [e/oradata/ORCLCD]
7F02655ADC80 79732F42 78756173 642E3130 00006662 [B/sysaux01.dbf..]
7F02655ADC90 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655ADE60 00000000 00000000 00010007 00000000 [................]
7F02655ADE70 6F2F0000 6F2F7470 6C636172 726F2F65 [../opt/oracle/or]
7F02655ADE80 74616461 524F2F61 44434C43 65742F42 [adata/ORCLCDB/te]
7F02655ADE90 3130706D 6662642E 00000000 00000000 [mp01.dbf........]
7F02655ADEA0 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AE070 00000000 00050004 00000000 6F2F0000 [............../o]
7F02655AE080 6F2F7470 6C636172 726F2F65 74616461 [pt/oracle/oradat]
7F02655AE090 524F2F61 44434C43 64702F42 65657362 [a/ORCLCDB/pdbsee]
7F02655AE0A0 79732F64 6D657473 642E3130 00006662 [d/system01.dbf..]
7F02655AE0B0 00000000 00000000 00000000 00000000 [................]
Repeat 28 times
7F02655AE280 00060004 00000000 6F2F0000 6F2F7470 [........../opt/o]
7F02655AE290 6C636172 726F2F65 74616461 524F2F61 [racle/oradata/OR]
7F02655AE2A0 44434C43 64702F42 65657362 79732F64 [CLCDB/pdbseed/sy]
7F02655AE2B0 78756173 642E3130 00006662 00000000 [saux01.dbf......]
7F02655AE2C0 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
7F02655AE480 00000000 00000000 00000000 00080004 [................]
7F02655AE490 00000000 6F2F0000 6F2F7470 6C636172 [....../opt/oracl]
7F02655AE4A0 726F2F65 74616461 524F2F61 44434C43 [e/oradata/ORCLCD]
7F02655AE4B0 64702F42 65657362 6E752F64 62746F64 [B/pdbseed/undotb]
7F02655AE4C0 2E313073 00666264 00000000 00000000 [s01.dbf.........]
7F02655AE4D0 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
.... and so on ..
... So, in the above section we see the physical datafiles of the database.




7F02655B0760 6F000000 6F2F7470 6C636172 72702F65 [...opt/oracle/pr]
7F02655B0770 6375646F 39312F74 62642F63 656D6F68 [oduct/19c/dbhome]
7F02655B0780 642F315F 732F7362 6370616E 524F5F66 [_1/dbs/snapcf_OR]
7F02655B0790 44434C43 00662E42 00000000 00000000 [CLCDB.f.........]
7F02655B07A0 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
7F02655B0960 00000000 00000000 00000000 6F000000 [...............o]
7F02655B0970 6F2F7470 6C636172 72702F65 6375646F [pt/oracle/produc]
7F02655B0980 39312F74 62642F63 656D6F68 642F315F [t/19c/dbhome_1/d]
7F02655B0990 732F7362 6370616E 524F5F66 44434C43 [bs/snapcf_ORCLCD]
7F02655B09A0 00662E42 00000000 00000000 00000000 [B.f.............]
7F02655B09B0 00000000 00000000 00000000 00000000 [................]
... here we see the Snapshot Controlfile



Logical block number 177
Dump of memory from 0x00007F02655C1000 to 0x00007F02655C5000
7F02655C1000 0000C215 00000163 3DC230C2 0401FFFF [....c....0.=....]
7F02655C1010 0000993A 00000001 59530006 58554153 [:.........SYSAUX]
7F02655C1020 00000000 00000000 00000000 00000000 [................]
Repeat 8 times
7F02655C10B0 00000000 00010003 00000000 00000000 [................]
7F02655C10C0 00000000 00000000 00000000 59530006 [..............SY]
7F02655C10D0 4D455453 00000000 00000000 00000000 [STEM............]
7F02655C10E0 00000000 00000000 00000000 00000000 [................]
Repeat 7 times
7F02655C1160 00000000 00000000 00010001 00000000 [................]
7F02655C1170 00000000 00000000 00000000 00000002 [................]
7F02655C1180 4E550008 42544F44 00003153 00000000 [..UNDOTBS1......]
7F02655C1190 00000000 00000000 00000000 00000000 [................]
Repeat 7 times
7F02655C1210 00000000 00000000 00000000 00010004 [................]
7F02655C1220 00000000 00000000 00000000 00000000 [................]
7F02655C1230 00000004 53550005 00535245 00000000 [......USERS.....]
7F02655C1240 00000000 00000000 00000000 00000000 [................]
Repeat 8 times
7F02655C12D0 00010007 00000000 00000000 00000000 [................]
7F02655C12E0 00000000 00000003 45540004 0000504D [..........TEMP..]
7F02655C12F0 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
7F02655C1360 00000000 00000000 00010000 00000000 [................]
7F02655C1370 00000000 00000000 00000000 00000000 [................]
7F02655C1380 00000000 00010001 00000000 00000000 [................]
7F02655C1390 00000000 00000000 00000000 59530006 [..............SY]
7F02655C13A0 4D455453 00000000 00000000 00000000 [STEM............]
7F02655C13B0 00000000 00000000 00000000 00000000 [................]
Repeat 7 times
7F02655C1430 00000000 00000000 00020005 00000000 [................]
7F02655C1440 00000000 00000000 00000000 00000001 [................]
7F02655C1450 59530006 58554153 00000000 00000000 [..SYSAUX........]
7F02655C1460 00000000 00000000 00000000 00000000 [................]
.... and so on ...
... this section has Tablespace Names.




Logical block number 193
Dump of memory from 0x00007F02655C1000 to 0x00007F02655C5000
7F02655C1000 0000C215 00000183 3DC1CC8F 0401FFFF [...........=....]
7F02655C1010 00000887 48435241 4C455649 4420474F [....ARCHIVELOG D]
7F02655C1020 54454C45 204E4F49 494C4F50 00005943 [ELETION POLICY..]
7F02655C1030 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F02655C1050 00000000 204F5400 4C505041 20444549 [.....TO APPLIED ]
7F02655C1060 41204E4F 53204C4C 444E4154 42205942 [ON ALL STANDBY B]
7F02655C1070 454B4341 50552044 54203120 53454D49 [ACKED UP 1 TIMES]
7F02655C1080 204F5420 4B534944 00000000 00000000 [ TO DISK........]
7F02655C1090 00000000 00000000 00000000 00000000 [................]
Repeat 60 times
7F02655C1460 00000000 00000000 49564544 54204543 [........DEVICE T]
7F02655C1470 00455059 00000000 00000000 00000000 [YPE.............]
7F02655C1480 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F02655C14A0 00000000 00000000 53494400 4150204B [.........DISK PA]
7F02655C14B0 4C4C4152 53494C45 2032204D 4B434142 [RALLELISM 2 BACK]
7F02655C14C0 54205055 20455059 42204F54 554B4341 [UP TYPE TO BACKU]
7F02655C14D0 54455350 00000000 00000000 00000000 [PSET............]
7F02655C14E0 00000000 00000000 00000000 00000000 [................]
Repeat 944 times
... this secion has RMAN CONFGURATION information



Logical block number 211
Dump of memory from 0x00007F0265589000 to 0x00007F026558D000
7F0265589000 0000C215 000001A8 3DC21EA3 0401FFFF [...........=....]
7F0265589010 000008C1 40845AAE 00010012 000001EE [.....Z.@........]
7F0265589020 00493300 00000000 3DC1C40E 00E6DAE8 [.3I........=....]
7F0265589030 00008000 40845629 00E6E7E1 00008000 [....)V.@........]
7F0265589040 40845A9A 0002631D 00000200 00000001 [.Z.@.c..........]
7F0265589050 A7521CCD 00010000 74706F2F 61726F2F [..R...../opt/ora]
7F0265589060 2F656C63 68637261 6C657669 4F2F676F [cle/archivelog/O]
7F0265589070 434C4352 312F4244 3439345F 3330315F [RCLCDB/1_494_103]
7F0265589080 38303136 2E343138 00666264 00000000 [6108814.dbf.....]
7F0265589090 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
7F0265589250 00000000 00000000 00000000 40A141E4 [.............A.@]
7F0265589260 00010012 000001EF 00493300 00000000 [.........3I.....]
7F0265589270 3DC1C40E 00E6E7E1 00008000 40845A9A [...=.........Z.@]
7F0265589280 00E6E7E4 00008000 40A141E3 00000001 [.........A.@....]
7F0265589290 00000200 00000001 A7521CCD 00010000 [..........R.....]
7F02655892A0 74706F2F 61726F2F 2F656C63 68637261 [/opt/oracle/arch]
7F02655892B0 6C657669 4F2F676F 434C4352 312F4244 [ivelog/ORCLCDB/1]
7F02655892C0 3539345F 3330315F 38303136 2E343138 [_495_1036108814.]
7F02655892D0 00666264 00000000 00000000 00000000 [dbf.............]
7F02655892E0 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
7F02655894A0 00000000 40A143D5 00010012 000001F0 [.....C.@........]
7F02655894B0 00493300 00000000 3DC1C40E 00E6E7E4 [.3I........=....]
7F02655894C0 00008000 40A141E3 00E86F7B 00008000 [.....A.@{o......]
7F02655894D0 40A143D5 000000A9 00000200 00000001 [.C.@............]
7F02655894E0 A7521CCD 00010000 74706F2F 61726F2F [..R...../opt/ora]
7F02655894F0 2F656C63 68637261 6C657669 4F2F676F [cle/archivelog/O]
7F0265589500 434C4352 312F4244 3639345F 3330315F [RCLCDB/1_496_103]
7F0265589510 38303136 2E343138 00666264 00000000 [6108814.dbf.....]
7F0265589520 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
.... and so on ...
... this section has *some* ArchiveLog FileNames
... other ArchiveLog FileNames appear further down the file



Repeat 30 times
7F0265589DC0 00000000 40A143DD 0001061A 000001EA [.....C.@........]
7F0265589DD0 00493300 00000000 3DC1C40E 00E683F5 [.3I........=....]
7F0265589DE0 00008000 40844BA3 00E6841B 00008000 [.....K.@........]
7F0265589DF0 40844BA9 00000017 00000200 003C0001 [.K.@..........<.]
7F0265589E00 A7521CCD 00030000 42445453 00000032 [..R.....STDB2...]
7F0265589E10 00000000 00000000 00000000 00000000 [................]
.... here I see a reference to one of my Standby Databases STDB2




Logical block number 218
Dump of memory from 0x00007F02655A5000 to 0x00007F02655A9000
7F02655A5000 0000C215 000001B5 3DC1D6CE 0401FFFF [...........=....]
7F02655A5010 0000B131 3D94E011 3D94E010 00000001 [1......=...=....]
7F02655A5020 00018003 3D94E011 00000000 00008FA0 [.......=........]
7F02655A5030 4B534944 00000000 00000000 00000000 [DISK............]
7F02655A5040 706F0000 726F2F74 656C6361 6F72702F [..opt/oracle/pro]
7F02655A5050 74637564 6339312F 6862642F 5F656D6F [duct/19c/dbhome_]
7F02655A5060 62642F31 2D632F73 38373732 30333834 [1/dbs/c-27784830]
7F02655A5070 322D3735 30303230 2D333232 00003030 [57-20200223-00..]
7F02655A5080 00000000 00000000 00000000 00000000 [................]
Repeat 36 times
7F02655A52D0 00000000 32474154 30303230 54333232 [....TAG20200223T]
7F02655A52E0 37343232 00003434 00000000 00000000 [224744..........]
7F02655A52F0 00000000 00000000 B637B686 657ADF2F [..........7./.ze]
7F02655A5300 06F753E0 CA270AE8 3D94E011 00000000 [.S....'....=....]
7F02655A5310 00000000 00000000 00000000 00000000 [................]
7F02655A5320 3DC1C42D 3DC1C42C 00000024 0001C003 [-..=,..=$.......]
7F02655A5330 3DC1C42D 00000000 00008FA0 4B534944 [-..=........DISK]
7F02655A5340 00000000 00000000 00000000 706F0000 [..............op]
7F02655A5350 726F2F74 656C6361 4152462F 43524F2F [t/oracle/FRA/ORC]
7F02655A5360 4244434C 43524F2F 4244434C 7475612F [LCDB/ORCLCDB/aut]
7F02655A5370 6361626F 2F70756B 30323032 5F33305F [obackup/2020_03_]
7F02655A5380 6F2F3732 666D5F31 315F735F 31363330 [27/o1_mf_s_10361]
7F02655A5390 34383830 37685F34 66666E73 2E5F7862 [08844_h7snffbx_.]
7F02655A53A0 00706B62 00000000 00000000 00000000 [bkp.............]
7F02655A53B0 00000000 00000000 00000000 00000000 [................]
Repeat 34 times
7F02655A55E0 32474154 30303230 54373233 30303030 [TAG20200327T0000]
7F02655A55F0 00003434 00000000 00000000 00000000 [44..............]
7F02655A5600 00000000 B637B686 657ADF2F 06F753E0 [......7./.ze.S..]
7F02655A5610 CA270AE8 3DC1C42D 00000000 00000000 [..'.-..=........]
7F02655A5620 00000000 00000000 00000000 3E0E1E81 [...............>]
7F02655A5630 3E0E1E80 00000027 0001C003 3E0E1E97 [...>'..........>]
7F02655A5640 00000040 00093AA0 4B534944 00000000 [@....:..DISK....]
7F02655A5650 00000000 00000000 706F0000 726F2F74 [..........opt/or]
7F02655A5660 656C6361 4152462F 43524F2F 4244434C [acle/FRA/ORCLCDB]
7F02655A5670 43524F2F 4244434C 6361622F 7370756B [/ORCLCDB/backups]
7F02655A5680 322F7465 5F303230 325F3530 316F2F32 [et/2020_05_22/o1]
7F02655A5690 5F666D5F 646E6E6E 41545F66 32303247 [_mf_nnndf_TAG202]
7F02655A56A0 32353030 31325432 34323835 6864685F [00522T215824_hdh]
7F02655A56B0 31316E70 622E5F6C 0000706B 00000000 [pn11l_.bkp......]
7F02655A56C0 00000000 00000000 00000000 00000000 [................]
Repeat 31 times
7F02655A58C0 00000000 00000000 00000000 3E0E1E80 [...............>]
7F02655A58D0 00000000 0000002C 00000000 00000000 [....,...........]
7F02655A58E0 00000000 00000000 00000000 32474154 [............TAG2]
7F02655A58F0 30303230 54323235 38353132 00003432 [0200522T215824..]
7F02655A5900 00000000 00000000 00000000 00000000 [................]
7F02655A5910 B637B686 657ADF2F 06F753E0 CA270AE8 [..7./.ze.S....'.]
7F02655A5920 3E0E1E81 00000000 00000000 00000000 [...>............]
7F02655A5930 00000000 00000000 3E0E1E9A 3E0E1E9A [...........>...>]
7F02655A5940 00000028 0001C003 3E0E1EAC 00000040 [(..........>@...]
7F02655A5950 000409F0 4B534944 00000000 00000000 [....DISK........]
7F02655A5960 00000000 706F0000 726F2F74 656C6361 [......opt/oracle]
7F02655A5970 4152462F 43524F2F 4244434C 43524F2F [/FRA/ORCLCDB/ORC]
7F02655A5980 4244434C 3138382F 39324532 46343638 [LCDB/8812E29864F]
7F02655A5990 31363734 35304535 30313033 37303030 [47615E0530100007]
7F02655A59A0 32344146 61622F34 70756B63 2F746573 [FA424/backupset/]
7F02655A59B0 30323032 5F35305F 6F2F3232 666D5F31 [2020_05_22/o1_mf]
7F02655A59C0 6E6E6E5F 545F6664 30324741 35303032 [_nnndf_TAG202005]
7F02655A59D0 32543232 32383531 64685F34 746E7068 [22T215824_hdhpnt]
7F02655A59E0 2E5F7939 00706B62 00000000 00000000 [9y_.bkp.........]
.... and so on ...
... here I see some of the BackupPiece FileNames
... strangely enough BackupPieces from May 2020 are still in the controlfile !!
... more appear later down the file


7F02655BF7B0 00000000 00000000 00000000 41374F03 [.............O7A]
7F02655BF7C0 00000000 00000155 00000000 00000000 [....U...........]
7F02655BF7D0 00000000 00000000 00000000 32474154 [............TAG2]
7F02655BF7E0 30323230 54363131 39343631 00003830 [0220116T164908..]
7F02655BF7F0 00000000 00000000 00000000 00000000 [................]
7F02655BF800 98E21288 1576F464 000153E0 24A47F00 [....d.v..S.....$]
7F02655BF810 41374F08 00000000 00000000 00000000 [.O7A............]
7F02655BF820 00000000 00000000 41374F3D 41374F3C [........=O7AO7A....]
7F02655BF840 000092E0 4B534944 00000000 00000000 [....DISK........]
7F02655BF850 00000000 706F2F00 726F2F74 656C6361 [...../opt/oracle]
7F02655BF860 4152462F 43524F2F 4244434C 43524F2F [/FRA/ORCLCDB/ORC]
7F02655BF870 4244434C 7475612F 6361626F 2F70756B [LCDB/autobackup/]
7F02655BF880 32323032 5F31305F 6F2F3631 666D5F31 [2022_01_16/o1_mf]
7F02655BF890 315F735F 31343930 30383334 796A5F34 [_s_1094143804_jy]
7F02655BF8A0 78317137 2E5F3170 00706B62 00000000 [7q1xp1_.bkp.....]
7F02655BF8B0 00000000 00000000 00000000 00000000 [................]
Repeat 32 times
7F02655BFAC0 00000000 00000000 41374F3C 00000000 [........



So, it seems that not every entry in the Controlfile is ordered.
And also, that there are many entries referencing ArchiveLogs or BackupPieces that no longer exist but haven't been cleaned up yet !




Categories: DBA Blogs

The contents of the Database Controlfile -- 1 : Logical Sructure

Sun, 2022-01-16 03:51

 (No, I am not referring to the "alter database backup controlfile to trace ..." command here.

In Oracle, the controlfile for a database is the "master" reference to the physical structure of the database and "known" backups.  


This what I see in my 19c database :

SQL> select *
2 from v$controlfile_record_section
3 order by type
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ACM OPERATION 104 64 11 0 0 0 0
ARCHIVED LOG 584 383 383 161 160 1407 0
AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 175 1 175 175 0
BACKUP PIECE 780 1006 136 1 136 136 0
BACKUP REDOLOG 76 215 153 1 153 153 0
BACKUP SET 96 1022 125 1 125 125 0
BACKUP SPFILE 124 131 25 1 25 25 0
CKPT PROGRESS 8180 11 0 0 0 0 0
COPY CORRUPTION 40 1227 0 0 0 0 0
DATABASE 316 1 1 0 0 0 0
DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0
DATABASE INCARNATION 56 292 4 1 4 4 0
DATAFILE 520 1024 32 0 0 1493 0
DATAFILE COPY 736 1000 3 1 3 3 0
DATAFILE HISTORY 568 57 0 0 0 0 0
DELETED OBJECT 20 818 635 1 635 635 0
FILENAME 524 4146 28 0 0 0 0
FLASHBACK LOG 84 2048 2 0 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0
GUARANTEED RESTORE POINT 256 2048 1 0 0 2 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0
LOG HISTORY 56 292 292 28 27 611 0
MTTR 100 8 1 0 0 0 0
MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0
OFFLINE RANGE 200 1063 678 1 678 678 0
PDB RECORD 780 10 5 0 0 26 0
PDBINC RECORD 144 113 0 0 0 0 0
PROXY COPY 928 1004 0 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0 0
REDO LOG 72 16 4 0 0 15 0
REDO THREAD 256 8 1 0 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0
RESTORE POINT 256 2108 0 0 0 0 0
RMAN CONFIGURATION 1108 50 2 0 0 4 0
RMAN STATUS 116 141 141 58 57 339 0
STANDBY DATABASE MATRIX 400 128 128 0 0 0 0
TABLESPACE 180 1024 21 0 0 72 0
TABLESPACE KEY HISTORY 108 151 0 0 0 0 0
TEMPORARY FILENAME 56 1024 7 0 0 36 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0

42 rows selected.

SQL>


This structure is explained by :

SQL> select view_Definition from v$fixed_view_definition where view_name = 'GV$CONTROLFILE_RECORD_SECTION'
2 /

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------
select inst_id,decode(indx,0,'DATABASE',1, 'CKPT PROGRESS', 2, 'REDO THREAD',3,'REDO LOG',4,'DATAFILE',5,'FILENAME',6,'TABLESPACE',7
,'TEMPORARY FILENAME',8,'RMAN CONFIGURATION',9,'LOG HISTORY',10,'OFFLINE RANGE',11,'ARCHIVED LOG',12,'BACKUP SET',13,'BACKUP PIECE',
14,'BACKUP DATAFILE',15, 'BACKUP REDOLOG',16,'DATAFILE COPY',17,'BACKUP CORRUPTION',18,'COPY CORRUPTION',19,'DELETED OBJECT',20,'PRO
XY COPY',21,'BACKUP SPFILE',23,'DATABASE INCARNATION',24,'FLASHBACK LOG',25, 'RECOVERY DESTINATION', 26,'INSTANCE SPACE RESERVATION'
, 27, 'REMOVABLE RECOVERY FILES', 28, 'RMAN STATUS', 29, 'THREAD INSTANCE NAME MAPPING', 30, 'MTTR', 31, 'DATAFILE HISTORY', 32, 'ST
ANDBY DATABASE MATRIX', 33, 'GUARANTEED RESTORE POINT', 34, 'RESTORE POINT', 35, 'DATABASE BLOCK CORRUPTION', 36, 'ACM OPERATION', 3
7, 'FOREIGN ARCHIVED LOG', 38, 'PDB RECORD', 39, 'AUXILIARY DATAFILE COPY', 40, 'MULTI INSTANCE REDO APPLY', 41, 'PDBINC RECORD', 42
, 'TABLESPACE KEY HISTORY', 'UNKNOWN'),rsrsz,rsnum,rsnus,rsiol,rsilw,rsrlw, con_id from x$kccrs where indx not in (22)


SQL>


Thus, for example :

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create tablespace x;

Tablespace created.

SQL>
SQL> select *
2 from v$controlfile_record_section
3 where type in ('DATAFILE','TABLESPACE')
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
DATAFILE 520 1024 33 0 0 1494 0
TABLESPACE 180 1024 22 0 0 73 0

SQL>


The datafile and tablespace record counts incremented by 1 for the new tablespace and datafile in PDB ORCLPDB1

oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 16 16:48:54 2022
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)

RMAN> backup pluggable database orclpdb1;

Starting backup at 16-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=371 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00031 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
input datafile file number=00033 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_x_jy7po5gg_.dbf
channel ORA_DISK_1: starting piece 1 at 16-JAN-22
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
input datafile file number=00026 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
input datafile file number=00032 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf
channel ORA_DISK_2: starting piece 1 at 16-JAN-22
channel ORA_DISK_1: finished piece 1 at 16-JAN-22
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q04z2_.bkp tag=TAG20220116T164908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_2: finished piece 1 at 16-JAN-22
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q08kj_.bkp tag=TAG20220116T164908 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:56
Finished backup at 16-JAN-22

Starting Control File and SPFILE Autobackup at 16-JAN-22
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143804_jy7q1xp1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-JAN-22
RMAN-08591: warning: invalid archived log deletion policy

RMAN>

SQL> select *
2 from v$controlfile_record_section
3 where type like 'BACKUP%'
4 order by type
5 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
BACKUP CORRUPTION 44 1115 0 0 0 0 0
BACKUP DATAFILE 200 1063 185 1 185 185 0
BACKUP PIECE 780 1006 140 1 140 140 0
BACKUP REDOLOG 76 215 153 1 153 153 0
BACKUP SET 96 1022 129 1 129 129 0
BACKUP SPFILE 124 131 27 1 27 27 0

6 rows selected.

SQL>


My RMAN Backup of was for 8 datafiles and ran to 3 BackupPieces and 3 BackupSets.
Yet, the number of "BACKUP DATAFILE" records increased by 10, the number of "BACKUP PIECE" by 4 and the number of "BACKUP SET"s by 4.  Also, note the "BACKUP SPFILE" records also increased by 2.
The difference is explained by the AutoBackup created immediately after I added the new tablespace 'X' and datafile to the Pluggable Database ORCLPDB1 and the fact that the controlfile AutoBackup  is included in the "BACKUP DATAFILE" count.


RMAN> list backup completed after "sysdate-1"
2> ;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
126 Full 18.36M DISK 00:00:02 16-JAN-22
BP Key: 137 Status: AVAILABLE Compressed: NO Tag: TAG20220116T164836
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143716_jy7pz5dd_.bkp
SPFILE Included: Modification time: 16-JAN-22
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 16813310 Ckp time: 16-JAN-22

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
127 Full 342.80M DISK 00:00:42 16-JAN-22
BP Key: 138 Status: AVAILABLE Compressed: NO Tag: TAG20220116T164908
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q04z2_.bkp
List of Datafiles in backup set 127
Container ID: 3, PDB Name: ORCLPDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
10 Full 16813384 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
11 Full 16813384 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
31 Full 16813384 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
33 Full 16813384 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_x_jy7po5gg_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
128 Full 635.72M DISK 00:00:51 16-JAN-22
BP Key: 139 Status: AVAILABLE Compressed: NO Tag: TAG20220116T164908
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2022_01_16/o1_mf_nnndf_TAG20220116T164908_jy7q08kj_.bkp
List of Datafiles in backup set 128
Container ID: 3, PDB Name: ORCLPDB1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 16813386 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
12 Full 16813386 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
26 Full 16813386 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
32 Full 16813386 16-JAN-22 NO /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
129 Full 18.36M DISK 00:00:02 16-JAN-22
BP Key: 140 Status: AVAILABLE Compressed: NO Tag: TAG20220116T165004
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2022_01_16/o1_mf_s_1094143804_jy7q1xp1_.bkp
SPFILE Included: Modification time: 16-JAN-22
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 16813424 Ckp time: 16-JAN-22

RMAN>


Thus, BackupSet 126 is the automatically-created fourth "BACKUP SET"and the AutoBackup in BackupSets 126 and 129 are the two additional "BACKUP DATAFILE"s.  Simillarly, the SPFiles included in the two AutoBackups also incremented the "BACKUP SPFILE" count.

However, when you DROP a Tablespace (and remove it's Datafile(s), the record count does NOT decrement.


SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> drop tablespace X including contents and datafiles;

Tablespace dropped.

SQL>

SQL> select *
2 from v$controlfile_record_section
3 where type in ('DATAFILE','TABLESPACE')
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
DATAFILE 520 1024 33 0 0 1495 0
TABLESPACE 180 1024 22 0 0 74 0

SQL>


It simply means that the "33rd" DATAFILE and "22nd" TABLESPACE records are reusable later. (Note that LAST_RECID also has got incremented for the two entries). Note how "RECORDS_TOTAL" is 1024 for "DATAFILE" and "TABLESPACE". This allows for slots that are present but not in use currently.

What about ArchiveLogs ?


SQL> select *
2 from v$controlfile_record_section
3 where type like 'ARCHIVE%'
4 order by type
5 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ARCHIVED LOG 584 383 383 161 160 1407 0

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> select *
2 from v$controlfile_record_section
3 where type like 'ARCHIVE%'
4 order by type
5 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ARCHIVED LOG 584 383 383 165 164 1411 0

SQL>
SQL> select count(*)
2 from v$archived_log
3 /

COUNT(*)
----------
383

SQL>


Apparently, Oracle reuses "ARCHIVED LOG" records in the Controlfile, while adjusting the FIRST_INDEX, LAST_INDEX and LAST_RECID values (Note how, in this cas, "FIRST_INDEX" is actually less than "LAST_INDEX"). So, this seems to be Circular Structure that will expand only when necessary.

So, if I still generate two more ArchiveLogs and then check and delete missing ArchiveLogs with :


SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL>


RMAN> crosscheck archivelog all; -- which returns a number of "validation failed for archived log" warnings, indicating ArchiveLogs that have been deleted at the OS level
and
RMAN> delete noprompt expired archivelog all; -- to delete all those marked expired after validation failed

SQL> select *
2 from v$controlfile_record_section
3 where type like 'ARCHIVE%'
4 order by type
5 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
ARCHIVED LOG 584 383 383 169 168 1415 0

SQL>
SQL> select dest_id, standby_dest, deleted, status, count(*)
2 from v$archived_log
3 group by dest_id, standby_dest, deleted, status
4 order by 1,2,3,4
5 /

DEST_ID STA DEL S COUNT(*)
---------- --- --- - ----------
1 NO NO A 102
1 NO YES D 23
2 NO NO A 8
2 YES NO A 97
3 YES NO A 105
4 YES NO A 48

6 rows selected.

SQL>


23 ArchiveLog entries at DEST_ID=1 are now marked as DELETED (by the "DELETE .. EXPIRED ARCHIVELOG ALL" command).
The FIRST_INDEX and LAST_INDEX have changed again. 

 My query on v$archived_log shows a a number of entries for DEST_IDs 2 to 4 as Standby Destinations (I have 3 Standby Databases, so this Database as a Primary, is tracking the ArchiveLogs it has to send to the Standbys).  Only entries for DEST_ID=1 are on the Local Filesystem on this server.

So : The 383 Records in the Controlfile does not represent the actual count of Physical ArchiveLogs for this Database present on this server.  At some point in time in the past, the number of entries had hit 383 but now there are "empty slots" that are being reused.

In the meantime, "DELETED OBJECT" count has increased by 48.


SQL> select *
2 from v$controlfile_record_section
3 where type = 'DELETED OBJECT'
4 /

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
---------------------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
DELETED OBJECT 20 818 683 1 683 683 0

SQL>


Is it some form of "garbage collector" ?
Categories: DBA Blogs

Pages