oracle 数据库 controlfile to trace 12.1.0.2

矫情吗；* 2023-03-01 03:27 8阅读 0赞

SQL> set lines 200;
    set pages 200;
    
    SQL> select * from v$version;
    
    BANNER										     CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production		  0
    PL/SQL Release 12.1.0.2.0 - Production							                      0
    CORE	12.1.0.2.0	Production								                          0
    TNS for Linux: Version 12.1.0.2.0 - Production						                  0
    NLSRTL Version 12.1.0.2.0 - Production							                      0
    
    SQL>  alter database backup controlfile  to trace as '/home/oracle/con.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="orclp"
    --
    -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
    -- LOG_ARCHIVE_CONFIG='DG_CONFIG=("orcls1")'
    -- LOG_ARCHIVE_MAX_PROCESSES=4
    -- STANDBY_FILE_MANAGEMENT=auto
    -- STANDBY_ARCHIVE_DEST=?/dbs/arch
    -- FAL_CLIENT=orclp
    -- FAL_SERVER=orcls1
    --
    -- LOG_ARCHIVE_DEST_2='SERVICE=tns_orcls1'
    -- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
    -- LOG_ARCHIVE_DEST_2='LGWR AFFIRM NOVERIFY ASYNC=0'
    -- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
    -- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
    -- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=orcls1'
    -- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
    -- LOG_ARCHIVE_DEST_STATE_2=ENABLE
    --
    -- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog'
    -- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
    -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
    -- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
    -- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
    -- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=orclp'
    -- 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 1024
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
    -- STANDBY LOGFILE
    --   GROUP 4 '/u01/app/oracle/oradata/orcl/standby_redo01.log'  SIZE 200M BLOCKSIZE 512,
    --   GROUP 5 '/u01/app/oracle/oradata/orcl/standby_redo02.log'  SIZE 200M BLOCKSIZE 512,
    --   GROUP 6 '/u01/app/oracle/oradata/orcl/standby_redo03.log'  SIZE 200M BLOCKSIZE 512,
    --   GROUP 7 '/u01/app/oracle/oradata/orcl/standby_redo04.log'  SIZE 200M BLOCKSIZE 512,
    --   GROUP 8 '/u01/app/oracle/oradata/orcl/standby_redo05.log'  SIZE 200M BLOCKSIZE 512
    DATAFILE
      '/u01/app/oracle/oradata/orcl/system01.dbf',
      '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
      '/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',
      '/u01/app/oracle/oradata/orcl/users01.dbf',
      '/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/system01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/pdb1_users01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb2/system01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb2/sysaux01.dbf'
    CHARACTER SET AL32UTF8
    ;
    
    -- 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 '/u01/app/oracle/archivelog/1_1_852269927.dbf';
    -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archivelog/1_1_1046278184.dbf';
    -- Recovery is required if any of the datafiles are restored backups,
    -- or if the last shutdown was not normal or immediate.
    RECOVER DATABASE
    
    -- Set Database Guard and/or Supplemental Logging
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX, FOREIGN KEY, ALL) COLUMNS;
    -- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
         SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB$SEED;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdbseed/pdbseed_temp012020-07-20_04-50-31-PM.dbf'
         SIZE 104857600  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB1;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdb1/temp012020-07-20_04-50-31-PM.dbf'
         SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB2;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdb2/pdbseed_temp012020-07-20_04-50-31-PM.dbf'
         SIZE 20971520  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 0 '/u01/app/oracle/oradata/orcl/standby_redo01.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo02.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo03.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo04.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo05.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 1024
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
      GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
    -- STANDBY LOGFILE
    DATAFILE
      '/u01/app/oracle/oradata/orcl/system01.dbf',
      '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
      '/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf',
      '/u01/app/oracle/oradata/orcl/users01.dbf',
      '/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/system01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb1/pdb1_users01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb2/system01.dbf',
      '/u01/app/oracle/oradata/orcl/pdb2/sysaux01.dbf'
    CHARACTER SET AL32UTF8
    ;
    
    -- 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 '/u01/app/oracle/archivelog/1_1_852269927.dbf';
    -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archivelog/1_1_1046278184.dbf';
    -- 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
    
    -- Set Database Guard and/or Supplemental Logging
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX, FOREIGN KEY, ALL) COLUMNS;
    -- Create log files for threads other than thread one.
    ;
    
    -- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf'
         SIZE 206569472  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB$SEED;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdbseed/pdbseed_temp012020-07-20_04-50-31-PM.dbf'
         SIZE 104857600  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB1;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdb1/temp012020-07-20_04-50-31-PM.dbf'
         SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    ALTER SESSION SET CONTAINER = PDB2;
    ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/pdb2/pdbseed_temp012020-07-20_04-50-31-PM.dbf'
         SIZE 20971520  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 0 '/u01/app/oracle/oradata/orcl/standby_redo01.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo02.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo03.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo04.log'
     SIZE 200M BLOCKSIZE 512 REUSE;
    ALTER DATABASE ADD STANDBY LOGFILE THREAD 0 '/u01/app/oracle/oradata/orcl/standby_redo05.log'
     SIZE 200M BLOCKSIZE 512 REUSE;