Please send your Questions & Answers or Feedback to ""

Please send your Questions & Answers or Feedback to ""

Loss of Non- system datafile

root> su - oracle
oracle> export ORACLE_SID=venu
oracle> sqlplus / as sysdba
SQL> startup
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;

oracle> cd /u01/app/oracle/venu
oracle venu> ls
oracle venu> cp *.* /u01/coldbkp
oracle venu> rm nonsystemdatafile.dbf
We can restore the non system data file while the database is up and running.
SQL> conn scott/tiger
scott> insert into salgrade select * from salgrade;
scott> conn / as sysdba

SQL> select file#,error,status from v$datafile_header;
SQL> select * from v$recover_file;
Shows the missed datafile.
SQL> alter database datafile 4 offline;

oracle> cd /u01/coldbkp
oracle coldbkp> ls
oracle coldbkp> cp nonsystemdatafile /u01/app/oracle/venu

SQL> select file#,checkpoint_change# from v$datafile;
Picks the data from the control file.
save df.sql
SQL> select file#,checkpoint_change# from v$datafile_header;
Picks the data from the datafile headers.
save dfh.sql

SQL> recover datafile 4;
If the database is in noarchivelog mode, no need to perform recover after restoring the database.
SQL> alter database datafile 4 online;
SQL> select * from v$recover_file;
Now it shows no records as the missed datafile is restored and recovered.

Related Posts Plugin for WordPress, Blogger...

Flag Counter