Lost non-systematic non-current activity back to a data file in the table space

zhaozj2021-02-16  128

** Lost non-system non-current active returns a data file in the table space first is a hot stand (because the last time I have already done resetlogs) Microsoft Windows 2000 [Version 5.00.2195] (C) Copyright 1985-2000 Microsoft Corp. E: /> SQLPLUS INTERNAL SQL * PLUS: Release 8.1.7.0.0 - Production on Monday October 21 08:05:20 2002 (c) Copyright 2000 Oracle Corporation. All Rights Reserved. Connection to : Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production with the Partitioning Option JServer Release 8.1.7.0.0 - Production SQL> Archive LOST; Database Log Mode Archive Mode Auto Archive Enable Archive D: The earliest summary of / backupdb / archive Information Log Sequence 0 Next Archive Log Sequence 1 Current Log Sequence 1 SQL> @E: / backupdb / other / aa to_char (sysdate, '' 'yy --------------- ---- '2002-10-21 08:10:22' SQL> SQL> @e: /backupdb/other/backup_ts.sql sql> --set Termout Off; SQL> Set Echo Off Head Off Feedback Off PageSize 0 21- October -02 Begining Archive log number is: Database log mode archive mode Auto archive enable archive endpoint D: / backupdb / archive earliest summary information log sequence 0 Next Archive log sequence 1 Current log sequence 1 Begin Backup TableSpa CE system.'d: /backupdb/system01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Rbs.'d: /backupdb/rbs01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Users.'d: /backupdb/users01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Temp.'d: /backupdb/temp01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Tools.'d: /backupdb/tools01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Indx.'d: /backupdb/indx01.dbf '... 1 file has been copied.

Successed End Backup This File Begin Backup CONTROLFILE. 'D: /BACKUPDB/CONTROL01.CTL' ... Successed End Backup The CONTROLFILE Begin Backup CONTROLFILE To Trace ... Successed End Backup The CONTROLFILE Before Switch Log, The Current Log is.. : Database log mode archive mode Auto archive enable archive endpoint D: / backupdb / archive earliest summary information log sequence 0 Next Archive log sequence 1 Current log sequence 1 Begin Backup Switch Current Log ... Successed End Switch log. After switch log Switch log , The ending archive log number is: Database log mode archive mode Auto archive enable archive endpoint D: / backupdb / archive earliest summary information log sequence 1 Next Archive log sequence 2 Current log sequence 2 21- October -02 SQL> - -set Termout on; SQL> SQL> SELECT TO_CHAR (Sysdate, '' 'YYYY-MM-DD HH: mm: ss'') from Dual; '2002-10-21 08:10:54' Selected 1 line . SQL> Data Preparation 1 From the following situation, because the structure of the database is changed, the first need to be hot or cold to recover. If you have already backed up, you can retrieve the data INTO TEST2 VALUES (2); because 2 is a few times; 3 is no commit, so you can find 2.3 will be lost. SQL> ALTER TABLESPACE SYSTEM Add DataFile 'D: /backupdb/system02.dbf' size 10m; table space has changed. SQL> ALTER TABLESPACE USERS Add DataFile 'D: /backupdb/users02.dbf' size 10m; Table space has changed. SQL> SQL> CREATE TABLESPACE TEST DATAFILE 'D: /BACKUPDB/TEST01.DBF' size 10m; Table space has been created. SQL> SQL> Archive log list; database log mode archive mode Auto archive enable archive endpoint D: / backupdb / archive earliest information log sequence 1 Next Archive log sequence 2 Current log sequence 2 SQL> ALTER USER LUNAR Quota 10M on test The user has changed. SQL> SQL> CREATE TABLE TEST2 TABLESPACE TEST; Table has been created. SQL> INSERT INTO TEST2 VALUES (1); 1 line has been created. SQL> Commit; submit completion. SQL> ALTER SYSTEM SWITCH logfile; the system has changed.

SQL> SQL> Conn Lunar / Lunar Connected. SQL> Select * from Tab; TName TabType ClusterID ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- Test1 Test2 Table Testbackup3 Test2 Table SQL> Insert Into Test2 Values ​​(2); 1 Row Created. SQL> Commit; Commit Complete. SQL> Insert Into Test2 Values (3); 1 row created. It is necessary to make a hot standby or cold back, otherwise it will not be recovered after the data file is lost (increasing the tablespace, the data file must back up the database) SQL> CONNAL Connected. Sql> @E: / backupdb / other / aa sql> select to_char (sysdate, '' YYYY-mm-dd hh: mm: ss '') from dual; '2002-10-21 08:10:05' 1 row selected. sql > Set Termout Off SQL> SQL> @e: /backupdb/other/backup_ts.sql SQL> - Set Echo Off Head Off Feedback Off PageSize 0; 21-Oct-02 Begining Archive Log Number IS: Database log mode archive model automatic archival enabled archive destination d: / backupdb / archive oldest online log sequence 3 Next log sequence CE To Archive 5 CURRENT LOG Sequence 5 Begin Backup TableSpace System.'d: /backupdb/system01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Rbs.'d: /backupdb/rbs01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Users.'d: /backupdb/users01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Temp.'d: /backupdb/temp01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Tools.'d: /backupdb/tools01.dbf '... 1 file has been copied.

Successed End Backup this file. Begin Backup TableSpace Indx.'d: /backupdb/indx01.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace System.'d: /backupdb/system02.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Users.'d: /backupdb/users02.dbf '... 1 file has been copied. Successed End Backup this file. Begin Backup TableSpace Test.'d: /backupdb/test01.dbf '... 1 file has been copied. Successed End Backup This File Begin Backup CONTROLFILE. 'D: /BACKUPDB/CONTROL01.CTL' ... Successed End Backup The CONTROLFILE Begin Backup CONTROLFILE To Trace ... Successed End Backup The CONTROLFILE Before Switch Log, The Current Log is.. : Database log mode Archive Mode Automatic archival Enabled Archive destination d: / BACKUPDB / archive Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5 Begin Backup Switch Current log ... successed End Switch log After Switch log, The. Ending Archive log Number Is: Database log mode Archive Mode Automatic archival Enabled Archive destination d: / BACKUPDB / archive Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6 21-OCT-02 SQL> --set termout on; SQL> SQL> SELECT TO_CHAR (Sysdate, '' 'YYYY-MM-DD HH: MM: SS' ') from Dual;' 2002-10-21 08:10:31 '1 Row SELECTED. SQL> Number According to Preparation 2, you can retrieve data insert Into test2 values ​​(4);

SQL> Conn Lunar / Lunar Connected. SQL> Insert Into Test2 Values ​​(4); 1 Row Created. SQL> Commit; Commit Complete. SQL> INSERT INTO TEST2 VALUES (5); 1 Row Created. SQL> Shutdow Abort, then delete test01.dbf, simulation data file is missing SQL> conn internal Connected SQL> shutdown abort ORACLE instance shut down SQL> delete test01.dbf, data file backup test01.dbf copied Mount database SQL> startup mount;.. ORACLE instance started . Total System Global Area 25856028 bytes Fixed Size 75804 bytes Variable Size 8925184 bytes Database Buffers 16777216 bytes Redo Buffers 77824 bytes Database mounted SQL> alter database open; alter database open * ERROR at line 1:. ORA-01113: file 9 needs media recovery ORA-01110: Data file 9: 'D: /backupdb/test01.dbf' SQL> When opening the database, the prompt 'd: /backupdb/test01.dbf' requires media recovery because he and other file time points inconsistent . Restore data files (copy the most hot-standing files) SQL> Recover DataFile 'D: /backupdb/test01.dbf'; Media Recovery Complete. Open Database SQL> ALTER DATABASE Open; Database Altered. SQL> Verification Recovery Result: Fully restore SQL> Conn Lunar / Lunar Connected. SQL> Select * from test2; 1 2 3 4 5 5 Rows SELECTED. SQL> Description: [$ # 376] The first is a hot stand (because the last time is not complete Restore resetlogs) [$ # 376] After the above changes need to be a hot spare or cold, otherwise the data file cannot be recovered after the data file is lost (add table space, the data file must back up the database) 1. Shutdow Abort, then delete TEST01.DBF, The analog data file is lost 2. Remove TEST01.DBF, copy the backup data file TEST01.DBF 3. Mount Database 4. Restore data files (copy the nearest hot-standing files) 5. Open the database http: // www .cnoug.org / viewthread.php? Tid = 95 & highlight =% 2blunar

转载请注明原文地址:https://www.9cbs.com/read-14248.html

New Post(0)