DBA Blogs

HTML DB

Tom Kyte - 9 hours 8 min ago
Tom - I read a little bit about the new features in 10g for HTML DB. I'm pretty excited about it if it effectively delivers on these new features (e.g., the whole meta-data driven framework, the Application Builder, Session Management, Templates, Security, etc.) I have been thinking for awhile that there is opportunity in smaller markets to role out rock solid applications with a single Oracle database running mod/plsql. I was considering all the infrastructure services that would need to be built in order to quickly build one or more applications using mod/plsql. With HTML DB, it is looking like I don't have to worry about building these services on my own anymore, and that is great news. A feature that I didn't see that I would also consider very useful... A security framework where you can map Users to one or more Roles (I know this is in Oracle), then further mapping one or more Roles to one or more defined "Transaction codes" (e.g., CreateOrder, ViewOrder, DeleteOrder), and finally associating a group of "HTML Pages" to a transaction. This kind of framework could apply to many information system applications. It would both enforce at the server as well as influence the look of the application (e.g., limited menu options based on Roles.) Anyway, seeing these new HTML DB features along with 10g's ability to transparently scale in its clustering/grid configurations, Oracle 10g with HTML DB could be a solution for any size application. Finally some questions.... 1.) Does Oracle have any plans to build out-of-box applications/products on top of HTML DB? 2.) Do you see any potential confusion from your customers as to whether they should use HTML DB vs. the iAS? I did Java development for a few years and I like using core java a lot. I still struggle to decide whether or not the EJB architecture is that good. I think I've come to the conclusion that if you are building an "information system" application where most of the work is selecting,inserting,updating,and deleting from the database, then a simpler architecture is better, and HTML DB may be onto something here. At the end of the day I would think companies want to get ROBUST applications working QUICKLY, and J2EE is not necessarily the right choice in all cases. A couple more questions... 3.) Will HTML DB be available in the Standard edition? As I said before, I see opportunity in smaller markets for HTML DB as replacements for spreadsheets, MS-Access, and SQLServer applications, however, the cost of Oracle could prohibit this. Does Oracle have any interest in this market? 4.) Maybe I am just paranoid, but I see a strategy here. An alternative to .NET or J2EE, simpler and faster approach (for certain types of "information system apps"), it gets Oracle in the door, and allows them to sell additional licenses (10g grid) as the customers' applications need to scale further. Is this a stretch? Always curious, Dave
Categories: DBA Blogs

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

Hemant K Chitale - 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

Hierarchical Aggregation of Columns

Tom Kyte - Wed, 2022-01-19 21:26
Hello all, Greetings of the new year! Goal - generate an output which can be directly used for pasting hierarchical data into a flat file Link to liveSQL with sample data and scripts - https://livesql.oracle.com/apex/livesql/s/mw9olr17o7dir21jysn5jni1u Expected output <b>-#HDR#- 1 - HEADER1 - HEADER2 - -#Line#- 1 - 1 - DETAIL11 - DETAIL12 ; -#Line#- 1 - 2 - DETAIL21 - DETAIL22 ;</b> Hierarchy example: <b> Purchase Order HEADER - Only 1 Purchase Order LINE - 1-many under 1 HEADER Purchase Order Schedule - 1-many under 1 LINE Purchase Order Distribution - 1-many under 1 SCHEDULE</b> SQL included in LiveSQL currently produces the following: <b>-#HDR#- 1 - HEADER1 - HEADER2 - -#Line#- 1 - 1 - DETAIL11 - DETAIL12 ; -#HDR#- 1 - HEADER1 - HEADER2 - -#Line#- 1 - 2 - DETAIL21 - DETAIL22 ;</b> Idea is to have a single column which shows the header and detail level information. Assumption - single level detail. However, data exists with 3 levels of detail. Request your inputs on generating expected output so that it can be extended to support multiple detail levels. Thank you and stay safe! BR, Prashant ATMAN
Categories: DBA Blogs

How can I register my SMTP service in Oracle Apex?

Tom Kyte - Tue, 2022-01-18 08:46
In the company where I work, we have the services of JD Edwards and Orchestrator Studio, but we are using APEX for fast applications. The problem is that we have not managed to use the JD Edwards SMTP in APEX or an external one to test that the apex_mail.send() function works and so far we have not succeeded, and we watched videos and followed the steps and nothing and the documentation offered by Oracle is not entirely clear even confusing that you have to do, another thing we tried was to create a service in Orchestrator Studio and it works with JS the drawback is that the code is on the client side and not the server and the alternative was to use <b>UTL_HTTP</b> or <b>apex_web_service. make_rest_request()</b> and neither as I read that <b>UTL_HTTP</b> has some limits with apex and <b>apex_web_service.make_rest_request(</b>) blocks the service because the URL is not secure. I have read most of the forum and nothing I can do to make it send APEX mails because it really depends on a database developer to do that and I have been using this tool for a very short time. <b>I do not know if you could help me which are the steps I must follow to be able to send mails with APEX as in Cloud and in my local machine (localhost). </b> Sources consulted: <b>https://docs.oracle.com/en/database/oracle/application-express/21.1/aeapi/Configuring-Oracle-Application-Express-to-Send-Email.html#GUID-596E11FE-9289-4238-A3CA-D33F508E40F7 https://stackoverflow.com/questions/65528885/invoke-a-rest-api-json-in-plsql https://www.youtube.com/watch?v=sI37Be2EZhk https://technology.amis.nl/database/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/</b>
Categories: DBA Blogs

Performance issues in production

Tom Kyte - Tue, 2022-01-18 08:46
Hi Tom, We are supporting a Siebel application having Oracle 11g as backed. Last month we upgraded database to 19C and after that we are facing performance issues on daily basis. When we checked DB reports like AWR and ASH, I have below observations: a) In AWR report random queries are coming on top and there are no consistency so I am assuming issue is not related to a particular SQL. b) In the DB reports we can see top event is "gc buffer busy acquire" most of the time and this event is related to a insert statement. c) All top SQL visible in AWR/ASH reports are having multiple plans. Below is the snap shot for one of the SQL: SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO ---------- ------ ------------------------------ ------------- --------------- ------------ ------------ -------------- 2459 2 12-JAN-22 06.00.34.887 4hdhq06bahcnq 912142910 207 17.761 .0 2460 1 12-JAN-22 06.30.10.663 4hdhq06bahcnq 728991164 293 .024 1,376.6 2460 2 12-JAN-22 06.30.10.670 4hdhq06bahcnq 912142910 107 95.319 2,889,181.3 2461 1 12-JAN-22 07.00.04.996 4hdhq06bahcnq 728991164 439 .035 1,251.3 2461 2 12-JAN-22 07.00.04.990 4hdhq06bahcnq 912142910 149 66.469 2,106,097.7 My question from you guys are: 1. To investigate "gc buffer busy acquire" event what could be the sequence of investigation? 2. Since most of the SQLs coming on top in DB reports are generating multiple plans, Do we have any parameters in 19C which can be responsible for so many plans? Thanks
Categories: DBA Blogs

Library cache lock in wait class Concurrency

Tom Kyte - Tue, 2022-01-18 08:46
In the Production Application we randomly notice a hiccup (slowness) in the database. The AWR shows as `Waiting for event "library cache lock" in wait class "Concurrency"?. The respective SQL statement is one of the frequently running statement (15,000 times per minute) in the application. The SQL is a simple READ on primary key from a table which is cached in a logical memory. The table holds just 40,000 records and doesn?t grow at runtime. During the regular season, the same SQL statement elapsed time would be in milliseconds. But during the hiccup (slowness) time, the same SQL statement takes minutes to return the results. The other important thing to note is that the issue initiates most of the time at either 0th minute, 30th minute or 45th minute of an hour and resolves its own in couple of minutes. However, sometime the issue lasts more than an hour before it resolves its own. We would appreciate if you can help on what could be the reason the SQL performs slower for few minutes randomly.
Categories: DBA Blogs

Core Primary key concepts not mentioned in my Oracle training classes

Tom Kyte - Tue, 2022-01-18 08:46
Good Morning, Is there some underlying rule or DBA agreement that every table should have a primary key? To me it doesn't make logical sense that every table we define needs a primary key. Just recently I came across a primary key made up of two columns with each having about 60% unique values in a 1.5 million row table. The optimizer chooses to perform a full table when both those columns are used in the "Where" clause since the primary key isn't very selective. This table doesn't even have a child table. My assumption is that a primary key is only needed when it has a child table. On the other side, if a large, frequently accessed table doesn't have any child tables and doesn't have any unique column, should we create a column based off the value of a sequence and add a unique index (instead of primary key) on it? I would assume that isn't needed in all cases. An example where it would be needed is in the case where the application inserts a row and then plans to access it within the same session. In summary of questions: - Is there some underlying rule or DBA agreement that every table should have a primary key? - My assumption is that a primary key is only needed when it has a child table? - On the other side, if a large, frequently accessed table doesn't have any child tables and doesn't have a unique column, should we create a column based off the value of a sequence and add a unique index (instead of primary key) on it? Thanks, John
Categories: DBA Blogs

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

Hemant K Chitale - 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

How to properly assign ENABLE_PARALLEL_DML to a profile?

Tom Kyte - Mon, 2022-01-17 14:26
Hi, In a 19.0, I've found a daily update that lasts for more than 30 mins in a 3rd party software. I would like to execute it in parallel. PDML is disabled, and when I try to create a profile for that update, I cannot get the desired plan. I made a small case to show my problem: <code>CREATE TABLE TESTCASE_PDML (keynum number, attribute1 number) PARALLEL 8; --Load some data. INSERT INTO TESTCASE_PDML SELECT ROWNUM, ROWNUM*1000 FROM DUAL CONNECT BY ROWNUM <=100; commit; --Plans for the update: EXPLAIN PLAN FOR UPDATE TESTCASE_PDML SET attribute1 = keynum * 1001 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY()); Plan hash value: 3488641984 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 100 | 2600 | 2 (0)| 00:00:01 | | 1 | UPDATE | TESTCASE_PDML | | | | | | 2 | TABLE ACCESS FULL| TESTCASE_PDML | 100 | 2600 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Note ----- - dynamic statistics used: dynamic sampling (level=3) - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold EXPLAIN PLAN FOR UPDATE /*+ parallel(8) */ TESTCASE_PDML SET attribute1 = keynum * 1001 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY()); Plan hash value: 2008974791 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 100 | 2600 | 4 (0)| 00:00:01 | | | | | 1 | UPDATE | TESTCASE_PDML | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | 100 | 2600 | 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | 100 | 2600 | 4 (0)| 00:00:01 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| TESTCASE_PDML | 100 | 2600 | 4 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=3) - Degree of Parallelism is 8 because of hint <b>- PDML is disabled in current session </b> EXPLAIN PLAN FOR UPDATE /*+ parallel(8) enable_parallel_dml*/ TESTCASE_PDML SET attribute1 = keynum * 1001 SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY()); Plan hash value: 727441780 -------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 100 | 2600 | 4 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 100 | 2600 | 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3...
Categories: DBA Blogs

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

Hemant K Chitale - 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

Why Cpu time so important

Tom Kyte - Thu, 2022-01-13 18:46
hi tom i just finished studying about TKPROF and SQLtrace. when we use TKPROF we can analyze which part of the query is having performance problem. my question is why is cpu time is so important? what does cpu time affect? and what is the best method to reduce cpu time on queries thanks
Categories: DBA Blogs

how does the result cache work

Tom Kyte - Thu, 2022-01-13 18:46
hi tom i have a simple question about result cache. 1.how exactly does the oracle access the result cache? and how does it knew that it is the same sql statement executed last time? 2.when we cache result of the SQL statement into the result cache and re run the query does oracle instantly access the result cache? or does oracle go into the shared sql area first then go to the result cache to get the result thanks
Categories: DBA Blogs

RMAN Backup disappeared from catalog

Tom Kyte - Thu, 2022-01-13 18:46
Hello, I've configured RMAN backup with the following schedulation: - FULL Level 0 , every Sunday at 01:05 AM - INCREMENTAL Level 1, from Monday to Saturday at 01:05 AM - Archive Log Backup every day, every 30 minutes The retention policy is set to Redundancy 2: <code> RMAN> show all; RMAN configuration parameters for database with db_unique_name DATABASE are: CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_DATABASE.f'; # default </code> The Backup script are the following: - Level 0 <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; backup AS COMPRESSED BACKUPSET incremental level 0 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile; backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> - Level 1 <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; backup AS COMPRESSED BACKUPSET incremental level 1 database format '/u01/app/oracle/backup/DATABASE/data_%d_FULL_bks%s_%T_%U.bck' include current controlfile; backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> - Archivelog <code> CONFIGURE RETENTION POLICY TO REDUNDANCY 2; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/DATABASE/autobackup/cf_sp_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 3; run { backup AS COMPRESSED BACKUPSET archivelog all format '/u01/app/oracle/backup/DATABASE/arch_%d_ARCH_bks%s_%T_%U.bck' delete input filesperset 10; crosscheck backup; delete noprompt obsolete; } </code> With Redundancy set to 2, I expected that RMAN keeps 2 FULL Level 0 copies of the database. For example: I took a first FULL Level 0 at 26/12/2021 at 01:05 AM I took a second FULL Level 0 at 02/01/2022 at 01:05 AM On 09/01/2022 I expected that RMAN make a new FULL Level 0 and delete the Level 0 of 26/12/20...
Categories: DBA Blogs

Index-organized tables (IOT) and logical rowid

Tom Kyte - Thu, 2022-01-13 00:26
hello tom i wanted to know what is the perfect scenario to implement a IOT(index organized table) on a database design from what i know so far we need to create an IOT when the table is small(both row and column) and almost all of the column in the table are indexed.is there another scenario where IOT can boost the database performance? also i wanted to know why logical rowid is slower than physical rowid can you explain it pls? thanks
Categories: DBA Blogs

JSON containing date in Oracle Database

Tom Kyte - Thu, 2022-01-13 00:26
I have a SQL query as: <code> With jtable as (:inp) Select * from table t,jtable where t.DATE = jtable.DATE</code> Here <code>inp </code>is a dataframe which I take care of by converting to json and eveything and it works perfectly fine except when it has to deal with datetime object. So if I have dataframe with datetime object and I push it to json I get it in posix and hence the date in oracle which is still datetime wont be equal to it. I tried changing it to make string TO_DATE(%y-%m-%d,YYYY-MM-DD) when I see datetime in dataframe but its non numeric and hence binding doesnot work. Is there any way to bypass it so that I dont have to change my sql queries (sql query can be changed to TO_DATE and I can pass datettime simply as string) <code>jason-data == [{"TEST_DATE"16163612361}]</code> when the dataframe has TEST_DATE = pd.timestamp(2019,6,28) or something. Is there any way to tackle it?
Categories: DBA Blogs

Is Data Hub the new Staging environment?

Kubilay Çilkara - Wed, 2022-01-12 05:39

"A data hub is an architectural pattern that enables the mediation, sharing, and governance of data flowing from points of production in the enterprise to points of consumption in the enterprise” Ted Friedman, datanami.com

Aren't relational databases, data marts, data warehouses and more recently data lakes not enough? Why is there a need to come up with yet another strategy and paradigm for database management?

To begin answering the above questions, I suggest we start looking at the history of data management and figure out how data architecture developed a new architectural pattern like Data Hub. After all, history is important as a famous quote from Martin Luther King Jr. says "We are not makers of history. We are made by history"


Relational architecture




A few decades ago, businesses began using relational databases and data warehouses to store their interests in a consistent and coherent recordThe relational architecture still keeps the clocks ticking with its well understood architectural structures and relational data models. It is a sound and consistent architectural pattern based on mathematical theory which will continue serving data workloads. The relational architecture serves brilliantly the very specific use case of transactional workloads, where the data semantics are defined in advance before any data is stored in any system. If implemented correctly the relational model can become a hub of information that is centralised and easy to query. It is hard to see that the relational architecture could be the reason to cause a paradigm shift into something like a data hub. Most likely is something else. Could it be cloud computing?


When the cloud came, it changed everything. The Cloud brought along an unfathomable proliferation of apps and an incredible amount of raw and unorganised data. With this outlandish amount of disorganised data in the pipes, the suitability of the relational architecture for data storage had to be re-examined and reviewed.  Faced with a data deluge, the relational architecture couldn't scale quickly and couldn't serve the analytical workloads and the needs of the business in a reasonable time. Put simply, there was no time to understand and model data. The sheer weight of the number of unorganised chunks of data coming from the cloud, structured and unstructured, at high speeds, propelled the engineers to look for a new architectural pattern.


Data Lake 



In a data lake, the structured and unstructured data chunks are stored raw and no questions are asked. Data is not organised and is not kept in well-understood data models anymore and it can be stored infinitely and in abundance. Moreover, very conveniently the process of understanding and creating a data model in a data lake is deferred to the future, which is a process known as schema-on-read. We have to admit, the data lake is the new monolith where data is stored only, a mega data dump yard indeed. This new architectural pattern also brought with it the massively parallel (MPP) processing data platforms, tools and disciplines, such as machine learning, which became the standard methods for extracting business insights from the absurd amounts of data found in a data lake. Unfortunately, the havoc and the unaccounted amounts of unknown data living in a data lake didn't help with understanding data and made the life of engineers difficult. Does a data lake have any redundant data or bad data? Are there complex data silos living in a data lake? These are still hard questions to answer and the chaotic data lakes looked like are missing a mediator. 


Data Hub



Could the mediator be a "data hub"? It is an architectural pattern based on the hub and spoke architecture. A data hub, which itself is another database system, integrates and stores data for mediation, most likely temporarily, from diverse and complex transactional and analytical workloads. Once the data is stored, the data hub becomes the tool to harmonise and enrich data and then radiate data to the AI, Machine Learning and other enterprise insights and reporting systems via its spokes.

What's more, while sharing the data in its spokes, the data hub can also help engineers to govern and catalogue the data landscape of the enterprise. The separation of data via mediation from the source and target database systems inside a data hub also offers engineers the flexibility to operate and govern independently of the source and target systems. But this reminds me of something.

If the data hub paradigm is a mediator presented to understand, organise, correct, enrich and put an order in the data chaos inside data lake monoliths, doesn't the data hub look similar to the data management practice engineers have been doing for decades in data warehouses and we all know as "Staging"? Is data hub the evolved version of staging?

Conclusion

The most difficult thing in anything you do is to persuade yourself that there is some value in doing it. It is the same when adopting a new architectural pattern as a data management solution. You have to understand where the change is coming from and see the value before you embark on using it. The data upsurge brought by the internet and cloud computing forced wobbly changes in data architecture and data storage solutions. The data hub is a new architectural pattern in data management introduced to mediate the chaos of fast-flowing data tsunamis around us and we hope it will help us tally everything up.


Categories: DBA Blogs

How many times a package is called

Tom Kyte - Tue, 2022-01-11 11:26
Tom The client wants to find out how many times each package is called in a day to make decisions on database server capacity. a) Is there a way to find out how many times a package has been called ? b) Is there any database server capacity metric associated with how many times a package is called ? Someone suggested to sum up the execution counts for each package from v$sqlarea and divide by the number of days the database has been up. Is this the right way ? Thanks
Categories: DBA Blogs

SQL statement has different plan executed in procedure vs SQL*Plus Same database

Tom Kyte - Tue, 2022-01-11 11:26
I have a bit of an unexplainable situation. We have a procedure which executes a query and it consistently chooses the worst index. When I execute it in SQL*Plus or SQL Developer it chooses the correct index. The only way I can get it to work fast in the procedure is to add hints. The statistics are up to date no change in optimizer parameters. I ran a trace and I see in the trace it uses "ALL_ROWS" as that is the database parameter anyway. The explain plan in the trace show it uses the wrong index which is least selective. <code> SELECT EA.EVAS_ID EVASID, EA.EV_ID EVID, EA.CEVT_ID CEVID, EA.EV_ID_SOURCE EVSOURCEID, EA.CEVT_ID_SOURCE CEVTSOURCEID, EV.EVENT_START_DATETIME STARTDATE FROM MR_EVENTS EV, MR_EVENT_ASSOCIATIONS EA WHERE EV.EPI_ID = :B2 AND EV.ET_ID = :B1 AND EV.EV_ID = EA.EV_ID UNION ALL SELECT EA.EVAS_ID EVASID, EA.EV_ID EVID, EA.CEVT_ID CEVID, EA.EV_ID_SOURCE EVSOURCEID, EA.CEVT_ID_SOURCE CEVTSOURCEID, CE.SENT_DATETIME STARTDATE FROM MR_CONTACT_EVENTS CE, MR_EVENT_ASSOCIATIONS EA WHERE CE.EPI_ID = :B2 AND CE.ET_ID = :B1 AND CE.CEVT_ID = EA.CEVT_ID UNION ALL SELECT NULL, NULL, CE2.CEVT_ID CEVID, CE2.EV_ID_SOURCE EVSOURCEID, CE2.CEVT_ID_SOURCE CEVTSOURCEID, CE2.SENT_DATETIME STARTDATE FROM MR_CONTACT_EVENTS CE2 WHERE CE2.EPI_ID = :B2 AND CE2.ET_ID = :B1 AND NOT EXISTS ( SELECT 1 FROM MR_EVENT_ASSOCIATIONS EA WHERE CE2.CEVT_ID = EA.CEVT_ID) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.01 0.01 0 0 0 0 Execute 16 0.07 0.07 0 0 0 0 Fetch 16 236.32 253.81 1130634 2344502 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 35 236.41 253.90 1130634 2344502 0 0 Misses in library cache during parse: 2 Optimizer mode: ALL_ROWS Parsing user id: 240 (recursive depth: 1) Number of plan statistics captured: 3 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 UNION-ALL (cr=384508 pr=172552 pw=0 time=45178066 us starts=1) 0 0 0 NESTED LOOPS (cr=3 pr=1 pw=0 time=1442 us starts=1 cost=7 size=45 card=1) 0 0 0 NESTED LOOPS (cr=3 pr=1 pw=0 time=1436 us starts=1 cost=7 size=45 card=1) 0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED MR_EVENTS (cr=3 pr=1 pw=0 time=1433 us starts=1 cost=4 size=23 card=1) 0 0 0 INDEX RANGE SCAN MR_EV_I5 (cr=3 pr=1 pw=0 time=1428 us starts=1 cost=3 size=0 card=1)(object id 40477) 0 0 0 INDEX RANGE SCAN MR_EVAS_EV_FK_I (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=1)(object id 40487) 0 0 0 TABLE ACCESS BY INDEX ROWID MR_EVENT_ASSOCIATIONS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=22 card=1) 0 0 0 NESTED LOOPS (cr=192252 pr=86276 pw=0 time=29596732 us starts=1 cost=7 size=45 card=1) 0 0 0 NESTED LOOPS (cr=192252 pr=86276 pw=0 time=29596728 us starts=1 cost=7 size=45 card=1) 0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED MR_CONTACT_EVENTS (cr=192252 pr=86276 pw=0 time=29596726 us starts=1 cost=4 size=23 card=1) 0 1350887 4052661 INDEX RANGE SCAN MR_CEVT_ET_FK_I (cr=2709 pr=0 pw=0 time=654095 us starts=1 cost=3 size=0 card=5)(object id 40392) 0 0 0 INDEX RANGE SCAN MR_EVAS_CEVT_FK_I (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=1)(object id ...
Categories: DBA Blogs

MySQL Btree index

Tom Kyte - Mon, 2022-01-10 17:06
Does MySql Btree index store NULL value?I execute a sql like "explain select * from t1 where id is null"?most id of table t1 is NULL, I think it should use full table scan instead of index scan. But MySql use index. why?
Categories: DBA Blogs

Segment Advisor compression recommendation

Tom Kyte - Sun, 2022-01-09 22:46
How does the segment advisor (dbms_space.asa_recommendations) determine that table compression is recommended and why does it only recommend to "compress for oltp" when other compression options are available?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs