Saturday, February 16, 2013

Recover from the loss of Control File without RMAN

[oracle@node1 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@node1 orcl]$ ls
control01.ctl  inv02.dbf        log02_02.log  redo01.log  soeindex01.dbf  temp02.dbf
example01.dbf  inventory02.dbf  log03_02.log  redo02.log  sysaux01.dbf    undotbs01.dbf
inv01.dbf      log01_02.log     ORCL          redo03.log  system01.dbf    users01.dbf
[oracle@node1 orcl]$ ls -l /u01/app/oracle/flash_recovery_area/orcl/
total 9568
-rw-r----- 1 oracle oinstall 9781248 Feb 16 15:54 control02.ctl
[oracle@node1 orcl]$ rm -rf control01.ctl
[oracle@node1 orcl]$ ls
example01.dbf  inventory02.dbf  log03_02.log  redo02.log      sysaux01.dbf  undotbs01.dbf
inv01.dbf      log01_02.log     ORCL          redo03.log      system01.dbf  users01.dbf
inv02.dbf      log02_02.log     redo01.log    soeindex01.dbf  temp02.dbf
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 15:55:21 2013

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 instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 16 15:56:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 15:57:14 2013

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> shutdown abort
ORACLE instance shut down.
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$ cp /u01/app/oracle/flash_recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/
[oracle@node1 orcl]$ ll
total 2637336
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:58 control02.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 15:55 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 15:55 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:57 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:57 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:55 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:47 temp02.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 15:55 users01.dbf
[oracle@node1 orcl]$
[oracle@node1 orcl]$
[oracle@node1 orcl]$ mv control02.ctl control01.ctl
[oracle@node1 orcl]$ ll
total 2637336
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:58 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 15:55 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 15:55 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:57 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:57 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:55 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:55 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:55 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:55 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:47 temp02.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:55 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 15:55 users01.dbf
[oracle@node1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 16:00:37 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  506384384 bytes
Fixed Size                  1337464 bytes
Variable Size             314574728 bytes
Database Buffers          184549376 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Feb 16 16:00:57 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1302573621)

RMAN>

Posted by at No comments:
Labels: , , , , , ,

Recovery from loss of temporary tablespace without RMAN

Recover from loss of temporary datafile
=============================

[oracle@node1 ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@node1 orcl]$ ll
total 2642760
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:38 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:38 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Feb 16 15:17 temp01.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:36 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$ rm -rf temp01.dbf;

[oracle@node1 orcl]$ ll
total 2636308
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:38 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:38 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 15:39:49 2013

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 count(*) from hr.employees;

  COUNT(*)
----------
       107
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100M;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@node1 orcl]$ ll
total 2637336
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:48 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:48 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:48 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:45 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:47 temp02.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$
Posted by at No comments:
Labels: , , , ,
Subscribe to: Posts (Atom)