9. 探索 RAC 資料庫環境
現在，您已經成功安裝了虛擬雙節點 RAC 資料庫，下面我們來探究一下您剛剛配置的環境。

檢查應用程式資源的狀態。

[root@rac1 10.2.0]# su - oracle
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.devdb.db application ONLINE ONLINE rac1
ora....b1.inst application ONLINE ONLINE rac1
ora....b2.inst application ONLINE ONLINE rac2
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
[oracle@rac1 ~]$ srvctl status nodeapps -n rac1
VIP is running on node: rac1
GSD is running on node: rac1
Listener is running on node: rac1
ONS daemon is running on node: rac1
[oracle@rac1 ~]$ srvctl status nodeapps -n rac2
VIP is running on node: rac2
GSD is running on node: rac2
Listener is running on node: rac2
ONS daemon is running on node: rac2
[oracle@rac1 ~]$ srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.
[oracle@rac1 ~]$ srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.
[oracle@rac1 ~]$ srvctl status database -d devdb
Instance devdb1 is running on node rac1
Instance devdb2 is running on node rac2
[oracle@rac1 ~]$ srvctl status service -d devdb
[oracle@rac1 ~]$

檢查 Oracle 叢集件的狀態。

[oracle@rac1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@rac1 ~]$

[oracle@rac2 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@rac2 ~]$

在命令列執行 crsctl 以檢視所有可用選項。

列出 RAC 例項。

SQL> col HOST_NAME format a25
SQL> select instance_name,host_name,archiver,thread#,status from gv$instance;

INSTANCE_NAME HOST_NAME ARCHIVE THREAD# STATUS
---------------- ------------------------- ------- ---------- ------------
devdb2 rac2.mycorpdomain.com STARTED 2 OPEN
devdb1 rac1.mycorpdomain.com STARTED 1 OPEN

SQL>

檢查連線。
驗證您能夠連線到每個節點上的例項和服務。

sqlplus system/oracle@devdb1
sqlplus system/oracle@devdb2
sqlplus system/oracle@devdb

檢查資料庫配置。
[oracle@rac1 ~]$ export ORACLE_SID=devdb1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 10:51:03 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show sga

Total System Global Area 440401920 bytes
Fixed Size 1219880 bytes
Variable Size 142607064 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
SQL> col FILE_NAME format a45
SQL> select file_name,bytes/1024/1024 from dba_data_files;

FILE_NAME BYTES/1024/1024
--------------------------------------------- ---------------
+DG1/devdb/datafile/users.259.632908835 5
+DG1/devdb/datafile/sysaux.257.632908815 240
+DG1/devdb/datafile/undotbs1.258.632908829 25
+DG1/devdb/datafile/system.256.632908791 480
+DG1/devdb/datafile/example.264.632909999 100
+DG1/devdb/datafile/undotbs2.265.632912097 25

6 rows selected.

SQL>

SQL> col MEMBer format a55
SQL> select group#,type,member,is_recovery_dest_file from v$logfile order by group#;

GROUP# TYPE MEMBER IS_
---------- ------- ------------------------------------------------------- ---
1 ONLINE +RECOVERYDEST/devdb/onlinelog/group_1.257.632909721 YES
1 ONLINE +DG1/devdb/onlinelog/group_1.261.632909687 NO
2 ONLINE +RECOVERYDEST/devdb/onlinelog/group_2.258.632909763 YES
2 ONLINE +DG1/devdb/onlinelog/group_2.262.632909729 NO
3 ONLINE +DG1/devdb/onlinelog/group_3.266.632913307 NO
3 ONLINE +RECOVERYDEST/devdb/onlinelog/group_3.259.632913363 YES
4 ONLINE +DG1/devdb/onlinelog/group_4.267.632913371 NO
4 ONLINE +RECOVERYDEST/devdb/onlinelog/group_4.260.632913393 YES

8 rows selected.

SQL>

[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 10:57:03 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show sga

Total System Global Area 92274688 bytes
Fixed Size 1217884 bytes
Variable Size 65890980 bytes
ASM Cache 25165824 bytes
SQL> show parameter asm_disk

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string DG1, RECOVERYDEST
asm_diskstring string
SQL>

SQL> set wrap off
SQL> set linesize 150
SQL> col NAME format a15
SQL> col STATE format a10
SQL> select group_number,name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb from v$asm_diskgroup;

GROUP_NUMBER NAME ALLOC_UNIT_SIZE STATE TYPE TOTAL_MB USABLE_FILE_MB
------------ --------------- --------------- ---------- ------ ---------- --------------
1 DG1 1048576 MOUNTED NORMAL 6134 1823
2 RECOVERYDEST 1048576 MOUNTED EXTERN 2047 1713

SQL>

SQL> col PATH format a15
SQL> select name,path,header_status,total_mb free_mb,trunc(bytes_read/1024/1024) read_mb,
2 trunc(bytes_written/1024/1024) write_mb from v$asm_disk;

NAME PATH HEADER_STATU FREE_MB READ_MB WRITE_MB
--------------- --------------- ------------ ---------- ---------- ----------
ORCL:VOL1 UNKNOWN 3067
ORCL:VOL2 UNKNOWN 3067
ORCL:VOL3 UNKNOWN 2047
DG1_0000 /dev/raw/raw1 MEMBER 3067 333 1411
DG1_0001 /dev/raw/raw2 MEMBER 3067 235 1411
RECOVERYDEST_00 /dev/raw/raw3 MEMBER 2047 14 412

6 rows selected.

SQL>

建立表空間。
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 11:08:46 2007

SQL> connect system/oracle@devdb
Connected.
SQL> create tablespace test_d datafile '+DG1' size 10M;

Tablespace created.

SQL> col FILE_NAME format a40
SQL> col TABLESPACE_NAME format a10
SQL> select file_name,tablespace_name,bytes from dba_data_files where tablespace_name='TEST_D';

FILE_NAME TABLESPACE BYTES
---------------------------------------- ---------- ----------
+DG1/devdb/datafile/test_d.269.632920161 TEST_D 10485760

SQL>

建立線上重做日誌檔案組。

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 11:13:20 2007

SQL> connect system/oracle@devdb
Connected.
SQL> alter database add logfile thread 1 group 5 size 50m;

Database altered.

SQL> alter database add logfile thread 2 group 6 size 50M;

Database altered.

SQL>
SQL> select group#,thread#,bytes,members,status from v$log;

GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 1 52428800 2 CURRENT
2 1 52428800 2 INACTIVE
3 2 52428800 2 CURRENT
4 2 52428800 2 UNUSED
5 1 52428800 2 UNUSED
6 2 52428800 2 UNUSED

6 rows selected.

SQL>
SQL> col MEMBER format a55
SQL> select group#,type,member,is_recovery_dest_file from v$logfile where group# in (5,6) order by group#;

GROUP# TYPE MEMBER IS_
---------- ------- ------------------------------------------------------- ---
5 ONLINE +DG1/devdb/onlinelog/group_5.270.632920471 NO
5 ONLINE +RECOVERYDEST/devdb/onlinelog/group_5.261.632920493 YES
6 ONLINE +DG1/devdb/onlinelog/group_6.271.632920529 NO
6 ONLINE +RECOVERYDEST/devdb/onlinelog/group_6.262.632920555 YES

SQL>

檢查閃回恢復區空間使用率。
SQL> col NAME format a20
SQL> select * from v$recovery_file_dest;

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ----------- ---------- ----------------- ---------------
+RECOVERYDEST 2147483648 331366400 0 7

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 14.65 0 6
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

SQL>

啟動和停止應用程式資源。
遵循以下步驟啟動和停止單獨的應用程式資源。

srvctl start nodeapps -n
srvctl start nodeapps -n
srvctl start asm -n
srvctl start asm -n
srvctl start database -d
srvctl start service -d -s
crs_stat -t

srvctl stop service -d -s
srvctl stop database -d
srvctl stop asm -n
srvctl stop asm -n
srvctl stop nodeapps -n
srvctl stop nodeapps -n
crs_stat -t

[oracle@rac1 zhs]$ srvctl stop service -d devdb
[oracle@rac1 zhs]$ srvctl stop database -d devdb
[oracle@rac1 zhs]$ srvctl stop asm -n rac1
[oracle@rac1 zhs]$ srvctl stop asm -n rac2
[oracle@rac1 zhs]$ srvctl stop nodeapps -n rac1
[oracle@rac1 zhs]$ srvctl stop nodeapps -n rac2
[oracle@rac1 zhs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.....CRM.cs application OFFLINE OFFLINE
ora....db1.srv application OFFLINE OFFLINE
ora.devdb.db application OFFLINE OFFLINE
ora....b1.inst application OFFLINE OFFLINE
ora....b2.inst application OFFLINE OFFLINE
ora....SM1.asm application OFFLINE OFFLINE
ora....C1.lsnr application OFFLINE OFFLINE
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application OFFLINE OFFLINE
ora.rac1.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application OFFLINE OFFLINE
ora.rac2.vip application OFFLINE OFFLINE
[oracle@rac1 zhs]$

10. 測試透明故障切換 (TAF)
Oracle TAF 中的故障切換機制使任何失敗的資料庫連線能夠重新連線到叢集中的其他節點。故障切換對使用者是透明的。Oracle 在故障切換例項上重新執行查詢並繼續向使用者顯示餘下的結果。

建立一個新的資料庫服務。首先，建立一個名為 CRM 的新服務。可以使用 DBCA 或 srvctl 實用程式來建立資料庫服務。這裡，您將使用 DBCA 在 devdb1 上建立 CRM 服務。

服務名資料庫名首選例項可用例項 TAF 策略
CRM devdb devdb1 devdb2 BASIC

在 rac1 上，以 oracle 使用者身份執行

rac1-> dbca

1,歡迎頁面：選擇 Oracle Real Application Clusters database。
2,操作：選擇 Services Management。
3,叢集資料庫列表：單擊 Next。
4,資料庫服務：單擊 Add。
新增服務：輸入“CRM”。
選擇 devdb1 作為首選例項。
選擇 devdb2 作為可用例項。
TAF 策略：選擇 Basic。
單擊 Finish。
5,資料庫配置助手：單擊 No 退出。

資料庫配置助手將在 /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora 中建立以下 CRM 服務名項：

CRM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CRM)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 12:29:49 2007

SQL> connect system/oracle@devdb1
Connected.
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string devdb, CRM
SQL> connect system/oracle@devdb2
Connected.
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string devdb
SQL>

使用 CRM 服務連線第一個會話。如果 failover_type 和 failover_mode 返回的輸出為“NONE”，則驗證是否在 tnsnames.ora 中正確配置了 CRM 服務。

SQL> connect
Connected.
SQL> col HOST_NAME format a30
SQL> select instance_number instance#,instance_name,host_name,status from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- ------------------------------ ------------
1 devdb1 rac1.mycorpdomain.com OPEN

SQL>

SQL> select failover_type,failover_method,failed_over from v$session where username='SYSTEM';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC NO

SQL>

從其他會話中關閉該例項。在 CRM 例項上以 sys 使用者身份連線，並關閉該例項。
[oracle@rac1 ~]$ export ORACLE_SID=devdb1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 12:40:28 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> col HOST_NAME format a30
SQL> select instance_number instance#,instance_name,host_name,status from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- ------------------------------ ------------
1 devdb1 rac1.mycorpdomain.com OPEN

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

驗證會話已經完成故障切換。從您先前開啟的同一 CRM 會話執行以下查詢，以驗證該會話已經故障切換到其他例項。

SQL> col HOST_NAME format a30
SQL> select instance_number instance#,instance_name,host_name,status from v$instance;

INSTANCE# INSTANCE_NAME HOST_NAME STATUS
---------- ---------------- ------------------------------ ------------
2 devdb2 rac2.mycorpdomain.com OPEN

SQL> select failover_type,failover_method,failed_over from v$session where username='SYSTEM';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT BASIC YES

SQL>

將 CRM 服務重新定位到首選例項。恢復 devdb1 之後，CRM 服務不會自動重新定位到首選例項。您必須手動將服務重新定位到 devdb1。

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 440401920 bytes
Fixed Size 1219880 bytes
Variable Size 146801368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 14:01:37 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string devdb, CRM
SQL>

[oracle@rac1 ~]$ srvctl relocate service -d devdb -s crm -i devdb2 -t devdb1
[oracle@rac1 ~]$ export ORACLE_SID=devdb1
[oracle@rac1 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 14:04:26 2007

SQL> connect system/oracle@devdb1
Connected.
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string devdb, CRM
SQL> connect system/oracle@devdb2
Connected.
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string devdb
SQL>

11. 資料庫備份與恢復
使用 Oracle 恢復管理器 (RMAN) 備份和恢復 Oracle RAC 資料庫的過程與單例項資料庫的備份和恢復過程相同。

在本部分中，您將看到一個非常簡單的備份和恢復案例：

1. 執行完整的資料庫備份。
2. 在 test_d 表空間中建立 mytable 表。
3. 在 t1 時間，向 mytable 中插入第一個記錄。
4. 在 t2 時間，向 mytable 中插入第二個記錄。
5. 在 t3 時間，刪除 mytable 表。
6. 將 test_d 表空間恢復到某個時間點。
7. 驗證恢復結果。

執行完整的資料庫備份。

[oracle@rac1 ~]$ rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 10 14:06:55 2007

connected to target database: DEVDB (DBID=537639642)
using target database control file instead of recovery catalog

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> backup database plus archivelog delete input;

Starting backup at 10-SEP-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 instance=devdb1 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=632926208
input archive log thread=1 sequence=3 recid=2 stamp=632930975
input archive log thread=2 sequence=1 recid=3 stamp=632930990
channel ORA_DISK_1: starting piece 1 at 10-SEP-07
channel ORA_DISK_1: finished piece 1 at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/annnf0_tag20070910t140955_0.263.632931003 tag=TAG20070910T140955 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:27
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DG1/devdb/1_2_632909685.dbf recid=1 stamp=632926208
archive log filename=+DG1/devdb/1_3_632909685.dbf recid=2 stamp=632930975
archive log filename=+DG1/devdb/2_1_632909685.dbf recid=3 stamp=632930990
Finished backup at 10-SEP-07

Starting backup at 10-SEP-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DG1/devdb/datafile/system.256.632908791
input datafile fno=00003 name=+DG1/devdb/datafile/sysaux.257.632908815
input datafile fno=00005 name=+DG1/devdb/datafile/example.264.632909999
input datafile fno=00002 name=+DG1/devdb/datafile/undotbs1.258.632908829
input datafile fno=00006 name=+DG1/devdb/datafile/undotbs2.265.632912097
input datafile fno=00007 name=+DG1/devdb/datafile/test_d.269.632920161
input datafile fno=00004 name=+DG1/devdb/datafile/users.259.632908835
channel ORA_DISK_1: starting piece 1 at 10-SEP-07
channel ORA_DISK_1: finished piece 1 at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/nnndf0_tag20070910t141040_0.264.632931047 tag=TAG20070910T141040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:40
Finished backup at 10-SEP-07

Starting backup at 10-SEP-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=4 stamp=632931153
input archive log thread=2 sequence=2 recid=5 stamp=632931158
channel ORA_DISK_1: starting piece 1 at 10-SEP-07
channel ORA_DISK_1: finished piece 1 at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/annnf0_tag20070910t141240_0.265.632931169 tag=TAG20070910T141240 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DG1/devdb/1_4_632909685.dbf recid=4 stamp=632931153
archive log filename=+DG1/devdb/2_2_632909685.dbf recid=5 stamp=632931158
Finished backup at 10-SEP-07

Starting Control File and SPFILE Autobackup at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/autobackup/2007_09_10/s_632931181.266.632931189 comment=NONE
Finished Control File and SPFILE Autobackup at 10-SEP-07

RMAN>

在 test_d 表空間中建立 mytable 表。

[oracle@rac1 ~]$ sqlplus system/oracle@devdb2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 14:19:04 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create table mytable (coll number) tablespace test_d;

Table created.

在 t1 時間，向 mytable 中插入第一個記錄。

SQL> !date
Mon Sep 10 14:19:39 CST 2007

SQL> insert into mytable values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>

在 t2 時間，向 mytable 中插入第二個記錄。

SQL> !date
Mon Sep 10 14:21:12 CST 2007

SQL> insert into mytable values (2);

1 row created.

SQL> commit;

Commit complete.

SQL>

在 t3 時間，刪除 mytable 表。

SQL> !date
Mon Sep 10 14:21:56 CST 2007

SQL> drop table mytable;

Table dropped.

SQL>

將 test_d 表空間恢復到某個時間點。

為輔助資料庫建立輔助目錄。

[oracle@rac1 ~]$ mkdir /u01/app/oracle/aux

[oracle@rac1 ~]$ rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Sep 10 14:24:44 2007

connected to target database: DEVDB (DBID=537639642)
using target database control file instead of recovery catalog

RMAN> recover tablespace test_d
2> until time "to_date('10-Sep-2007 14:21:12','DD-MON-YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/app/oracle/aux';

Starting recover at 10-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 instance=devdb1 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1
tablespace UNDOTBS2

Creating automatic instance, with SID='jrzp'

initialization parameters used for automatic instance:
db_name=DEVDB
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DEVDB_jrzp
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/aux
control_files=/u01/app/oracle/aux/cntrl_tspitr_DEVDB_jrzp.f

starting up automatic instance DEVDB

Oracle instance started

Total System Global Area 201326592 bytes

Fixed Size 1218508 bytes
Variable Size 146802740 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "to_date('10-Sep-2007 14:21:12','DD-MON-YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 10-SEP-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=33 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/devdb/autobackup/2007_09_10/s_632931181.266.632931189
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/devdb/autobackup/2007_09_10/s_632931181.266.632931189 tag=TAG20070910T141301
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
output filename=/u01/app/oracle/aux/cntrl_tspitr_DEVDB_jrzp.f
Finished restore at 10-SEP-07

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('10-Sep-2007 14:21:12','DD-MON-YYYY HH24:MI:SS')";
plsql <<declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'TEST_D' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination filename for restore
set newname for clone datafile 6 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 7 to
"+DG1/devdb/datafile/test_d.269.632920161";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 6, 7;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 6 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 7 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "TEST_D", "SYSTEM", "UNDOTBS1", "UNDOTBS2" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TEST_D offline for recover

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-SEP-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs2_%u_.dbf
restoring datafile 00007 to +DG1/devdb/datafile/test_d.269.632920161
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/devdb/backupset/2007_09_10/nnndf0_tag20070910t141040_0.264.632931047
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/nnndf0_tag20070910t141040_0.264.632931047 tag=TAG20070910T141040
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:15
Finished restore at 10-SEP-07

datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=632932484 filename=/u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_system_3g9s6025_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=632932484 filename=/u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs1_3g9s6045_.dbf
datafile 6 switched to datafile copy
input datafile copy recid=8 stamp=632932484 filename=/u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs2_3g9s608p_.dbf

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 6 online

sql statement: alter database datafile 7 online

Starting recover at 10-SEP-07
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file +DG1/devdb/1_5_632909685.dbf
archive log thread 2 sequence 3 is already on disk as file +DG1/devdb/2_3_632909685.dbf
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=2 sequence=2
channel ORA_AUX_DISK_1: reading from backup piece +RECOVERYDEST/devdb/backupset/2007_09_10/annnf0_tag20070910t141240_0.265.632931169
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/annnf0_tag20070910t141240_0.265.632931169 tag=TAG20070910T141240
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_4_632909685.dbf thread=1 sequence=4
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_2_632909685.dbf thread=2 sequence=2
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_4_632909685.dbf recid=6 stamp=632932521
archive log filename=+DG1/devdb/1_5_632909685.dbf thread=1 sequence=5
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch2_2_632909685.dbf recid=7 stamp=632932522
archive log filename=+DG1/devdb/2_3_632909685.dbf thread=2 sequence=3
media recovery complete, elapsed time: 00:00:05
Finished recover at 10-SEP-07

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =") as sysdba" point_in_time_recover=y tablespaces=
TEST_D file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid ="/@ as sysdba" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TEST_D online";
sql "alter tablespace TEST_D offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

Export: Release 10.2.0.1.0 - Production on Mon Sep 10 14:37:48 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TEST_D ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MYTABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down

Import: Release 10.2.0.1.0 - Production on Mon Sep 10 14:40:19 2007

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. . importing table "MYTABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TEST_D online

sql statement: alter tablespace TEST_D offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/aux/cntrl_tspitr_DEVDB_jrzp.f deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_system_3g9s6025_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs1_3g9s6045_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_undotbs2_3g9s608p_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/datafile/o1_mf_temp_3g9sjrx9_.tmp deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/onlinelog/o1_mf_1_3g9sflq6_.log deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/onlinelog/o1_mf_2_3g9sfxox_.log deleted
auxiliary instance file /u01/app/oracle/aux/TSPITR_DEVDB_JRZP/onlinelog/o1_mf_5_3g9shdbo_.log deleted
Finished recover at 10-SEP-07

RMAN>

RMAN> backup tablespace test_d;

Starting backup at 10-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 instance=devdb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=+DG1/devdb/datafile/test_d.269.632920161
channel ORA_DISK_1: starting piece 1 at 10-SEP-07
channel ORA_DISK_1: finished piece 1 at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/backupset/2007_09_10/nnndf0_tag20070910t144401_0.267.632933047 tag=TAG20070910T144401 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 10-SEP-07

Starting Control File and SPFILE Autobackup at 10-SEP-07
piece handle=+RECOVERYDEST/devdb/autobackup/2007_09_10/s_632933059.268.632933067 comment=NONE
Finished Control File and SPFILE Autobackup at 10-SEP-07

RMAN> sql 'alter tablespace test_d online';

sql statement: alter tablespace test_d online

RMAN>

驗證恢復結果。

[oracle@rac1 ~]$ sqlplus system/oracle@devdb2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 10 14:47:13 2007

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select * from mytable;

COLL
----------
1

SQL>

12. 探索 Oracle 企業管理器 (OEM) 資料庫控制檯
Oracle 企業管理器資料庫控制檯提供了一個非常好的整合式綜合 GUI 介面，用於管理叢集資料庫環境。您可以在控制檯內執行幾乎所有的任務。

要訪問資料庫控制檯，請開啟 Web 瀏覽器並輸入下面的 URL。
以 sysman 使用者身份登入，並輸入您先前在資料庫安裝期間選擇的口令。

啟動和停止資料庫控制檯。

emctl status dbconsole
emctl stop dbconsole
emctl start dbconsole

更改語言設定。參考以前文章。

使用 VMware 在 Oracle Enterprise Linux 上安裝 Oracle RAC 10g (2)

相關文章