Oracle user-maneged recovery (2)

Oracle user-maneged recovery (2) Restoration Process for restoring data files to a new location: 1. use the operating system command to COPY the file to the new location 2. start and load the database 3. use the alter database command to update the control file, for example, alter database rename file 'xxxxxxxx' to 'xxxxxxxx '; SQL> create tablespace tbs_test datafile '/database/oradata/skyread/tbs_test.dbf' size 10 M; -- create a test table space Tablespace created. SQL> select file_name from dba_data_files where tablespace_name = 'tbs _ test'; FILE_NAME ----------------------------------- -----------------------------------------/Database/oradata/skyread/tbs_test.dbf1 rows selected. SQL> alter tablespace tbs_test begin backup; -- Hot Standby Tablespace altered. SQL>! Oracle @ readerlogdb-> cp/database/oradata/skyread/tbs_test.dbf/home/oracle/tbs_test.dbforacle @ readerlogdb-> exitexitSQL> alter tablespace tbs_test end backup; Tablespace altered. SQL> alter system archive log current; System altered. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. total System Global Area 5049942016 BytesFixed Size 2090880 bytesVariable Size 1375733888 bytesDatabase Buffers 3657433088 bytesRedo Buffers 14684160 bytesDatabase mounted. SQL> alter database rename file '/database/oradata/skyread/tbs_test.dbf' to '/home/oracle/tbs_test.dbf'; -- Restore Database altered with a new data file. SQL> alter database open; -- open the database alter database open * ERROR at line 1: ORA-01113: file 2 needs media recoveryORA-01110: data file 2: '/Home/oracle/tbs_test.dbf' SQL> recover automatic datafile 2; -- Media recovery is required. Restore Media recovery complete. SQL> alter database open; SQL> select file_name, tablespace_name from dba_data_files where tablespace_name = 'tbs _ test '; -- the new data file FILE_NAME TABLESPACE_NAME ------------------------------------------------------------------------------/home/oracle/tbs_test.dbf TBS_TEST has been completely restored: 1. the recovered database file is a system table. To restore space or UNDO tablespaces, you must restore the entire database or most data file databases to run SQL> alter tablespace SYSTEM begin backup; Tablespace altered. SQL>! Cp/database/oradata/skyread/system01.dbf/home/oracle/system01.dbf -- Hot Standby system tablespace SQL> alter Tablespace system end backup; tablespace altered. SQL> create table t2 (a int); SQL>! Mv/database/oradata/skyread/system01.dbf/database/oradata/skyread/system01.dbf. bak -- damage the system tablespace SQL> shutdown abort; ORACLE instance shut down. SQL> startup; ORACLE instance started. total System Global Area 5049942016 bytesFixed Size 2090880 bytesVariable Size 1375733888 bytesDatabase Buffers 3657433088 bytesRedo Buffers 14684160 bytesDatabase mounted. ORA-01157: cannot identify/lock data file 1- See DBWR trace fileORA-01110: data file 1: '/database/oradata/skyread/system01.dbf' SQL>! Cp/home/oracle/system01.dbf/database/oradata/skyread/system01.dbf -- copy the file SQL> recover database from the backup; -- restore the media. You can also use recover datafile 1 here; all archives and redoMedia recovery complete are applied. SQL> alter database open; -- open Database altered. SQL> desc t2 -- check that the status Name is Null before the server is down? Type ----------------------------------------- -------- ---------------------------- a number (38) 2. recover the opened database (the database is not down due to a fault): the corrupted file is not the system tablespace and the rollback tablespace run for 7x24 hours without causing the database to close SQL> alter tablespace TBS_SF begin backup; SQL>! Cp/database/oradata/skyread/sf01.dbf/home/oracle/sf01.dbf -- perform Hot backup SQL> alter tablespace tbs_sf end backup; rm-rf/database/oradata/skyread/sf01.dbf -- simulate a system failure to accidentally delete data files SQL> drop table t1; -- drop table t1 * ERROR at line 1: ORA-01116: error in opening database file 26ORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf' ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or direc ToryAdditional information: 3SQL> col NAME for a50SQL> SELECT d. file # f #, d. name, d. status, h. status 2 FROM v $ datafile d, v $ datafile_header h 3 WHERE d. file # = h. file #; F # name status ---------- certificate ------- 1/database/oradata/skyread/system01.dbf system online 3/database/oradata/skyread/sysaux01.dbf ONLINE 4/database/oradata/sk Yread/users01.dbf ONLINE 18/database2/oradata/skyread/offline ONLINE 26/database/oradata/skyread/sf01.dbf ONLINE 31/database2/oradata/skyread/undotbs02 online onlinesql>! Cp/home/oracle/sf01.dbf/database/oradata/skyread/sf01.dbf -- copy backup data file SQL> recover datafile 26; -- recover error ORA-00283: recovery session canceled due to errorsORA-01124: cannot recover data file 26-file is in use or recoveryORA-01110: data file 26: '/database/oradata/skyread/sf01.dbf' SQL> alter database datafile'/database/oradata/skyread/sf01.dbf' offline drop; -- manual offlineDatabase altered. SQL> SE LECT d. file # f #, d. name, d. status, h. status 2 FROM v $ datafile d, v $ datafile_header h 3 WHERE d. file # = h. file #; F # name status ---------- certificate ------- 1/database/oradata/skyread/system01.dbf system online 3/database/oradata/skyread/sysaux01.dbf ONLINE 4/database/oradata/skyread/users01.dbf ONLINE 18/database2/oradata/sky Read/TBS_MRPMUSIC01.dbf ONLINE 26/database/oradata/skyread/sf01.dbf recover offline 31/database2/oradata/skyread/undotbs02 ONLINE 6 rows selected. SQL> recover datafile 26; -- Restore Media recovery complete again. SQL> alter database datafile '/database/oradata/skyread/sf01.dbf' online; -- data file onlineDatabase altered. SQL> drop table t1; -- Restore Table dropped. Note that after the data file is accidentally deleted, the data does not automatically offline data. Files may be automatically offline files. If the offline file is not automatically offline, You need to manually offline the file. If a tablespace contains multiple data files, if only one data file is damaged, it does not affect table space usage, but data in corrupted data files cannot be used.