Wednesday, 5 March 2014

how to recover System table space After lost/corrupt?

In the following cases, it is assumed that database is running in archive log
mode.I explain all steps before and after file lost or Corrupt.

[oracle@dns1 ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 4 23:19:18 2014

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

Enter user-name: /as sysdba

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

SQL> select name from v$database;

NAME
---------
DB11G

SQL> shut immediate;
Database closed.
Database dismounted.

[oracle@dns1 DB11G]$ cd /u01/app/oracle/oradata/DB11G/

[oracle@dns1 DB11G]$ ls
control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  ts_ing_ecs01  undotbs01.dbf  users01.dbf

-- I am taking system file backup trough OS command.

[oracle@dns1 DB11G]$ cp system01.dbf  /u01/Archive/

[oracle@dns1 DB11G]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 4 23:21:22 2014

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 5261217792 bytes
Fixed Size                  2222560 bytes
Variable Size            3271558688 bytes
Database Buffers         1979711488 bytes
Redo Buffers                7725056 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.
SQL> shut abort;
ORACLE instance shut down.
SQL> !
[oracle@dns1 DB11G]$ rm system01.dbf
[oracle@dns1 DB11G]$ ls
control01.ctl  control02.ctl  redo01.log  redo02.log  redo03.log  sysaux01.dbf  temp01.dbf  ts_ing_ecs01  undotbs01.dbf  users01.dbf

[oracle@dns1 DB11G]$ exit
exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area 5261217792 bytes
Fixed Size                  2222560 bytes
Variable Size            3271558688 bytes
Database Buffers         1979711488 bytes
Redo Buffers                7725056 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/oradata/DB11G/system01.dbf' to '/u01/Archive/system01.dbf'; ---rename lost file

Database altered.

SQL> recover datafile 1;
ORA-00279: change 9369328 generated at 03/04/2014 23:20:14 needed for thread 1
ORA-00289: suggestion : /u01/Archive/arch_837387824_1_771
ORA-00280: change 9369328 for thread 1 is in sequence #771


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto             ---recover using AUTO option.
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

===========================END=======================================

No comments:

Post a Comment

work on autovacuum postgreSQL parameter

 In This blog, we are discussing the auto vacuum parameter on a small scale. we will understand the below parameters and will see how to mod...