Loss of all control files, restoration of all data files, non-Restoration of control files, recoverd

Note: Create Table test03, switch logs, create table test04, switch logs, delete all data files, restore all backup data files, and do not need to restore control files; the archive log of test03 is not lost, and the archive log of test04 is lost. test041 cannot be restored, and all control files of the database are lost. 2. The control files before the backup are restored.

Note: Create Table test03, switch logs, create table test04, switch logs, delete all data files, restore all backup data files, and do not need to restore control files; the archive log of test03 is not lost, and the archive log of test04 is lost. test04 cannot be restored. 1. All control files in the database are lost. 2. The control files before the backup are restored.

Note: Create Table test03, switch logs, create table test04, switch logs, delete all data files, restore all backup data files, and do not need to restore control files; the archive log of test03 is not lost, and the archive log of test04 is lost. cannot restore test04
1. loss of all control files in the database 2. Restoration of control files before backup, data files not restored 3. until cancel using bakup controlfile running partial archiving logs
(1) Consistent full backup
SQL> shutdown immediate;

# Mkdir-p/oradata/bossbak/20140609 allbackup
# Cp-rf $ ORACLE_BASE/oradata/boss/*. dbf/oradata/bossbak/20140609 allbackup
# Cp-rf $ ORACLE_BASE/oradata/boss/*. ctl/oradata/bossbak/20140609 allbackup
# Cp-rf $ ORACLE_HOME/dbs/spfileboss. ora/oradata/bossbak/20140609
# Cd/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/
# Rm-rf *

(2) view database information
SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ ONLY

SQL> select table_name, status, tablespace_name from user_tables where tablespace_name like 'testtbs % ';

TABLE_NAME STATUS TABLESPACE_NAME
--------------------------------------------------------------------
TEST01 VALID TESTTBS01
BOSS_NEW_TEST VALID TESTTBS01

(3) Create Table test03 and insert data
SQL> create table test03 (id number, name varchar2 (30) tablespace testtbs01;
SQL> insert into test03 values (1, '20140901 ');
SQL> insert into test03 values (2, '20140901 ');
SQL> insert into test03 values (3, '20140901 ');
SQL> insert into test03 values (1, '20140901 ');
SQL> insert into test03 values (2, '20140901 ');
SQL> insert into test03 values (3, '20140901 ');
SQL> commit;

(4) log Switching
SQL> alter system switch logfile;
SQL> select group #, members, sequence #, archived, status, first_change # from v $ log;

GROUP # members sequence # arc status FIRST_CHANGE #
--------------------------------------------------------------
1 1 2 yes inactive 675131
2 1 4 no current 700583
1 3 yes active 699759
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> create table test04 (id number, name varchar2 (30) tablespace testtbs01;
SQL> insert into test04 values (1, '123 ');
SQL> insert into test04 values (2, '123 ');
SQL> insert into test04 values (3, '123 ');
SQL> commit;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> select group #, members, sequence #, archived, status, first_change # from v $ log;

GROUP # members sequence # arc status FIRST_CHANGE #
--------------------------------------------------------------
1 1 8 yes inactive 704201
2 1 7 yes inactive 704199
3 1 9 no current 704204

(5) back up all archived logs after full backup
# Pwd
/Oracle/flash_recovery_area/BOSS/archivelog/2014_06_09
# Cp-rf *. arc/oradata/bossbak/20140609

(6) Delete All archives and all data files
# Rm-rf *. arc

(7) shut down the database
SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup open;
ORA-01157: cannot identify/lock data file 1-see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
(8) restore the backup data file without restoring the control file
$ Cp-rf *. dbf/oracle/oradata/boss

SQL> col "file name" for a40
SQL> set linesize 150
SQL> select
2 ts. name "tablespace name"
3, df. file # "file number"
4, df. checkpoint_change # "checkpoint"
5. df. name "file name"
6. df. status "online"
7. rf. error "recovery reason"
8, rf. change # "system change no"
9, rf. time
10 from v $ tablespace ts, v $ datafile df, v $ recover_file rf
11 where ts. ts # = df. ts # and df. file # = rf. file #
12 order by df. file #;
##### Scn after the last normal database startup
Tablespace name file number checkpoint file name online recovery reason System Change number TIME
-----------------------------------------------------------------------------------------------------------------------------------------
SYSTEM 1 704204/oracle/oradata/boss/system01.dbf system file not found 0
UNDOTBS1 2 704204/oracle/oradata/boss/undotbs01.dbf online file not found 0
SYSAUX 3 704204/oracle/oradata/boss/sysaux01.dbf online file not found 0
USERS 4 704204/oracle/oradata/boss/users01.dbf online file not found 0
EXAMPLE 5 704204/oracle/oradata/boss/example01.dbf online file not found 0
TESTTBS01 6 704204/oracle/oradata/boss/testtbs01_01.dbf online file not found 0
TESTTBS01 7 704204/oracle/oradata/boss/testtbs01_02.dbf online file not found 0
TESTTBS02 8 652783/oracle/oradata/boss/testtbs02_01.dbf offline normal 0
TESTTBS03 9 652799/oracle/oradata/boss/testtbs03_01.dbf online file not found 0

SQL> col name for a40
SQL> select file #, name, status, CHECKPOINT_CHANGE #, recover from v $ datafile_header;

FILE # name status CHECKPOINT_CHANGE # REC
------------------------------------------------------------------------------
1 ONLINE 0
2 ONLINE 0
3 ONLINE 0
4 ONLINE 0
5 ONLINE 0
6 ONLINE 0
7 ONLINE 0
8 OFFLINE 0
9 ONLINE 0

(9) copy some of the archived logs and restore the logs based on termination.
# Cp-rf o1_mf_1_3_9sbbrfsx _. arc o1_mf_1_4_9sbbsn5j _. arc/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/

# Cat boss_dbw0_8622.trc
ORA-01157: cannot identify/lock data file 1-see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
ORA-27041: unable to open file
Linux Error: 13: Permission denied

SQL> col name for a40;
SQL> set linesize 120;
SQL> select file #, name, status, CHECKPOINT_CHANGE #, recover from v $ datafile_header;

FILE # name status CHECKPOINT_CHANGE # REC
------------------------------------------------------------------------------
1/oracle/oradata/boss/system01.dbf ONLINE 700222 YES
2/oracle/oradata/boss/undotbs01.dbf ONLINE 700222 YES
3/oracle/oradata/boss/sysaux01.dbf ONLINE 700222 YES
4/oracle/oradata/boss/users01.dbf ONLINE 700222 YES
5/oracle/oradata/boss/example01.dbf ONLINE 700222 YES
6/oracle/oradata/boss/testtbs01_01.dbf ONLINE 700222 YES
7/oracle/oradata/boss/testtbs01_02.dbf ONLINE 700222 YES
8 OFFLINE 0
9/oracle/oradata/boss/testtbs03_01.dbf ONLINE 652799

SQL> alter database open;
Alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'

SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion:/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3 _ % u _. arc
ORA-00280: change 700222 for thread 1 is in sequence #3

Specify log :{ = Suggested | filename | AUTO | CANCEL}

# Ll
Total usage 1047064
-Rw-r ----- 1 oracle oinstall 7094272 June 9 11:21 control02.ctl
-Rw-r ----- 1 oracle oinstall 7094272 June 9 11:21 control03.ctl
-Rw-r ----- 1 oracle oinstall 104865792 June 9 11:19 example01.dbf
-Rw-r ----- 1 oracle oinstall 622080 June 9 12:38 o1_mf_1_3_9sbbrfsx _. arc
-Rw-r ----- 1 oracle oinstall 11264 12:38 o1_mf_1_4_9sbbsn5j _. arc
-Rw-r ----- 1 oracle oinstall 2560 12:38 o1_mf_1_5_9sbbst96 _. arc
-Rw-r ----- 1 oracle oinstall 4336640 12:38 o1_mf_1_6_9sbgg5or _. arc
-Rw-r ----- 1 oracle oinstall 1024 June 9 12:38 o1_mf_3167_9sbgg862 _. arc
-Rw-r ----- 1 oracle oinstall 2560 12:38 o1_mf_1_8_9sbggfm7 _. arc

# Cp-rf o1_mf_1_3_9sbbrfsx _. arc o1_mf_1_4_9sbbsn5j _. arc/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/

SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion:/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3 _ % u _. arc
ORA-00280: change 700222 for thread 1 is in sequence #3

Specify log :{ = Suggested | filename | AUTO | CANCEL}
Auto
ORA-00279: change 700583 generated at 06/09/2014 11:33:33 needed for thread 1
ORA-00289: suggestion:/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4 _ % u _. arc
ORA-00280: change 700583 for thread 1 is in sequence #4
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_9sbbrfsx _. arc' no longer needed
For this recovery

ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion:/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5 _ % u _. arc
ORA-00280: change 700603 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4_9sbbsn5j _. arc' no longer needed
For this recovery

ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_9sbbst96 _. arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database until cancel;
ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion:/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5 _ % u _. arc
ORA-00280: change 700603 for thread 1 is in sequence #5

Specify log :{ = Suggested | filename | AUTO | CANCEL}
Cancel
Media recovery canceled.

SQL> alter database open resetlogs;

SQL> select * from test03;

ID NAME
--------------------------------------------------
1 11111
2 22222
3 33333

SQL> select * from test04;
Select * from test04
*
ERROR at line 1:
ORA-00942: table or view does not exist