Generally, we use RMAN to restore data files and archive them for recovery. If there is no valid backup set, but there is a file generated by hot backup, we can recover the data in the same way, the data file corresponding to the non-system tablespace is restored after corruption. -- Obtain the Hot Backup statement SQLselectaltertablespace | tablespace
Generally, we use RMAN to restore data files and archive them for recovery. If there is no valid backup set, but there is a file generated by hot backup, we can recover the data in the same way, the data file corresponding to the non-system tablespace is restored after corruption. -- Obtain the Hot Backup statement SQL select 'alter tablespace' | tablespace
Generally, we use RMAN to restore data files and archive them for recovery. If there is no valid backup set, but there is a file generated by hot backup, we can recover the data in the same way, the data file corresponding to the non-system tablespace is restored after corruption.
-- Obtain the Hot Backup statement firstSQL> select 'alter tablespace' | tablespace_name | 'in in backup ;'
2 | chr (10) 3 | 'cp' | file_name | '/u01/'4 | chr (10) 5 | 'alter tablespace' | tablespace_name | 'end backup; 'as "script" 6 from dba_data_files where tablespace_name = 'zlm ';
Script implements alter tablespace ZLM begin backup; cp/u01/app/oracle/oradata/ora10g/zlm01.dbf/u01/alter tablespace ZLM end backup;
SQL> alter tablespace ZLM begin backup;
SQL>! [Oracle @ ora10g ~] $ Cp/u01/app/oracle/oradata/ora10g/zlm01.dbf/u01/[oracle @ ora10g ~] $ Exitexit
SQL> alter tablespace ZLM end backup;
-- Verify that hot standby has been generatedSQL> select * from v $ backup;
FILE # status change # TIME ---------- ------------------ ---------- 1 not active 0 2 not active 0 3 not active 0 4 not active 0 5 not active 0 6 not active 1340174
6 rows selected.
-- Connect to the test user to start transaction executionSQL> conn zlm/zlmConnected. SQL> create table emp as select * from scott. emp;
Table created.
SQL> set lin 130SQL> set pages 130SQL> select * from emp;
Empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- 7369 smith clerk 7902 Jun 800 20 7499 allen salesman 7698 Jun 1600 30 300 jones manager 7839 1981-04-02 2975 20 7654 martin salesman 7698 1981-09-28 1250 1400 30 7698 blke MANAGER 7839 1981-05-01 2850 30 7782 clark manager 7839 1981-06-09 2450 10 7788 scott analyst 7566 1987-04-19 3000 20 7839 king president 1981-11-17 5000 10 7844 turner salesman 7698 1981-09-08 1500 0 30 7876 adams clerk 7788 1987-05-23 1100 20 7900 james clerk 7698 1981-12-03 950 30 7902 ford analyst 7566 1981-12-03 3000 20 7934 miller clerk 7782 1982-01-23 1300 10
14 rows selected.
SQL> update emp set sal = sal + 100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal = sal + 100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal = sal + 100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> update emp set sal = sal + 100;
14 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
A total of 4 update operations were performed, and 4 logs were switched.
-- View the current dataSQL> select * from emp;
Empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- 7369 smith clerk 7902 Jun 1200 20 7499 allen salesman 7698 Jun 2000 30 300 jones manager 7839 1981-04-02 3375 20 7654 martin salesman 7698 1981-09-28 1650 1400 30 7698 blke MANAGER 7839 1981-05-01 3250 30 7782 clark manager 7839 1981-06-09 2850 10 7788 scott analyst 7566 1987-04-19 3400 20 7839 king president 1981-11-17 5400 10 7844 turner salesman 7698 1981-09-08 1900 0 30 7876 adams clerk 7788 1987-05-23 1500 20 7900 james clerk 7698 1981-12-03 1350 30 7902 ford analyst 7566 1981-12-03 3400 20 7934 miller clerk 7782 1982-01-23 1700 10
14 rows selected.
-- Destroys data filesSQL>! [Oracle @ ora10g ~] $ Cat> abc.txt <EOF> abc> efg> hij> EOF [oracle @ ora10g ~] $ Cat abc.txt abcefghij [oracle @ ora10g ~] $ Cp abc.txt/u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle @ ora10g ~] $ Cat/u01/app/oracle/oradata/ora10g/zlm01.dbfabcefghij [oracle @ ora10g ~] $
-- Switch logs three times and continue viewingSQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
SQL>! [Oracle @ ora10g ~] $ Cp/u01/zlm01.dbf/u01/app/oracle/oradata/ora10g/zlm01.dbf [oracle @ ora10g ~] $ Exitexit
SQL> select * from emp; select * from emp * ERROR at line 1: ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
Replace the file generated by the original hot backup with the damaged file No. 6, and the system still prompts that the file cannot be read.
-- Offline the fault file before going onlineSQL> alter database datafile 6 offline;
Database altered.
SQL> alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recoveryORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'
In this case, the system will prompt that file 6 needs to be restored by media.
SQL> col error for a10SQL> select * from v $ recover_file;
FILE # ONLINE _ error change # TIME ---------- ------- ---------- 6 OFFLINE 1385889
We can see that file 6 is in the offline status and needs to be restored.
SQL> select * from v $ recovery_log;
THREAD # SEQUENCE # TIME ---------- ARCHIVE_NAME upper --- 1 58 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/lower _. arc
1 59 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_000059_b7mbsb96 _. arc
1 60 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j _. arc
1 61 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8 _. arc
1 62 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_000062_b7mbw693 _. arc
1 63 2014-11-29/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_63_b7mc54hm _. arc
6 rows selected.
All the queried values in the v $ recovery_log view are the archive log files needed to be restored.
SQL> recover datafile 6; ORA-00279: change 1385889 generated at 11/29/2014 18:46:26 needed for thread 1ORA-00289: suggestion: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_000058 _ % u _. arcORA-00280: change 1385889 for thread 1 is in sequence #58
Specify log :{ = Suggested | filename | AUTO | CANCEL} autoORA-00279: change 1387492 generated at 11/29/2014 19:15:34 needed for thread 1ORA-00289: suggestion: /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_000059 _ % u _. arcORA-00280: change 1387492 for thread 1 is in sequence #59ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_58_b7mbppk8 _. arc' no longer neededfor this recovery
ORA-00279: change 1387536 generated at 11/29/2014 19:16:58 needed for thread 1ORA-00289: suggestion:/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/change _ % u _. arcORA-00280: change 1387536 for thread 1 is in sequence #60ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_59_b7mbsb96 _. arc' no longer neededfor this recovery
ORA-00279: change 1387553 generated at 11/29/2014 19:17:21 needed for thread 1ORA-00289: suggestion:/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/change _ % u _. arcORA-00280: change 1387553 for thread 1 is in sequence #61ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_60_b7mbt18j _. arc' no longer neededfor this recovery
ORA-00279: change 1387562 generated at 11/29/2014 19:17:41 needed for thread 1ORA-00289: suggestion:/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/change _ % u _. arcORA-00280: change 1387562 for thread 1 is in sequence #62ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o1_mf_1_61_b7mbtoy8 _. arc' no longer neededfor this recovery
ORA-00279: change 1387587 generated at 11/29/2014 19:18:30 needed for thread 1ORA-00289: suggestion:/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/change _ % u _. arcORA-00280: change 1387587 for thread 1 is in sequence #63ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_11_29/o%mf_%62_b7mbw693 _. arc' no longer neededfor this recovery
Log applied. Media recovery complete.
After applying all the five archive logs listed in v $ recovery_log, the media recovery is complete.
-- Put file 6 online againSQL> alter database datafile 6 online;
Database altered.
SQL> select * from emp;
Empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- 7369 smith clerk 7902 Jun 1200 20 7499 allen salesman 7698 Jun 2000 30 300 jones manager 7839 1981-04-02 3375 20 7654 martin salesman 7698 1981-09-28 1650 1400 30 7698 blke MANAGER 7839 1981-05-01 3250 30 7782 clark manager 7839 1981-06-09 2850 10 7788 scott analyst 7566 1987-04-19 3400 20 7839 king president 1981-11-17 5400 10 7844 turner salesman 7698 1981-09-08 1900 0 30 7876 adams clerk 7788 1987-05-23 1500 20 7900 james clerk 7698 1981-12-03 1350 30 7902 ford analyst 7566 1981-12-03 3400 20 7934 miller clerk 7782 1982-01-23 1700 10
14 rows selected.
At this time, the tablespace ZLM and the corresponding data file zlm01.dbf have been successfully restored.
