Home » RDBMS Server » Server Administration » No SCN in controlfile after BACKUP TO TRACE (Windows Server 2019 Oracle 19c)
No SCN in controlfile after BACKUP TO TRACE [message #687608] Thu, 13 April 2023 06:25 Go to next message
Andrey_R
Messages: 439
Registered: January 2012
Location: Israel
Senior Member
Hi DBAs,

I've lived my DBA life so far knowing that the Control file contains among the rest the SCN of the database,
So it can manage the Backup & Recovery mechanism as well as the recovery during any startup & Recovery operations.

Nevertheless, as I performed a backup of the controlfile to a readable format, I examined the contents and did not find any SCN information.

Here is the command I used to do the backup with RMAN

RMAN> alter database backup controlfile to trace as 'F:\ORACLE_BACKUP\\controlfile.txt';

using target database control file instead of recovery catalog
Statement processed
And here are the contents of the file:

-- 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=ARC%S_%R.%T
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("orcl_stby")'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=AUTO
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='LGWR NOAFFIRM NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER'
-- LOG_ARCHIVE_DEST_2='NOALTERNATE'
-- LOG_ARCHIVE_DEST_2='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED DB_UNIQUE_NAME=orcl_stby'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER'
-- 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 "ORCL" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 10 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_10_KYFLNN8T_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 11 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_11_KYFLNR3B_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 12 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_12_KYFLNW48_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 13 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_13_KYFLNZ8B_.LOG'  SIZE 200M BLOCKSIZE 512
DATAFILE
  'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'E:\ORACLE\ORADATA\ORCL\USERS01.DBF'
CHARACTER SET AL32UTF8
;

-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('F:\ORACLE_BACKUP\SNP_CTL_ORCL.ORA');
-- Configure RMAN configuration record 1
-- Replace * with correct password.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''F:\ORACLE_BACKUP/%U''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''F:\ORACLE_BACKUP\SNP_CTL_ORCL.ORA''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_%F_%T.ORA''');
-- 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 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\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;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- 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 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_10_KYFLNN8T_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_11_KYFLNR3B_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_12_KYFLNW48_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_13_KYFLNZ8B_.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 "ORCL" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 10 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_10_KYFLNN8T_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 11 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_11_KYFLNR3B_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 12 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_12_KYFLNW48_.LOG'  SIZE 200M BLOCKSIZE 512,
--   GROUP 13 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_13_KYFLNZ8B_.LOG'  SIZE 200M BLOCKSIZE 512
DATAFILE
  'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF',
  'E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF',
  'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF',
  'E:\ORACLE\ORADATA\ORCL\USERS01.DBF'
CHARACTER SET AL32UTF8
;

-- Configure snapshot controlfile filename
EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('F:\ORACLE_BACKUP\SNP_CTL_ORCL.ORA');
-- Configure RMAN configuration record 1
-- Replace * with correct password.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''F:\ORACLE_BACKUP/%U''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('SNAPSHOT CONTROLFILE NAME','TO ''F:\ORACLE_BACKUP\SNP_CTL_ORCL.ORA''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''F:\ORACLE_BACKUP\CTL_AUTOBACKUP_DBID_%F_%T.ORA''');
-- 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 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'E:\ORACLE\RECOVERY_AREA\ORCL\ARCHIVELOG\2023_04_13\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;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- 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 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_10_KYFLNN8T_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_11_KYFLNR3B_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_12_KYFLNW48_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 'E:\ORACLE\RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_13_KYFLNZ8B_.LOG'
 SIZE 200M BLOCKSIZE 512 REUSE;


As I tried to look up some official docs in https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-control-files.html#GUID-5F6F9382-6921-4992-9789-E63B18E99C5F

I could not find a specific declaration saying that it should be included,
however, in many blogs, forums and websites it does say so, as well as in my memory of errors about controlfile and data file/log file header SCN missmatches..

I wonder if maybe the "backup controlfile to trace" command creates some specific synthax format that "gets" the SCN from file headers or some other "default" to work with.

Would be great if anyone can shed some light on it, or refer me to the right docs that can explain what am I missing here - why don't I have the SCN in my backed up controlfile ?


Many thanks in advance
Andrey R
Re: No SCN in controlfile after BACKUP TO TRACE [message #687610 is a reply to message #687608] Fri, 14 April 2023 04:08 Go to previous messageGo to next message
Alien
Messages: 289
Registered: June 1999
Senior Member
Hi Andrey,

there is no reason to include the SCN in the trace file. It would be outdated the moment the command finished.
If the database is shutdown cleanly, there will have been a checkpoint and the datafiles will have the correct scn. This can then be used for recreating the controlfile.
In most cases however, the create controlfile will be followed by a recovery. The recovery process will roll the archive and online redo logs forward as far as possible. This will also update the scn in the new controlfile.

Regards,

Arian
Re: No SCN in controlfile after BACKUP TO TRACE [message #687615 is a reply to message #687610] Fri, 14 April 2023 07:18 Go to previous messageGo to next message
Andrey_R
Messages: 439
Registered: January 2012
Location: Israel
Senior Member
Alien wrote on Fri, 14 April 2023 12:08
Hi Andrey,

there is no reason to include the SCN in the trace file. It would be outdated the moment the command finished.
If the database is shutdown cleanly, there will have been a checkpoint and the datafiles will have the correct scn. This can then be used for recreating the controlfile.
In most cases however, the create controlfile will be followed by a recovery. The recovery process will roll the archive and online redo logs forward as far as possible. This will also update the scn in the new controlfile.

Regards,

Arian
Thanks for the reply, and for the explanation.

So the controlfile itself ( binary file ) does contain the SCN, and updated in real-time for every checkpoint,
however the trace controlfile is just used to create a controlfile with maximum SCN possible (optionally) rolled forward, per the SCN in the datafiles headers

Did I understand correctly ?

I think the information from Oracle official forum implies the same ( although dates back to Oracle 10g days.. )

https://forums.oracle.com/ords/apexds/post/database-recovery-1778

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-DATABASE.html

https://forums.oracle.com/ords/apexds/post/checkpoint-scn-in-redolog-and-controlfile-0182

Regards,
Andrey R

[Updated on: Fri, 14 April 2023 07:19]

Report message to a moderator

Re: No SCN in controlfile after BACKUP TO TRACE [message #687616 is a reply to message #687615] Fri, 14 April 2023 10:11 Go to previous message
Alien
Messages: 289
Registered: June 1999
Senior Member
Hi,

that's correct.

Regards,

Arian
Previous Topic: system ORACLE ID all objects were removed
Next Topic: disconnect the schema
Goto Forum:
  


Current Time: Sun Feb 25 12:24:21 CST 2024