Oracle-full recovery of user management (III)

Oracle-full recovery of user management (iii) I. Description of control files 1. control files are generally multiplexing. When control files are updated, each file is updated at the same time. 2. Multiple Control Files. If one is corrupted, the database cannot work. This is different from online redo log files. 3. Each time a database is opened, the database automatically checks whether the SCN of the control file is consistent with that of the data file. In view v $ datafile, you can see the SCN Number of the control file. View v $ datafile reads the SCN number from the control file. Read the SCN number on the data file header in view v $ datafile_header. View the two views, SQL> select file #, checkpoint_change # from v $ datafile; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 3016606 2 3016606 3 3016606 4 3016606 5 3016606 6 3016606 8 3016606 12 30166068 rows selected. SQL> select file #, checkpoint_change # from v $ datafile_header; FILE # CHECKPOINT_CHANGE # ---------- ---------------- 1 3016606 2 3016606 3 3016606 4 3016606 5 3016606 6 3016606 8 3016606 12 30166068 rows selected. compare the two SCN numbers. (1) if the SCN number in the control file is smaller than the SCN number in the data file, the control file is old and a new control file must be created. (2) If the SCN number in the control file is greater than the SCN number in the data file, the data file must be restored. (3) If the SCN Number of the control file is the same as that of the data file, the database can be opened normally. II. because the control file is multiplexing, there are two types of control File Corruption: 1. if the control file is not all corrupted, you can copy the control file directly to the original location or modify the control file location in the spfile. (1) Open the database, delete a control file, and simulate damage to a control file. [oracle @ oracle11g wilson] $ rm-f control01.ctl will encounter an error when closing the database normally, SQL> shutdown immediateDatabase closed. ORA-00210: cannot open the specified control fileORA-00202: control file: '/u01/oradata/wilson/control01.ctl' ORA-27041: unable to open fileLinux Error: 2: no such file or directoryAdditional information: 3 the database cannot be opened normally now. view the alarm Log file alert. the wilson_log file shows the lost information of the control file. (2) restore the data file. Run the following command to open the database at/u01/oradata/wilson [oracle @ oracle11g wilson] $ cp control02.ctl control01.ctl. 2. If all the control files are lost, how can we restore them. (1) create a directory backupcf under the/u01 directory, and back up the control file SQL> alter database backup controlfile to trace as '/u01/backupcf/c. trc'; the backup here is a script for creating control files. (2) Open the database and delete all control files. [oracle @ oracle11g wilson] $ rm-f control0 * and then close the database. The database cannot be closed normally. Run the following command, SQL> shutdown abort; ORACLE instance shut down. (3) Use the backup script to restore the control file. There are two groups of restoration methods in the script:. b. if online redo logs are abnormal, select a in this example. Set c. trc to copy another copy. The command is as follows: [oracle @ oracle11g backupcf] $ cp c. trc c1. SQL: Modify the c1. SQL script, and delete all the methods in method B. Leave method a and delete some comments in method, in particular, this annotation-standby logfile affects script execution in the middle. Another point is that there is no space between the line and the line in the following code, or it cannot be executed. Startup nomountcreate controlfile reuse database "WILSON" noresetlogs archivelog maxlogfiles 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 logfile group 1'/u01/oradata/wilson/comment 'size 50 m blocksize 512, GROUP 2'/u01/oradata/wilson/redo02.log 'SIZE 50 m blocksize 512, GROUP 3 '/u01/oradata/wilson/redo03.log' SIZE 50 m blocksize 512 DATAFILE '/u01/oradata/wilson/system01. Dbf', '/u01/oradata/wilson/sysaux01.dbf','/u01/oradata/wilson/undotbs01.dbf ','/u01/oradata/wilson/users01.dbf ', '/u01/oradata/wilson/example01.dbf','/u01/oradata/wilson/app2_01.dbf', '/u01/oradata/wilson/smallundo1.dbf ', '/u01/oradata/wilson/app1_01.dbf' character set AL32UTF8 ;.......................... ........ (4) execute the script to restore. All control files can be restored. SQL> @/u01/backupcf/c1.sqlORACLE instance started. total System Global Area 146472960 bytesFixed Size 1335080 bytesVariable Size 92274904 bytesDatabase Buffers 50331648 bytesRedo Buffers 2531328 bytesControl file created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. media recovery complete. system altered. database altered. tablespace altered. tablespace altered. III. lab To operate on a complex example, first back up the control file and then add a tablespace (in this way, the database structure will change, and the same control file will also change ), delete all existing control files, and restore them with backup control files. 1. Backup control file. This is not the above backup control file script, but backup binary control file. SQL> alter database backup controlfile to '/tmp/w1.bin'; Database altered. 2. create a tablespace and data file. SQL> create Tablespace app3 datafile '/u01/oradata/wilson/app3_01.dbf' size 100 m; tablespace created. under Dave, SQL> create table t4 (id int, name char (10) tablespace app3; Table created. SQL> insert into t4 values (0, 'jack'); 1 row created. 3. now all the control files are deleted, the simulated control file is corrupted, and the database is closed. [Oracle @ oracle11g wilson] $ rm-f control0 * shut down the database, SQL> shutdown abort; ORACLE instance shut down. 4. restore the control file, and then open the database to the mount State. At this time, the database cannot be opened, [racle @ oracle11g wilson] $ cp/tmp/w1.bin/u01/oradata/wilson/control01.ctl [oracle @ oracle11g wilson] $ cp/tmp/w1.bin/u01/oradata/wilson/control02.ctl [oracle @ oracle11g wilson] $ cp/tmp/w1.bin/u01/oradata/wilson/control03.ctl enters the mount status, SQL> startup mount; 5. restore the control file. The command is as follows: (1) start to enter the recovery command, will find problems, SQL> recover database using backup controlfile; ORA-00279: change 3105228 generated at 09/01/2013 01:22:03 needed for thread 1ORA-00289: suggestion: /u01/oradata/wilson/arch/sun_1_0000000014_824777760.arcORA-00280: change 3105228 for thread 1 is in sequence #14 Specify log: {<RET> = suggested | filename | AUTO | CANCEL}. The file sun_00000000000014_0000777760.arc needs to be archived. The maximum value found by viewing the archive file is 13., [Oracle @ oracle11g arch] $ lltotal 228556-rw-r ----- 1 oracle oinstall 6060544 Sep 1 0:21 sun_1_0000000011_824777760.arc-rw-r ----- 1 oracle oinstall 522240 Sep 1 0:21 sun_1_0000000012_824777760.arc-rw-r ----- 1 oracle oinstall 65536 Sep 1 0:21 then 14 Archive files are in online redo log files, you can view the view v $ log or give each redo log a try. SQL> select group #, status, members from v $ log; GROUP # STATUS MEMBERS ---------- -------------- ---------- 1 INACTIVE 1 3 INACTIVE 1 2 CURRENT 1 so input redo02.log, Specify log: {<RET> = suggested | filename | AUTO | CANCEL}/u01/oradata/wilson/redo02.logORA-00283: recovery session canceled due to errorsORA-01244: unnamed datafile (s) added to control file by media recoveryORA-01110: data file 7: '/u01/ Oradata/wilson/app3_01.dbf you can see that there is a data file that cannot be identified, because this control file is old. (2) view the view to find the data file to be modified, view v $ recover_file and v $ datafile, SQL> select * from v $ recover_file; FILE # ONLINE _ error change # TIME ---------- ------- ------------------ ---------- --------- 7 online file missing 0 SQL> select file #, name from v $ datafile; FILE # NAME ---------- --------------------------------- 1/u01/oradata/wilson/system01.dbf 2/u01/oradata/wilson/sysaux01.dbf 3/u01/orad Ata/wilson/keys 4/u01/oradata/wilson/users01.dbf 5/u01/oradata/wilson/example01.dbf 6/u01/oradata/wilson/app2_01.dbf 7/u01/oracle/dbs/ UNNAMED00007 8/u01/oradata/wilson/smallundo1.dbf 12/u01/oradata/wilson/app1_01.dbf9 rows selected. you can see that the name of file 7 has changed. Modify the name of this file and add it to the control file, SQL> alter database rename file '/u01/oracle/dbs/UNNAMED00007' to '/u01/oradata/wilson/app3_01.dbf'; Database altered. You can view the alarm log to identify how it exactly corresponds to this file. (3) restore again, the command is as follows, SQL> recover database using backup controlfile; ORA-00279: change 3106444 generated at 09/01/2013 01:41:19 needed for thread 1ORA-00289: suggestion: /u01/oradata/wilson/arch/sun_1_0000000014_824777760.arcORA-00280: change 3106444 for thread 1 is in sequence #14 Specify log: {<RET> = suggested | filename | AUTO | CANCEL}/u01/oradata/wilson/redo02.logLog applied. media recovery complete. Open the database and check the data. SQL> alter Database open resetlogs; database altered. SQL> select * from Dave. t4; id name ---------- ----------------------------------- 0 jack can see that the data is still in progress and the recovery is successful.