物理Data Guard的配置

置顶 于 2014-03-20 20:43:38 发布
阅读量1.2k 收藏 2
点赞数
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u013827322/article/details/21644389
版权

环境：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)
    )
  )


 


 

 

然然昇気
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值