Oracle-full recovery of user management (2)

Oracle-full recovery of user management (2) full recovery of User Management (1) describes full recovery in non-archive mode. The following describes full recovery in archive mode. I. full recovery in archive Mode 1. in full recovery in archive mode, the backup file is used for restoration, and the log file (online redo log and archive log file) is used for restoration. 2. If the data file is damaged during full recovery in archive mode, you only need to restore and restore the file, and other files are not disturbed to continue running. This is different from non-archive mode. 3. Restore and restore a data file, which must be in offline state. 2. Full recovery method 1. When the database is opened, the data file is lost or damaged, and the database needs to be closed for recovery. -- When the system tablespace and undo tablespace are faulty. 2. When the database is opened, data files are lost or damaged (files in non-system tablespace and undo tablespace) and how to restore the database when it is opened. 3. The database file in the closed state is corrupted (not the file in the system tablespace). You need to open the database before restoring the database. 4. If data files are not backed up but are lost, how can I use online redo logs and archive log files for recovery. Iii. Preparations The following describes the four situations in the experiment. First, make preparations. Instead of backing up the entire database, back up a tablespace. What needs to be done is as follows: one tablespace app1, one user Dave, one table (test table), one cold backup, and one hot backup. 1. create a tablespace app1 with the following command: SQL> create tablespace app1 datafile '/u01/oradata/wilson/app1_01.dbf' size 100 M 2 extent management local uniform 3 segment space management auto; tablespace created. 2. create a user SQL> create User Dave identified by Dave_12345 2 default tablespace app1; user created. SQL> grant connect, resource to Dave; Grant succeeded. 3. open another session, log on with Dave, create a table, insert data SQL> create table t (id int, name var Char2 (10); Table created. SQL> insert into t values (0, 'Dave. sun '); 1 row created. SQL> commit; Commit complete. 4. cold backup and hot Backup create two folders: cold and hot under/u01/ubackup. For cold backup, run the following command: [oracle @ oracle11g cold] $ cp/u01/oradata/wilson/*/u01/ubackup/cold/hot backup. log on to Dave as a user, insert a row of data, SQL> insert into t values (1, 'sunshine'); 1 row created. start hot backup and Log On As sys. The command is as follows: SQL> alter tablespace app1 begin backup; Tablespace altered. SQL>! Cp/u01/oradata/wilson/app1_01.dbf/u01/ubackup/hot SQL> alter tablespace app1 end backup; Tablespace altered. 5. in the table Dave. t, insert a piece of data to store it in the archive log, but it is not backed up; under user Dave, SQL> insert into t values (2, 'sunny '); 1 row created. SQL> commit; Commit complete. SQL> select * from t; ID NAME ---------- 0 Dave. sun 1 sunshine 2 sunny: the data is stored in the online redo log. In user sys, SQL> alter system switch logfile; System altered. store this data Put it in the archive log. To sum up, there is a line of data in the cold backup file app1_01.dbf, and there are two lines of data in the hot backup file under the data file, there are three lines of data in the archive log file. 4. When the database is opened, the data file is lost or damaged, and the database needs to be closed for recovery. -- The system tablespace and undo tablespace should be faulty. When the system file is damaged, the database cannot be opened and must be closed for recovery. 1. open the database and delete the app1_01.dbf data file and System File system01.dbf under/u01/oradata/wilson (simulate file corruption or loss). The command is as follows, [oracle @ oracle11g wilson] $ rm-f/u01/oradata/wilson/app1_01.dbf [oracle @ oracle11g wilson] $ rm-f/u01/oradata/wilson/system01.dbf shut down the database. 2. copy the cold backup data file to the/u01/oradata/wilson/directory, [oracle @ oracle11g wilson] $ cp/u01/ubackup/cold/app1_01.dbf/u01/oradata/wilson/[oracle @ oracle11g wilson] $ cp/u01/ubackup/cold/system01.dbf/ u01/oradata/wilson/the restored data file is inconsistent with the control file, therefore, when you open the database, it only enters the mount state. 3. view the view v $ recover_file, SQL> select * from v $ recover_file; FILE # ONLINE _ error change # TIME ---------- ------- ------------------ ---------- --------- 1 ONLINE 3003034 30-AUG-13 12 ONLINE 3003034 30-AUG-13 this dynamic performance view shows which data files are required, you can see the file number. 4. use the recover command to restore the file. SQL> recover datafile 1; Media recovery complete. SQL> recover datafile 12; Media recovery complete. alternatively, you can use recover database. You can check that the view v $ recover_file has no content. SQL> select * from v $ recover_file; no rows selected 5. open the database and view the table Dave. whether the t content is restored. SQL> alter database open; Database altered. SQL> select * from Dave. t; id name ---------- 0 Dave. sun 1 sunshine 2 sunny can see Dave. data in t has been recovered. 5. In Experiment 2, when the database is opened, data files are lost or damaged (files in non-system tablespace and undo tablespace) and how to restore the database when it is opened. 1. in the table Dave. t inserts a row of data. The backup does not contain this record. 1 is as follows: SQL> insert into t values (3, 'wound'); 1 row created. SQL> commit; Commit complete. switch logs to write data in the memory to the data file and archive the online redo log files. SQL> alter system switch logfile; System altered. 2. delete the data file append 01.dbf, and view the view v $ recover_file, [oracle @ oracle11g wilson] $ rm-f append 01.dbf SQL> select * from v $ recover_file; FILE # ONLINE _ error change # TIME ---------- ------- ------------------ ---------- --------- 12 offline file not found 0. You can see that FILE 12 is not found, for details about the file name of file 12, you can view the view v $ datafile; SQL> select file #, name from v $ Datafile; FILE # NAME ---------- upload 1/u01/oradata/wilson/system01.dbf 2/u01/oradata/wilson/sysaux01.dbf 3/u01/oradata/wilson/keys 4/u01/oradata/wilson/ users01.dbf 5/u01/oradata/wilson/example01.dbf 8/u01/oradata/wilson/smallundo1.dbf 12/u01/oradata/wilson/app1_01.dbf7 rows selected. 3. recover the data file. Because the database is being opened, you must first offline the problematic data file. SQL> alter database datafile 12 offline; Database altered. Or use the file name. 4. copy the backup data file to/u01/oradata/wilson (restore the data file ), [oracle @ oracle11g wilson] $ cp/u01/ubackup/hot/app1_01.dbf/u01/oradata/wilson/if it is copied to a new directory, you also need the following command, alter database rename file '........ 'to '......... '; to modify the control file. 5. restore the data file and change its status to online, SQL> recover datafile 12; Media recovery complete. SQL> alter database datafile 12 online; Database altered. 6. view the table Dave. whether the data in t is restored. SQL> select * from Dave. t order by 1; id name ---------- 0 Dave. sun 1 sunshine 2 sunny 3 wound, you can see that the data has been restored. 6. In experiment 3, files in the closed state of the database are damaged (not files in the system tablespace). You need to open the database before restoring the database. 1. open the database first, in the table Dave. t to add a row of data. The newly added data is not in the original backup. Put the data in the archive log file or online redo log file, and finally check whether the data has been restored. SQL> insert into t values (4, 'mm'); 1 row created. SQL> commit; Commit complete. archive the online redo log files. SQL> alter system archive log current; System altered. 2. close the database, simulate damage to the app1_01.dbf file, [oracle @ oracle11g wilson] $ rm-f app1_01.dbf (simulate damage to the file), and restore the data file when the database is closed, however, due to business requirements, you need to open the database first. open the database and automatically enter the mount status. view the view v $ recover_file, SQL> select * from v $ recover_file; FILE # ONLINE _ ERROR CHANGE # TIME ----------- ------ ------- ------------------ ---------- --------- 12 online file not found 0 you can see that FILE 12 is not found, you can also view v $ datafile to find the FILE name. 4. first, open the database SQL> alter Database datafile 12 offline; database altered. SQL> alter database open; Database altered. now the database can be used, but it cannot access corrupted data files. 5. restore the data file and view the table Dave. whether the data in t is restored, restore the data file [oracle @ oracle11g wilson] $ cp/u01/ubackup/cold/app1_01.dbf/u01/oradata/wilson/restore the data file SQL> recover datafile 12; Media recovery complete. change the data file status to online, SQL> alter database datafile 12 online; Database altered. view the table Dave. data in t, SQL> select * from Dave. t order by 1; id name ---------- 0 Dave. sun 1 sunshine 2 sunny 3 wound 4mm shows the data in the backup and the number of online redo log files or archived log files. All data has been recovered. 7. In Experiment 4, data files are not backed up, but are lost. How can I use online redo logs and archive log files for recovery. In this case, there are several conditions that need to be met before recovery can be completed: (1) the data file cannot be a file in the system tablespace, because the redo log cannot be applied to it. (2) After this data file is created, all archived log files are stored. (3) After the data file is corrupted, the control file is not re-created and contains the file name of the damaged file. 1. create a tablespace and data file, and then create a table and insert data under user Dave. SQL> create tablespace app2 datafile '/u01/oradata/wilson/app2_01.dbf' size 10 m; tablespace created. under Dave, SQL> create table t2 (id int, name char (10) tablespace app2; Table created. SQL> insert into t2 values (0, 'Dave '); 1 row created. SQL> commit; Commit complete. write Data in the memory to the data file. SQL> alter system checkpoint; System altered. that is, the data in Table t2 is written to the app2_01.dbf data file. 2. Delete the app2_01.dbf data file and simulate File Corruption. [oracle @ oracle11g wilson] $ rm-f app2_01.dbf view v $ recover_file will see no records. What is the problem? SQL> select * from v $ recover_file; no rows selected this is because the database has not found any file loss and can only be found when this data file is used. After a while, you will find that SQL> select * from v $ recover_file; FILE # ONLINE _ error change # TIME ---------- ------- ------------------ ---------- --------- 6 offline file not found 0. Then you can view the data FILE name corresponding to FILE 6 through the view v $ datafile. 3. remove the problematic data file offline and create a new one. SQL> alter database datafile 6 offline; Database altered. create an app2_01.dbf data file. This file is still blank at this time. SQL> alter database create datafile '/u01/oradata/wilson/app2_01.dbf'; Database altered. 4. restore the file and view the result SQL> recover datafile 6; Media recovery complete. SQL> alter database datafile 6 online; Database altered. SQL> select * from Dave. t2; id name ------------------------------------------ --- 0 Dave can see that the data recovery has been completed.