Oracle Control File Operations

Oracle Control File Operations
The control file is the link connecting the instance and database. The structure information of the database is recorded. The control file is a binary file. The status of the current database is recorded. There can be multiple control files. In the parameter file, you can use the control_files parameter to specify the location. When you want to write data to the control file, the data is synchronized to multiple control files. Only the first control file is read. If any of the control files is corrupted, the instance will be abort. The control file can only be associated with one database. The control file is created when the database is created. You can also re-create it when it is started to the nomount status. View v $ controlfile related to control file: information about all control files in the current instance. V $ controlfile_record_section: All section information in the control file. View the information of the current control file:

 select * from v$controlfile; select * from v$parameter where name like '%control%'; show parameter control; select * from v$controlfile_record_section;

 

Use commands to modify the control file path

alter system set control_files='/u01/app/oracle/oradata/saigon/control01.ctl',                               '/u01/app/oracle/oradata/saigon/control02.ctl',                               '/u01/app/oracle/oradata/saigon/control03.ctl' scope=spfile;

 

Use spfile to increase the number of control files or modify the control file path (1) Use v $ controlfile to obtain the name and location of the existing control file. (2) modify the spfile and use alter system set control_files = 'd: \ DISK3 \ CONTROL01.CTL ', 'd: \ DISK6 \ CONTROL02.CTL', 'd: \ DISK9 \ CONTROL03.CTL 'SCOPE = SPFIL; (3) shut down the database normally (shutdown, shutdown immediate ). (4) use the copy command of the operating system to copy the existing control file to the specified location. (5) restart the oracle database (startup) (6) use the data field v $ controlfile to verify that the new control file name is correct. (7) If any error occurs, repeat the above operation: If there is no error, delete the original control file. use pfile to increase the number of control files or modify the Control File Path 1. close the database cleanly. 2. Copy and rename a new control file on the operating system. 3. Add the previous parameter file to the control_files parameter in initSID. ora. 4. Start the database. Back up control files during oracle operation

 1.alter database backup controlfile to 'D:\aaa.bak'; 2.alter database backup controlfile to trace;  

Translate the control file into a script for creating the control file. The path is in the directory of the user warning file (you can view it through show parameter user_dump;). The suffix is trc. Or find the following method:

SELECT d.VALUE|| '/'|| LOWER (RTRIM (i.INSTANCE, CHR (0)))|| '_ora_'|| p.spid|| '.trc' trace_file_nameFROM (SELECT p.spidFROM v$mystat m, v$session s, v$process pWHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,(SELECT t.INSTANCEFROM v$thread t, v$parameter vWHERE v.NAME = 'thread'AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,(SELECT VALUEFROM v$parameterWHERE NAME = 'user_dump_dest') d/3.run{backup current controlfile format'/backup1/controlfile_%d_%s.ctl';}

 

Control File restore resetlog method to open data as long as you have the current log file, you can achieve full recovery. Whether to use resetlogs to open the backup depends on whether the backup control file is used. If you are using a backup control file, you need to use the resetlogs method to open the database. If you have the current control file or restore it by recreating the control file, you do not need to use the resetlogs method to open it.

RMAN> restore controlfile to '/tmp/control01.ctl' from 'C-3152029224-20051221-00 '------- run {startup force nomount; set dbid = restore controlfile from autobackup; alter database mount; recover database; alter database open resetlogs;} ------- restore control file open in Normal Mode 1. startup nomount; 2. RMAN> restore controlfile from autobackup; 3. alter database mount; 4. SQL> alter database backup controlfile to trace;

 

5. Find the trace file

SELECT d.VALUE|| '/'|| LOWER (RTRIM (i.INSTANCE, CHR (0)))|| '_ora_'|| p.spid|| '.trc' trace_file_nameFROM (SELECT p.spidFROM v$mystat m, v$session s, v$process pWHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,(SELECT t.INSTANCEFROM v$thread t, v$parameter vWHERE v.NAME = 'thread'AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,(SELECT VALUEFROM v$parameterWHERE NAME = 'user_dump_dest') d/

 

6. Rebuild control file open the database reconstruction control file as follows: A. determine the control file, redo the log file, and the data file location (A is required in some cases );

select * from v$controlfile;select * from v$logfile;select file#,name from v$datafile;

 

B. Generate a script that can recreate the control file.

alter database backup controlfile to trace;

 

C. Obtain the script of the trc file location.

SELECT    d.VALUE       || '/'       || LOWER (RTRIM (i.INSTANCE, CHR (0)))       || '_ora_'       || p.spid       || '.trc' trace_file_name  FROM (SELECT p.spid          FROM v$mystat m, v$session s, v$process p         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,       (SELECT t.INSTANCE          FROM v$thread t, v$parameter v         WHERE v.NAME = 'thread'           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,       (SELECT VALUE          FROM v$parameter         WHERE NAME = 'user_dump_dest') d/

 

D. view the trc file content.

[Oracle @ orcl admin] $ cat/home/oracle/admin/orcl/udump/orcl_ora_10202.trc/home/oracle/admin/orcl/udump/orcl_ora_10202.trcOracle Database 10g Enterprise Edition Release 10.2.0.1.0- productionWith the Partitioning, OLAP and Data Mining optionsORACLE_HOME =/home/oracle/oracleSystem name: LinuxNode name: orclRelease: BRL. 18-164. el5Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009 Machine: i686Instance name: OrclRedo thread mounted by this instance: 1 Oracle process number: 15 Unix process pid: 10202, image: oracle @ orcl (TNS V1-V3) *** service name :( SYS $ USERS) 12:14:16. 466 *** session id: (159.3) 12:14:16. 466 *** 12:14:16. 466 -- The following are current System-scope REDO Log Archival related -- parameters and can be encoded in the database initialization file. ---- LOG_ARCH IVE_DEST = ''-- LOG_ARCHIVE_DUPLEX_DEST ='' ---- LOG_ARCHIVE_FORMAT = % t _ % s _ % r. dbf ---- DB_UNIQUE_NAME = "orcl" ---- LOG_ARCHIVE_CONFIG = 'send, RECEIVE, NODG_CONFIG '-- LOG_ARCHIVE_MAX_PROCESSES = 2 -- STANDBY_FILE_MANAGEMENT = MANUAL -- STANDBY_ARCHIVE_DEST =? /Dbs/arch -- FAL_CLIENT = ''-- FAL_SERVER ='' ---- LOG_ARCHIVE_DEST_10 = 'location = taobao' -- token = 'optional REOPEN = 300 nodelay' -- Signature = 'arch noaffrem NOEXPEDITE NOVERIFY SYNC '-- region = 'register noalternate nodependency' -- LOG_ARCHIVE_DEST_10 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME '-- region = 'valid _ FOR = (PRIMARY_ROLE, ONLINE_LOGFILES) '-- LOG_ARCHIVE_DEST_STATE_10 = 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 shoshould be used only if -- the current versions of all online logs are available. the second -- set opens the database with the RESETLOGS option and shoshould 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 performancestartup nomountcreate controlfile reuse database "ORCL" noresetlogs noarchivelog maxlogfiles 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 logfile group 1 '/home/oracle/oradata//redo01.log 'size 50 M, GROUP 2'/home/oracle/oradata/orcl/redo02.log 'SIZE 50 M, GROUP 3 '/home/oracle/oradata/orcl/redo03.log' SIZE 50 M -- standby logfiledatafile '/home/oracle/oradata/orcl/system01.dbf ', '/home/oracle/oradata/orcl/undotbs01.dbf','/home/oracle/oradata/orcl/sysaux01.dbf', '/home/oracle/oradata/orcl/users01.dbf ', '/home/oracle/oradata/orcl/example01.dbf' character set WE8ISO8859P1; -- 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 '/home/oracle/flash_recovery_area/ORCL/archivelog/2012_05_27/o1_mf_1_1 _ % u _. arc'; -- alter database register logfile '/home/oracle/flash_recovery_area/ORCL/archivelog/2012_05_27/o1_mf_1_1 _ % u _. arc'; -- rediscovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. recover database -- 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 '/home/oracle/oradata/orcl/temp01.dbf' SIZE 22020096 reuse autoextend on next 655360 MAXSIZE 32767 M; -- End of tempfile additions. ---- 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 define performancestartup nomountcreate controlfile reuse database "ORCL" RESETLOGS limit MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 logfile group 1 '/home/oracle/oradata /redo01.log 'size 50 M, GROUP 2'/home/oracle/oradata/orcl/redo02.log 'SIZE 50 M, GROUP 3 '/home/oracle/oradata/orcl/redo03.log' SIZE 50 M -- standby logfiledatafile '/home/oracle/oradata/orcl/system01.dbf ', '/home/oracle/oradata/orcl/undotbs01.dbf','/home/oracle/oradata/orcl/sysaux01.dbf', '/home/oracle/oradata/orcl/users01.dbf ', '/home/oracle/oradata/orcl/example01.dbf' character set WE8ISO8859P1; -- 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 '/home/oracle/flash_recovery_area/ORCL/archivelog/2012_05_27/o1_mf_1_1 _ % u _. arc'; -- alter database register logfile '/home/oracle/flash_recovery_area/ORCL/archivelog/2012_05_27/o1_mf_1_1 _ % u _. arc'; -- rediscovery 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 '/home/oracle/oradata/orcl/temp01.dbf' SIZE 22020096 reuse autoextend on next 655360 MAXSIZE 32767 M; -- End of tempfile additions. e. Obtain the script for creating the control file. Depending on the database status, you can use RESETLOGS (without redo logs)/NORESETLOGS (with redo logs) to recreate the control file, the following is NORESETLOGS. startup nomountcreate controlfile reuse database "ORCL" using noarchivelog maxlogfiles 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 logfile group 1'/home/oracle/oradata/orcl/comment 'size 50 M, GROUP 2'/home/oracle/oradata/orcl/redo02.log 'SIZE 50 M, GROUP 3 '/home/oracle/oradata/orcl/redo03.log' SIZE 50 M -- standby logfiledatafile '/home/oracle/oradata/orcl/system01.dbf ', '/home/oracle/oradata/orcl/undotbs01.dbf','/home/oracle/oradata/orcl/sysaux01.dbf', '/home/oracle/oradata/orcl/users01.dbf ', '/home/oracle/oradata/orcl/example01.dbf' character set WE8ISO8859P1; recover databasealter database open; alter tablespace temp add tempfile '/home/oracle/oradata/orcl/temp01.dbf' SIZE 22020096 reuse autoextend on next 655360 MAXSIZE 32767 M; F, run the script to recreate the control file SQL> set echo onSQL> STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/home/ oradata/orcl/redo01.log 'size 50 M, GROUP 2'/home/oracle/oradata/orcl/redo02.log 'SIZE 50 M, GROUP 3 '/home/oracle/oradata/orcl/redo03.log' SIZE 50 M -- standby logfiledatafile '/home/oracle/oradata/orcl/system01.dbf ', '/home/oracle/oradata/orcl/undotbs01.dbf','/home/oracle/oradata/orcl/sysaux01.dbf', '/home/oracle/oradata/orcl/users01.dbf ', '/home/oracle/oradata/orcl/example01.dbf' character set WE8ISO8859P1; recover databasealter database open; alter tablespace temp add tempfile '/home/oracle/oradata/orcl/temp01.dbf' SIZE 22020096 reuse autoextend on next 655360 MAXSIZE 32767 M; ORACLE instance started. total System Global Area 167772160 bytesFixed Size 1218316 bytesVariable Size 71305460 bytesDatabase Buffers 92274688 bytesRedo Buffers 2973696 bytesSQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Control file created. SQL> Media recovery complete. SQL> Database altered. SQL> 2 Tablespace altered.