2012年2月 – Oracle and Linux

oracle 10.2.0.3 bug

2月 28,2012 留下评论

目前已经遇到了两个oracle 10.2.0.3的bug ，在这里跟大家分享下：
操作系统： HP-UX Itanium 11.31
bug 1
现象：
连接数据库的时候会提示无效用户名或者密码，在alert 日志里面有如下提示 ksvcreate: Process(m000) creation failed
Tue Feb 28 11:09:39 2012
Process m000 died, see its trace file
Tue Feb 28 11:09:39 2012
ksvcreate: Process(m000) creation failed
Tue Feb 28 11:10:40 2012
Process m000 died, see its trace file
Tue Feb 28 11:10:40 2012
ksvcreate: Process(m000) creation failed

去查看m000的trace file 有如下信息：
ksugetosstat failed: op = pstat_getprocessor, location = slsgetactive ()
ksugetosstat failed: op = pstat_getprocessor, location = slsgetactive ()

遇到此问题数据库需要重启。重启之后很难再现。建议升级到10.2.0.4 版本

bug 2

现象：
客户端通过plsql 连接数据库过于缓慢，有时甚至无法连接。在服务器端用sqlplus 连接也会出现hung的情况。在某一次连接到数据库系统后查询系统有很多等待事件，事件名称为：cursor: pin S wait on X ，发现之前hung住的回话都有该事件。判断是该事件引起了系统响应故障。
在数据库、操作系统上kill 掉产生该事件的会话，系统运行正常。

解决方法
建议升级到10.2.0.4 或者在参数文件里面加上：_kks_use_mutex_pin=FALSE

修改WordPress页面宽度小记

2月 25,2012 有5条评论 WordPress

周六下午，在家闲着没事儿，就尝试着动手将自己博客的页面宽度修改了下，修改之后的整个样子就是现在的这个新面目了，个人感觉还挺好的，看着至少比以前舒服多了。

以前的整个页面宽度不够，尤其是在文章正文中引用一些shell脚本或者是贴出SQL语句的执行结果时，看起来很糟糕，现在修改好了之后，简单测试了下，发现效果还不错，比较得意。

其实，主要修改的几个地方就是style.css这个样式文件中关于header,container,footer,content这几个地方。由于本人对CSS，php不太熟悉，就发挥小马过河的精神，一边改一边测，最终得以完成。

当然，如果作为读者的您或者各位网友路过的时候，如果您正好是用wordpress搭建的博客，又正好选择的是DeepBlue主题，再正好您也想修改您博客的页面宽度的话，那么不妨参照我修改后的这个style.css文件，也是本博客目前正在使用的style.css文件。

最后，关于wordpress还有很多可以客户化的地方，慢慢折腾呗~~~~~~

Oracle 10g RAC 配置物理dataguard系列4：switchover及功能测试

2月 22,2012 有2条评论 Dataguard, Oracle

在上一篇文章Oracle 10g RAC 配置物理dataguard系列3：物理备库配置步骤中，我们成功地给双节点的RAC主库成功配置Physical Standby database。在本篇中，我们开始对这套由双节点RAC主库+单实例Physical Standby database的dataguard环境做下述3点的功能测试。

主库创建数据文件，确认物理备库是否自动生成对应的数据文件？
主库做事务操作，在物理备库上确认是否可以看到对应的记录？
主备库完成一次switchover的Role transaction。

测试一：主库创建数据文件，备库确认能否看到对应的数据文件？

1 首先，在主库上创建一个测试表空间TEST：

[oracle@oracle-rac1 arch1]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 13:58:23 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      glndb
SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +ORADATA
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL> create tablespace TEST datafile size 5m autoextend on;

Tablespace created.

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

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL>

2 然后，在物理备库上查看是否生成该文件？

[oracle@ora10grac-dg arch2]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 14:50:43 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL>

发现，在备库上并没有看到我们预期的TEST表空间被自动创建出来，说明数据数据并没有同步到备库上来。其实，原因是备库并没有启动redo apply，也没有相应的MRP0后台进程。

3 其次，我们在备库上启动Redo Apply：

SQL> show parameter instance_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
instance_name                        string   pridb
SQL> show parameter db_unique_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_unique_name                       string   pridb
SQL> show parameter db_create_file

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_create_file_dest                  string   +ORADATA
SQL> show parameter db_file_name_convert

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_file_name_convert                 string   +ORADATA/glndb/, +ORADATA/pridb/
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

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

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         2 +ORADATA/pridb/datafile/test.274.775925977
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

7 rows selected.

SQL>

此时，我们可以看到一旦在备库启用redo apply之后，我们几乎可以立即看到备库上自动创建出预期的TEST表空间，说明测试成功！因为，我们主库上log_archive_dest_1参数是是：service=pridb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=pridb 同时，我们在物理备库的alert日志文件里也看到了下述信息：

Wed Feb 22 14:59:29 CST 2012
alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:29 CST 2012
Attempt to start background Managed Standby Recovery process (pridb)
MRP0 started with pid=14, OS id=18837
Wed Feb 22 14:59:29 CST 2012
MRP0: Background Managed Standby Recovery process started (pridb)
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 3 processes
Wed Feb 22 14:59:35 CST 2012
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 2 sequence 3297 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 2 Group 8 Seq 3297 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_8.266.775479229
Media Recovery Waiting for thread 1 sequence 7745 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7745 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_3.263.775479203
Wed Feb 22 14:59:35 CST 2012
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:38 CST 2012
Successfully added datafile 2 to media recovery
Datafile #2: '+ORADATA/pridb/datafile/test.274.775925977'

4 最后，我们在主库上删除TEST表空间后，同样在备库上看到TEST表空间自动被删除！至此，说明我们的Dataguard功能测试一完成。

主库删TEST：

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

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

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

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL>

备库确认：

Wed Feb 22 15:11:07 CST 2012
Recovery deleting file #2:'+ORADATA/pridb/datafile/test.274.775925977' from controlfile.
Deleted Oracle managed file +ORADATA/pridb/datafile/test.274.775925977
Recovery dropped tablespace 'TEST'
.....
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL>

测试二：主库创建测试数据，到备库上去查看，query是否同步？

1 主库创建测试数据：

SQL> conn human/hr
Connected.
SQL> create table test(id number,name varchar2(20));

Table created.

SQL> insert into test values(1,'WWW.OracleOnLinux.CN');

1 row created.

SQL> insert into test select * from test;

1 row created.

SQL> insert into test select * from test;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL>

2 备库确认：

SQL> select name,open_mode from v$database;

NAME                OPEN_MODE
------------------- ----------
GLNDB               MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY

SQL> conn human/hr
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL>

3 最后，发现备库停止Redo Apply后，并将数据库以read only方式打开后，我们可以看到数据已经同步到物理备库！说明功能测试二已经达到预期目的！

测试三：角色转换之switchover

1 物理备库置于MOUNT状态。在switchover过程中，如果目标库是物理备库的话，最好是将物理备库置于MOUNT状态，当然也可以置于OPEN READ ONLY方式，不过官方文档说处于OPEN READ ONLY方式时，可能会延长switchover的时间。

另：在physical standby database配置下如需switchover时，如果主库、物理备库都是RAC数据库时，需要先将主库的其它实例停止，只留一个实例运行；然后，将物理备库的其它实例停止，只留一个实例运行。即：如果主、备库都是RAC数据库的话，要确保主、备库均留一个实例运行。

在这里，我们的物理备库是单实例数据库，所以只需将该库置于MOUNT状态：

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL>

2 RAC主库停止glndb2实例，只留glndb1运行：

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    OFFLINE   OFFLINE               
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac1 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$

3 确认主库switchover状态。如果是to standby状态，说明一切正常。当然，也有可能是SESSIONS ACTIVE状态，不过也不要紧，这只是说明在主库上还有其他会话连接中，我们可以断开这些会话，或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326322 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE SESSIONS ACTIVE

SQL>

4 切换原RAC主库到备库角色。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> select * from v$log;
select * from v$log
              *
ERROR at line 1:
ORA-01507: database not mounted


SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database
                                                                                                *
ERROR at line 1:
ORA-01507: database not mounted


SQL> !ps  -ef |grep ora_
oracle   18271     1  0 14:04 ?        00:00:00 ora_o000_glndb1
oracle   22321     1  0 11:31 ?        00:00:01 ora_pmon_glndb1
oracle   22323     1  0 11:31 ?        00:00:00 ora_diag_glndb1
oracle   22325     1  0 11:31 ?        00:00:00 ora_psp0_glndb1
oracle   22332     1  0 11:31 ?        00:00:14 ora_lmon_glndb1
oracle   22334     1  0 11:31 ?        00:00:10 ora_lmd0_glndb1
oracle   22341     1  0 11:31 ?        00:00:07 ora_lms0_glndb1
oracle   22345     1  0 11:31 ?        00:00:07 ora_lms1_glndb1
oracle   22350     1  0 11:31 ?        00:00:02 ora_mman_glndb1
oracle   22356     1  0 11:31 ?        00:00:01 ora_dbw0_glndb1
oracle   22360     1  0 11:31 ?        00:00:02 ora_lgwr_glndb1
oracle   22370     1  0 11:31 ?        00:00:04 ora_ckpt_glndb1
oracle   22378     1  0 11:31 ?        00:00:10 ora_smon_glndb1
oracle   22391     1  0 11:31 ?        00:00:00 ora_reco_glndb1
oracle   22452     1  0 11:31 ?        00:00:03 ora_lck0_glndb1
oracle   22468     1  0 11:31 ?        00:00:00 ora_asmb_glndb1
oracle   22482     1  0 11:31 ?        00:00:00 ora_rbal_glndb1
oracle   30712     1  0 17:20 ?        00:00:00 ora_s001_glndb1
oracle   30730     1  0 17:20 ?        00:00:00 ora_o001_glndb1
oracle   30797     1  0 17:20 ?        00:00:00 ora_o002_glndb1
oracle   31131     1  0 17:21 ?        00:00:00 ora_d000_glndb1
oracle   31135     1  0 17:21 ?        00:00:00 ora_mmon_glndb1
oracle   31144     1  0 17:21 ?        00:00:00 ora_mmnl_glndb1
oracle   31683  3106  0 17:22 pts/2    00:00:00 /bin/bash -c ps  -ef |grep ora_
oracle   31685 31683  0 17:22 pts/2    00:00:00 grep ora_

SQL>

5 关闭原主库实例glndb1，重新启动glndb1到MOUNT状态：

SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show user;
USER is "SYS"
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL>

6 确认原备库switchover状态。如果是TO PRIMARY状态，说明一切正常。当然，也有可能是SESSIONS ACTIVE状态，不过也不要紧，这只是说明在备库上还有其他会话连接中，我们可以断开这些会话，或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL>select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL>

7 切换原备库至主库角色。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select open_mode,switchover_status,database_role from v$database;
select open_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted


SQL>

此时，原备库alert日志信息如下：

......
......
Wed Feb 22 17:35:52 CST 2012
alter database commit to switchover to primary
Wed Feb 22 17:35:52 CST 2012
ALTER DATABASE SWITCHOVER TO PRIMARY (pridb)
Wed Feb 22 17:35:52 CST 2012
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 447326597
Online log +ORADATA/pridb/onlinelog/group_1.269.775479753: Thread 1 Group 1 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_2.270.775479755: Thread 1 Group 2 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_4.271.775479755: Thread 2 Group 4 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_5.272.775479757: Thread 2 Group 5 was previously cleared
Standby became primary SCN: 447326595
Wed Feb 22 17:35:53 CST 2012
Switchover: Complete - Database shutdown required (pridb)
Completed: alter database commit to switchover to primary
Wed Feb 22 17:35:58 CST 2012
SUCCESS: diskgroup ORADATA was dismounted
Wed Feb 22 17:36:05 CST 2012
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Wed Feb 22 17:36:15 CST 2012
ARCH shutting down
ARC0: Archival stopped
Wed Feb 22 17:36:20 CST 2012
ARCH shutting down
ARC1: Archival stopped
.....
.....

8 完成原主库到新主库的角色转换。

这里需要注意：

①如果该原备库自从上次启动以来，从来没有以READ ONLY方式打开过，那么可以直接通过ALTER DATABASE OPEN命令来打开；
②如果该原备库自从上次启动以来，曾经以READ ONLY方式打开过，那么需要先SHUTDOWN，然后STARTUP。这里，我们的原物理备库复合第②中情况，需要先SHUTDOWN，然后STARTUP。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
READ WRITE SESSIONS ACTIVE      PRIMARY

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
         1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
         2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES          UNUSED                 0
         5          2          0   52428800          1 YES          UNUSED                 0

SQL>

9 在新备库上[原RAC主库]启动glndb2实例至MOUNT状态，并启用redo apply，确认数据是否能与新主库[原单实例备库]同步？

SQL> show user;
USER is "SYS"
SQL> show parameter instance;
ORA-01034: ORACLE not available


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             520094216 bytes
Database Buffers         1073741824 bytes
Redo Buffers               14680064 bytes
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    OFFLINE   OFFLINE               
ora....db1.srv application    OFFLINE   OFFLINE               
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    OFFLINE   OFFLINE               
ora....db2.srv application    OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ ps -ef | grep ora_
oracle   26363     1  0 17:42 ?        00:00:00 ora_pmon_glndb2
oracle   26370     1  0 17:42 ?        00:00:00 ora_diag_glndb2
oracle   26377     1  0 17:42 ?        00:00:00 ora_psp0_glndb2
oracle   26384     1  0 17:42 ?        00:00:00 ora_lmon_glndb2
oracle   26387     1  2 17:42 ?        00:00:01 ora_lmd0_glndb2
oracle   26393     1  2 17:42 ?        00:00:01 ora_lms0_glndb2
oracle   26402     1  2 17:42 ?        00:00:01 ora_lms1_glndb2
oracle   26406     1  6 17:42 ?        00:00:03 ora_mman_glndb2
oracle   26415     1  0 17:42 ?        00:00:00 ora_dbw0_glndb2
oracle   26417     1  0 17:42 ?        00:00:00 ora_lgwr_glndb2
oracle   26424     1  0 17:42 ?        00:00:00 ora_ckpt_glndb2
oracle   26426     1  0 17:42 ?        00:00:00 ora_smon_glndb2
oracle   26433     1  0 17:42 ?        00:00:00 ora_reco_glndb2
oracle   26435     1  0 17:43 ?        00:00:00 ora_cjq0_glndb2
oracle   26442     1  0 17:43 ?        00:00:00 ora_mmon_glndb2
oracle   26445     1  0 17:43 ?        00:00:00 ora_mmnl_glndb2
oracle   26451     1  0 17:43 ?        00:00:00 ora_d000_glndb2
oracle   26457     1  0 17:43 ?        00:00:00 ora_s000_glndb2
oracle   26506     1  0 17:43 ?        00:00:00 ora_lck0_glndb2
oracle   26706     1  0 17:43 ?        00:00:00 ora_pz99_glndb2
oracle   26860 26712  0 17:43 pts/1    00:00:00 grep ora_
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> alter database mount;

Database altered.

SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1       7750   52428800          1 YES CLEARING             447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2       3297   52428800          1 YES INACTIVE             447302887 2012/02/22 14:07:29
         5          2       3298   52428800          1 YES ACTIVE               447326021 2012/02/22 17:11:49

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          0   52428800          1 YES UNUSED               447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES UNUSED               447302887 2012/02/22 14:07:29
         5          2          0   52428800          1 YES UNUSED               447326021 2012/02/22 17:11:49

SQL>

新主库切换日志：

SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
     2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7751   52428800          1 NO           CURRENT        447336270 2012/02/22 20:42:28
     2          1       7750   52428800          1 YES          ACTIVE         447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7753   52428800          1 YES          INACTIVE       447336484 2012/02/22 20:47:35
     2          1       7754   52428800          1 NO           CURRENT        447336486 2012/02/22 20:47:39
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL>

最后，从新备库[原RAC主库]的alert日志中可以看到：

[root@oracle-rac1 ~]# tail -f /u01/app/oracle/admin/glndb/bdump/alert_glndb1.log 
 All grantable enqueues granted
Wed Feb 22 17:43:15 CST 2012
 LMS 1: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 LMS 0: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Wed Feb 22 20:38:32 CST 2012
Managed Standby Recovery starting Real Time Apply
Wed Feb 22 20:40:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:55 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'

同时，在新备库中可以看到下述信息

SQL> select thread#,sequence#,name,first_time,next_time,applied from v$archived_log where thread#=1 order by 1;
   THREAD#  SEQUENCE# NAME                                               FIRST_TIM NEXT_TIME APP
---------- ---------- -------------------------------------------------- --------- --------- ---
         1       7751 /home/oracle/arch1/ARC_1_0000007751_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7752 /home/oracle/arch1/ARC_1_0000007752_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7753 /home/oracle/arch1/ARC_1_0000007753_726057844.arc  22-FEB-12 22-FEB-12 YES

从上，可以确定我们的新备库同新主库已经同步！至此，我们的switchover角色切换成功！

Oracle数据字典表和动态性能视图学习之1：V$DATAGUARD_STATS

2月 20,2012 留下评论 Data Dictionary, Dataguard, Oracle

在管理和维护Oracle数据库的工作中，DBA不得不通过查询数据库的数据字典表、动态性能视图来获取数据库的相关信息。

本文通过介绍V$DATAGUARD_STATS这一动态性能视图来获取关于Physical standby database的相关信息。在一套Dataguard环境下，如果需要做failover Role Transition的话，建议先在备库上通过查询V$DATAGUARD_STATS视图来估算failover切换需要的时间(failover time=apply finish time+estimated startup time)。

首先来看两个查询：

查询1，来源于10g Physical standby database ：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select protection_mode,database_role,open_mode from v$database;

PROTECTION_MODE     DATABASE_ROLE     OPEN_MODE
------------------- ----------------- ---------
MAXIMUM PERFORMANCE PHYSICAL STANDBY  MOUNTED

SQL> select * from v$dataguard_stats;

NAME                    VALUE          UNIT                           TIME_COMPUTED
----------------------- -------------- ------------------------------ --------------------
apply finish time       +00 00:00:00.1 day(2) to second(1) interval   20-FEB-2012 14:05:18
apply lag               +00 00:00:15   day(2) to second(0) interval   20-FEB-2012 14:05:18
estimated startup time  161            second                         20-FEB-2012 14:05:18
standby has been open   Y                                             20-FEB-2012 14:05:18
transport lag           +00 00:00:07   day(2) to second(0) interval   20-FEB-2012 14:05:18

SQL>

查询2，来源于11g Physical standby database：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select protection_mode,database_role,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    OPEN_MODE
-------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select * from v$dataguard_stats;

NAME                    VALUE            UNIT                          TIME_COMPUTED        DATUM_TIME
----------------------- ---------------- ----------------------------- -------------------- -------------------
transport lag           +00 00:00:00     day(2) to second(0) interval  02/20/2012 14:07:37  02/20/2012 14:07:36
apply lag               +00 00:00:00     day(2) to second(0) interval  02/20/2012 14:07:37  02/20/2012 14:07:36
apply finish time       +00 00:00:00.000 day(2) to second(3) interval  02/20/2012 14:07:37
estimated startup time  36               second                        02/20/2012 14:07:37

SQL>

然后，依据上面的输出结果来简单介绍一下V$DATAGUARD_STATS这一动态性能视图的相关信息：

在官方文档上，关于V$DATAGUARD_STATS是这样描述的：该动态性能视图显示出在主库上产生了多少重做日志数据，但是还没有被备库所应用。所以，通过查询该视图可以基本确定如果万一主库出现崩溃的话，备库上将丢失多少重做日志数据。我们可以在一套Dataguard环境下的任一备库的实例上从该视图里获取相关信息，然而，在主库的实例上查询该视图返回的信息都将是空。也就是说，只可以从备库的实例上查询V$DATAGUARD_STATS，从主库实例上是看不到任何有用信息的。

接下来，解释一下各个字段的值信息：

NAME：

apply lag,该值表示在通过在备库上应用主库传递过来的重做日志与出库同步所延迟的时间。APPLY LAG: Amount of time that the application of redo data on the standby database lags behind the primary database.从查询中可以看到第1个延迟15秒，第2个延迟0秒。说明该11g的备库应用重做日志已经与该主库完全同步了。
transport lag,该值表示在单位时间内主库上产生的重做日志还没有传输到备库上，或者主库上产生的重做日志还没有被备库所应用。从查询中看到第1个10g备库上的日志传输延迟7秒，而第2个11g备库的日志传输延迟为0。
apply finish time,该值表示在备库上完成应用重做日志所需要的时间。从第1个查询中看到完成应用重做日志还需要0.1秒，第2个查询中则为0，因为已经完全同步。
estimated startup time,该值表示启动和打开物理备库所需要的时间，该字段不是适用于逻辑备库。 An estimate of the time needed to start and open the database.
standby has been open,该值表示物理备库自从上次启动以来，是否以OPEN READ ONLY方式打开过？该参数值如果是Y，现在需要做FAILOVER，那么就需要先将该物理备库shutdown然后以OPEN READ WRITE方式打开。从第1个查询中，看到该物理备库如果做FAILOVER，那么就需要shutdown—>startup open read write；第2个查询中则没有该记录，因为11g的dataguard可以一边OPEN READ ONLY，一边执行redo apply,也就是11g 的ACTIVE Dataguard。

VALUE：给出各个参数的值。如第1个查询中的，apply finish time值为+00 00:00:00.1，说明该物理备库需要0.1秒的时间来完成应用剩余的重做日志数据。

UNIT：各个参数的时间单元。

TIME_COMPUTED：物理备库上估算各个参数的本地时间。

DATUM_TIME：在物理备库上获取元数据来估算 APPLY LAG 和 TRANSPORT LAG 这两个参数值的本地时间。如果从多次查询中看到该时间值对应的APPLY LAG 和 TRANSPORT LAG 这两个参数值保持不变的话，那么就说明该物理备库已经停止从主库接收到重做数据！该字段是11g中新出现的。

最后，这是在学习dataguard时，新了解和学习的动态性能视图，个人觉得比较有用，就根据自己的理解简单记录之。

Oracle 10g RAC 配置物理dataguard系列3：物理备库配置步骤

2月 17,2012 有6条评论 Dataguard, Oracle, RAC

在上一篇文章Oracle 10g RAC 配置物理dataguard系列2：备库安装、升级oracle软件、配置ASM实例中，我们已经在物理备库上顺利安装、升级oracle软件同RAC主库版本一致的10.2.0.5，并且配置好ASM实例，这些准备工作一切就绪之后。接下来，我们需要在物理备库机器上一步一步的配置physical dataguard。

1 主库执行RMAN备份的准备工作：

其实，在配置physical standby dataguard时，主库必须要置于归档模式，且建议将数据库也置于FORCE LOGGING模式。在这里，我们在系列一文章里查看主库基本信息中，已经看到这些已经准备就绪。否则，需要将主库置于归档+FORCE LOGGING模式，在这里就省略这一步骤，当然我们也可以通过下述得以确认。在主库的第一个节点上执行：

[root@oracle-rac1 ~]# su - oracle
[oracle@oracle-rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? glndb
[oracle@oracle-rac1 ~]$ export ORACLE_SID=glndb1
[oracle@oracle-rac1 ~]$ env | grep ORA
ORACLE_SID=glndb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac1 ~]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac2 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac1 ~]$

[oracle@oracle-rac1 ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 16 10:27:39 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select name,log_mode,force_logging from gv$database;

NAME      LOG_MODE     FOR
--------- ------------ ---
GLNDB     ARCHIVELOG   YES
GLNDB     ARCHIVELOG   YES

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      glndb 
SQL> set line 160
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       7706   52428800          1 NO  CURRENT              445956133 16-FEB-12
         2          1       7705   52428800          1 YES INACTIVE             445918203 15-FEB-12
         4          2       3273   52428800          1 NO  CURRENT              445956150 16-FEB-12
         5          2       3272   52428800          1 YES INACTIVE             445817248 15-FEB-12

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch1
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      defer
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest_2='location=/home/oracle/arch1 valid_for=(all_logfiles,all_roles) db_unique_name=glndb' sid='glndb1';

System altered.

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch1 va
                                                 lid_for=(all_logfiles,all_role
                                                 s) db_unique_name=glndb
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      defer
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL>

节点2连接SQL*PLUS登录数据库：

SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      glndb
SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch2
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      DEFER
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest_2='location=/home/oracle/arch2 valid_for=(all_logfiles,all_roles) db_unique_name=glndb' sid='glndb2';

System altered.

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch2 va
                                                 lid_for=(all_logfiles,all_role
                                                 s) db_unique_name=glndb
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      DEFER
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL>

我们分别修改了两个节点的归档路径，节点1的归档指向/home/oracle/arch1，节点2的归档指向/home/oracle/arch2,这两个路径事先已经建好，这一步骤必须完成！

由于我们的RAC主库归档路径都各自指向本地存储，并没有放到共享存储上，所以，这里我们在用RMAN做备份的时候，需要连接到每个实例上进行备份。我们备份的路径分别指向每台机器的/oracle_backup，且在每个节点上也事先建好/oracle_backup路径，且确保oracle用户有对该路径的路写权限！

接下来，在节点1上执行备份，注意下述的rman备份命令中的GLNDB2连接字符串是连接到节点2的实例上，已经在节点1的/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora事先配置好。

[oracle@oracle-rac1 ~]$ cat /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

GLNDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.36)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
      (INSTANCE_NAME = glndb2)
    )
  )

GLNDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.35)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
      (INSTANCE_NAME = glndb1)
    )
  )

GLNDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.35)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.36)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
    )
  )
[oracle@oracle-rac1 ~]$

2 节点1上开始执行主库的RMAN备份：

[oracle@oracle-rac1 ~]$ env | grep ORA
ORACLE_SID=glndb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac1 ~]$ rman

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Feb 16 11:01:19 2012

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

connected to target database: GLNDB (DBID=3995745524)

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_glndb1.f'; # default

RMAN> list archivelog all;

using target database control file instead of recovery catalog
specification does not match any archive log in the recovery catalog

RMAN> list backup;


RMAN> report schema;

Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    6039     SYSTEM               ***     +ORADATA/glndb/datafile/system.260.726057851
3    1290     SYSAUX               ***     +ORADATA/glndb/datafile/sysaux.262.726057863
4    150      UNDOTBS2             ***     +ORADATA/glndb/datafile/undotbs2.264.726057869
5    2        USERS                ***     +ORADATA/glndb/datafile/users.265.726057871
6    30       UNDOTBS3             ***     +ORADATA/glndb/datafile/undotbs3.268.774870843
169  2        EXAMPLE              ***     +ORADATA/glndb/datafile/example.435.756233417

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    10       TEMP                 32767       +ORADATA/glndb/tempfile/temp.263.726057865

RMAN> run {
2> allocate channel c1 device type disk format '/oracle_backup/full_%U' connect sys/oracle@glndb1;
3> allocate channel c2 device type disk format '/oracle_backup/full_%U' connect sys/oracle@glndb2;
4> backup database plus archivelog;
5> release channel c1;
6> release channel c2;
7> }

allocated channel: c1
channel c1: sid=396 instance=glndb1 devtype=DISK

allocated channel: c2
channel c2: sid=399 instance=glndb2 devtype=DISK


Starting backup at 2012/02/16 11:07:39
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7706 recid=2116 stamp=775393668
channel c1: starting piece 1 at 2012/02/16 11:07:50
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=2 sequence=3273 recid=2115 stamp=775393663
channel c2: starting piece 1 at 2012/02/16 11:07:46
channel c1: finished piece 1 at 2012/02/16 11:07:52
piece handle=/oracle_backup/full_1mn3f4c6_1_1 tag=TAG20120216T110749 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c2: finished piece 1 at 2012/02/16 11:07:55
piece handle=/oracle_backup/full_1nn3f4c2_1_1 tag=TAG20120216T110749 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:09
Finished backup at 2012/02/16 11:07:59

Starting backup at 2012/02/16 11:08:00
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=+ORADATA/glndb/datafile/system.260.726057851
input datafile fno=00005 name=+ORADATA/glndb/datafile/users.265.726057871
input datafile fno=00169 name=+ORADATA/glndb/datafile/example.435.756233417
channel c1: starting piece 1 at 2012/02/16 11:08:04
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00003 name=+ORADATA/glndb/datafile/sysaux.262.726057863
input datafile fno=00004 name=+ORADATA/glndb/datafile/undotbs2.264.726057869
input datafile fno=00006 name=+ORADATA/glndb/datafile/undotbs3.268.774870843
channel c2: starting piece 1 at 2012/02/16 11:08:00
channel c2: finished piece 1 at 2012/02/16 11:10:48
piece handle=/oracle_backup/full_1pn3f4cg_1_1 tag=TAG20120216T110803 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:48
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current control file in backupset
channel c2: starting piece 1 at 2012/02/16 11:10:58
channel c2: finished piece 1 at 2012/02/16 11:11:06
piece handle=/oracle_backup/full_1qn3f4hp_1_1 tag=TAG20120216T110803 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:17
channel c2: starting full datafile backupset
channel c2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel c2: starting piece 1 at 2012/02/16 11:11:07
channel c2: finished piece 1 at 2012/02/16 11:11:08
piece handle=/oracle_backup/full_1rn3f4ib_1_1 tag=TAG20120216T110803 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 2012/02/16 11:13:19
piece handle=/oracle_backup/full_1on3f4ck_1_1 tag=TAG20120216T110803 comment=NONE
channel c1: backup set complete, elapsed time: 00:05:15
Finished backup at 2012/02/16 11:13:19

Starting backup at 2012/02/16 11:13:19
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7707 recid=2117 stamp=775393999
channel c1: starting piece 1 at 2012/02/16 11:13:23
channel c2: starting archive log backupset
channel c2: specifying archive log(s) in backup set
input archive log thread=2 sequence=3274 recid=2118 stamp=775393996
channel c2: starting piece 1 at 2012/02/16 11:13:19
channel c1: finished piece 1 at 2012/02/16 11:13:24
piece handle=/oracle_backup/full_1sn3f4mj_1_1 tag=TAG20120216T111323 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 2012/02/16 11:13:20
piece handle=/oracle_backup/full_1tn3f4mf_1_1 tag=TAG20120216T111323 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
Finished backup at 2012/02/16 11:13:24

released channel: c1

released channel: c2

RMAN> list backup;


List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
48      26.34M     DISK        00:00:01     2012/02/16 11:07:51
        BP Key: 48   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110749
        Piece Name: /oracle_backup/full_1mn3f4c6_1_1

  List of Archived Logs in backup set 48
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    7706    445956133  2012/02/16 01:33:55 446024208  2012/02/16 11:07:43

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
49      10.56M     DISK        00:00:07     2012/02/16 11:07:53
        BP Key: 49   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110749
        Piece Name: /oracle_backup/full_1nn3f4c2_1_1

  List of Archived Logs in backup set 49
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    3273    445956150  2012/02/16 01:33:53 446024205  2012/02/16 11:07:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
50      Full    1.08G      DISK        00:02:45     2012/02/16 11:10:45
        BP Key: 50   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110803
        Piece Name: /oracle_backup/full_1pn3f4cg_1_1
  List of Datafiles in backup set 50
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3       Full 446024239  2012/02/16 11:08:00 +ORADATA/glndb/datafile/sysaux.262.726057863
  4       Full 446024239  2012/02/16 11:08:00 +ORADATA/glndb/datafile/undotbs2.264.726057869
  6       Full 446024239  2012/02/16 11:08:00 +ORADATA/glndb/datafile/undotbs3.268.774870843

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
51      Full    15.30M     DISK        00:00:17     2012/02/16 11:11:06
        BP Key: 51   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110803
        Piece Name: /oracle_backup/full_1qn3f4hp_1_1
  Control File Included: Ckp SCN: 446024474    Ckp time: 2012/02/16 11:10:49

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
52      Full    80.00K     DISK        00:00:01     2012/02/16 11:11:08
        BP Key: 52   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110803
        Piece Name: /oracle_backup/full_1rn3f4ib_1_1
  SPFILE Included: Modification time: 2012/02/16 10:47:30

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
53      Full    5.81G      DISK        00:05:09     2012/02/16 11:13:13
        BP Key: 53   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T110803
        Piece Name: /oracle_backup/full_1on3f4ck_1_1
  List of Datafiles in backup set 53
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 446024221  2012/02/16 11:08:04 +ORADATA/glndb/datafile/system.260.726057851
  5       Full 446024221  2012/02/16 11:08:04 +ORADATA/glndb/datafile/users.265.726057871
  169     Full 446024221  2012/02/16 11:08:04 +ORADATA/glndb/datafile/example.435.756233417

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
54      46.50K     DISK        00:00:00     2012/02/16 11:13:19
        BP Key: 54   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T111323
        Piece Name: /oracle_backup/full_1tn3f4mf_1_1

  List of Archived Logs in backup set 54
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    3274    446024205  2012/02/16 11:07:37 446024635  2012/02/16 11:13:16

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
55      80.00K     DISK        00:00:00     2012/02/16 11:13:23
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20120216T111323
        Piece Name: /oracle_backup/full_1sn3f4mj_1_1

  List of Archived Logs in backup set 55
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    7707    446024208  2012/02/16 11:07:43 446024632  2012/02/16 11:13:19

RMAN>

到各个节点的/oracle_backup路径下确认备份文件信息：

节点1：

[oracle@oracle-rac1 ~]$ cd /oracle_backup/ && ls -lh
total 5.9G
-rw-r----- 1 oracle oinstall  27M Feb 16 11:07 full_1mn3f4c6_1_1
-rw-r----- 1 oracle oinstall 5.9G Feb 16 11:13 full_1on3f4ck_1_1
-rw-r----- 1 oracle oinstall  81K Feb 16 11:13 full_1sn3f4mj_1_1
[oracle@oracle-rac1 oracle_backup]$

节点2：

[oracle@oracle-rac2 oracle_backup]$ ll -h
total 1.2G
-rw-r----- 1 oracle oinstall  11M Feb 16 11:07 full_1nn3f4c2_1_1
-rw-r----- 1 oracle oinstall 1.1G Feb 16 11:10 full_1pn3f4cg_1_1
-rw-r----- 1 oracle oinstall  16M Feb 16 11:11 full_1qn3f4hp_1_1
-rw-r----- 1 oracle oinstall  96K Feb 16 11:11 full_1rn3f4ib_1_1
-rw-r----- 1 oracle oinstall  47K Feb 16 11:13 full_1tn3f4mf_1_1
[oracle@oracle-rac2 oracle_backup]$

3 节点1上生成物理备库控制文件：

RMAN> backup device type disk format '/oracle_backup/standby_ctl_%U' current controlfile for standby;

Starting backup at 2012/02/16 11:21:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=411 instance=glndb1 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 2012/02/16 11:21:26
channel ORA_DISK_1: finished piece 1 at 2012/02/16 11:21:33
piece handle=/oracle_backup/standby_ctl_1un3f55k_1_1 tag=TAG20120216T112123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 2012/02/16 11:21:33

RMAN>

确认物理备库控制文件信息：

[oracle@oracle-rac1 ~]$ cd /oracle_backup/ && ll
total 6140020
-rw-r----- 1 oracle oinstall   27619328 Feb 16 11:07 full_1mn3f4c6_1_1
-rw-r----- 1 oracle oinstall 6237437952 Feb 16 11:13 full_1on3f4ck_1_1
-rw-r----- 1 oracle oinstall      82432 Feb 16 11:13 full_1sn3f4mj_1_1
-rw-r----- 1 oracle oinstall   16056320 Feb 16 11:21 standby_ctl_1un3f55k_1_1
[oracle@oracle-rac1 oracle_backup]$

4 在主库创建物理备库的初始化参数文件：

[oracle@oracle-rac1 ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 16 11:23:53 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL> create pfile='/oracle_backup/initpridb.ora' from spfile;

File created.

SQL> !
[oracle@oracle-rac1 ~]$ cd /oracle_backup/ && ll -h
total 5.9G
-rw-r----- 1 oracle oinstall  27M Feb 16 11:07 full_1mn3f4c6_1_1
-rw-r----- 1 oracle oinstall 5.9G Feb 16 11:13 full_1on3f4ck_1_1
-rw-r----- 1 oracle oinstall  81K Feb 16 11:13 full_1sn3f4mj_1_1
-rw-r--r-- 1 oracle oinstall 2.3K Feb 16 11:25 initpridb.ora
-rw-r----- 1 oracle oinstall  16M Feb 16 11:21 standby_ctl_1un3f55k_1_1
[oracle@oracle-rac1 oracle_backup]$

5 分别FTP两个节点上/oracle_backup下的所有文件到备库机器172.16.0.202上的/oracle_backup路径下，备库机器的/oracle_backup路径在系列2中创建磁盘的那一节里已经提到过，现在就派上了用场！

节点1上：

[oracle@oracle-rac1 oracle_backup]$ pwd
/oracle_backup
[oracle@oracle-rac1 oracle_backup]$ ll
total 6140028
-rw-r----- 1 oracle oinstall   27619328 Feb 16 11:07 full_1mn3f4c6_1_1
-rw-r----- 1 oracle oinstall 6237437952 Feb 16 11:13 full_1on3f4ck_1_1
-rw-r----- 1 oracle oinstall      82432 Feb 16 11:13 full_1sn3f4mj_1_1
-rw-r--r-- 1 oracle oinstall       2324 Feb 16 11:25 initpridb.ora
-rw-r----- 1 oracle oinstall   16056320 Feb 16 11:21 standby_ctl_1un3f55k_1_1
[oracle@oracle-rac1 oracle_backup]$ scp * oracle@172.16.0.202:/oracle_backup
The authenticity of host '172.16.0.202 (172.16.0.202)' can't be established.
RSA key fingerprint is 87:6f:ef:3c:d2:e9:6b:c2:11:36:3d:75:22:77:e2:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.0.202' (RSA) to the list of known hosts.
oracle@172.16.0.202's password: 
full_1mn3f4c6_1_1                                                                                 100%   26MB   2.6MB/s   00:10    
full_1on3f4ck_1_1                                                                                 100% 5948MB   2.0MB/s   49:29    
full_1sn3f4mj_1_1                                                                                 100%   81KB  80.5KB/s   00:00    
initpridb.ora                                                                                     100% 2324     2.3KB/s   00:00    
standby_ctl_1un3f55k_1_1                                                                          100%   15MB  15.3MB/s   00:01    
[oracle@oracle-rac1 oracle_backup]$

节点2上：

[oracle@oracle-rac2 ~]$ cd /oracle_backup/
[oracle@oracle-rac2 oracle_backup]$ ll
total 1155632
-rw-r----- 1 oracle oinstall   11072000 Feb 16 11:07 full_1nn3f4c2_1_1
-rw-r----- 1 oracle oinstall 1154891776 Feb 16 11:10 full_1pn3f4cg_1_1
-rw-r----- 1 oracle oinstall   16056320 Feb 16 11:11 full_1qn3f4hp_1_1
-rw-r----- 1 oracle oinstall      98304 Feb 16 11:11 full_1rn3f4ib_1_1
-rw-r----- 1 oracle oinstall      48128 Feb 16 11:13 full_1tn3f4mf_1_1
[oracle@oracle-rac2 oracle_backup]$ scp * oracle@172.16.0.202:/oracle_backup
The authenticity of host '172.16.0.202 (172.16.0.202)' can't be established.
RSA key fingerprint is 87:6f:ef:3c:d2:e9:6b:c2:11:36:3d:75:22:77:e2:f3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.0.202' (RSA) to the list of known hosts.
oracle@172.16.0.202's password: 
full_1nn3f4c2_1_1                                                                                 100%   11MB   5.3MB/s   00:02    
full_1pn3f4cg_1_1                                                                                 100% 1101MB   1.8MB/s   10:15    
full_1qn3f4hp_1_1                                                                                 100%   15MB   7.7MB/s   00:02    
full_1rn3f4ib_1_1                                                                                 100%   96KB  96.0KB/s   00:00    
full_1tn3f4mf_1_1                                                                                 100%   47KB  47.0KB/s   00:00    
[oracle@oracle-rac2 oracle_backup]$

物理备库机器上确认：

[oracle@ora10grac-dg ~]$ cd /oracle_backup/
[oracle@ora10grac-dg oracle_backup]$ ll
total 7295636
-rw-r----- 1 oracle oinstall   27619328 Feb 16 11:32 full_1mn3f4c6_1_1
-rw-r----- 1 oracle oinstall   11072000 Feb 16 11:35 full_1nn3f4c2_1_1
-rw-r----- 1 oracle oinstall 6237437952 Feb 16 12:22 full_1on3f4ck_1_1
-rw-r----- 1 oracle oinstall 1154891776 Feb 16 11:46 full_1pn3f4cg_1_1
-rw-r----- 1 oracle oinstall   16056320 Feb 16 11:46 full_1qn3f4hp_1_1
-rw-r----- 1 oracle oinstall      98304 Feb 16 11:46 full_1rn3f4ib_1_1
-rw-r----- 1 oracle oinstall      82432 Feb 16 12:22 full_1sn3f4mj_1_1
-rw-r----- 1 oracle oinstall      48128 Feb 16 11:46 full_1tn3f4mf_1_1
-rw-r--r-- 1 oracle oinstall       2324 Feb 16 12:22 initpridb.ora
drwx------ 2 oracle oinstall      16384 Feb 15 13:52 lost+found
-rw-r----- 1 oracle oinstall   16056320 Feb 16 12:22 standby_ctl_1un3f55k_1_1
[oracle@ora10grac-dg oracle_backup]$

6 在备库上使用orapwd命令创建口令文件，要确保sys用户或者具有sysdba系统权限用户的口令要同主库两个节点上sys用户的口令一致。为简单起见，也可以在主库两个节点上分别修改sys用户口令为一致，然后，直接拷贝主库任意节点的口令文件到备库$ORACLE_HOME/dbs路径下，并且重命名为orapwSID，其中SID为备库上实例名，这里备库SID为pridb,，所以口令文件名应该为orapwpridb。

这里，在RAC两个节点上分别修改sys用户口令，然后，cp节点1下的口令文件到物理备库172.16.0.202的$ORACLE_HOME/dbs路径下，然后在备库上重命名该文件：

节点1：

[oracle@oracle-rac1 dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@oracle-rac1 dbs]$ ll
total 15716
-rw-rw---- 1 oracle oinstall     1544 Feb 14 16:58 hc_glndb1.dat
-rw-r--r-- 1 oracle oinstall    12920 May  3  2001 initdw.ora
-rw-r--r-- 1 oracle oinstall       40 Dec 31 01:28 initglndb1.ora
-rw-r----- 1 oracle oinstall     8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle oinstall     2048 Feb 16 11:05 orapwglndb1
-rw-r----- 1 oracle oinstall 16007168 Feb 16 11:21 snapcf_glndb1.f
[oracle@oracle-rac1 dbs]$ scp orapwglndb1 172.16.0.202:/u01/app/oracle/dbs
oracle@172.16.0.202's password: 
orapwglndb1                                                                                       100% 2048     2.0KB/s   00:00    
[oracle@oracle-rac1 dbs]$

物理备库：

[oracle@ora10grac-dg dbs]$ env | grep ORA
ORACLE_SID=pridb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@ora10grac-dg dbs]$ pwd
/u01/app/oracle/dbs
[oracle@ora10grac-dg dbs]$ ll
total 52
-rw-rw---- 1 oracle oinstall   577 Feb 15 16:12 ab_+ASM.dat
-rw-rw---- 1 oracle oinstall  1544 Feb 15 16:12 hc_+ASM.dat
-rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-rw---- 1 oracle oinstall    24 Feb 15 16:12 lk+ASM
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:12 orapw+ASM
-rw-r----- 1 oracle oinstall  2048 Feb 16 14:17 orapwglndb1
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:18 spfile+ASM.ora
[oracle@ora10grac-dg dbs]$ mv orapwglndb1 orapwpridb
[oracle@ora10grac-dg dbs]$ ll
total 52
-rw-rw---- 1 oracle oinstall   577 Feb 15 16:12 ab_+ASM.dat
-rw-rw---- 1 oracle oinstall  1544 Feb 15 16:12 hc_+ASM.dat
-rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-rw---- 1 oracle oinstall    24 Feb 15 16:12 lk+ASM
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:12 orapw+ASM
-rw-r----- 1 oracle oinstall  2048 Feb 16 14:17 orapwpridb
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:18 spfile+ASM.ora
[oracle@ora10grac-dg dbs]$

7 配置主备库3台机器上的tnsnames.ora文件，使之三个$ORACLE_HOME/network/admin/tnsnames.ora文件内容完全一致！修改后，如下：

[oracle@ora10grac-dg admin]$ pwd
/u01/app/oracle/network/admin
[oracle@ora10grac-dg admin]$ ll
total 16
-rw-r--r-- 1 oracle oinstall  468 Feb 15 09:16 listener.ora
drwxr-x--- 2 oracle oinstall 4096 Feb 14 16:28 samples
-rw-r--r-- 1 oracle oinstall  172 Dec 26  2003 shrept.lst
-rw-r--r-- 1 oracle oinstall 1017 Feb 16 14:31 tnsnames.ora
[oracle@ora10grac-dg admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

GLNDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.36)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
      (INSTANCE_NAME = glndb2)
    )
  )

GLNDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.35)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
      (INSTANCE_NAME = glndb1)
    )
  )

GLNDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.35)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.36)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = glndb)
    )
  )

PRIDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pridb)
    )
  )
[oracle@ora10grac-dg admin]$

8 确保主、备库3台机器上的listener工作正常，并且在备库上利用上述的tnsnames.ora文件来分别访问两个节点：

[oracle@ora10grac-dg admin]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 16 15:06:40 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn human/hr@glndb
Connected.
SQL> conn human/hr@glndb1
Connected.
SQL> conn human/hr@glndb2
Connected.
SQL> conn sys/oracle@glndb as sysdba
Connected.
SQL> conn sys/oracle@glndb1 as sysdba
Connected.
SQL> conn sys/oracle@glndb2 as sysdba
Connected.
SQL>

9 在备库上修改初始化参数文件，去掉所有的RAC参数，修改后如下：

[oracle@ora10grac-dg dbs]$ pwd
/u01/app/oracle/dbs
[oracle@ora10grac-dg dbs]$ ll
total 52
-rw-rw---- 1 oracle oinstall   577 Feb 15 16:12 ab_+ASM.dat
-rw-rw---- 1 oracle oinstall  1544 Feb 15 16:12 hc_+ASM.dat
-rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-rw---- 1 oracle oinstall    24 Feb 15 16:12 lk+ASM
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:12 orapw+ASM
-rw-r----- 1 oracle oinstall  2048 Feb 16 14:17 orapwpridb
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:18 spfile+ASM.ora
[oracle@ora10grac-dg dbs]$ cp /oracle_backup/initpridb.ora .
[oracle@ora10grac-dg dbs]$ ll
total 56
-rw-rw---- 1 oracle oinstall   577 Feb 15 16:12 ab_+ASM.dat
-rw-rw---- 1 oracle oinstall  1544 Feb 15 16:12 hc_+ASM.dat
-rw-r--r-- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r--r-- 1 oracle oinstall  2324 Feb 16 15:16 initpridb.ora
-rw-rw---- 1 oracle oinstall    24 Feb 15 16:12 lk+ASM
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:12 orapw+ASM
-rw-r----- 1 oracle oinstall  2048 Feb 16 14:17 orapwpridb
-rw-r----- 1 oracle oinstall  1536 Feb 15 16:18 spfile+ASM.ora
[oracle@ora10grac-dg dbs]$ cat initpridb.ora 
*.audit_file_dest='/u01/app/admin/pridb/adump'
*.background_dump_dest='/u01/app/admin/pridb/bdump'
*.cluster_database=false
*.compatible='10.2.0.5.0'
*.control_files='+ORADATA/pridb/controlfile'
*.core_dump_dest='/u01/app/admin/pridb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+ORADATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_files=300
*.db_name='glndb'
*.db_unique_name='pridb'
*.disk_asynch_io=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=glndbXDB)'
*.event='10298 trace name context forever, level 32'
*.global_names=FALSE
*.job_queue_processes=10
*.log_archive_config='dg_config=(glndb,pridb)'
*.log_archive_dest_2='location=/home/oracle/arch2 valid_for=(all_logfiles,all_roles) db_unique_name=pridb'
*.log_archive_dest_1='service=glndb1 lgwr sync valid_for=(online_logfiles,primary_roles) db_unique_name=glndb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='ARC_%t_%S_%r.arc'
*.nls_length_semantics='CHAR'
*.open_cursors=300
*.pga_aggregate_target=1034944512
*.processes=400
*.remote_login_passwordfile='exclusive'
*.sessions=445
*.sga_target=1610612736
*.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS3'
*.user_dump_dest='/u01/app/admin/pridb/udump'
*.db_file_name_convert='+ORADATA/glndb/','+ORADATA/pridb/'
*.log_file_name_convert='+ORADATA/glndb/','+ORADATA/pridb/'
*.standby_file_management='auto'
*.fal_server='glndb1','glndb2'
*.fal_client='pridb'
*.service_names='pridb'
[oracle@ora10grac-dg dbs]$

依据上述的参数文件配置，在物理备库上建立相应的路径：

[oracle@ora10grac-dg ~]$ cd /u01/app/admin/
[oracle@ora10grac-dg admin]$ pwd
/u01/app/admin
[oracle@ora10grac-dg admin]$ ll
total 4
drwxr-x--- 6 oracle oinstall 4096 Feb 15 16:12 +ASM
[oracle@ora10grac-dg admin]$ mkdir -p pridb/{a,b,c,u}dump
[oracle@ora10grac-dg admin]$ ll
total 8
drwxr-x--- 6 oracle oinstall 4096 Feb 15 16:12 +ASM
drwxr-xr-x 6 oracle oinstall 4096 Feb 16 15:52 pridb
[oracle@ora10grac-dg admin]$ cd pridb/
[oracle@ora10grac-dg glndb]$ ll
total 16
drwxr-xr-x 2 oracle oinstall 4096 Feb 16 15:52 adump
drwxr-xr-x 2 oracle oinstall 4096 Feb 16 15:52 bdump
drwxr-xr-x 2 oracle oinstall 4096 Feb 16 15:52 cdump
drwxr-xr-x 2 oracle oinstall 4096 Feb 16 15:52 udump 
[oracle@ora10grac-dg glndb~]$ mkdir -p /home/oracle/arch2
[oracle@ora10grac-dg glndb~]$

同时，依据上述的参数文件配置，在物理备库的ASM实例中上建立相应的路径：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ export ORACLE_SID=+ASM
[oracle@ora10grac-dg ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Feb 16 16:25:00 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select name from v$asm_diskgroup;

NAME
--------------------------------------------------------------------------------
ORADATA

SQL> alter diskgroup oradata add directory '+ORADATA/PRIDB';

Diskgroup altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora10grac-dg ~]$ asmcmd
ASMCMD> ls
ORADATA/
ASMCMD> cd oradata
ASMCMD> ls
PRIDB/
ASMCMD> exit
[oracle@ora10grac-dg ~]$

10 启动备库至nomount状态：

 [root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ env | grep ORA
ORACLE_SID=pridb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@ora10grac-dg ~]$ ps -ef | grep asm_
oracle    4074     1  0 Feb16 ?        00:00:05 asm_pmon_+ASM
oracle    4076     1  0 Feb16 ?        00:00:01 asm_psp0_+ASM
oracle    4078     1  0 Feb16 ?        00:00:01 asm_mman_+ASM
oracle    4080     1  0 Feb16 ?        00:00:01 asm_dbw0_+ASM
oracle    4082     1  0 Feb16 ?        00:00:01 asm_lgwr_+ASM
oracle    4084     1  0 Feb16 ?        00:00:01 asm_ckpt_+ASM
oracle    4086     1  0 Feb16 ?        00:00:01 asm_smon_+ASM
oracle    4088     1  0 Feb16 ?        00:00:01 asm_rbal_+ASM
oracle    4090     1  0 Feb16 ?        00:00:04 asm_gmon_+ASM
oracle    7431  7400  0 09:45 pts/2    00:00:00 grep asm_
[oracle@ora10grac-dg ~]$ ps -ef | grep ora_
oracle    7435  7400  0 09:45 pts/2    00:00:00 grep ora_
[oracle@ora10grac-dg ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 17 09:45:50 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
SQL> !
[oracle@ora10grac-dg ~]$ ps -ef | grep ora_
oracle    7440     1  0 09:45 ?        00:00:00 ora_pmon_pridb
oracle    7442     1  0 09:45 ?        00:00:00 ora_psp0_pridb
oracle    7444     1  4 09:45 ?        00:00:01 ora_mman_pridb
oracle    7446     1  0 09:45 ?        00:00:00 ora_dbw0_pridb
oracle    7448     1  0 09:45 ?        00:00:00 ora_lgwr_pridb
oracle    7450     1  0 09:45 ?        00:00:00 ora_ckpt_pridb
oracle    7452     1  0 09:45 ?        00:00:00 ora_smon_pridb
oracle    7454     1  0 09:45 ?        00:00:00 ora_reco_pridb
oracle    7456     1  0 09:45 ?        00:00:00 ora_cjq0_pridb
oracle    7458     1  0 09:45 ?        00:00:00 ora_mmon_pridb
oracle    7460     1  0 09:45 ?        00:00:00 ora_mmnl_pridb
oracle    7462     1  0 09:45 ?        00:00:00 ora_d000_pridb
oracle    7464     1  0 09:45 ?        00:00:00 ora_s000_pridb
oracle    7491  7466  0 09:46 pts/4    00:00:00 grep ora_
[oracle@ora10grac-dg ~]$

11 备库恢复：

首先，RMAN连接物理备库，恢复备库控制文件：

[oracle@ora10grac-dg ~]$ rlwrap rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Feb 17 10:03:25 2012

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

connected to target database: glndb (not mounted)

RMAN> restore standby controlfile from '/oracle_backup/standby_ctl_1un3f55k_1_1';

Starting restore at 2012/02/17 10:03:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=431 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+ORADATA/pridb/control.ctl
Finished restore at 2012/02/17 10:03:44

RMAN> host;

[oracle@ora10grac-dg ~]$ export ORACLE_SID=+ASM
[oracle@ora10grac-dg ~]$ asmcmd
ASMCMD> ls
ORADATA/
ASMCMD> cd oradata
ASMCMD> ls
PRIDB/
ASMCMD> cd pridb
ASMCMD> ls
CONTROLFILE/
control.ctl
ASMCMD> cd controlfile
ASMCMD> ls
current.256.775476221
ASMCMD> exit
[oracle@ora10grac-dg ~]$ export ORACLE_SID=pridb
[oracle@ora10grac-dg ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 17 10:06:01 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set line 160
SQL> show parameter control

NAME                                 TYPE                                         VALUE
------------------------------------ -------------------------------------------- ------------------------------
control_file_record_keep_time        integer                                      7
control_files                        string                                       +ORADATA/pridb/control.ctl
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ora10grac-dg ~]$ exit
exit
host command complete

RMAN>

然后，在确认物理备库控制文件恢复成功后，将物理备库置于MOUNT状态，接下来restore物理备库：

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> restore database;
5> release channel c1;
6> release channel c2;
7> }

allocated channel: c1
channel c1: sid=431 devtype=DISK

allocated channel: c2
channel c2: sid=430 devtype=DISK

Starting restore at 2012/02/17 10:14:18

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +ORADATA/pridb/datafile/sysaux.262.726057863
restoring datafile 00004 to +ORADATA/pridb/datafile/undotbs2.264.726057869
restoring datafile 00006 to +ORADATA/pridb/datafile/undotbs3.268.774870843
channel c1: reading from backup piece /oracle_backup/full_1pn3f4cg_1_1
channel c2: starting datafile backupset restore
channel c2: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +ORADATA/pridb/datafile/system.260.726057851
restoring datafile 00005 to +ORADATA/pridb/datafile/users.265.726057871
restoring datafile 00169 to +ORADATA/pridb/datafile/example.435.756233417
channel c2: reading from backup piece /oracle_backup/full_1on3f4ck_1_1
channel c1: restored backup piece 1
piece handle=/oracle_backup/full_1pn3f4cg_1_1 tag=TAG20120216T110803
channel c1: restore complete, elapsed time: 00:19:47
channel c2: restored backup piece 1
piece handle=/oracle_backup/full_1on3f4ck_1_1 tag=TAG20120216T110803
channel c2: restore complete, elapsed time: 00:23:23
Finished restore at 2012/02/17 10:37:50

released channel: c1

released channel: c2

RMAN> exit


Recovery Manager complete.
[oracle@ora10grac-dg ~]$

其次，SQL*PLUS连接物理备库，确认状态：

[oracle@ora10grac-dg ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 17 10:39:58 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile
  2  union
  3  select name from v$controlfile
  4  union
  5  select member from v$logfile
  6  ;

NAME
--------------------------------------------------------------------------------
+ORADATA/pridb/control.ctl
+ORADATA/pridb/datafile/example.261.775476929
+ORADATA/pridb/datafile/sysaux.257.775476887
+ORADATA/pridb/datafile/system.258.775476897
+ORADATA/pridb/datafile/undotbs2.259.775476909
+ORADATA/pridb/datafile/undotbs3.262.775476939
+ORADATA/pridb/datafile/users.260.775476921
+ORADATA/pridb/onlinelog/group_1.257.726057849
+ORADATA/pridb/onlinelog/group_2.258.726057849
+ORADATA/pridb/onlinelog/group_4.266.726058757
+ORADATA/pridb/onlinelog/group_5.267.726058757

11 rows selected.
SQL> set line 160
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database;

      DBID NAME      SWITCHOVER DB_UNIQUE_ DATABASE_ROLE        OPEN_MODE  CURRENT_SCN
---------- --------- ---------- ---------- -------------------- ---------- -----------
3995745524 GLNDB     TO PRIMARY pridb      PHYSICAL STANDBY     MOUNTED      446025625

SQL>

最后，至此，物理备库已经成功恢复！

12 在备库上创建standby logfile:

创建之前：

SQL> col status for a10
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
         1          1       7708   52428800          1 NO           CURRENT        446024632 2012/02/16 11:13:19
         2          1       7707   52428800          1 YES          INACTIVE       446024208 2012/02/16 11:07:43
         4          2       3275   52428800          1 NO           CURRENT        446024635 2012/02/16 11:13:16
         5          2       3274   52428800          1 YES          INACTIVE       446024205 2012/02/16 11:07:37

SQL> col member for a50
SQL> select * from v$logfile;

    GROUP# STATUS     TYPE                         MEMBER                                             IS_RECOVERY_
---------- ---------- ---------------------------- -------------------------------------------------- ------------
         1            ONLINE                       +ORADATA/pridb/onlinelog/group_1.257.726057849     NO
         2            ONLINE                       +ORADATA/pridb/onlinelog/group_2.258.726057849     NO
         4            ONLINE                       +ORADATA/pridb/onlinelog/group_4.266.726058757     NO
         5            ONLINE                       +ORADATA/pridb/onlinelog/group_5.267.726058757     NO

SQL> select * from v$standby_log;

no rows selected

SQL>

开始创建：

SQL> alter database add standby logfile thread 1 group 3 size 50m,group 6 size 50m,group 7 size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;

Database altered.

SQL>

创建之后：

SQL> select * from v$standby_log;

    GROUP# DBID          THREAD#  SEQUENCE#      BYTES       USED ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME          LAST_CHANGE# LAST_TIME
---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ------------- ------------------- ------------ -------------------
         3 UNASSIGNED          1          0   52428800        512 YES          UNASSIGNED             0                                0
         6 UNASSIGNED          1          0   52428800        512 YES          UNASSIGNED             0                                0
         7 UNASSIGNED          1          0   52428800        512 YES          UNASSIGNED             0                                0
         8 UNASSIGNED          2          0   52428800        512 YES          UNASSIGNED             0                                0
         9 UNASSIGNED          2          0   52428800        512 YES          UNASSIGNED             0                                0
        10 UNASSIGNED          2          0   52428800        512 YES          UNASSIGNED             0                                0

6 rows selected.

SQL> select * from v$logfile;

    GROUP# STATUS     TYPE                         MEMBER                                             IS_RECOVERY_
---------- ---------- ---------------------------- -------------------------------------------------- ------------
         1            ONLINE                       +ORADATA/pridb/onlinelog/group_1.257.726057849     NO
         2            ONLINE                       +ORADATA/pridb/onlinelog/group_2.258.726057849     NO
         3            STANDBY                      +ORADATA/pridb/onlinelog/group_3.263.775479203     NO
         4            ONLINE                       +ORADATA/pridb/onlinelog/group_4.266.726058757     NO
         5            ONLINE                       +ORADATA/pridb/onlinelog/group_5.267.726058757     NO
         6            STANDBY                      +ORADATA/pridb/onlinelog/group_6.264.775479205     NO
         7            STANDBY                      +ORADATA/pridb/onlinelog/group_7.265.775479205     NO
         8            STANDBY                      +ORADATA/pridb/onlinelog/group_8.266.775479229     NO
         9            STANDBY                      +ORADATA/pridb/onlinelog/group_9.267.775479229     NO
        10            STANDBY                      +ORADATA/pridb/onlinelog/group_10.268.775479231    NO

10 rows selected.

SQL>

13 备库启动redo apply：

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select sequence#,name,first_time,next_time,applied from v$archived_log;

no rows selected

SQL> col switchover_status for a16
SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database;

      DBID NAME      SWITCHOVER_STATU DB_UNIQUE_ DATABASE_ROLE        OPEN_MODE  CURRENT_SCN
---------- --------- ---------------- ---------- -------------------- ---------- -----------
3995745524 GLNDB     SESSIONS ACTIVE  pridb      PHYSICAL STANDBY     MOUNTED      446025625

SQL> select sequence#,name,first_time,next_time,applied from v$archived_log;

no rows selected

SQL>

发现，这时候，备库根本没有应用归档日志，且从alert日志里可以看到下述信息：

alter database add standby logfile thread 1 group 3 size 50m,group 6 size 50m,group 7 size 50m
Fri Feb 17 10:53:26 CST 2012
Completed: alter database add standby logfile thread 1 group 3 size 50m,group 6 size 50m,group 7 size 50m
Fri Feb 17 10:53:49 CST 2012
alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m
Fri Feb 17 10:53:51 CST 2012
Completed: alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m
Fri Feb 17 11:02:26 CST 2012
alter database recover managed standby database using current logfile disconnect from session
Fri Feb 17 11:02:26 CST 2012
Attempt to start background Managed Standby Recovery process (pridb)
MRP0 started with pid=16, OS id=7915
Fri Feb 17 11:02:26 CST 2012
MRP0: Background Managed Standby Recovery process started (pridb)
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 3 processes
Fri Feb 17 11:02:33 CST 2012
Waiting for all non-current ORLs to be archived...
Fri Feb 17 11:02:33 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+ORADATA/pridb/onlinelog/group_1.257.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_1.257.726057849
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_1.257.726057849' does not exist
Fri Feb 17 11:02:33 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+ORADATA/pridb/onlinelog/group_1.257.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_1.257.726057849
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_1.257.726057849' does not exist
Clearing online redo logfile 1 +ORADATA/pridb/onlinelog/group_1.257.726057849
Clearing online log 1 of thread 1 sequence number 7708
Fri Feb 17 11:02:33 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+ORADATA/pridb/onlinelog/group_1.257.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_1.257.726057849
ORA-15173: entry 'group_1.257.726057849' does not exist in directory 'onlinelog'
Deleted Oracle managed file +ORADATA/pridb/onlinelog/group_1.257.726057849
Fri Feb 17 11:02:33 CST 2012
Completed: alter database recover managed standby database using current logfile disconnect from session
Fri Feb 17 11:02:35 CST 2012
Clearing online redo logfile 1 complete
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+ORADATA/pridb/onlinelog/group_2.258.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_2.258.726057849
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_2.258.726057849' does not exist
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+ORADATA/pridb/onlinelog/group_2.258.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_2.258.726057849
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_2.258.726057849' does not exist
Clearing online redo logfile 2 +ORADATA/pridb/onlinelog/group_2.258.726057849
Clearing online log 2 of thread 1 sequence number 7707
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '+ORADATA/pridb/onlinelog/group_2.258.726057849'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_2.258.726057849
ORA-15173: entry 'group_2.258.726057849' does not exist in directory 'onlinelog'
Deleted Oracle managed file +ORADATA/pridb/onlinelog/group_2.258.726057849
Clearing online redo logfile 2 complete
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+ORADATA/pridb/onlinelog/group_4.266.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_4.266.726058757
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_4.266.726058757' does not exist
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+ORADATA/pridb/onlinelog/group_4.266.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_4.266.726058757
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_4.266.726058757' does not exist
Clearing online redo logfile 4 +ORADATA/pridb/onlinelog/group_4.266.726058757
Clearing online log 4 of thread 2 sequence number 3275
Fri Feb 17 11:02:35 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '+ORADATA/pridb/onlinelog/group_4.266.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_4.266.726058757
ORA-15173: entry 'group_4.266.726058757' does not exist in directory 'onlinelog'
Deleted Oracle managed file +ORADATA/pridb/onlinelog/group_4.266.726058757
Clearing online redo logfile 4 complete
Fri Feb 17 11:02:36 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 5 of thread 2
ORA-00312: online log 5 thread 2: '+ORADATA/pridb/onlinelog/group_5.267.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_5.267.726058757
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_5.267.726058757' does not exist
Fri Feb 17 11:02:36 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 5 of thread 2
ORA-00312: online log 5 thread 2: '+ORADATA/pridb/onlinelog/group_5.267.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_5.267.726058757
ORA-15012: ASM file '+ORADATA/pridb/onlinelog/group_5.267.726058757' does not exist
Clearing online redo logfile 5 +ORADATA/pridb/onlinelog/group_5.267.726058757
Clearing online log 5 of thread 2 sequence number 3274
Fri Feb 17 11:02:36 CST 2012
Errors in file /u01/app/admin/pridb/bdump/pridb_mrp0_7915.trc:
ORA-00313: open failed for members of log group 5 of thread 2
ORA-00312: online log 5 thread 2: '+ORADATA/pridb/onlinelog/group_5.267.726058757'
ORA-17503: ksfdopn:2 Failed to open file +ORADATA/pridb/onlinelog/group_5.267.726058757
ORA-15173: entry 'group_5.267.726058757' does not exist in directory 'onlinelog'
Deleted Oracle managed file +ORADATA/pridb/onlinelog/group_5.267.726058757
Clearing online redo logfile 5 complete
Media Recovery Waiting for thread 1 sequence 7707

从上述的错误信息，可以看到备库整等待主库第一个节点上的日志号为7707的日志。其它的错误信息提示是指备库第一次置于recover状态时，备库将生成对应的online redo log，这是正常的，可不必理会。

14 调整主库参数，使主库上的归档日志可以顺利传递到备库上去，其中也包括在主库备份完成到备库置于recover状态之间主库产生的归档日志，及archive log gap。

[oracle@oracle-rac1 ~]$ env | grep ORA
ORACLE_SID=glndb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac1 ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 17 11:14:17 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       7710   52428800          1 YES INACTIVE             446188257 17-FEB-12
         2          1       7711   52428800          1 NO  CURRENT              446237897 17-FEB-12
         4          2       3277   52428800          1 YES INACTIVE             446178255 17-FEB-12
         5          2       3278   52428800          1 NO  CURRENT              446238085 17-FEB-12

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch1 va
                                                 lid_for=(all_logfiles,all_role
                                                 s) db_unique_name=glndb
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      defer
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> alter system set log_archive_dest_1='service=pridb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=pridb' sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1='enable';

System altered.

SQL> alter system set log_archive_config='dg_config=(glndb,pridb)' sid='*';

System altered.

SQL> show parameter log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      dg_config=(glndb,pridb)
log_archive_dest                     string
log_archive_dest_1                   string      service=pridb lgwr sync valid_
                                                 for=(online_logfiles,primary_r
                                                 ole) db_unique_name=pridb
log_archive_dest_10                  string
log_archive_dest_2                   string      location=/home/oracle/arch1 va
                                                 lid_for=(all_logfiles,all_role
                                                 s) db_unique_name=glndb
log_archive_dest_3                   string
log_archive_dest_4                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_format                   string      ARC_%t_%S_%r.arc
log_archive_local_first              boolean     TRUE
log_archive_max_processes            integer     2
log_archive_min_succeed_dest         integer     1
log_archive_start                    boolean     FALSE
log_archive_trace                    integer     0
SQL> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_client                           string
fal_server                           string
SQL> alter system set fal_server='pridb' sid='*';

System altered.

SQL> alter system set fal_client='glndb1' sid='*';

System altered.

SQL> alter system set fal_client='glndb1' sid='glndb1';

System altered.

SQL> alter system set fal_client='glndb2' sid='glndb2';

System altered.

SQL>

配置完主库的归档参数之后，在主库节点1上尝试切换日志，到备库上查看是否可以正常接收主库传递过来的归档日志？

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       7710   52428800          1 YES INACTIVE             446188257 17-FEB-12
         2          1       7711   52428800          1 NO  CURRENT              446237897 17-FEB-12
         4          2       3277   52428800          1 YES INACTIVE             446178255 17-FEB-12
         5          2       3278   52428800          1 NO  CURRENT              446238085 17-FEB-12

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1       7712   52428800          1 NO  CURRENT              446238670 17-FEB-12
         2          1       7711   52428800          1 YES ACTIVE               446237897 17-FEB-12
         4          2       3277   52428800          1 YES INACTIVE             446178255 17-FEB-12
         5          2       3278   52428800          1 NO  CURRENT              446238085 17-FEB-12

SQL> !
[oracle@oracle-rac1 ~]$ cd /home/oracle/arch1/ && ll -h
total 134M
-rw-rw---- 1 oracle oinstall  27M Feb 16 11:07 ARC_1_0000007706_726057844.arc
-rw-r----- 1 oracle oinstall  79K Feb 16 11:13 ARC_1_0000007707_726057844.arc
-rw-rw---- 1 oracle oinstall  44M Feb 16 22:06 ARC_1_0000007708_726057844.arc
-rw-rw---- 1 oracle oinstall  44M Feb 17 04:17 ARC_1_0000007709_726057844.arc
-rw-rw---- 1 oracle oinstall  21M Feb 17 11:23 ARC_1_0000007710_726057844.arc
-rw-rw---- 1 oracle oinstall 228K Feb 17 11:30 ARC_1_0000007711_726057844.arc
[oracle@oracle-rac1 arch1]$

可以看到，主库节点1已经产生7711归档的本地日志，而备库上却没有接收到任何归档日志：

[oracle@ora10grac-dg arch2]$ pwd
/home/oracle/arch2
[oracle@ora10grac-dg arch2]$ ll
total 0
[oracle@ora10grac-dg arch2]$

并且物理备库的告警日志暂时也没有任何提示信息！！在这一步，尝试了N久，才发现备库的监听没有启动，导致主库无法连接物理备库，自然，主库的归档也就无法传递到物理备库。具体，可见下面的记录信息，首先在节点1上，在SQL*PLUS里尝试用pridb网络服务名连接物理备库时报错，没有监听，然后到物理备库启动监听，在物理备库上重新执行应用归档，最后问题的以解决！

主库尝试连接备库报错：

SQL> conn sys/oracle@pridb as sysdba
ERROR:
ORA-12541: TNS:no listener


Warning: You are no longer connected to ORACLE.
SQL>

备库监听未启，然后启动：

[oracle@ora10grac-dg arch2]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 17-FEB-2012 11:41:59

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10grac-dg)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@ora10grac-dg arch2]$ lsnrctl start 

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 17-FEB-2012 11:42:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /u01/app/oracle/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /u01/app/oracle/network/admin/listener.ora
Log messages written to /u01/app/oracle/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10grac-dg)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10grac-dg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                17-FEB-2012 11:42:06
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10grac-dg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora10grac-dg arch2]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 17-FEB-2012 11:42:27

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10grac-dg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                17-FEB-2012 11:42:06
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10grac-dg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "pridb" has 1 instance(s).
  Instance "pridb", status READY, has 1 handler(s) for this service...
Service "pridb_XPT" has 1 instance(s).
  Instance "pridb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora10grac-dg arch2]$

备库重启启动redo apply：

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

过一段时间后，看到下述信息：

SQL> select sequence#,name,first_time,next_time,applied from v$archived_log;

 SEQUENCE# NAME                                               FIRST_TIME          NEXT_TIME           APPLIED
---------- -------------------------------------------------- ------------------- ------------------- ------------
      7708 /home/oracle/arch2/ARC_1_0000007708_726057844.arc  2012/02/16 11:13:19 2012/02/16 22:05:46 YES
      7709 /home/oracle/arch2/ARC_1_0000007709_726057844.arc  2012/02/16 22:05:46 2012/02/17 04:17:07 YES
      7710 /home/oracle/arch2/ARC_1_0000007710_726057844.arc  2012/02/17 04:17:07 2012/02/17 11:23:14 YES
      7711 /home/oracle/arch2/ARC_1_0000007711_726057844.arc  2012/02/17 11:23:14 2012/02/17 11:30:44 YES
      7707 /home/oracle/arch2/ARC_1_0000007707_726057844.arc  2012/02/16 11:07:43 2012/02/16 11:13:19 YES
      3274 /home/oracle/arch2/ARC_2_0000003274_726057844.arc  2012/02/16 11:07:37 2012/02/16 11:13:16 YES
      3275 /home/oracle/arch2/ARC_2_0000003275_726057844.arc  2012/02/16 11:13:16 2012/02/16 23:03:02 YES
      3276 /home/oracle/arch2/ARC_2_0000003276_726057844.arc  2012/02/16 23:03:02 2012/02/17 03:00:36 YES
      3277 /home/oracle/arch2/ARC_2_0000003277_726057844.arc  2012/02/17 03:00:36 2012/02/17 11:25:24 YES
      3278 /home/oracle/arch2/ARC_2_0000003278_726057844.arc  2012/02/17 11:25:24 2012/02/17 11:41:04 NO

10 rows selected.

SQL> select process,status,client_process,group#,thread#,sequence# from v$managed_standby;

PROCESS                              STATUS          CLIENT_PROCESS                   GROUP    THREAD#  SEQUENCE#
------------------------------------ --------------- -------------------------------- ----- ---------- ----------
ARCH                                 CONNECTED       ARCH                             N/A            0          0
ARCH                                 CONNECTED       ARCH                             N/A            0          0
MRP0                                 WAIT_FOR_LOG    N/A                              N/A            1       7712
RFS                                  IDLE            UNKNOWN                          N/A            0          0
RFS                                  IDLE            UNKNOWN                          N/A            0          0

SQL> !
[oracle@ora10grac-dg ~]$ cd /home/oracle/arch2/ && ll
total 210424
-rw-r----- 1 oracle oinstall    80896 Feb 17 11:46 ARC_1_0000007707_726057844.arc
-rw-r----- 1 oracle oinstall 45309952 Feb 17 11:45 ARC_1_0000007708_726057844.arc
-rw-r----- 1 oracle oinstall 45311488 Feb 17 11:46 ARC_1_0000007709_726057844.arc
-rw-r----- 1 oracle oinstall 21249024 Feb 17 11:46 ARC_1_0000007710_726057844.arc
-rw-r----- 1 oracle oinstall   233472 Feb 17 11:46 ARC_1_0000007711_726057844.arc
-rw-r----- 1 oracle oinstall    46592 Feb 17 11:47 ARC_2_0000003274_726057844.arc
-rw-r----- 1 oracle oinstall 47448576 Feb 17 11:47 ARC_2_0000003275_726057844.arc
-rw-r----- 1 oracle oinstall 45309952 Feb 17 11:48 ARC_2_0000003276_726057844.arc
-rw-r----- 1 oracle oinstall  9995776 Feb 17 11:48 ARC_2_0000003277_726057844.arc
-rw-r----- 1 oracle oinstall   214528 Feb 17 11:49 ARC_2_0000003278_726057844.arc
[oracle@ora10grac-dg arch2]$

此时，备库alert日志文件里也看到成功应用归档的信息如下：

Completed: alter database recover managed standby database using current logfile disconnect from session
Fri Feb 17 11:44:24 CST 2012
Using STANDBY_ARCHIVE_DEST parameter default value as /home/oracle/arch2
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 8107
RFS[1]: Identified database type as 'physical standby'
Fri Feb 17 11:44:24 CST 2012
RFS LogMiner: Client disabled from further notification
Fri Feb 17 11:44:26 CST 2012
Fetching gap sequence in thread 1, gap sequence 7707-7707
Fri Feb 17 11:44:59 CST 2012
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 8112
RFS[2]: Identified database type as 'physical standby'
Fri Feb 17 11:45:21 CST 2012
RFS[1]: Archived Log: '/home/oracle/arch2/ARC_1_0000007708_726057844.arc'
Fri Feb 17 11:46:04 CST 2012
RFS[1]: Archived Log: '/home/oracle/arch2/ARC_1_0000007709_726057844.arc'
RFS[1]: Archived Log: '/home/oracle/arch2/ARC_1_0000007710_726057844.arc'
RFS[1]: Archived Log: '/home/oracle/arch2/ARC_1_0000007711_726057844.arc'
RFS[1]: Archived Log: '/home/oracle/arch2/ARC_1_0000007707_726057844.arc'
Fri Feb 17 11:46:29 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_1_0000007707_726057844.arc
Media Recovery Waiting for thread 2 sequence 3274
Fetching gap sequence in thread 2, gap sequence 3274-3274
Fri Feb 17 11:47:02 CST 2012
RFS[2]: Archived Log: '/home/oracle/arch2/ARC_2_0000003274_726057844.arc'
Fri Feb 17 11:47:30 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_2_0000003274_726057844.arc
Media Recovery Log /home/oracle/arch2/ARC_1_0000007708_726057844.arc
Fri Feb 17 11:47:40 CST 2012
Media Recovery Waiting for thread 2 sequence 3275 (in transit)
Fri Feb 17 11:47:51 CST 2012
RFS[2]: Archived Log: '/home/oracle/arch2/ARC_2_0000003275_726057844.arc'
Fri Feb 17 11:47:55 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_2_0000003275_726057844.arc
Fri Feb 17 11:48:49 CST 2012
RFS[2]: Archived Log: '/home/oracle/arch2/ARC_2_0000003276_726057844.arc'
Fri Feb 17 11:48:59 CST 2012
RFS[2]: Archived Log: '/home/oracle/arch2/ARC_2_0000003277_726057844.arc'
RFS[2]: Archived Log: '/home/oracle/arch2/ARC_2_0000003278_726057844.arc'
Fri Feb 17 11:49:12 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_1_0000007709_726057844.arc
Fri Feb 17 11:49:38 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_2_0000003276_726057844.arc
Fri Feb 17 11:50:01 CST 2012
Media Recovery Log /home/oracle/arch2/ARC_2_0000003277_726057844.arc
Media Recovery Log /home/oracle/arch2/ARC_1_0000007710_726057844.arc
Media Recovery Log /home/oracle/arch2/ARC_1_0000007711_726057844.arc
Media Recovery Log /home/oracle/arch2/ARC_2_0000003278_726057844.arc
Media Recovery Waiting for thread 1 sequence 7712

看到这些信息后，才说明主库所有节点的归档日志已经全部传递到物理备库，且物理备库也成功应用了这些归档！

15 主库创建standby logfile。在主库上创建standby logfile的目的和作用是，将来在做switchover时，RAC主库变成新standby备库，而原来的物理备库变成新主库时，新standby备库可以顺利接受并应用新主库的归档日志。当然，也别忘记了要修改主库的standby_file_management、db_file_name_convert、log_file_name_convert这三个重要的参数！

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  +ORADATA/glndb/onlinelog/group_1.257.726057849     NO
         2         ONLINE  +ORADATA/glndb/onlinelog/group_2.258.726057849     NO
         4         ONLINE  +ORADATA/glndb/onlinelog/group_4.266.726058757     NO
         5         ONLINE  +ORADATA/glndb/onlinelog/group_5.267.726058757     NO

SQL> select * from v$standby_log;

no rows selected

SQL> alter database add standby logfile thread 1 group 3 size 50m,group 6 size 50m,group 7 size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;

Database altered.

SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#      BYTES       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM LAST_CHANGE# LAST_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- ------------- --------- ------------ ---------
         3 UNASSIGNED                                        1          0   52428800        512 YES UNASSIGNED             0  0
         6 UNASSIGNED                                        1          0   52428800        512 YES UNASSIGNED             0  0
         7 UNASSIGNED                                        1          0   52428800        512 YES UNASSIGNED             0  0
         8 UNASSIGNED                                        2          0   52428800        512 YES UNASSIGNED             0  0
         9 UNASSIGNED                                        2          0   52428800        512 YES UNASSIGNED             0  0
        10 UNASSIGNED                                        2          0   52428800        512 YES UNASSIGNED             0  0

6 rows selected.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         1         ONLINE  +ORADATA/glndb/onlinelog/group_1.257.726057849     NO
         2         ONLINE  +ORADATA/glndb/onlinelog/group_2.258.726057849     NO
         3         STANDBY +ORADATA/glndb/onlinelog/group_3.261.775490705     NO
         4         ONLINE  +ORADATA/glndb/onlinelog/group_4.266.726058757     NO
         5         ONLINE  +ORADATA/glndb/onlinelog/group_5.267.726058757     NO
         6         STANDBY +ORADATA/glndb/onlinelog/group_6.430.775490705     NO
         7         STANDBY +ORADATA/glndb/onlinelog/group_7.444.775490707     NO
         8         STANDBY +ORADATA/glndb/onlinelog/group_8.431.775491149     NO
         9         STANDBY +ORADATA/glndb/onlinelog/group_9.445.775491149     NO
        10         STANDBY +ORADATA/glndb/onlinelog/group_10.446.775491151    NO

10 rows selected.

SQL> 
SQL> show parameter convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string
SQL> show parameter standby_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL> alter system set standby_file_management='auto' sid='*';

System altered.
SQL> alter system set db_file_name_convert='+ORADATA/pridb/','+ORADATA/glndb/'  sid='*' scope=spfile;
alter system set db_file_name_convert='+ORADATA/pridb/','+ORADATA/glndb/'  sid='*' scope=spfile
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option


SQL> alter system set log_file_name_convert='+ORADATA/pridb/','+ORADATA/glndb/' sid='*' scope=spfile;
alter system set log_file_name_convert='+ORADATA/pridb/','+ORADATA/glndb/' sid='*' scope=spfile
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>

发现，最后两个参数不能修改！百思不得其解，不得已，只好通过创建PFILE，在PFILE里修改这两个参数，然后再利用该PFILE来创建SPFILE的方式来修改之！

终于，终于，经过漫长的配置，我们的dataguard成功创建！关于dataguard的功能测试，将在下一篇文章里介绍！依然敬请期待！

Oracle 10g RAC 配置物理dataguard系列2：备库安装、升级oracle软件、配置ASM实例

2月 14,2012 有2条评论 Experience, Oracle

在上一篇文章Oracle 10g RAC 配置物理dataguard系列1：RAC主库信息概览、备库准备工作中，我们了解了双节点RAC主库的基本信息，并且成功创建了一台将来用作物理备库的机器。在本篇中，我们将完成以下任务：

在物理备库机器上安装oracle 10g 数据库软件；
在物理备库机器上升级oracle 10g 软件到10.2.0.5.0；
在物理备库机器上配置ASM存储，创建ASM实例；

一安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

1 创建oracle用户、用户组，参照以下shell脚本完成：

[root@ora10grac-dg Pre10g]# pwd
/root/Pre10g
[root@ora10grac-dg Pre10g]# ll
total 24
-rw-r--r-- 1 root root 2165 Feb 14 14:49 1preuser.sh
-rw-r--r-- 1 root root  510 Feb 14 14:47 2predir.sh
-rw-r--r-- 1 root root  801 Feb 14 14:47 3prelimits.sh
-rw-r--r-- 1 root root  541 Feb 14 14:47 4prelogin.sh
-rw-r--r-- 1 root root  652 Feb 14 14:47 5preprofile.sh
-rw-r--r-- 1 root root 2300 Feb 14 14:47 6presysctl.sh
[root@ora10grac-dg Pre10g]# chmod u+x *.sh
[root@ora10grac-dg Pre10g]# ll
total 24
-rwxr--r-- 1 root root 2165 Feb 14 14:49 1preuser.sh
-rwxr--r-- 1 root root  510 Feb 14 14:47 2predir.sh
-rwxr--r-- 1 root root  801 Feb 14 14:47 3prelimits.sh
-rwxr--r-- 1 root root  541 Feb 14 14:47 4prelogin.sh
-rwxr--r-- 1 root root  652 Feb 14 14:47 5preprofile.sh
-rwxr--r-- 1 root root 2300 Feb 14 14:47 6presysctl.sh
[root@ora10grac-dg Pre10g]# cat 1preuser.sh 
#!/bin/bash
#Purpose:Create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment
#variable for oracle user.
# 
#Usage:Log on as the superuser('root'),and then execute the command:#./1preuser.sh
#Author:Asher Huang

echo "Now create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment"
groupadd oinstall 
groupadd dba
useradd -g oinstall -G dba -c "Oracle software owner" -d  /home/oracle   oracle 
echo "oracle" | passwd --stdin oracle
echo "export ORACLE_BASE=/u01/app" >> /home/oracle/.bash_profile
echo 'export ORACLE_HOME=$ORACLE_BASE/oracle' >> /home/oracle/.bash_profile
echo "export ORACLE_SID=pridb"  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/usr/X11R6/lib'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/jdk/jre/lib/i386/server'  >> /home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/rdbms/lib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/rdbms/jlib'  >> /home/oracle/.bash_profile
echo 'export CLASS_PATH=$CLASS_PATH:$ORACLE_HOME/network/jlib'  >> /home/oracle/.bash_profile
echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/oracle/.bash_profile
echo "export NLS_LANG=american_america.AL32UTF8" >> /home/oracle/.bash_profile
echo "export ORACLE_TERM=xterm" >> /home/oracle/.bash_profile
echo "export EDITOR=vi" >> /home/oracle/.bash_profile
echo 'export PATH=$ORACLE_HOME/bin:$PATH' >> /home/oracle/.bash_profile
echo "export LANG=en_US" >> /home/oracle/.bash_profile
echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/oracle/.bash_profile

echo "The two groups named 'oinstall' and 'dba' plus the oracle user has been created."
echo "The Environment variable for oracle user also has been setted sussessfully."
[root@ora10grac-dg Pre10g]#

2 创建相关目录，其中/u01/app用做Oracle Base 目录，/u01/app/oracle用作Oracle Home目录，具体参照下述脚本：

[root@ora10grac-dg Pre10g]# cat 2predir.sh 
#!/bin/bash
#Purpose:Create the necessary directory for oracle user and change the authention to oracle user.
#Usage:Log on as the superuser('root'),and then execute the command:#./2predir.sh
#Author:Asher Huang

echo "Now create the necessary directory for oracle user and change the authention to oracle user..."
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01
chown oracle:oinstall /home/oracle
echo "The necessary directory for oracle user and change the authention to oracle user has been finished"[root@ora10grac-dg Pre10g]#

3 配置oracle用户的SHELL限制，我们通过下述脚本来修改/etc/security/limits.conf配置文件：

[root@ora10grac-dg Pre10g]# cat 3prelimits.sh 
#!/bin/bash
#Purpose:Change the /etc/security/limits.conf. 
#        "Configure the SHELL limits on oracle user."
#        配置oracle用户的SHELL限制
#  nofile ----可打开的文件描述符的最大数
#  nproc  ----单个用户可用的最大进程数量
#Usage:Log on as the superuser('root'),and then execute the command:#./3prelimits.sh
#Author:Asher Huang


echo "Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before"
cp /etc/security/limits.conf /etc/security/limits.conf.bak
echo "oracle soft nproc 2047" >>/etc/security/limits.conf
echo "oracle hard nproc 16384" >>/etc/security/limits.conf
echo "oracle soft nofile 1024" >>/etc/security/limits.conf
echo "oracle hard nofile 65536" >>/etc/security/limits.conf
echo "Modifing the /etc/security/limits.conf has been succeed."[root@ora10grac-dg Pre10g]#

4 配置SHELL对oracle用户的限制生效，需要修改/etc/pam.d/login文件，脚本如下：

[root@ora10grac-dg Pre10g]# cat 4prelogin.sh 
#!/bin/bash
#Purpose:Modify the /etc/pam.d/login.
# 为使第3步骤中配置SHELL对oracle用户的限制生效，需要修改/etc/pam.d/login文件.
#Usage:Log on as the superuser('root'),and then execute the command:#./4prelimits.sh
#Author:Asher Huang

echo "Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak"
cp /etc/pam.d/login /etc/pam.d/login.bak
echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login
echo "session required pam_limits.so" >>/etc/pam.d/login
echo "Modifing the /etc/pam.d/login has been succeed."

5 配置系统相关的环境变量，修改/etc/profile,脚本：

[root@ora10grac-dg Pre10g]# cat 5preprofile.sh 
#!/bin/bash
#Purpose:Modify the /etc/profile.配置系统相关的环境变量
#Usage:Log on as the superuser('root'),and then execute the command:#./5preprofile.sh
#Author:Asher Huang

echo "Now modify the  /etc/profile,but with a backup named  /etc/profile.bak"
cp /etc/profile /etc/profile.bak
echo 'if [ $USER = "oracle" ]; then' >>  /etc/profile
echo 'if [ $SHELL = "/bin/ksh" ]; then' >> /etc/profile
echo 'ulimit -p 16384' >> /etc/profile
echo 'ulimit -n 65536' >> /etc/profile
echo 'else' >> /etc/profile
echo 'ulimit -u 16384 -n 65536' >> /etc/profile
echo 'fi' >> /etc/profile
echo 'fi' >> /etc/profile
echo "Modifing the /etc/profile has been succeed."

6 修改系统内核参数，修改/etc/sysctl.conf配置文件，脚本：

[root@ora10grac-dg Pre10g]# cat 6presysctl.sh 
#!/bin/bash
#Purpose:Modify the /etc/sysctl.conf.
# 修改系统内核参数，若配置有误，在安装时有先决条件检查，即会报错!!!
#Usage:Log on as the superuser('root'),and then execute the command:#./6presysctl.sh
#Author:Asher Huang

echo "Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak"
cp /etc/sysctl.conf /etc/sysctl.conf.bak
echo "fs.file-max = 65536" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf

echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.shmmax = 2147483648" >> /etc/sysctl.conf

echo "net.core.rmem_default=1048576" >> /etc/sysctl.conf
echo "net.core.rmem_max=262144" >> /etc/sysctl.conf
echo "net.core.wmem_default=262144" >> /etc/sysctl.conf
echo "net.core.wmem_max=262144" >> /etc/sysctl.conf

echo "net.ipv4.ip_local_port_range = 1024 65000" >> /etc/sysctl.conf
echo "Modifing the /etc/sysctl.conf has been succeed."
echo "Now make the changes take effect....."

## fs.file-max = 65536 表示文件句柄的最大数量。文件句柄设置表示在linux系统中可以打开的文件数量 

## kernel.sem = 250 32000 100 128  sem 4个参数依次是
##SEMMSL:每个用户拥有信号量最大数;
##SEMMNS:系统信号量最大数;
##SEMOPM:每次semopm系统调用操作数;
##SEMMNI:系统信号量集数最大数。这4个参数为固定内容大小。

## kernel.shmall = 2097152 该参数表示系统一次可以使用的共享内存总量（以页为单位，）。缺省值就是2097152，通常不需要修改。
## 可用共享内存的总量，单位页，在32位系统上一页=4k，也就是4096字节。按照这个设置2097152*4k/1024/1024 = 8G， 就是说可用共享内存一共 8G
## kernel.shmmax = 2147483648 该参数定义了共享内存段的最大尺寸（以字节为单位）。缺省为32M，对于oracle来说，该缺省值太低了，通常将其设置为2G
## 单个共享内存段的最大数，2147483648/1024/1024=2048M=2G
## kernel.shmmni = 4096 这个内核参数用于设置系统范围内单个共享内存段的最小值。该参数的默认值是 4096 。通常不需要更改。


## 设置UDP send/receive buffers high enough ,通常在RAC环境下，私网最好是千兆网卡，协议为UDP。
## net.core.rmem_default=262144   //  默认的接收窗口大小 
## net.core.rmem_max=262144       //  接收窗口的最大大小 
## net.core.wmem_default=262144   //  默认的发送窗口大小 
## net.core.wmem_max=262144       //  发送窗口的最大大小


## net.ipv4.ip_local_port_range = 1024 65000 //表示端口范围


#使配置生效，执行下述命令：
sysctl -p

7 将上述脚本编写无误后，以root用户依次执行：

[root@ora10grac-dg Pre10g]# ./1preuser.sh 
Now create two groups named 'oinstall' and 'dba', plus a user named 'oracle'.Also setting the Environment
Changing password for user oracle.
passwd: all authentication tokens updated successfully.
The two groups named 'oinstall' and 'dba' plus the oracle user has been created.
The Environment variable for oracle user also has been setted sussessfully.
[root@ora10grac-dg Pre10g]# ./2predir.sh 
Now create the necessary directory for oracle user and change the authention to oracle user...
The necessary directory for oracle user and change the authention to oracle user has been finished
[root@ora10grac-dg Pre10g]# ./3prelimits.sh 
Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before
Modifing the /etc/security/limits.conf has been succeed.
[root@ora10grac-dg Pre10g]# ./4prelogin.sh 
Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak
Modifing the /etc/pam.d/login has been succeed.
[root@ora10grac-dg Pre10g]# ./5preprofile.sh 
Now modify the  /etc/profile,but with a backup named  /etc/profile.bak
Modifing the /etc/profile has been succeed.
[root@ora10grac-dg Pre10g]# ./6presysctl.sh 
Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak
Modifing the /etc/sysctl.conf has been succeed.
Now make the changes take effect.....
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 65536
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default = 1048576
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.ip_local_port_range = 1024 65000
[root@ora10grac-dg Pre10g]#

8 以oracle用户登录系统，并确认环境变量生效：

[root@ora10grac-dg Pre10g]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)
[root@ora10grac-dg Pre10g]# su - oracle
[oracle@ora10grac-dg ~]$ pwd
/home/oracle
[oracle@ora10grac-dg ~]$ env | grep ORA
ORACLE_SID=pridb
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@ora10grac-dg ~]$

9 挂载oracle软件安装介质，并以oracle用户登录图形界面，开始执行软件安装：

① 挂载安装介质，这里oracle 10g软件的安装包、升级包、集群件已经刻录好成一个ISO文件，所以只需挂载光盘：

[root@ora10grac-dg ~]# mount /dev/cdrom /mnt/
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@ora10grac-dg ~]# cd /mnt/
[root@ora10grac-dg mnt]# ll
total 6
dr-xr-xr-x 1 root root 2048 Jul 27  2010 10201_clusterware_linux_x86_64
dr-xr-xr-x 1 root root 2048 Jul 27  2010 10201_database_linux_x86_64
dr-xr-xr-x 1 root root 2048 Jul 27  2010 p8202632_10205_Linux-x86-64
[root@ora10grac-dg mnt]# cd 10201_database_linux_x86_64/ && ll
total 25
dr-xr-xr-x 1 root root  2048 Jul 27  2010 doc
dr-xr-xr-x 1 root root  2048 Jul 27  2010 install
dr-xr-xr-x 1 root root  2048 Jul 27  2010 response
-r-xr-xr-x 1 root root  1326 Oct 23  2005 runInstaller
dr-xr-xr-x 1 root root 12288 Jul 27  2010 stage
-r-xr-xr-x 1 root root  5416 Oct 20  2005 welcome.html
[root@ora10grac-dg 10201_database_linux_x86_64]#

② Oracle用户登录图形界面，执行安装：

③ 发现报错，原因是Oracle 10g 软件不支持Red Hat 5版本的操作系统，Oracle 10g在2004年就发布了，而Red Hat 5则在2007年发布。修改/etc/redhat-release文件，使之骗过Oracle：

[root@ora10grac-dg ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
[root@ora10grac-dg ~]# cp /etc/redhat-release /etc/redhat-release.bak
[root@ora10grac-dg ~]# vi /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
[root@ora10grac-dg ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
[root@ora10grac-dg ~]#

④ 修改之后，重新执行OUI图形界面安装，发现这次报错如下：

[oracle@ora10grac-dg ~]$ /mnt/10201_database_linux_x86_64/runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-14_03-25-08PM. Please wait ...[oracle@ora10grac-dg ~]$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2012-02-14_03-25-08PM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.(Unknown Source)
        at java.awt.Component.(Unknown Source)
[oracle@ora10grac-dg ~]$

⑤ 原因是缺少libXp-1.0.0-8.1.el5.i386.rpm及libXp-1.0.0-8.1.el5.x86_64.rpm这两个RPM软件包，接下来，卸载oracle安装介质的光盘，挂载操作系统安装光盘，到操作系统安装光盘里找到这两个RPM包，并执行安装：

[root@ora10grac-dg ~]# umount /mnt/
[root@ora10grac-dg ~]# mount /dev/cdrom /mnt/
mount: block device /dev/cdrom is write-protected, mounting read-only
[root@ora10grac-dg ~]# cd /mnt/Server/
[root@ora10grac-dg Server]# ll libXp*
-rw-r--r-- 2 oracle root 22852 Jan 22  2009 libXp-1.0.0-8.1.el5.i386.rpm
-rw-r--r-- 2 oracle root 23561 Jan 22  2009 libXp-1.0.0-8.1.el5.x86_64.rpm
-rw-r--r-- 2 oracle root 15103 Jan 22  2009 libXp-devel-1.0.0-8.1.el5.i386.rpm
-rw-r--r-- 2 oracle root 15072 Jan 22  2009 libXp-devel-1.0.0-8.1.el5.x86_64.rpm
-rw-r--r-- 2 oracle root 46410 Jan 22  2009 libXpm-3.5.5-3.i386.rpm
-rw-r--r-- 2 oracle root 45336 Jan 22  2009 libXpm-3.5.5-3.x86_64.rpm
-rw-r--r-- 2 oracle root 31307 Jan 22  2009 libXpm-devel-3.5.5-3.i386.rpm
-rw-r--r-- 2 oracle root 32469 Jan 22  2009 libXpm-devel-3.5.5-3.x86_64.rpm
[root@ora10grac-dg Server]# rpm -ivh libXp-1.0.0-8.1.el5.i386.rpm 
warning: libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@ora10grac-dg Server]# rpm -ivh libXp-1.0.0-8.1.el5.x86_64.rpm 
warning: libXp-1.0.0-8.1.el5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@ora10grac-dg Server]# cd 
[root@ora10grac-dg ~]# umount /mnt/
[root@ora10grac-dg ~]#

⑥ 接下来，重新挂载Oracle安装介质光盘，重新执行OUI安装，进入熟悉的OUI安装界面，选择高级安装，单击Next，一路走下去：

⑦ 单击Next，进入到安装前的与检查页面，提示交换分区不够大的警告，还有一个网络配置的信息，说是建议采用DHCP，这里直接忽略：

⑧ Next 进入下一步，注意选择只安装软件，不要建库，因为我们是要配置物理dataguard。

⑨ 最后，以root用户执行下述两个脚本，完成oracle软件的安装：

[root@ora10grac-dg ~]# /u01/app/oraInventory/orainstRoot.sh 
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete  
[root@ora10grac-dg ~]# /u01/app/oracle/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@ora10grac-dg ~]#

⑩ 以Oracle用户执行SQL*PLUS命令行工具，做简单验证：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 14 16:53:01 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL>

至此，Oracle 10g数据库软件已经安装完成！！！

二 oracle软件安装完成之后，接下来，开始升级Oracle软件到10.2.0.5.0版本：

① 同样，10.2.0.5.0的升级包也在安装介质的光盘上，如果没有的话，需要自行到oracle的官方网站下载。依然以oracle用户在图形界面下执行下述命令，进入OUI的图形界面进行升级：

[oracle@ora10grac-dg ~]$ /mnt/p8202632_10205_Linux-x86-64/Disk1/runInstaller 
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-02-14_04-59-35PM. Please wait ...[oracle@ora10grac-dg ~]$ Oracle Universal Installer, Version 10.2.0.5.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

② Next 进入下一步：

③ 继续Next：

④ Next，进入下一步：

⑤ 发现，此时提示内核的fs.file-max = 65536参数太小，需要调整为101365。以及net.ipv4.ip_local_port_range = 1024 65000需要调整为net.ipv4.ip_local_port_range = 9000 65500。依据错误提示信息，对/etc/sysctl.conf配置文件重新进行修改。修改后，单击Retry，此时不再提示该警告，转而提示swap过小，可直接忽略：

[root@ora10grac-dg ~]# tail -10 /etc/sysctl.conf
fs.file-max = 101365
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default=1048576
net.core.rmem_max= 1048576
net.core.wmem_default=262144
net.core.wmem_max=262144
net.ipv4.ip_local_port_range = 9000 65500
[root@ora10grac-dg ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
fs.file-max = 101365
kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.shmmax = 2147483648
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
net.ipv4.ip_local_port_range = 9000 65500
[root@ora10grac-dg ~]#

⑥ 单击，Install，进入安装升级包：

⑦ 最后，执行提示脚本/u01/app/oracle/root.sh，完成oracle软件的升级！

[root@ora10grac-dg ~]# /u01/app/oracle/root.sh
Running Oracle 10g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n) 
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
[root@ora10grac-dg ~]#

⑧ 当然，再次以oracle用户在命令行执行SQL*PLUS命令，确认数据库软件已经升级到10.2.0.5.0版本：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 17:31:12 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL>

⑨ 最后，别忘记了配置监听程序。可以使用Oracle提供的Net Configuration Assisstant、Net Manager图形化界面工具进行配置，也可以直接到$ORACLE_HOME/network/admin/目录下直接编辑一个listener.ora的配置文件。这里以netca配置：

配置完成后的信息如下：

[oracle@ora10grac-dg ~]$ cat /u01/app/oracle/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora10grac-dg)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@ora10grac-dg ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 15-FEB-2012 09:17:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora10grac-dg)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                15-FEB-2012 09:16:03
Uptime                    0 days 0 hr. 1 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/network/admin/listener.ora
Listener Log File         /u01/app/oracle/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora10grac-dg)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@ora10grac-dg ~]$

三接下去的工作便是配置ASM存储，创建ASM实例：

① 关闭物理备库机器，添加新的硬盘，该硬盘将来用作ASM磁盘，用于数据库的存储。我这里添加两块硬盘/dev/sdb将来用作ASM存储，/dev/sdc将来用作备份用。添加硬盘后的信息如下：

[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  4.8G  2.6G  66% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
[root@ora10grac-dg ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1044     8385898+  83  Linux
/dev/sda2            1045        1305     2096482+  82  Linux swap / Solaris

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdb doesn't contain a valid partition table

Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Disk /dev/sdc doesn't contain a valid partition table
[root@ora10grac-dg ~]#

接下来，分别格式化/dev/sdb、/dev/sdc:

[root@ora10grac-dg ~]# fdisk /dev/sdb 
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305): 
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ora10grac-dg ~]# fdisk /dev/sdc
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.


The number of cylinders for this disk is set to 1044.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1044, default 1): 
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1044, default 1044): 
Using default value 1044

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@ora10grac-dg ~]# partprobe 
Warning: Unable to open /dev/hdc read-write (Read-only file system).  /dev/hdc has been opened read-only.
[root@ora10grac-dg ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1        1044     8385898+  83  Linux
/dev/sda2            1045        1305     2096482+  82  Linux swap / Solaris

Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        1305    10482381   83  Linux

Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1044     8385898+  83  Linux
[root@ora10grac-dg ~]#

在这里，顺便给/dev/sdc1做ext3的文件系统，然后挂载到/oracle_backup下，并更改权限，步骤如下：

[root@ora10grac-dg ~]# mkdir /oracle_backup  
[root@ora10grac-dg ~]# mkfs.ext3 /dev/sdc1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1048576 inodes, 2096474 blocks
104823 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2147483648
64 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Writing inode tables: done                            
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 25 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@ora10grac-dg ~]# mount -t ext3 /dev/sdc1 /oracle_backup/
[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  4.8G  2.6G  66% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
/dev/sdc1     ext3    7.9G  147M  7.4G   2% /oracle_backup
[root@ora10grac-dg ~]# chown -R oracle:oinstall /oracle_backup/
[root@ora10grac-dg ~]# cd / && ll
total 184
drwxr-xr-x   2 root   root      4096 Feb 14 12:26 bin
drwxr-xr-x   3 root   root      4096 Feb 14 11:07 boot
drwxr-xr-x  10 root   root      3800 Feb 15 09:57 dev
drwxr-xr-x  89 root   root     12288 Feb 15 10:03 etc
drwxr-xr-x   3 root   root      4096 Feb 14 14:59 home
drwxr-xr-x  11 root   root      4096 Feb 14 12:26 lib
drwxr-xr-x   7 root   root     12288 Feb 14 12:26 lib64
drwx------   2 root   root     16384 Feb 14 11:00 lost+found
drwxr-xr-x   2 root   root      4096 Feb 15 09:38 media
drwxr-xr-x   2 root   root         0 Feb 15 09:38 misc
drwxr-xr-x   2 root   root      4096 Feb 17  2010 mnt
drwxr-xr-x   2 root   root         0 Feb 15 09:38 net
drwxr-xr-x   3 root   root      4096 Feb 14 16:43 opt
drwxr-xr-x   3 oracle oinstall  4096 Feb 15 10:03 oracle_backup
dr-xr-xr-x 120 root   root         0 Feb 15 09:37 proc
drwxr-x---  16 root   root      4096 Feb 14 17:46 root
drwxr-xr-x   2 root   root     12288 Feb 14 12:26 sbin
drwxr-xr-x   2 root   root      4096 Feb 14 11:01 selinux
drwxr-xr-x   2 root   root      4096 Feb 17  2010 srv
drwxr-xr-x  11 root   root         0 Feb 15 09:37 sys
drwxr-xr-x   3 root   root      4096 Feb 14 11:06 tftpboot
drwxrwxrwt  17 root   root      4096 Feb 15 09:51 tmp
drwxr-xr-x   3 oracle oinstall  4096 Feb 14 14:59 u01
drwxr-xr-x  15 root   root      4096 Feb 14 11:05 usr
drwxr-xr-x  22 root   root      4096 Feb 14 11:10 var
[root@ora10grac-dg /]#

② 配置ASM，在这里用裸设备做，不采用oracle 的asmlib进行配置(虽然我个人以前一直使用)，所以在这里也就不需要安装asmlib软件包。

首先，获取/dev/sdb设备的UUID：

[root@ora10grac-dg rules.d]# scsi_id -g -v -s /block/sdb
36000c29e0aa117a4e812fb78d5fb3517
[root@ora10grac-dg rules.d]#

然后，在/etc/udev/rules.d/60-raw.rules文件里添加配置规则如下：

[root@ora10grac-dg rules.d]# pwd
/etc/udev/rules.d
[root@ora10grac-dg rules.d]# ll
total 156
-rw-r--r-- 1 root root   515 Jul  3  2009 05-udev-early.rules
-rw-r--r-- 1 root root   900 Mar 31  2010 40-multipath.rules
-rw-r--r-- 1 root root 15647 Jul  3  2009 50-udev.rules
-rw-r--r-- 1 root root   471 Jul  3  2009 51-hotplug.rules
-rw-r--r-- 1 root root   143 Jul  4  2009 60-net.rules
-rw-r--r-- 1 root root  1088 Jun  6  2007 60-pcmcia.rules
-rw-r--r-- 1 root root   475 Feb 15 15:30 60-raw.rules
-rw-r--r-- 1 root root  9014 Apr  1  2010 60-wacom.rules
-rw-r--r-- 1 root root   129 Apr  1  2010 61-uinput-stddev.rules
-rw-r--r-- 1 root root   214 Apr  1  2010 61-uinput-wacom.rules
-rw-r--r-- 1 root root    61 Feb 15 15:32 65-raw-permissions.rules
-rw-r--r-- 1 root root  1823 Nov  5  2008 85-pcscd_ccid.rules
-rw-r--r-- 1 root root   114 Nov  5  2008 90-alsa.rules
-rw-r--r-- 1 root root    61 Jul  3  2009 90-dm.rules
-rw-r--r-- 1 root root    82 Apr  1  2010 90-hal.rules
-rw-r--r-- 1 root root   107 Jul  3  2009 95-pam-console.rules
-rw-r--r-- 1 root root   292 Apr  1  2010 98-kexec.rules
-rw-r--r-- 1 root root  2319 Jul 15  2008 bluetooth.rules
[root@ora10grac-dg rules.d]# cat 60-raw.rules 
# Enter raw device bindings here.
#
# An example would be:
#   ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"
# to bind /dev/raw/raw1 to /dev/sda, or
#   ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"
# to bind /dev/raw/raw2 to the device with major 8, minor 1.
ACTION=="add", KERNEL=="sd*", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s %p", RESULT=="36000c29e0aa117a4e812fb78d5fb3517",RUN+="/bin/raw /dev/raw/raw1 %N" 
[root@ora10grac-dg rules.d]#

其次，在/etc/udev/rules.d/路径下添加一个权限控制文件，这里命名为65-raw-permissions.rules，其作用是确保操作系统重启后，oracle用户仍然有对裸设备操作的权限。否则，重启系统后，ASM 实例将不可用。

[root@ora10grac-dg rules.d]# cat 65-raw-permissions.rules 
KERNEL=="raw1", OWNER="oracle", GROUP="oinstall", MODE="660"
[root@ora10grac-dg rules.d]#

最后，重新加载规则，并启动udev：

[root@ora10grac-dg rules.d]# /sbin/udevcontrol reload_rules
[root@ora10grac-dg rules.d]# /sbin/start_udev 
Starting udev: [  OK  ]
[root@ora10grac-dg rules.d]# ll /dev/raw/raw1 
crw-rw---- 1 oracle oinstall 162, 1 Feb 15 15:34 /dev/raw/raw1
[root@ora10grac-dg rules.d]#

至此，raw设备配置完毕！

③ 创建ASM实例：

首先，确认asm相应的软件包安装成功：

[root@ora10grac-dg ~]# rpm -qa | grep asm
oracleasm-support-2.1.3-1.el5
oracleasm-2.6.18-194.el5-2.0.5-1.el5
[root@ora10grac-dg ~]#

从上，可以看到系统并没有安装asmlib包，因为我们是用裸设备作asm磁盘。

然后，配置ASM服务：

[root@ora10grac-dg ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@ora10grac-dg ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [  OK  ]
Scanning the system for Oracle ASMLib disks: [  OK  ]
[root@ora10grac-dg ~]# /etc/init.d/oracleasm listdisks
[root@ora10grac-dg ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [  OK  ]
[root@ora10grac-dg ~]# /etc/init.d/oracleasm listdisks
[root@ora10grac-dg ~]# /etc/init.d/oracleasm querydisk /dev/sd*
Device "/dev/sda" is not marked as an ASM disk
Device "/dev/sda1" is not marked as an ASM disk
Device "/dev/sda2" is not marked as an ASM disk
Device "/dev/sdb" is not marked as an ASM disk
Device "/dev/sdb1" is not marked as an ASM disk
Device "/dev/sdc" is not marked as an ASM disk
Device "/dev/sdc1" is not marked as an ASM disk
[root@ora10grac-dg ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: yes
Checking if /dev/oracleasm is mounted: yes
[root@ora10grac-dg ~]#

从上面的输出中，可以看到asm服务会随着操作系统启动而加载，并且在没有用asmlib包配置asm磁盘的情况下，执行/etc/init.d/oracleasm listdisks命令是看不到磁盘的。

其次，oracle用户登录图形界面，执行dbca，进入图形界面进行安装：

单击Next,进入下一步：

选择最后一项，配置ASM，单击Next：

提示需要以root用户执行”/u01/app/oracle/bin/localconfig add”命令来启动CSS服务：

[root@ora10grac-dg ~]# /u01/app/oracle/bin/localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab 
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        ora10grac-dg
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@ora10grac-dg ~]# ps -ef | grep css
root     25911     1  0 16:06 ?        00:00:00 /bin/sh /etc/init.d/init.cssd run
oracle   26151 25911  4 16:06 ?        00:00:01 /u01/app/oracle/bin/ocssd.bin
root     26171  3352  0 16:07 pts/1    00:00:00 grep css
[root@ora10grac-dg ~]#

执行完上述命令之后，回到图形界面，单击OK，进入下一步：

输入ASM实例的SYS用户的口令，单击Next：

提示，DBCA将创建ASM实例，单击OK，下一步：

这时，可以在操作系统上看到ASM实例的后台进程：

[root@ora10grac-dg ~]# ps -ef | grep asm_
oracle   26211     1  0 16:12 ?        00:00:00 asm_pmon_+ASM
oracle   26213     1  0 16:12 ?        00:00:00 asm_psp0_+ASM
oracle   26215     1  0 16:12 ?        00:00:00 asm_mman_+ASM
oracle   26217     1  0 16:12 ?        00:00:00 asm_dbw0_+ASM
oracle   26219     1  0 16:12 ?        00:00:00 asm_lgwr_+ASM
oracle   26221     1  0 16:12 ?        00:00:00 asm_ckpt_+ASM
oracle   26223     1  0 16:12 ?        00:00:00 asm_smon_+ASM
oracle   26225     1  0 16:12 ?        00:00:00 asm_rbal_+ASM
oracle   26227     1  0 16:12 ?        00:00:00 asm_gmon_+ASM
root     26233  3352  0 16:12 pts/1    00:00:00 grep asm_
[root@ora10grac-dg ~]#

单击Create New,创建磁盘组，输入磁盘组的名称，oradata,冗余级别选择外部，选中磁盘/dev/raw/raw1，单击Ok：

最后，单击Finish完成ASM实例的创建。

我们，可以在SQL*PLUS的字符命令行下访问asm实例：

[root@ora10grac-dg ~]# su - oracle
[oracle@ora10grac-dg ~]$ export ORACLE_SID=+ASM
[oracle@ora10grac-dg ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 15 16:20:45 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter spfile;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
spfile                               string                            /u01/app/oracle/dbs/spfile+ASM
                                                                       .ora
SQL> show parameter asm

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
asm_diskgroups                       string                            ORADATA
asm_diskstring                       string
asm_power_limit                      integer                           1
SQL> col name for a30
SQL> select name,state,total_mb,free_mb from v$asm_diskgroup;

NAME                           STATE                               TOTAL_MB    FREE_MB
------------------------------ --------------------------------- ---------- ----------
ORADATA                        MOUNTED                                10236      10186

SQL>

至此，我们的物理备库已经准备就绪，但是我们的物理备库还没有搭建出来，未完待续！下一篇，介绍物理备库搭建的详细步骤，敬请期待！！！

Oracle 10g RAC 配置物理dataguard系列1：RAC主库信息概览、备库准备工作

2月 14,2012 有1条评论 Dataguard, Experience, Oracle, RAC

最近在学习Oracle dataguard相关知识。本系列就Oracle Enterprise Linux 5.5 X86_64位环境下Oracle 10g 10.2.0.5.0双节点RAC数据库配置一套物理dataguard做一记录，一来给自己的学习做一简单记录，二来，同样希望可以给广大网友及Oracle数据库技术爱好者提供思路。

在实施之前，简单介绍下背景、最终目标及配置步骤：

背景介绍：主库是一套运行在Oracle Enterprise Linux 5.5 X86_64位环境下Oracle 10g 10.2.0.5.0双节点RAC数据库，主库用的是ASM存储，ASM磁盘利用raw来实现；
最终目标：需要给该主库搭建一套同样是运行在Oracle Enterprise Linux 5.5 X86_64位环境下的物理备库，并且能够switchover，确保高可用
配置步骤： 1 了解现有主库的配置信息；

2 搭建一台干净的新机器，用于创建物理备库使用；

3 在物理备机上安装oracle数据库软件，并且升级到同主库软件版本一致，即10.2.0.5.0；

4 在物理备机上创建ASM实例，ASM磁盘同样选择裸设备来实现；

5 配置物理备库；

6 确认switchover成功，双节点RAC+physical standby既可提供实例级别容灾(RAC功能)又可以提供存储级别容灾(Dataguard功能)，做到真正的高可用！

本篇是整个实施过程的系列1：本篇的内容主要是查看主库基本配置信息，及创建一台干净的Linux服务器，用作备库。

一首先了解下现有双节点RAC数据库配置信息：

1 节点1系统基本信息：

[root@oracle-rac1 ~]# hostname 
oracle-rac1.gillion.com.cn
[root@oracle-rac1 ~]# uname -rm
2.6.18-194.0.0.0.3.el5 x86_64
[root@oracle-rac1 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
              ext3     20G   12G  6.8G  64% /
/dev/sda1     ext3     99M   19M   75M  21% /boot
tmpfs        tmpfs    3.9G     0  3.9G   0% /dev/shm
[root@oracle-rac1 ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:31  
          inet addr:172.16.0.33  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fe3a:b831/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:836563 errors:0 dropped:0 overruns:0 frame:0
          TX packets:10459 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:73068451 (69.6 MiB)  TX bytes:2856621 (2.7 MiB)
eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:31  
          inet addr:172.16.0.35  Bcast:172.16.15.255  Mask:255.255.240.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
eth1      Link encap:Ethernet  HWaddr 00:0C:29:3A:B8:3B  
          inet addr:192.168.139.13  Bcast:192.168.139.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fe3a:b83b/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1484344 errors:0 dropped:0 overruns:0 frame:0
          TX packets:707841 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:327352385 (312.1 MiB)  TX bytes:353601291 (337.2 MiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:265726 errors:0 dropped:0 overruns:0 frame:0
          TX packets:265726 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:61035429 (58.2 MiB)  TX bytes:61035429 (58.2 MiB)
[root@oracle-rac1 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost
172.16.0.33             oracle-rac1.gillion.com.cn oracle-rac1
172.16.0.35             oracle-rac1-vip oracle-rac1-vip.gillion.com.cn
192.168.139.13          oracle-rac1-priv
172.16.0.34             oracle-rac2.gillion.com.cn oracle-rac2
172.16.0.36             oracle-rac2-vip oracle-rac2-vip.gillion.com.cn
192.168.139.14          oracle-rac2-priv
[root@oracle-rac1 ~]# su - oracle
[oracle@oracle-rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? glndb
[oracle@oracle-rac1 ~]$ export ORACLE_SID=glndb1
[oracle@oracle-rac1 ~]$ env | grep ORA
ORACLE_SID=glndb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac1 ~]$

2 节点1数据库基本信息：

[oracle@oracle-rac1 ~]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 09:11:00 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> select dbid,name,created,log_mode,db_unique_name from gv$database;
      DBID NAME      CREATED             LOG_MODE     DB_UNIQUE_NAME
---------- --------- ------------------- ------------ ---------
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
SQL> show parameter db_name;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_name                              string      glndb
SQL> show parameter db_unique;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_unique_name                       string      glndb
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch1
Oldest online log sequence     7699
Next log sequence to archive   7700
Current log sequence           7700
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL>

3 节点2基本信息：

[root@oracle-rac2 ~]# hostname 
oracle-rac2.gillion.com.cn
[root@oracle-rac2 ~]# uname -rm
2.6.18-194.0.0.0.3.el5 x86_64
[root@oracle-rac2 ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
              ext3     20G   12G  6.9G  63% /
/dev/sda1     ext3     99M   19M   75M  21% /boot
tmpfs        tmpfs    3.9G     0  3.9G   0% /dev/shm
[root@oracle-rac2 ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:4C  
          inet addr:172.16.0.34  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fed1:b54c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:833250 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8442 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:72725394 (69.3 MiB)  TX bytes:2440410 (2.3 MiB)
eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:4C  
          inet addr:172.16.0.36  Bcast:172.16.15.255  Mask:255.255.240.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
eth1      Link encap:Ethernet  HWaddr 00:0C:29:D1:B5:56  
          inet addr:192.168.139.14  Bcast:192.168.139.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fed1:b556/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1534148 errors:0 dropped:0 overruns:0 frame:0
          TX packets:655976 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:424024468 (404.3 MiB)  TX bytes:256611983 (244.7 MiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:274642 errors:0 dropped:0 overruns:0 frame:0
          TX packets:274642 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:61783289 (58.9 MiB)  TX bytes:61783289 (58.9 MiB)
[root@oracle-rac2 ~]# cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost
172.16.0.33             oracle-rac1.gillion.com.cn oracle-rac1
172.16.0.35             oracle-rac1-vip oracle-rac1-vip.gillion.com.cn
192.168.139.13          oracle-rac1-priv
172.16.0.34             oracle-rac2.gillion.com.cn oracle-rac2
172.16.0.36             oracle-rac2-vip oracle-rac2-vip.gillion.com.cn
192.168.139.14          oracle-rac2-priv
[root@oracle-rac2 ~]# su - oracle
[oracle@oracle-rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? glndb
[oracle@oracle-rac2 ~]$ export ORACLE_SID=glndb2
[oracle@oracle-rac2 ~]$ env |grep ORA
ORACLE_SID=glndb2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@oracle-rac2 ~]$

4 点2数据库基本信息：

[oracle@oracle-rac2 ~]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 14 09:25:47 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
Session altered.
SQL> set line 160
SQL> select dbid,name,created,log_mode,db_unique_name from gv$database;
      DBID NAME      CREATED             LOG_MODE     DB_UNIQUE_NAME
---------- --------- ------------------- ------------ ---------
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
3995745524 GLNDB     2010/08/03 10:44:04 ARCHIVELOG   glndb
SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_name                              string      glndb
SQL> show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
db_unique_name                       string      glndb
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch2
Oldest online log sequence     3270
Next log sequence to archive   3271
Current log sequence           3271
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +ORADATA/glndb/spfileglndb.ora
SQL>

5 数据文件、控制文件基本信息：

SQL> select name,bytes/1024/1024 M from v$datafile
  2  union
  3  select name,0 from v$controlfile
  4  order by 2
  5  ;
NAME                                                        M
-------------------------------------------------- ----------
+ORADATA/glndb/controlfile/current.256.726057849            0
+ORADATA/glndb/datafile/example.435.756233417               2
+ORADATA/glndb/datafile/users.265.726057871                 2
+ORADATA/glndb/datafile/undotbs3.268.774870843            200
+ORADATA/glndb/datafile/undotbs2.264.726057869            261
+ORADATA/glndb/datafile/sysaux.262.726057863             1290
+ORADATA/glndb/datafile/system.260.726057851             6039
7 rows selected.
SQL>

6 联机日志文件信息：

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1       7700   52428800          1 NO  CURRENT              445626703 2012/02/13 23:06:00
         2          1       7699   52428800          1 YES INACTIVE             445622543 2012/02/13 23:05:29
         4          2       3271   52428800          1 NO  CURRENT              445627780 2012/02/13 23:05:58
         5          2       3270   52428800          1 YES INACTIVE             445332258 2012/02/13 09:05:39
SQL>

7 资源状态信息：

[oracle@oracle-rac2 ~]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 ~]$

二在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用

本文采用的是在一套虚拟机上搭建另外的备库机器，该虚拟机软件是VMware ESX 4.0，该软件其实可以认为是一个操作系统，是直接装在物理机器上的，有别于Vmware公司的VMware Server、VMware Workstation软件，后两者是先需要有操作系统，然后在操作系统上安装该虚拟机软件。而且，VMware ESX 4.0虚拟机软件相对来说也要比后两者稳定。

用Vmware vSphere Client工具登录虚拟机控制台：

登录控制台后，虚拟机信息概览：

2 选择File–New–Virtual Machine，新建虚拟机命名为10gRAC-Dataguard：

3 接下来一步一步走，同在VMware Server、VMware Workstation里创建新虚拟机一样，在此不再赘述。配置后的虚拟机概览如下：

4 给10gRAC-Dataguard加电，开始安装操作系统，虚拟机磁盘分区如下：

5 虚拟机网络配置信息如下：

IPv4:172.16.0.202

Netmask:255.255.240.0

Gateway:172.16.15.254

DNS:Null

Hostname:ora10grac-dg

6 软件包安装时，选择客户化，其中，下述是选择要安装的软件包：

Desktop Environments：GNOME Desktop Environment

Applications:

Editors

Graphics

Development:

Development Libraries

Development Tools

GNOME Software Development

Legacy Software Development

X software Development

Base System:

Administration Tools

Base

Legacy Software Support

System Tools （其中，选择oracleasm软件包。注：默认情况下并没有oracleasmlib-2.0.4-1.el5软件包，如果采用asmlib来管理和创建asm磁盘的话，仍需要后续手工安装该RPM包，否则将来配置ASM存储时会找不到ASM磁盘！！！）

暂时，先安装这些软件包，稍后在安装oracle软件时，如有缺失软件包的话，再续安装，至于其它的Server，虚拟化，集群软件包一概不装！

7 接下来，进行格式化文件系统，进入系统安装。稍后，还有一些后续的配置工作，记得不要开启SELinux并且关闭防火墙！最后，一台新的虚拟机配置成功，登录界面如下：

8 新虚拟机基本信息如下：

[root@ora10grac-dg ~]# hostname 
ora10grac-dg
[root@ora10grac-dg ~]# uptime 
 11:17:18 up 2 min,  2 users,  load average: 1.98, 1.23, 0.47
[root@ora10grac-dg ~]# df -Th
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/sda1     ext3    7.8G  2.8G  4.7G  37% /
tmpfs        tmpfs    1.1G     0  1.1G   0% /dev/shm
[root@ora10grac-dg ~]# ifconfig 
eth0      Link encap:Ethernet  HWaddr 00:0C:29:2E:60:24  
          inet addr:172.16.0.202  Bcast:172.16.15.255  Mask:255.255.240.0
          inet6 addr: fe80::20c:29ff:fe2e:6024/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3356 errors:0 dropped:0 overruns:0 frame:0
          TX packets:196 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:333071 (325.2 KiB)  TX bytes:21084 (20.5 KiB)
lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:1056 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1056 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:1753796 (1.6 MiB)  TX bytes:1753796 (1.6 MiB)
[root@ora10grac-dg ~]# uname -rm
2.6.18-194.el5 x86_64
[root@ora10grac-dg ~]#

这样，一台新的物理备库机器配置成功！接下来需要在该机器上安装oracle软件，配置ASM，升级oracle软件到10.2.0.5.0版本，下篇继续！

删除undo表空间遇到ORA-30013及ORA-01548的解决思路

2月 10,2012 留下评论 Oracle

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时，遭遇ORA-30013及ORA-01548的错误，下面记录过程及解决方法。

1 创建新的UNDO表空间UNDOTBS3，并将当前实例的UNDO切换到UNDOTBS3之后，删除旧的UNDOTBS1时，遇到下述错误：

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

2 从上可以看到UNDOTBS1目前正在被使用。查询MetaLink,Unable to Drop Undo Tablespace ORA-30013 [ID 835944.1]获取基本思路，依据该文档给出的提示，执行下述命令：

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs 
  2  where tablespace_name='UNDOTBS1' and status = 'ONLINE';

SEGMENT_NAME     OWNER  TABLESPACE_NAME    STATUS
---------------- ------ ------------------ ---------
_SYSSMU2$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU3$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU6$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU8$        PUBLIC UNDOTBS1           ONLINE
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.

3 从上步的结果，结合文档ID 835944.1，基本上可以找到问题的原因：当前数据库中有未结束的分布式事务，而这些未结束的分布式事务占用了UNDOTBS1，最终导致不能删除UNDOTBS1。文档ID 835944.1给出的解决方案是结束掉这些分布式事务。MetaLink上给出的思路是参照 Note 401302.1文档，而不凑巧的是，现在这篇文档无法查看，不得已，尝试重启数据库并删除UNDOTBS1。重启之后，alert日志里看到如下错误信息：

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-10 11:27:15.527
*** SESSION ID:(431.1) 2012-02-10 11:27:15.527
*** 2012-02-10 11:27:15.527
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:27:50.676
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:28:42.707
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found

尝试再次删除UNDOTBS1：

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

这次报出ORA-01548的错误！！！原来还是分布式事务未提交导致的：

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.
SQL>

4 继续MetaLink：ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]根据该文档，执行下述命令：

SQL> Select segment_id,segment_name,status,tablespace_name
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         2 _SYSSMU2$                      PARTLY AVAILABLE UNDOTBS1
         3 _SYSSMU3$                      PARTLY AVAILABLE UNDOTBS1
         6 _SYSSMU6$                      PARTLY AVAILABLE UNDOTBS1
         8 _SYSSMU8$                      PARTLY AVAILABLE UNDOTBS1
SQL>

You find you have segments that are 'Partly Available' This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. 当回滚段状态为Partly Available时，说明还是有事务没结束！！！

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn
  7  in(2,3,6,8);

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL>

通过上面的结果，看到2、3、6、8号回滚段上有活动的事务。该文档依然指出解决方案是查看Note 401302.1文档，而该文档又无法打开，不得已Google之，参照 http://blog.itpub.net/post/38439/477038 获得解决问题的最终方法。

5 根据http://blog.itpub.net/post/38439/477038 直接强制提交这些活动的分布式事务：

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '2.38.583286';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '8.7.957413';

Commit complete.

SQL> commit force '3.29.982959';

Commit complete.

SQL> commit force '6.14.945326';

Commit complete.

SQL> commit force '13.19.507098';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             forced commit
3.29.982959            forced commit
6.14.945326            forced commit
13.19.507098           forced commit

6 rows selected.

SQL> Select segment_id,segment_name,status,tablespace_name           
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

no rows selected

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 
  2  KTUXESTA Status,                                          
  3  KTUXECFL Flags                                            
  4  FROM x$ktuxe                                              
  5  WHERE ktuxesta!='INACTIVE'                                
  6  AND ktuxeusn                                              
  7  in(2,3,6,8);                                              

no rows selected

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

6 终于干掉了那个旧的UNDO，而此时，alert日志里的信息如下：

Fri Feb 10 15:08:16 CST 2012
DISTRIB TRAN 44444444.D7D4863A714B974489CD48496956271900000000
  is local tran 2.38.583286 (hex=02.26.8e676)
  change pending prepared tran, scn=125560421 (hex=0.077be665)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:08:56 CST 2012
DISTRIB TRAN 44444444.D88A6D59B486E44D8BAD8DABFDCF289C00000000
  is local tran 8.7.957413 (hex=08.07.e9be5)
  change pending prepared tran, scn=194136242 (hex=0.0b9248b2)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:08 CST 2012
DISTRIB TRAN 44444444.4A67F0F3F3EA464081883577EE646AAB00000000
  is local tran 3.29.982959 (hex=03.1d.effaf)
  change pending prepared tran, scn=195270309 (hex=0.0ba396a5)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:20 CST 2012
DISTRIB TRAN 44444444.97CB87A6BAE9E943B761C9C7FDA9844600000000
  is local tran 6.14.945326 (hex=06.0e.e6cae)
  change pending prepared tran, scn=196753377 (hex=0.0bba37e1)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:36 CST 2012
DISTRIB TRAN 44444444.192AEFF6D316B2468F5D74FE5EBDC9EC00000000
  is local tran 13.19.507098 (hex=0d.13.7bcda)
  change pending prepared tran, scn=332059676 (hex=0.13cad41c)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:13:17 CST 2012
drop tablespace undotbs1 including contents and datafiles
Fri Feb 10 15:13:25 CST 2012
Deleted Oracle managed file +ORADATA/glndb/datafile/undotbs1.261.726057859
Completed: drop tablespace undotbs1 including contents and datafiles

7 总结：对于分布式事务，目前还不是很清楚。而这个案例中涉及到的情况还有可能出现更为复杂的情况，需要深入研究一下，而我遇到的这种情况属于比较简单的。

删除用户报ORA-24005错误及解决办法

2月 9,2012 有1条评论 Oracle

在一套10.2.0.5.0的双节点RAC数据库上，删除用户时报出ORA-00604及ORA-24005的错误：

 
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> drop user gdhytest cascade;
drop user gdhytest cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
SQL>

后经查找MetaLink：Ora-24005 Error Trying To Drop User Sysman Cascade [ID 456437.1] 找到原因：被删除的用户ghhytest拥有queue table。

 
SQL> set pagesize 100 
SQL> col object_name format a40 
SQL> select object_name,object_type from dba_objects
  2  where owner='GDHYTEST' AND OBJECT_NAME LIKE '%AQ%'
  3  ;
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
AQ$_GPSSTATUS_QUEUE_TABLE_H              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_I              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T              TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR              TABLE
SQL>

解决方法：

1 gdhytest登录数据库，执行DBMS_AQADM.DROP_QUEUE_TABLE进行删除queue talbe:

 
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2 发现权限不够，赋权，重新删除：

 
SQL> conn / as sysdba;
Connected.
SQL> grant dba to gdhytest;
Grant succeeded.
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24
characters
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE GDHYTEST.GPSSTATUS_QUEUE_TABLE_H does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1

3 依然报错！！！QUEUE_TABLE too long,不得已，重命名queue table进行删除：

 
SQL> rename  AQ$_GPSSTATUS_QUEUE_TABLE_H to queue1;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
QUEUE1                         TABLE
7 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE1',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
6 rows selected.
SQL>

4 如法炮制，重命名其它queue table,然后执行删除：

 
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_I to queue_a;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_NR to queue_b;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_T to queue_c;
Table renamed.
SQL> rename aq$_GPS_TEMP_QUEUE_TABLE_NR to queue_d;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_60452             TABLE
SYS_IOT_OVER_60459             TABLE
QUEUE_B                        TABLE
QUEUE_A                        TABLE
QUEUE_C                        TABLE
QUEUE_D                        TABLE
6 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_A',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_B',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_C',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_D',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
no rows selected

5 最后彻底删除gdhytest用户：

 
SQL> conn / as sysdba;
Connected.
SQL> drop user gdhytest cascade;
User dropped.
SQL>

调试存储过程：ORA-0131 Insufficient privileges 处理

2月 7,2012 留下评论 Oracle

昨天，一开发同事反映说在PL/SQL Developer工具里无法调试存储过程，报错信息如下：

ORA-0131:Insufficient privileges.

Note:Debugging requires the DEBUG CONNECT SESSION system privileges.

后经查找，是缺失 DEBUG CONNECT SESSION 系统权限所致。

解决办法：以SYS用户登录数据库，执行赋权操作：

SQL> grant  DEBUG CONNECT SESSION to user_name;

附1：有网友指出还需赋予DEBUG ANY PROCEDURE的权限，经测试，该权限可不用赋予！

附2：可以从数据字典role_sys_privs表查看该权限相关信息：

SQL> conn / as sysdba;
Connected.
SQL> select * from role_sys_privs where privilege like 'DEBUG%' order by 2;
ROLE             PRIVILEGE                ADM
---------------- ------------------------ ---
DBA              DEBUG ANY PROCEDURE      YES
JAVADEBUGPRIV    DEBUG ANY PROCEDURE      NO
DBA              DEBUG CONNECT SESSION    YES
JAVADEBUGPRIV    DEBUG CONNECT SESSION    NO

简单记录，以作备忘！

知识还是点点记录好！

Archive 2012年2月28日

主库创建数据文件，确认物理备库是否自动生成对应的数据文件？

主库做事务操作，在物理备库上确认是否可以看到对应的记录？

主备库完成一次switchover的Role transaction。

测试一：主库创建数据文件，备库确认能否看到对应的数据文件？

1 首先，在主库上创建一个测试表空间TEST：

2 然后，在物理备库上查看是否生成该文件？

3 其次，我们在备库上启动Redo Apply：

4 最后，我们在主库上删除TEST表空间后，同样在备库上看到TEST表空间自动被删除！至此，说明我们的Dataguard功能测试一完成。

测试二：主库创建测试数据，到备库上去查看，query是否同步？

1 主库创建测试数据：

2 备库确认：

3 最后，发现备库停止Redo Apply后，并将数据库以read only方式打开后，我们可以看到数据已经同步到物理备库！说明功能测试二已经达到预期目的！

测试三：角色转换之switchover

1 物理备库置于MOUNT状态。在switchover过程中，如果目标库是物理备库的话，最好是将物理备库置于MOUNT状态，当然也可以置于OPEN READ ONLY方式，不过官方文档说处于OPEN READ ONLY方式时，可能会延长switchover的时间。

2 RAC主库停止glndb2实例，只留glndb1运行：

4 切换原RAC主库到备库角色。

5 关闭原主库实例glndb1，重新启动glndb1到MOUNT状态：

7 切换原备库至主库角色。

8 完成原主库到新主库的角色转换。

9 在新备库上[原RAC主库]启动glndb2实例至MOUNT状态，并启用redo apply，确认数据是否能与新主库[原单实例备库]同步？

一 安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

二 oracle软件安装完成之后，接下来，开始升级Oracle软件到10.2.0.5.0版本：

三 接下去的工作便是配置ASM存储，创建ASM实例：

一 首先了解下现有双节点RAC数据库配置信息：

二 在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用

一安装oracle软件，在一台干净的Linux服务器上安装一套oracle 10g数据库软件，需要完成以下任务：

三接下去的工作便是配置ASM存储，创建ASM实例：

一首先了解下现有双节点RAC数据库配置信息：

二在获取了RAC主库的基本信息之后，接下来搭建一套新的机器做物理dataguard用