rac asm 恢復到 單例項 2

1.5.恢復rac到單機

1.5.1.從備份集中恢復spfile並儲存成pfile

[oracle@secdb1 oracle]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 26 14:16:22 2012

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

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initRACDB.ora'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1218268 bytes

Variable Size 54528292 bytes

Database Buffers 100663296 bytes

Redo Buffers 2973696 bytes

RMAN> exit

Recovery Manager complete.

[oracle@secdb1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Dec 27 15:35:20 2012

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

connected to target database: DUMMY (not mounted)

RMAN> restore spfile to pfile "/home/oracle/initracdb.ora" from "/u01/app/oracle/backup/racfull_blk_0gnttvoh_1_16.rmn";

Starting restore at 27-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=39 devtype=DISK

channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/racfull_blk_0gnttvoh_1_16.rmn

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 27-DEC-12

RMAN>

1.5.2.編輯/home/oracle/initracdb.ora

檢視initracdb.ora內容

[oracle@secdb1 ~]$ cat /home/oracle/initracdb.ora

RACDB2.__db_cache_size=79691776

RACDB1.__db_cache_size=71303168

RACDB1.__java_pool_size=4194304

RACDB2.__java_pool_size=4194304

RACDB1.__large_pool_size=4194304

RACDB2.__large_pool_size=4194304

RACDB2.__shared_pool_size=75497472

RACDB1.__shared_pool_size=83886080

RACDB1.__streams_pool_size=0

RACDB2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/RACDB/adump'

*.background_dump_dest='/u01/app/oracle/admin/RACDB/bdump'

*.cluster_database_instances=2

*.cluster_database=true

*.compatible='10.2.0.1.0'

*.control_files='+RAC_DISK/racdb/controlfile/current.260.802987579'

*.core_dump_dest='/u01/app/oracle/admin/RACDB/cdump'

*.db_block_size=8192

*.db_create_file_dest='+RAC_DISK'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='RACDB'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'

RACDB2.instance_number=2

RACDB1.instance_number=1

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_listener='LISTENERS_RACDB'

*.remote_login_passwordfile='exclusive'

*.sga_target=167772160

RACDB2.thread=2

RACDB1.thread=1

*.undo_management='AUTO'

RACDB2.undo_tablespace='UNDOTBS2'

RACDB1.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/RACDB/udump'

備份initracdb.ora

[oracle@secdb1 ~]$ cp initracdb.ora initracdb.orabak

[oracle@secdb1 ~]$

修改initracdb.ora後內容

*.audit_file_dest='/u01/app/oracle/admin/RACDB/adump'

*.background_dump_dest='/u01/app/oracle/admin/RACDB/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/app/oracle/oradata/RACDB/control01.ctl','/u01/app/oracle/oradata/RACDB/control02.ctl'

*.core_dump_dest='/u01/app/oracle/admin/RACDB/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='RACDB'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=16777216

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=300m

*.undo_management='AUTO'

undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/RACDB/udump'

使用pfile生成spfile

[oracle@secdb1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 26 14:45:40 2012

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> create spfile from pfile='/home/oracle/initracdb.ora';

File created.

1.5.3.從備份集中恢復controlfile

使用spfile啟動資料庫到nomount

SQL> startup nomount;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219160 bytes

Variable Size 96470440 bytes

Database Buffers 213909504 bytes

Redo Buffers 2973696 bytes

SQL>

恢復控制檔案前先設定dbid

[oracle@secdb1 oracle]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 26 14:53:46 2012

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

connected to target database: RACDB (not mounted)

RMAN> set dbid=800604347;

executing command: SET DBID

使用rman恢復controlfile

RMAN> restore controlfile from '/u01/app/oracle/backup/racfull_blk_0fnttvo6_1_15.rmn';

Starting restore at 27-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

output filename=/u01/app/oracle/oradata/RACDB/control01.ctl

output filename=/u01/app/oracle/oradata/RACDB/control02.ctl

Finished restore at 27-DEC-12

RMAN>

1.5.4.啟動單機到mount狀態

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

1.5.5.註冊rman備份集到控制檔案

RMAN> catalog start with '/u01/app/oracle/backup';

searching for all files that match the pattern /u01/app/oracle/backup

List of Files Unknown to the Database

=====================================

File Name: /u01/app/oracle/backup/0hnttvot_1_1

File Name: /u01/app/oracle/backup/racfull_blk_0dnttvmk_1_13.rmn

File Name: /u01/app/oracle/backup/racfull_blk_0fnttvo6_1_15.rmn

File Name: /u01/app/oracle/backup/0inttvov_1_1

File Name: /u01/app/oracle/backup/racfull_blk_0gnttvoh_1_16.rmn

File Name: /u01/app/oracle/backup/0cnttvmf_1_1

File Name: /u01/app/oracle/backup/0anttvm4_1_1

File Name: /u01/app/oracle/backup/0bnttvm4_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/backup/0hnttvot_1_1

File Name: /u01/app/oracle/backup/racfull_blk_0dnttvmk_1_13.rmn

File Name: /u01/app/oracle/backup/racfull_blk_0fnttvo6_1_15.rmn

File Name: /u01/app/oracle/backup/0inttvov_1_1

File Name: /u01/app/oracle/backup/racfull_blk_0gnttvoh_1_16.rmn

File Name: /u01/app/oracle/backup/0cnttvmf_1_1

File Name: /u01/app/oracle/backup/0anttvm4_1_1

File Name: /u01/app/oracle/backup/0bnttvm4_1_1

檢查控制檔案備份集

RMAN> list backup;

List of Backup Sets

===================

BS Key Size

------- ----------

9 2.26M

List of Archived Logs in backup set 9

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 5 573893 27-DEC-12 580845 27-DEC-12

Backup Set Copy #1 of backup set 9

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:08 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 9 Copy #1

BP Key Pc# Status Piece Name

------- --- ----------- ----------

9 1 AVAILABLE /u01/app/oracle/product/10.2.0/db_1/dbs/0anttvm4_1_1

Backup Set Copy #2 of backup set 9

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:08 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 9 Copy #2

BP Key Pc# Status Piece Name

------- --- ----------- ----------

19 1 AVAILABLE /u01/app/oracle/backup/0anttvm4_1_1

BS Key Size

------- ----------

10 2.25M

List of Archived Logs in backup set 10

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 6 580845 27-DEC-12 582135 27-DEC-12

2 4 573892 27-DEC-12 581403 27-DEC-12

Backup Set Copy #1 of backup set 10

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:09 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 10 Copy #1

BP Key Pc# Status Piece Name

------- --- ----------- ----------

10 1 AVAILABLE /u01/app/oracle/product/10.2.0/db_1/dbs/0bnttvm4_1_1

Backup Set Copy #2 of backup set 10

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:09 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 10 Copy #2

BP Key Pc# Status Piece Name

------- --- ----------- ----------

20 1 AVAILABLE /u01/app/oracle/backup/0bnttvm4_1_1

BS Key Size

------- ----------

11 25.00K

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

2 5 581403 27-DEC-12 582137 27-DEC-12

Backup Set Copy #1 of backup set 11

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:00 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 11 Copy #1

BP Key Pc# Status Piece Name

------- --- ----------- ----------

11 1 AVAILABLE /u01/app/oracle/product/10.2.0/db_1/dbs/0cnttvmf_1_1

Backup Set Copy #2 of backup set 11

Device Type Elapsed Time Completion Time Compressed Tag

----------- ------------ --------------- ---------- ---

DISK 00:00:00 27-DEC-12 YES TAG20121227T152243

List of Backup Pieces for backup set 11 Copy #2

BP Key Pc# Status Piece Name

------- --- ----------- ----------

18 1 AVAILABLE /u01/app/oracle/backup/0cnttvmf_1_1

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

12 Full 30.42M DISK 00:00:45 27-DEC-12

BP Key: 12 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152259

Piece Name: /u01/app/oracle/backup/racfull_blk_0enttvmk_1_14.rmn

List of Datafiles in backup set 12

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

2 Full 582171 27-DEC-12 +RAC_DISK/racdb/datafile/undotbs1.258.802987479

3 Full 582171 27-DEC-12 +RAC_DISK/racdb/datafile/sysaux.257.802987477

5 Full 582171 27-DEC-12 +RAC_DISK/racdb/datafile/example.264.802987619

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

13 10.50K DISK 00:00:00 27-DEC-12

BP Key: 13 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152413

Piece Name: /u01/app/oracle/backup/0hnttvot_1_1

List of Archived Logs in backup set 13

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

1 7 582135 27-DEC-12 582206 27-DEC-12

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

14 Full 83.54M DISK 00:00:00 27-DEC-12

BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152259

Piece Name: /u01/app/oracle/backup/racfull_blk_0dnttvmk_1_13.rmn

List of Datafiles in backup set 14

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 582168 27-DEC-12 +RAC_DISK/racdb/datafile/system.256.802987477

4 Full 582168 27-DEC-12 +RAC_DISK/racdb/datafile/users.259.802987479

6 Full 582168 27-DEC-12 +RAC_DISK/racdb/datafile/undotbs2.265.802987827

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

15 Full 1.05M DISK 00:00:00 27-DEC-12

BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152259

Piece Name: /u01/app/oracle/backup/racfull_blk_0fnttvo6_1_15.rmn

Control File Included: Ckp SCN: 582192 Ckp time: 27-DEC-12

BS Key Size Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

16 2.50K DISK 00:00:00 27-DEC-12

BP Key: 16 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152413

Piece Name: /u01/app/oracle/backup/0inttvov_1_1

List of Archived Logs in backup set 16

Thrd Seq Low SCN Low Time Next SCN Next Time

---- ------- ---------- --------- ---------- ---------

2 6 582137 27-DEC-12 582208 27-DEC-12

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

17 Full 80.00K DISK 00:00:00 27-DEC-12

BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20121227T152259

Piece Name: /u01/app/oracle/backup/racfull_blk_0gnttvoh_1_16.rmn

SPFILE Included: Modification time: 27-DEC-12

檢視控制檔案內容

SQL> set line 100

SQL> col NAME for a80

SQL> col MEMBER for a80

SQL> select file#,status,name from v$datafile

2 union all

3 select group#,status,member from v$logfile

4 union all

5 select file#,status,name from v$tempfile;

FILE# STATUS NAME

---------- ------- --------------------------------------------------------------------------------

1 SYSTEM +RAC_DISK/racdb/datafile/system.256.802987477

2 ONLINE +RAC_DISK/racdb/datafile/undotbs1.258.802987479

3 ONLINE +RAC_DISK/racdb/datafile/sysaux.257.802987477

4 ONLINE +RAC_DISK/racdb/datafile/users.259.802987479

5 ONLINE +RAC_DISK/racdb/datafile/example.264.802987619

6 ONLINE +RAC_DISK/racdb/datafile/undotbs2.265.802987827

2 +RAC_DISK/racdb/onlinelog/group_2.262.802987587

1 +RAC_DISK/racdb/onlinelog/group_1.261.802987583

3 +RAC_DISK/racdb/onlinelog/group_3.266.802987899

4 +RAC_DISK/racdb/onlinelog/group_4.267.802987903

1 ONLINE +RAC_DISK/racdb/tempfile/temp.263.802987605

11 rows selected.

SQL>

1.5.7.restore資料檔案

使用rman的set命令重新命名資料檔案

[oracle@secdb1 oracle]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 26 15:51:34 2012

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

connected to target database: RACDB (DBID=800604347, not open)

RMAN> run{

2> set newname for datafile 1 to '/u01/app/oracle/oradata/RACDB/system01.dbf';

3> set newname for datafile 2 to '/u01/app/oracle/oradata/RACDB/undotbs1.dbf';

4> set newname for datafile 3 to '/u01/app/oracle/oradata/RACDB/sysaux01.dbf';

5> set newname for datafile 4 to '/u01/app/oracle/oradata/RACDB/users01.dbf';

6> set newname for datafile 5 to '/u01/app/oracle/oradata/RACDB/example01.dbf';

7> set newname for datafile 6 to '/u01/app/oracle/oradata/RACDB/undotbs2.dbf';

8> restore database;

9> switch datafile all;

10> }

executing command: SET NEWNAME

using target database control file instead of recovery catalog

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-DEC-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u01/app/oracle/oradata/RACDB/undotbs1.dbf

restoring datafile 00003 to /u01/app/oracle/oradata/RACDB/sysaux01.dbf

restoring datafile 00005 to /u01/app/oracle/oradata/RACDB/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/racfull_blk_0enttvmk_1_14.rmn

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/backup/racfull_blk_0enttvmk_1_14.rmn tag=TAG20121227T152259

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/RACDB/system01.dbf

restoring datafile 00004 to /u01/app/oracle/oradata/RACDB/users01.dbf

restoring datafile 00006 to /u01/app/oracle/oradata/RACDB/undotbs2.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/racfull_blk_0dnttvmk_1_13.rmn

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/backup/racfull_blk_0dnttvmk_1_13.rmn tag=TAG20121227T152259

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 27-DEC-12

datafile 1 switched to datafile copy

input datafile copy recid=8 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=9 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/undotbs1.dbf

datafile 3 switched to datafile copy

input datafile copy recid=10 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=11 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=12 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/example01.dbf

datafile 6 switched to datafile copy

input datafile copy recid=13 stamp=803146589 filename=/u01/app/oracle/oradata/RACDB/undotbs2.dbf

RMAN>

1.5.8.修改redo file的檔名

[oracle@secdb1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 26 16:00:38 2012

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> alter database rename file '+RAC_DISK/racdb/onlinelog/group_1.261.802987583' to '/u01/app/oracle/oradata/RACDB/redo01.log';

alter database rename file '+RAC_DISK/racdb/onlinelog/group_2.262.802987587' to '/u01/app/oracle/oradata/RACDB/redo02.log';

alter database rename file '+RAC_DISK/racdb/onlinelog/group_3.266.802987899' to '/u01/app/oracle/oradata/RACDB/redo03.log';

alter database rename file '+RAC_DISK/racdb/onlinelog/group_4.267.802987903' to '/u01/app/oracle/oradata/RACDB/redo04.log';

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

Database altered.

SQL>

1.5.9. recover資料庫

[oracle@secdb1 oracle]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Dec 26 16:02:32 2012

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

connected to target database: RACDB (DBID=800604347, not open)

RMAN> recover database;

Starting recover at 27-DEC-12

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=1 sequence=7

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/0hnttvot_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/backup/0hnttvot_1_1 tag=TAG20121227T152413

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_7_802987583.dbf thread=1 sequence=7

channel ORA_DISK_1: starting archive log restore to default destination

channel ORA_DISK_1: restoring archive log

archive log thread=2 sequence=6

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/0inttvov_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/backup/0inttvov_1_1 tag=TAG20121227T152413

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_6_802987583.dbf thread=2 sequence=6

unable to find archive log

archive log thread=1 sequence=8

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/27/2012 16:18:43

RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 582206

RMAN>

查詢rac的redo情況

SQL> set line 1000

QL> select group#,thread#,sequence#,archived,status from v$log;

GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------------

1 1 8 NO CURRENT

2 1 7 YES INACTIVE

3 2 7 NO CURRENT

4 2 6 YES INACTIVE

SQL>

thread1的當前redo序列是8，thread2的當前組redo序列是8

根據提示thread 1 seq 8 lowscn 582206，重新recover執行不完全恢復

RMAN> recover database until sequence 8;

Starting recover at 27-DEC-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 27-DEC-12

1.6.0.使用resetlogs開啟資料庫

控制檔案和資料檔案scn一致，使用resetlogs開啟資料庫

RMAN> sql 'alter database open resetlogs';

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN> exit

1.6.1.新增臨時表空間

重新建立臨時表空間

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/RACDB/temp01.dbf' size 50m REUSE autoextend on;

Tablespace altered.

SQL>

檢查臨時表空間狀態

SQL> col PROPERTY_NAME for a30

SQL> col DESCRIPTION for a50

SQL> col PROPERTY_VALUE for a20

SQL> select * from database_properties where property_value='TEMP';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

----------------------- --------------- ------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

SQL> select * from database_properties where property_value='USERS';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

---------------------------- -------------- ------------------------------------

DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace

SQL>

1.6.2.清理rac相關的表空間及redo logfile

查詢thread狀態

SQL> select THREAD#, STATUS, ENABLED from v$thread;

THREAD# STATUS ENABLED

---------- ------ --------

1 OPEN PUBLIC

2 CLOSED PRIVATE

SQL>

禁用thread 2

SQL> alter database disable thread 2;

Database altered.

查詢thread 2的redo logfile

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

GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------------

1 1 0 YES UNUSED

2 1 1 NO CURRENT

3 2 0 YES UNUSED

4 2 1 NO INACTIVE

SQL>

清理thread 2的redo logfile

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

alter database drop logfile group 4

*

ERROR at line 1:

ORA-00350: log 4 of instance RACDB2 (thread 2) needs to be archived

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/RACDB/redo04.log'

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

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

GROUP# THREAD# SEQUENCE# ARC STATUS

---------- ---------- ---------- --- ----------------

1 1 0 YES UNUSED

2 1 1 NO CURRENT

清除多餘的undo檔案

檢視當前undo表空間

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME

------------------------------

UNDOTBS1

UNDOTBS2

SQL> show parameter undo;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

刪除undotbs2表空間及資料檔案

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

SQL>

驗證undo表空間

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME

------------------------------

UNDOTBS1

1.6.3.檢查資料檔案狀態

SQL> set line 1000

SQL> select file#,status,fuzzy,checkpoint_change#,name from v$datafile_header;

FILE# STATUS FUZ CHECKPOINT_CHANGE# NAME

---------- ------- --- ------------------ --------------------------------------------------------------------------------

1 ONLINE YES 582619 /u01/app/oracle/oradata/RACDB/system01.dbf

2 ONLINE YES 582619 /u01/app/oracle/oradata/RACDB/undotbs1.dbf

3 ONLINE YES 582619 /u01/app/oracle/oradata/RACDB/sysaux01.dbf

4 ONLINE YES 582619 /u01/app/oracle/oradata/RACDB/users01.dbf

5 ONLINE YES 582619 /u01/app/oracle/oradata/RACDB/example01.dbf

SQL>

1.6.4.檢查控制檔案狀態

SQL> select file#,status,checkpoint_change#,last_change#,name from v$datafile;

FILE# STATUS CHECKPOINT_CHANGE# LAST_CHANGE# NAME

---------- ------- ------------------ ------------ --------------------------------------------------------------------------------

1 SYSTEM 582619 /u01/app/oracle/oradata/RACDB/system01.dbf

2 ONLINE 582619 /u01/app/oracle/oradata/RACDB/undotbs1.dbf

3 ONLINE 582619 /u01/app/oracle/oradata/RACDB/sysaux01.dbf

4 ONLINE 582619 /u01/app/oracle/oradata/RACDB/users01.dbf

5 ONLINE 582619 /u01/app/oracle/oradata/RACDB/example01.dbf

SQL>

1.6.4.驗證資料恢復情況

SQL> conn test/test

Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

TEST2 TABLE

TEST TABLE

SQL> select count(*) from test;

COUNT(*)

----------

50351

SQL> select count(*) from test2;

COUNT(*)

----------

50351

SQL>