[Oracle11g, 9] control file, oracle11g Control

[Oracle11g, 9] control file, oracle11g Control
I. Role of the control file: 1. records the physical status of the database. 2. Maintain database consistency. The control file records the database system scn number, the data file scn number, and the START scn number in the data file header. If the three scn numbers are consistent, the database can be started. If they are inconsistent, they must be restored. 3. Define the location and number of control files in the parameter file. The minimum number of control files is 1, and the maximum number is 8. Multiple files are the relationship between images. Define the control file *. control_files = '/opt/oracle/oradata/orcl/control01.ctl', '/opt/oracle/oradata/orcl/control02.ctl ', '/opt/oracle/oradata/orcl/control03.ctl' 4. the control file is a binary file that generally does not exceed 100 MB. 5. The database starts to use the control file from the mount stage. 6. The control file can only connect to one database. 7. Restore if it is lost
II. the control file contains the following content: Database Name and identifier (db id) the location of the name data file of the tablespace created by the database and the location of the online redo log file, and the name of the sequence number of the current online redo log file. the start and end of the checkpoint information rollback segment of the online redo log archive information backup information
III. dump control file: because the control file is a binary file, you cannot open it directly. However, you can use the following commands described in the previous chapter to dump the control file for easy viewing: alter session set events 'immediate trace name controlf level 12'; The following is a dump test from Oracle Database 11g: SQL> alter session set events 'immediate trace name controlf level 12 '; session altered. # Back up the control file to the trace file SQL> alter database backup controlfile to trace;
Database altered.
SQL> select value from v $ diag_info where name = 'default Trace file'; VALUE limit/u01/app/oracle/diag/rdbms/orcl/trace/orcl_ora_limit 9.trc
Note: Starting from 11 GB, you can get the name of the current session dump file through v $ diag_info.

4. control File Check Database Consistency: 1. the control file records the database system scn number, the data file scn number, and the START scn number in the data file header column. If the three scn numbers are consistent, the database can be started. If they are inconsistent, they must be restored. # System scn SQL> select checkpoint_change # from v $ database;
CHECKPOINT_CHANGE # ---------------- 469717 # SQL> select checkpoint_change # from v $ datafile;
CHECKPOINT_CHANGE # ------------------ 469717 469717 469717
Start scn SQL> select checkpoint_change # from v $ datafile_header;
CHECKPOINT_CHANGE # ------------------ 469717 469717 469717

2. Conversion between scn numbers and time: # global checkpoint SQL> alter system checkpoint;
System altered. # create a function SQL> create or replace function scn_to_timestamp (query_scn in NUMBER) return TIMESTAMPIS EXTERNALNAME "ktfexscntot" WITH CONTEXT PARAMETERS (context, query_scn OCINUMBER, RETURN) LIBRARY DBMS_TRAN_LIB; /2 3 4 5 6 7 8 9
Function created. # system scnSQL> select checkpoint_change # from v $ database;
CHECKPOINT_CHANGE # ------------------ 473363 # converting scn to time SQL> select scn_to_timestamp (473363) from dual;
SCN_TO_TIMESTAMP (473363) ----------------------------------------------------------------------------- 16-JUN-14 06.16.14.000000000 AM
# Convert time to scnSQL> select timestamp_to_scn ('16-JUN-14 06.16.14.000000000 AM ') from dual;
TIMESTAMP_TO_SCN ('16-JUN-1406.16.14.000000000AM ') ------------------------------------------------- 473362

5. Control File diversification (increase or decrease the volume of control file data) 1. Planning Principles: Reuse multiple examples, define multiple control files, and place them on different disks.
2. manage the number and location of Control Files: spfile or pfile allows you to manage the number and location of control files. spfile steps: modify the spfile parameter control_file to disable Database Consistency. Increase or decrease the control file. Use spfile to start the database verification result.

Example: modify the number of control files

-- Reduce the control file SQL> alter system set control_files = '/opt/oracle/oradata/orcl/control01.ctl' scope = spfile;
System altered. or

-- Add a control file: SQL> alter system set control_files = '/opt/oracle/oradata/orcl/control01.ctl', '/opt/oracle/oradata/orcl/control02.ctl ', '/opt/oracle/oradata/orcl/control03.ctl' scope = spfile;
System altered.

-- Close the database

SQL> shutdown immediate
-- Copy the control file to the Add directory or delete the control file [oracle @ master disk3] $ cp/u01/app/oracle/oradata/prod/disk3/control01.ctl/u01/app/oracle/ oradata/prod/disk4/control01.ctl

-- Start the database

SQL> startup ORACLE instance started.
Total System Global Area 418484224 bytesFixed Size 1336932 bytesVariable Size 276826524 bytesDatabase Buffers 134217728 bytesRedo Buffers 6103040 bytesDatabase mounted. Database opened.

3. pfile step: Modify pfile parameter consistency to close the database and add or reduce control files. Use pfile to start the database verification result.
Modify the control file :*. control_files = '/u01/app/oracle/oradata/prod/disk1/control01.ctl', '/u01/app/oracle/oradata/prod/disk2/control01.ctl ', '/u01/app/oracle/oradata/prod/disk3/control01.ctl', '/u01/app/oracle/oradata/prod/disk4/control01.ctl ', '/u01/app/oracle/oradata/prod/disk5/control01.ctl' Shut down the database: SQL> shutdown immediate
Copy file: [oracle @ master dbs] $ cp/u01/app/oracle/oradata/prod/disk4/control01.ctl/u01/app/oracle/oradata/prod/disk5/control01.ctl start the database: SQL> startupORACLE instance started.
Total System Global Area 418484224 bytesFixed Size 1336932 bytesVariable Size 276826524 bytesDatabase Buffers 134217728 bytesRedo Buffers 6103040 bytesDatabase mounted. Database opened.
6. control file recovery: (the control file is lost and the control file needs to be rebuilt) 1. When multiple control files exist, the recovery of one of the control files is lost.
Solution: copy other control files to the lost directory.
2. If all the control files are lost, but controlfile is backed up to the trace file: alter database backup controlfile to trace has been executed;
Solution: Start nomount and recreate the control file: noresetlogs example: ①. Start the database: SQL> startupORACLE instance started.
Total System Global Area 418484224 bytesFixed Size 1336932 bytesVariable Size 276826524 bytesDatabase Buffers 134217728 bytesRedo Buffers 6103040 bytesORA-00205: error in identifying control file, check alert log for more info ②. view Alert Log: Error alter database MOUNTORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/prod/disk3/control01.ctl 'ora-27037: unable Obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/oracle/oradata/prod/disk2/control01.ctl' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/app/or Acle/oradata/prod/disk1/control01.ctl 'ora-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Mon Jun 16 15:16:04 2014 Checker run found 2 new persistent data failuresORA-205 signalled during: alter database mount... ③. find a sentence in the trace file (at the end of the trace file) and recreate the control file. Note: The PROD here is db_name. create controlfile reuse database "PROD" noresetlogs noarchivelog maxlogfiles 16 MAXLOGMEMBERS 2 MAXDATAFILES 30 MAXINSTANCES 1 MAXLOGHISTORY 292 logfile group 1 ('/u01/app/oracle/oradata/prod/disk1/redo01.log ', '/u01/app/oracle/oradata/prod/disk2/redo01.log') SIZE 100 m blocksize 512, GROUP 2 ('/u01/app/oracle/oradata/prod/disk1/redo02.log', '/u01/app/oracle/oradata/prod/disk 2/redo02.log ') SIZE 100 m blocksize 512, GROUP 3 ('/u01/app/oracle/oradata/prod/disk1/redo03.log ', '/u01/app/oracle/oradata/prod/disk2/redo03.log ') SIZE 100 m blocksize 512 -- standby logfiledatafile '/u01/app/oracle/oradata/prod/disk3/system01.dbf ', '/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf','/u01/app/oracle/oradata/prod/disk3/undotbs01.dbf ', '/u01/app/oracle/oradata/prod/disk3/users01.db F'character SET AL32UTF8; execute the preceding reconstruction control file statement in nomount.
③. Start the database to open, SQL> alter database open;
Database altered. View tablespace information SQL> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS ---------------------------- --------- system permanentsysaux permanentundotbs UNDOTEMPTS1 TEMPORARYUSERS PERMANENT
Check whether SQL> select file #, name from v $ tempfile exist in the data file of the temporary tablespace;
FILE # NAME ---------- ------------------------------------------------ 1/u01/app/oracle/oradata/prod/disk3/temp01.dbf


3. all control files are lost. Backup of Control Files: (the experiment was not successful) solution: Start to nomount, copy the old control file, control the corresponding directory of the file, start to mount, execute alter database backup controlfile to trace; close the database, delete the old control file, find the statement for recreating the control file in the trace, recreate the control file, and start the database to open, check whether the data file of the temporary tablespace exists. (generally, the temporary tablespace does not exist. Create a new temporary tablespace and set it to the default temporary tablespace ).




Create temporary tablespace SQL> create temporary tablespace tempts2 tempfile '/u01/app/oracle/oradata/prod/disk5/temp01.dbf' size 50 m;
Tablespace created. Set it to the default temporary tablespace SQL> alter database default temporary Tablespace tempts2;
Database altered. View SQL> select file #, name from v $ tempfile;
FILE # NAME ---------- ------------------------------------------------ 1/u01/app/oracle/oradata/prod/disk5/temp01.dbf

4. If there is no backup and backup to trace control file, you only need to find the database creation statement, modify (the path of the log file and data file must be consistent with that of the database), and then execute it.
5. Recover various situations of Control Files
You do not need to use resetlogs to open the database to recreate the control file. First try alter database open; Case 1: Smooth database opening
Scenario 2: Incomplete recovery is required. After recovery, the database recover database can be opened normally; alter database open; Case 3: after Incomplete recovery, only the database resetlogs can be opened. If the database is just shutdown abort, use recover database until cancel;

If Incomplete recovery is required, the system will prompt you to use using backup controlfile; SQL> recover database using backup controlfile; after executing the preceding sentence, enter the log file.






How to create a new control file in oracle

In addition to data files and log file information, the control file also contains some parameters closely related to the instance, such as maxlogfiles, maxlogmembers, and maxloghistory. If you do not set the parameter size properly when creating an instance, the instance may become unavailable. For example, maxdatafiles indicates the total number of data files in the instance, if the current data file has reached this value, you cannot create a new data file.
Detailed steps for creating a control file: (1) You must first prepare a control file that contains the path and file name of the data file and log file. Therefore, you must first query the data file (query the dba_data_files data dictionary) and log file (query the V $ logfile data dictionary) Details: select member from v $ logfile; select name from v $ datafile; select value from v $ parameter where name = 'control _ files '; (2) disable the database using immediate or abort to close the database SQL> connect as sysdbasql> shutdown immediate; (3) back up all data files and redo log files. When the database changes, backup is generally required, it is best to back up all data files, log files, and control files using cold backup of the operating system. (4) use the nomount option to start Database SQL> startup mount (5) use create controlfile to create a control file. (6) Back up a new control file to the device in offline mode. (7) modify the service parameter file path spfile. (8) restore the database if online or archived logs or data files are lost. Otherwise, perform step (9. (9) run the alter database open command to open the database. (10) shut down the database and back up the database
 
Who can tell me what the Oracle control file is?

For the control file of the Oracle database, it is a very small (within the MB range) binary file, and it contains

Information about the database structure. It can be understood as a metadata database repository for databases. It contains the database structure

-- Create a database data file and redo log file.

It is created when you use Oracle to create a database and is updated when physical changes occur. For example, when you add

Or when renaming a data file (alter data file ).

Control file is constantly updated and should be available at any time. (Note: Do not edit the control file content !)

Only the Oracle process can update its content!

The control file contains the following items:

1. Name of the database to which the control file belongs. One control file can belong to one database.

2. database creation time.

3. data file name, location, and online/offline status information.

4. Redo the log file name and location.

5. Redo the log archive information.

6. tablespace name

7. The current log serial number [log sequence number], which is incremental and recorded when online redo log files are switched

Unique identifier.

8. The latest checkpoint information.

9. start and end of the Undo segment.

10. Backup information of the Recovery Manager (RMAN ).

Oracle utility for backing up and recovering databases.