丟失所有控制文件，noresetlogs重建控制文件，alterdatabaseopen

測試2： （1）一致性的全備 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/*.

測試2：
（1）一致性的全備
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）查看數據庫的信息
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 "表空間名"
3 , df.file# "文件號"
4 , df.checkpoint_change# "檢查點"
5 , df.name "文件名"
6 from v$tablespace ts,v$datafile df
7 where ts.ts#=df.ts#
8 order by df.file#;

表空間名 文件號 檢查點 文件名
------------------------------ ---------- ---------- ----------------------------------------
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文件
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）創建表空間testtbs04，在表空間testtbs04創建表test02，然后直接shutdown abort
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(30)) tablespace testtbs04;

SQL> insert into test02 values(1,'nnnnn');
SQL> insert into test02 values(2,'mmmmm');
SQL> commit;

（5）刪除所有控制文件
$ 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）編輯trace文件
$ cp -rf control1.trace control.trace
CREATE CONTROLFILE REUSE DATABASE "BOSS" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
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,
10 GROUP 3 '/oracle/oradata/boss/redo03.log' SIZE 50M
11 DATAFILE
12 '/oracle/oradata/boss/system01.dbf',
13 '/oracle/oradata/boss/undotbs01.dbf',
14 '/oracle/oradata/boss/sysaux01.dbf',
15 '/oracle/oradata/boss/users01.dbf',
16 '/oracle/oradata/boss/example01.dbf',
17 '/oracle/oradata/boss/testtbs01_01.dbf',
18 '/oracle/oradata/boss/testtbs01_02.dbf',
19 '/oracle/oradata/boss/testtbs04_01.dbf'
20 CHARACTER SET ZHS16GBK
21 ;

Control file created.

SQL> alter system archive log all; ##如果沒有執行歸檔，那么不需要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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com