oracle 笔记5（Fundamentals II）

来源：互联网发布：南京网络问政高新园编辑：程序博客网时间：2024/06/03 19:39

十二、User-Managed Complete Recovery

1、缺少联机重做日志文件

SQL>conn / as sysdba
Connected to an idle instance.
SQL>startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 130025040 bytes
Database Buffers 150994944 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>recover database until cancel using backup controlfile;

ORA-00279: change 4898698 generated at 08/14/2011 17:11:18 needed for thread 1
ORA-00289: suggestion :
/home/oracle/flash_recovery_area/TS/archivelog/1_140_752533274.arc
ORA-00280: change 4898698 for thread 1 is in sequence #140

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>alter database open resetlogs;

Database altered.

SQL>

2、Complete Recovery

3、Complete REcovery Methods

4、Open DB Recovery

5、Recovery without a Backup

(1)

6、Recovering Control Files

select file#,checkpoint_change# from v$datafile;

select file#,checkpoint_change# from v$datafile_header;

(2) Recovering Control Files

Methods to recover from loss of control file:
*Use the current control file
*Create a new control file
*Use a backup control file

SQL>shutdown abort;
ORACLE instance shut down.
SQL>startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 155190864 bytes
Database Buffers 125829120 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>select file#,checkpoint_change# from v$datafile;

   FILE# CHECKPOINT_CHANGE#
---------- ------------------
   1 5039725
   2 5039725
   3 5039725
   4 5039725
   5 5039725
   6 5039725
   7 5039725
   8 5039725
   9 5039725

9 rows selected.

SQL>select file#,checkpoint_change# from v$datafile_header;

   FILE# CHECKPOINT_CHANGE#
---------- ------------------
   1 5039725
   2 5039725
   3 5039725
   4 5039725
   5 5039725
   6 5039725
   7 5039725
   8 5039725
   9 5039725

9 rows selected.

SQL>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> set recovery off;
SP2-0158: unknown SET option "recovery"
SQL> set recovery off;
SP2-0158: unknown SET option "recovery"
SQL>recover database using backup controlfile;
ORA-00279: change 5039725 generated at 08/15/2011 09:29:32 needed for thread 1
ORA-00289: suggestion :
/home/oracle/flash_recovery_area/TS/archivelog/1_164_752533274.arc
ORA-00280: change 5039725 for thread 1 is in sequence #164

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/TS/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 10: '/home/oracle/oradata/TS/app3_01.dbf'

ORA-01112: media recovery not started

SQL>recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/home/oracle/db/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/home/oracle/db/dbs/UNNAMED00010'

SQL> col error for a18
SQL>select * from v$recover_file;

   FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ---------
   10 ONLINE ONLINE FILE MISSING 0

SQL> col name for a50
SQL>select file#,name from v$datafile;

   FILE# NAME
---------- --------------------------------------------------
   1 /home/oracle/oradata/TS/system01.dbf
   2 /home/oracle/oradata/TS/undotbs01.dbf
   3 /home/oracle/oradata/TS/sysaux01.dbf
   4 /home/oracle/oradata/TS/users01.dbf
   5 /home/oracle/oradata/TS/example01.dbf
   6 /home/oracle/oradata/TS/mytbs.dbf
   7 /home/oracle/oradata/TS/autostrace01.dbf
   8 /home/oracle/oradata/TS/app1_01.dbf
   9 /home/oracle/oradata/TS/app02_01.dbf
   10 /home/oracle/db/dbs/UNNAMED00010

10 rows selected.

SQL> alter database rename file '/home/oracle/db/dbs/UNNAMED00010' to '/home/oracle/oradata/TS/app2_01.dbf';
alter database rename file '/home/oracle/db/dbs/UNNAMED00010' to '/home/oracle/oradata/TS/app2_01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 10 - new file
'/home/oracle/oradata/TS/app2_01.dbf' not found
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/home/oracle/db/dbs/UNNAMED00010'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL>alter database rename file '/home/oracle/db/dbs/UNNAMED00010' to '/home/oracle/oradata/TS/app3_01.dbf';

Database altered.

SQL>select file#,name from v$datafile;

   FILE# NAME
---------- --------------------------------------------------
   1 /home/oracle/oradata/TS/system01.dbf
   2 /home/oracle/oradata/TS/undotbs01.dbf
   3 /home/oracle/oradata/TS/sysaux01.dbf
   4 /home/oracle/oradata/TS/users01.dbf
   5 /home/oracle/oradata/TS/example01.dbf
   6 /home/oracle/oradata/TS/mytbs.dbf
   7 /home/oracle/oradata/TS/autostrace01.dbf
   8 /home/oracle/oradata/TS/app1_01.dbf
   9 /home/oracle/oradata/TS/app02_01.dbf
   10 /home/oracle/oradata/TS/app3_01.dbf

10 rows selected.

SQL> recover database using backup controlfile untile cancel;
ORA-00905: missing keyword

SQL>recover database using backup controlfile until cancel;
ORA-00279: change 5040694 generated at 08/15/2011 09:37:30 needed for thread 1
ORA-00289: suggestion :
/home/oracle/flash_recovery_area/TS/archivelog/1_164_752533274.arc
ORA-00280: change 5040694 for thread 1 is in sequence #164

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/TS/redo01.log
Log applied.
Media recovery complete.
SQL>alter database open resetlogs;

Database altered.

SQL> select * usr1.t4;
select * usr1.t4
   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL>select * from usr1.t4;

   ID NAME
---------- --------------------------------------------------
   0 lin

SQL>