Test 2:
(1) Consistent, fully prepared
sql> shutdown immediate;
$ CP-RF $ORACLE _base/oradata/boss/*.dbf/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _base/oradata/boss/*.log/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _base/oradata/boss/*.ctl/oradata/bossbak/20140610allbackup
$ cp-rf/oradata/boss/control01.ctl/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _home/dbs/spfileboss.ora/oradata/bossbak/20140610allbackup
$ cd/oracle/flash_recovery_area/boss/archivelog/2014_06_10/
$ RM-RF *
(2) View the 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
Sql> Select
2 Ts.name "Table space name"
3, df.file# "file Number"
4, df.checkpoint_change# "Checkpoint"
5, df.name "file name"
6 from V$tablespace Ts,v$datafile DF
7 where ts.ts#=df.ts#
8 ORDER by df.file#;
Table space name file number checkpoint file name
------------------------------ ---------- ---------- ----------------------------------------
SYSTEM 1 708505/oracle/oradata/boss/system01.dbf
UNDOTBS1 2 708505/oracle/oradata/boss/undotbs01.dbf
Sysaux 3 708505/oracle/oradata/boss/sysaux01.dbf
USERS 4 708505/ORACLE/ORADATA/BOSS/USERS01.DBF
EXAMPLE 5 708505/oracle/oradata/boss/example01.dbf
TESTTBS01 6 708505/ORACLE/ORADATA/BOSS/TESTTBS01_01.DBF
TESTTBS01 7 708505/oracle/oradata/boss/testtbs01_02.dbf
TESTTBS02 8 652783/ORACLE/ORADATA/BOSS/TESTTBS02_01.DBF
TESTTBS03 9 652799/oracle/oradata/boss/testtbs03_01.dbf
(3) Trace files for backup control files
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control1.trace ' noresetlogs;
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control2.trace ';
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control3.trace ' resetlogs;
(4) Create TABLE space Testtbs04, CREATE table test02 in Tablespace testtbs04, and shutdown abort directly
Sql>
Create Tablespace Testtbs04
DataFile '/oracle/oradata/boss/testtbs04_01.dbf ' size 10m
Autoextend on next 1m maxsize Unlimited
Logging
Extent Management Local Autoallocate
BlockSize 8k
Segment Space Management Auto
Flashback on;
Tablespace created.
Sql> CREATE TABLE test02 (ID number, name VARCHAR2 ()) tablespace testtbs04;
sql> INSERT INTO test02 values (1, ' nnnnn ');
sql> INSERT INTO test02 values (2, ' mmmmm ');
Sql> commit;
(5) Delete all control files
$ RM-RF *.ctl
sql> shutdown abort;
ORACLE instance shut down.
sql> startup Open;
Ora-00205:error in identifying control file, check alert log for more info
(6) Edit trace file
$ cp-rf control1.trace control.trace
CREATE controlfile reuse DATABASE "BOSS" noresetlogs ARCHIVELOG
maxlogfiles
maxlogmembers 3
Maxdatafiles
maxinstances 8
maxloghistory 292
LOGFILE
Group 1 '/oracle/oradata/boss/redo01.log ' SIZE 50M,
Group 2 '/oracle/oradata/boss/redo02.log ' Size 50M,
GROUP 3 '/oracle/oradata/boss/redo03.log ' SIZE 50M
datafile
'/oracle/oradata/ Boss/system01.dbf ',
'/oracle/oradata/boss/undotbs01.dbf ',
'/oracle/oradata/boss/sysaux01.dbf ',
'/oracle/oradata/boss/users01.dbf ',
'/oracle/oradata/boss/example01.dbf ',
'/ Oracle/oradata/boss/testtbs01_01.dbf ',
'/oracle/oradata/boss/testtbs01_02.dbf ',
'/oracle/ ORADATA/BOSS/TESTTBS04_01.DBF '
CHARACTER SET ZHS16GBK
;
Sql> CREATE controlfile Reuse DATABASE "BOSS" Noresetlogs ARCHIVELOG
2 Maxlogfiles 16
3 Maxlogmembers 3
4 Maxdatafiles 100
5 Maxinstances 8
6 Maxloghistory 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/boss/redo01.log ' SIZE 50M,
9 GROUP 2 '/oracle/oradata/boss/redo02.log ' SIZE 50M,
Ten GROUP 3 '/oracle/oradata/boss/redo03.log ' SIZE 50M
DataFile
'/oracle/oradata/boss/system01.dbf ',
'/oracle/oradata/boss/undotbs01.dbf ',
'/oracle/oradata/boss/sysaux01.dbf ',
'/oracle/oradata/boss/users01.dbf ',
'/oracle/oradata/boss/example01.dbf ',
'/oracle/oradata/boss/testtbs01_01.dbf ',
'/oracle/oradata/boss/testtbs01_02.dbf ',
'/ORACLE/ORADATA/BOSS/TESTTBS04_01.DBF '
CHARACTER SET ZHS16GBK
21;
Control file created.
Sql> alter system archive log all; # #假设没有运行归档, so there's no need to recover database
Sql> select status from V$instance;
STATUS
------------
Mounted
sql> Recover database;
Media recovery complete.
sql> ALTER DATABASE open;
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
TESTTBS04 ONLINE
Sql> select * from test02;
ID NAME
---------- ------------------------------
1 nnnnn
2 Mmmmm