数据文件丢失，有归档，执行数据恢复

Last login: Thu Sep 20 11:22:48 2012 from 10.80.11.111
[root@oracle11g ~]# su - oracle
[oracle@oracle11g ~]$ pwd
/home/oracle
[oracle@oracle11g ~]$ stty erase '^H'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 20 11:23:20 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

 

SQL> create tablespace test datafile
  2  '/u01/test.ora' size 5m
  3  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
  4  default storage (initial 128K next 1M pctincrease 0)
  5  /

Tablespace created.

SQL> drop user test cascade;
drop user test cascade
          *
ERROR at line 1:
ORA-01918: user 'TEST' does not exist

SQL> create user test identified by test default tablespace test;
grant connect,resource to test;
User created.

SQL> grant connect,resource to test;
grant connect,resource to test;grant connect,resource to test
                              *
ERROR at line 1:
ORA-00911: invalid character

SQL> grant connect,resource to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table a(a number);

Table created.

SQL> insert into a values(1);

1 row created.

SQL> insert into a select * from a;

1 row created.

SQL> /

2 rows created.

SQL> /

4 rows created.

SQL> /

8 rows created.

SQL> /

16 rows created.

SQL> /

32 rows created.

SQL> /

64 rows created.

SQL> /

128 rows created.

SQL> /

256 rows created.

SQL> /

512 rows created.

SQL>
SQL> /

1024 rows created.

SQL> /

2048 rows created.

SQL> /

4096 rows created.

SQL> /

8192 rows created.

SQL> /

16384 rows created.

SQL> /

32768 rows created.

SQL> /
/
65536 rows created.

SQL>
/
131072 rows created.

SQL>
/
/
/
262144 rows created.

SQL>
/
/
/

 

524288 rows created.

SQL> /
/
/
/

1048576 rows created.

SQL>
2097152 rows created.

SQL> select
4194304 rows created.

SQL>

^C

^C^C

insert into a select * from a
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

 

SQL> SP2-0042: unknown command "" - rest of line ignored.
SQL> SQL> SQL>
SQL>
SQL>
SQL>
SQL> select count
  2 
SQL>
SQL> select count(*) from a;

  COUNT(*)
----------
   8388608

SQL> commit
  2  ;

Commit complete.

SQL>  connect internal
Enter password:
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

Warning: You are no longer connected to ORACLE.
SQL> connect sys
Enter password:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> connect as sysdba
Enter user-name: system
Enter password:
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /  

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     66
Next log sequence to archive   68
Current log sequence           68
SQL>
SQL> /

System altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1335528 bytes
Variable Size             146804504 bytes
Database Buffers           58720256 bytes
Redo Buffers                2375680 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/test.ora'

SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>  alter database create datafile '/u01/test.ora';

Database altered.

SQL> set autorecovery on
SQL> recovery datafile '/u01/test.ora';
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> recover datafile  '/u01/test.ora';
ORA-00279: change 1511838 generated at 09/20/2012 11:30:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_62_789708408.dbf
ORA-00280: change 1511838 for thread 1 is in sequence #62

ORA-00279: change 1512465 generated at 09/20/2012 11:32:24 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_63_789708408.dbf
ORA-00280: change 1512465 for thread 1 is in sequence #63

ORA-00279: change 1513085 generated at 09/20/2012 11:32:41 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_64_789708408.dbf
ORA-00280: change 1513085 for thread 1 is in sequence #64

ORA-00279: change 1513242 generated at 09/20/2012 11:33:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_65_789708408.dbf
ORA-00280: change 1513242 for thread 1 is in sequence #65

ORA-00279: change 1513427 generated at 09/20/2012 11:33:18 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_66_789708408.dbf
ORA-00280: change 1513427 for thread 1 is in sequence #66

ORA-00279: change 1513542 generated at 09/20/2012 11:37:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_67_789708408.dbf
ORA-00280: change 1513542 for thread 1 is in sequence #67

Log applied.
Media recovery complete.
SQL> conn test/test
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.
SQL> alter database open;
SP2-0640: Not connected
SQL> startup
ORA-01031: insufficient privileges
SQL> connect as sysdba
Enter user-name: system
Enter password:
Connected.
SQL> alter database open;

Database altered.

SQL> conn test/test
Connected.

SQL> SQL>            
SQL>
SQL>
SQL> select conut(*) from test;
select conut(*) from test
             *
ERROR at line 1:
ORA-00936: missing expression

SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> c/test/a
  1* select count(*) from a
SQL> r
  1* select count(*) from a
r

 

  COUNT(*)
----------
   8388608

SQL>   1* select count(*) from a

  COUNT(*)
----------
   8388608

SQL> SQL> SQL> SQL> conn test/test 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25448460/viewspace-745840/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25448460/viewspace-745840/