物理DataGuard的配置

环境：RHEL5Oracle 10g 源数据库： IP：192.168.1.222 数据库SID：orcl DB_UNIQUE_NAME：orclpre 数据库安装软件：/u01/app/oracle/10.2.0/db_1 数据库文件路径：/u01/app/oracle/oradata/orcl 本地归档路径：/u01/arch_orcl Debug日志输出路径：/u01/app/or

环境：RHEL5+Oracle 10g

源数据库：
IP：192.168.1.222
数据库SID：orcl
DB_UNIQUE_NAME：orclpre
数据库安装软件：/u01/app/oracle/10.2.0/db_1
数据库文件路径：/u01/app/oracle/oradata/orcl
本地归档路径：/u01/arch_orcl
Debug日志输出路径：/u01/app/oracle/admin/orcl
目标数据库：
IP：192.168.1.223
数据库SID：orclstd
DB_UNIQUE_NAME：orclpdg
数据库安装软件：/u01/app/oracle/10.2.0/db_1
数据库文件路径：/u01/app/oracle/oradata/orclstd
本地归档路径：/u01/arch_orclstd
Debug日志输出路径：/u01/app/oracle/admin/orclstd

1.确认主库处于归档模式：
SQL> set sqlprompt "ORCLPRE >"
ORCLPRE >archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch_orcl
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
2.将Primary数据库置于Force Logging模式
ORCLPRE >select force_logging from v$database;

FOR
---
YES
当为NO时：（alter database force logging）
3.配置Primary数据库的初始化参数
ORCLPRE >create pfile='/u01/dg/pfile_orcl.ora' from spfile;

File created.
修改：
*.db_name='orcl'
*.db_unique_name='orclpre'
*.log_archive_config='dg_config=(orclpre,orclpdg)'
*.log_archive_dest_2='service=orcls_192.168.1.223 arch valid_for=(online_logfiles,primary_role) db_unique_name=orclpdg'
*.log_archive_dest_state_2=defer
*.fal_client='orcl_192.168.1.222'
*.fal_server='orcls_192.168.1.223'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd'
*.standby_file_management=auto
通过PFILE重建SPFILE：
ORCLPRE >shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ORCLPRE >create spfile from pfile='/u01/dg/pfile_orcl.ora';

File created.
4.创建StandBy数据库控制文件
ORCLPRE >startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
ORCLPRE >alter database create standby controlfile as '/u01/dg/orclstd01.ctl';

Database altered.
5.复制相关文件到Standby数据库
[oracle@localhost u01]$ scp oracle@192.168.1.222:/u01/dg/orclstd01.ctl /u01/dg/orclstd01.ctl
oracle@192.168.1.222's password:
orclstd01.ctl 100% 7056KB 6.9MB/s 00:00
[oracle@localhost u01]$ scp oracle@192.168.1.222:/u01/dg/pfile_orcl.ora /u01/dg/pfile_orclstd.ora
oracle@192.168.1.222's password:
pfile_orcl.ora 100% 1619 1.6KB/s 00:00
[oracle@localhost u01]$ scp oracle@192.168.1.222:/u01/dg/orclstd01.ctl /u01/dg/orclstd02.ctl
oracle@192.168.1.222's password:
orclstd01.ctl 100% 7056KB 6.9MB/s 00:01
[oracle@localhost u01]$ scp oracle@192.168.1.222:/u01/dg/orclstd01.ctl /u01/dg/orclstd03.ctl
oracle@192.168.1.222's password:
orclstd01.ctl 100% 7056KB 3.5MB/s 00:02
Primary数据文件热备：
ORCLPRE>set line 150 pages 1000
ORCLPRE>col file_name for 50
SP2-0246: Illegal FORMAT string "50"
ORCLPRE>col file_name for a50
ORCLPRE>select file_name,tablespace_name from dba_data_files order by 2;

FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/rec_catalog/rmantbs.dbf RMANTBS
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/users01.dbf USERS
依次对表空间进行备份：
ORCLPRE >alter tablespace example begin backup;

Tablespace altered.

ORCLPRE >host cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/dg/data

ORCLPRE >alter tablespace example end backup;

Tablespace altered.

ORCLPRE >alter tablespace rmantbs begin backup;

Tablespace altered.

ORCLPRE >host cp /u01/rec_catalog/rmantbs.dbf /u01/dg/data;

ORCLPRE >alter tablespace rmantbs end backup;

Tablespace altered.

ORCLPRE >alter tablespace sysaux begin backup;

Tablespace altered.

ORCLPRE >host cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/dg/data

ORCLPRE >alter tablespace sysaux end backup;

Tablespace altered.

ORCLPRE >alter tablespace system begin backup;

Tablespace altered.

ORCLPRE >host cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/dg/data
ORCLPRE >alter tablespace system end backup;

Tablespace altered.
ORCLPRE >alter tablespace undotbs1 begin backup;

Tablespace altered.

ORCLPRE >host cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/dg/data

ORCLPRE >alter tablespace undotbs1 end backup;

Tablespace altered.

ORCLPRE >alter tablespace users begin backup;
Tablespace altered.

ORCLPRE >host cp /u01/app/oracle/oradata/orcl/users01.dbf /u01/dg/data

ORCLPRE >alter tablespace users end backup;

Tablespace altered.
从Primary复制数据库文件到Standby
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/example01.dbf /u01/dg/data/example01.dbf
oracle@192.168.1.222's password:
example01.dbf 100% 100MB 5.3MB/s 00:19
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/rmantbs.dbf /u01/dg/data/rmantbs.dbf
oracle@192.168.1.222's password:
rmantbs.dbf 100% 50MB 8.3MB/s 00:06
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/sysaux01.dbf /u01/dg/data/sysaux01.dbf
oracle@192.168.1.222's password:
sysaux01.dbf 100% 250MB 8.6MB/s 00:29
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/system01.dbf /u01/dg/data/system01.dbf
oracle@192.168.1.222's password:
system01.dbf 100% 490MB 8.2MB/s 01:00
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/undotbs01.dbf /u01/dg/data/undotbs01.dbf
oracle@192.168.1.222's password:
undotbs01.dbf 100% 30MB 15.0MB/s 00:02
[oracle@localhost dg]$ scp oracle@192.168.1.222:/u01/dg/data/users01.dbf /u01/dg/data/users01.dbf
oracle@192.168.1.222's password:
users01.dbf 100% 16MB 16.3MB/s 00:01
复制到数据文件目录下面：
Standby只安装了软件，没安装数据库
[oracle@localhost orclstd]$ cp /u01/dg/data/* /u01/app/oracle/oradata/orclstd/
[oracle@localhost orclstd]$ ls
example01.dbf sysaux01.dbf undotbs01.dbf
rmantbs.dbf system01.dbf users01.dbf
环境准备：
[oracle@localhost admin]$ mkdir orclstd
[oracle@localhost admin]$ ls
orclstd
[oracle@localhost admin]$ pwd
/u01/app/oracle/admin
[oracle@localhost orclstd]$ pwd
/u01/app/oracle/oradata/orclstd
[oracle@localhost orclstd]$ cp /u01/dg/*.ctl /u01/app/oracle/oradata/orclstd
[oracle@localhost orclstd]$ ls
example01.dbf orclstd02.ctl rmantbs.dbf system01.dbf users01.dbf
orclstd01.ctl orclstd03.ctl sysaux01.dbf undotbs01.dbf

6.配置监听和网络服务名
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
#### (PROGRAM = orcl)
(GLOBAL_DBNAME = orcl)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
)
重启监听服务：
[oracle@linux5 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-MAR-2014 01:05:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.222)(PORT=1521)))
The command completed successfully
[oracle@linux5 admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-MAR-2014 01:06:08

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

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

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.222)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 19-MAR-2014 01:06:08
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/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.222)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
tnsnames.ora
orcls_192.168.1.223=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orclstd)
)
)

orcl_192.168.1.222=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)

7.创建秘钥文件
[oracle@linux5 dbs]$ ls
alert_orcl.log initdw.ora lkORCL orapw_bak spfileorcl.ora
hc_orcl.dat init.ora lkORCLPRE snapcf_orcl.f
[oracle@linux5 dbs]$ pwd
/u01/app/oracle/10.2.0/db_1/dbs
[oracle@linux5 dbs]$ orapwd file=/u01/app/oracle/10.2.0/db_1/dbs/orapworclpassword=safe entries=30

命名规则：

windows：PWD[sid].ora

Linux:orapw[sid]千万不要加后缀，否则密码文件格式不对，导致Standby数据库无法接受归档文件。
[oracle@linux5 dbs]$ ls
alert_orcl.log initdw.ora lkORCL orapw_bak snapcf_orcl.f
hc_orcl.dat init.ora lkORCLPRE orapworcl spfileorcl.ora
复制该秘钥文件到Standby数据库
[oracle@localhost u01]$ scp oracle@192.168.1.222:/u01/app/oracle/10.2.0/db_1/dbs/orapworcl /u01/app/oracle/10.2.0/db_1/dbs/orapworclstd
oracle@192.168.1.222's password:
orapworcl.ora 100% 5120 5.0KB/s 00:00

Standby数据库配置：
1.
2.创建日志输出文件相关目录：
[oracle@localhost admin]$ pwd
/u01/app/oracle/admin
[oracle@localhost admin]$ ls
orclstd
[oracle@localhost admin]$ cd orclstd/
[oracle@localhost orclstd]$ ls
[oracle@localhost orclstd]$ mkdir -p {a,b,c,u}dump
[oracle@localhost orclstd]$ ls
adump bdump cdump udump
3.配置监听和网络服务名
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orclstd)
(ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
#### (PROGRAM = orclstd)
(GLOBAL_DBNAME = orclstd)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
)
tnsnames.ora
ORCL_192.168.1.222 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
ORCLS_192.168.1.223 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orclstd)
)
)
测试：
Primary：
[oracle@linux5 orcl]$ tnsping orcls_192.168.1.223

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAR-2014 01:25:27

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

Used parameter files:
/u01/app/oracle/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcls)))
OK (10 msec)

Standby：
[oracle@localhost admin]$ tnsping orcl_192.168.1.222

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAR-2014 01:25:00

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl)))
OK (0 msec)
4.修改初始化参数文件
*.audit_file_dest='/u01/app/oracle/admin/orclstd/adump'
*.background_dump_dest='/u01/app/oracle/admin/orclstd/bdump'
*.control_files='/u01/app/oracle/oradata/orclstd/orclstd01.ctl','/u01/app/oracle/oradata/orclstd/orclstd02.ctl','/u01/app/oracle/oradata/orclstd/orclstd03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orclstd/cdump'
*.db_name='orcl'
*.db_unique_name='orclpdg'
*.user_dump_dest='/u01/app/oracle/admin/orclstd/udump'
*.log_archive_config='dg_config=(orclpre,orclpdg)'
*.log_archive_dest_state_2=enable
*.log_archive_dest_1='location=/u01/arch_orclstd'
*.log_archive_dest_2='service=orcl_192.168.1.222 arch valid_for=(online_logfiles,primary_role) db_unique_name=orclpre'
*.fal_client='orcl_192.168.1.223'
*.fal_server='orcls_192.168.1.222'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl'
修改后保存，以sysdba身份连接到Standby
SQL> conn sys/safe@orcls_192.168.1.223
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
SQL> conn sys/safe@orcls_192.168.1.223 as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/oradata/orclstd/pfile_orclstd.ora';

File created.
启动Standby数据库到MOUNT状态
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
控制文件转换路径：
SQL> show parameter convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/orcl,
/u01/app/oracle/oradata/orclst
d, /u01/app/oracle/oradata/orc
lstd, /u01/app/oracle/oradata/
orcl
log_file_name_convert string /u01/app/oracle/oradata/orcl,
/u01/app/oracle/oradata/orclst
d, /u01/app/oracle/oradata/orc
lstd, /u01/app/oracle/oradata/
orcl
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclstd/system01.dbf
/u01/app/oracle/oradata/orclstd/undotbs01.dbf
/u01/app/oracle/oradata/orclstd/sysaux01.dbf
/u01/app/oracle/oradata/orclstd/users01.dbf
/u01/app/oracle/oradata/orclstd/example01.dbf
/u01/rec_catalog/rmantbs.dbf

6 rows selected.
接受归档文件：
******************
Pramary:
SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch_orcl
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
Standby:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch_orcl
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost admin]$ cd /u01/arch_orclstd/
[oracle@localhost arch_orclstd]$ ls
archive_1_10_842209949.arclog archive_1_4_842209949.arclog
archive_1_11_842209949.arclog archive_1_5_842209949.arclog
archive_1_12_842209949.arclog archive_1_6_842209949.arclog
archive_1_1_842209949.arclog archive_1_7_842209949.arclog
archive_1_2_842209949.arclog archive_1_8_842209949.arclog
archive_1_3_842209949.arclog archive_1_9_842209949.arclog
测试：
orclpre>conn sys/safe@orcl_192.168.1.222 as sysdba
Connected.
orclpre>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
27

orclpre>conn sys/safe@orcls_192.168.1.223 as sysdba
Connected.
orclpre>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
27
parmary:
SQL> conn test/oracle
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
BIN$8++Yu/zZ6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zc6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zd6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/ze6sLgQAB/AQBXxQ==$0 TABLE
BIN$8++Yu/zi6sLgQAB/AQBXxQ==$0 TABLE
TEST TABLE

11 rows selected.
SQL> select * from test;

ID
----------
1
2
3
4
5555555555
SQL> insert into test values(666666);

1 row created.

SQL> commit;

Commit complete.
Standby:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
针对这个错误，网上有不同的解决方案，但是没有我想要的，网上的可能有不同的出错原因，具体情况具体分析，于是查看日志：
cat alert_orclstd.log

Thu Mar 20 00:07:25 2014
ORA-16136 signalled during: alter database recover managed standby database cancel...
Thu Mar 20 00:13:33 2014
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Mar 20 00:15:50 2014
alter database open
Thu Mar 20 00:15:50 2014
Errors in file /u01/app/oracle/admin/orclstd/bdump/orclstd_dbw0_5099.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/rec_catalog/rmantbs.dbf'
ORA-27037: unable to obtain file status
原因：无法识别file6，这时才发现rmantbs以前做实验留下的表空间，路径里面没有添加转换。

修改为：
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'

重建spfile即可。
Primary添加测试数据：
SQL> select * from test;

ID
----------
1
2
3
4

SQL> insert into test values(5);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

Standby检验数据是否更新：
[oracle@localhost bdump]$ sqlplus sys/safe@orcls_192.168.1.223 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 20 01:44:27 2014

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

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

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from scott.test;

ID
----------
1
2
3
4
5
经过好多次的修改，物理DataGuard搭建成功……细节决定成败，一定要细心！

附件：初始化参数文件、监听文件、网络服务文件。
Primary数据库：

Pfile：

orcl.__db_cache_size=88080384
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=62914560
orcl.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orclpre'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl_192.168.1.222'
*.fal_server='orcls_192.168.1.223'
*.job_queue_processes=10
*.log_archive_config='dg_config=(orclpre,orclpdg)'
*.log_archive_dest_1='location=/u01/arch_orcl'
*.log_archive_dest_2='service=orcls_192.168.1.223 arch valid_for=(online_logfiles, primary_role) db_unique_name=orclpdg'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='archive_%t_%s_%r.arclog'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

listener.ora:

# listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
    ####  (PROGRAM = orcl)
	(GLOBAL_DBNAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
  )

tnsnames.ora:

# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

orcls_192.168.1.223=
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orclstd)
    )
  )

orcl_192.168.1.222=
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

Standby数据库：

Pfile：

orcl.__db_cache_size=96468992
orcls.__db_cache_size=100663296
orclstd.__db_cache_size=100663296
orcl.__java_pool_size=4194304
orcls.__java_pool_size=4194304
orclstd.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcls.__large_pool_size=4194304
orclstd.__large_pool_size=4194304
orcl.__shared_pool_size=54525952
orcls.__shared_pool_size=54525952
orclstd.__shared_pool_size=54525952
orcl.__streams_pool_size=4194304
orcls.__streams_pool_size=0
orclstd.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orclstd/adump'
*.background_dump_dest='/u01/app/oracle/admin/orclstd/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/orclstd/orclstd01.ctl','/u01/app/oracle/oradata/orclstd/orclstd02.ctl','/u01/app/oracle/oradata/orclstd/orclstd03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/orclstd/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orclpdg'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl_192.168.1.223'
*.fal_server='orcls_192.168.1.222'
*.job_queue_processes=10
*.log_archive_config='dg_config=(orclpre,orclpdg)'
*.log_archive_dest_1='location=/u01/arch_orclstd'
*.log_archive_dest_2='service=orcl_192.168.1.222 arch valid_for=(online_logfiles, primary_role) db_unique_name=orclpre'
*.log_archive_dest_state_2='enable'
*.log_archive_format='archive_%t_%s_%r.arclog'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orcl','/u01/rec_catalog','/u01/app/oracle/oradata/orclstd','/u01/app/oracle/oradata/orclstd','/u01/rec_catalog'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/orclstd/udump'

listener.ora：

# listener.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orclstd)
      (ORACLE_HOME = /u01/app/oracle/10.2.0/db_1)
####      (PROGRAM = orclstd)
	(GLOBAL_DBNAME = orclstd)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
  )

tnsnames.ora:

# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL_192.168.1.222 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.222)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

ORCLS_192.168.1.223 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.223)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orclstd)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )