(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.
Is it some form of "garbage collector" ?
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" ?
No comments:
Post a Comment