物理Data Guard的配置

最新推荐文章于 2022-07-11 14:00:00 发布
置顶 最新推荐文章于 2022-07-11 14:00:00 发布
阅读量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)
    )
  )


 


 

 

然然昇気
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle Data Guard详细配置过程
03-24
NULL 博文链接：https://wallimn.iteye.com/blog/1114371
Oracle Data Guard 重要配置参数
乐沙弥的世界
08-13 6497
Oracle Data Guard主要是通过为生产数据库提供一个或多个备用数据库(是产生数据库的一个副本)，以保证在主库不可用或异常时数据库不丢失并通过备用数据库继续提供服务。对于Oracle DG的配置，我们可以通过Grid Control来完成，也可以通过Data Guard Broker以及SQL*Plus来完成。对于前两者方式可以在图形界面上完成，操作简单。而对于使用SQL*Plus命令行
逻辑Data Guard的配置
u013827322的专栏
03-26 871
逻辑Data Guard的配置 物理Standby转换为逻辑Standby
physical data guard 的原理 (zt)
cuixie2370的博客
02-27 73
本文网址:http://bbs.bitscn.com/120821 复制 物理data guard原理的理解这里只谈物理data guard的原理*****************************************...
Oracle 11g Dataguard搭建及知识梳理
热门推荐
清风弄影
04-01 1万+
配置了一次ｄａｔａｇｕａｒｄ，发现还挺麻烦，梳理了一些知识点，并记录下来。一.dataguard的三种模式最大性能模式（Maximum Performance）：就是保证主库，备库数据不保证最大可用模式（Maximum Availability）：注意这里最大可用和我们平时理解不一样，是指dataguard最大可用，不是指主库最大可用！最大保护模式（Maximum Protection） ：主库备...
官方资料：技术白皮书Oracle Data Guard 11g数据保护和可用性的新纪元.pdf
06-22
DATA GUARD进程体系结构：主要的技术组件Data Guard配置，Data Guard 配置，保护模式，最大保护，最高可用性，最高性能，自动解析差异_处理通信故降，应用服务一重做应用和SQL应用，物理备用数据库一重做应用，将敢...
Oracle Data Guard 11g完全参考手册
12-31
这本精品指南全面介绍Data Guard无与伦比的数据保护、可用性和灾难恢复功能，详细讲解Data Guard基础知识，列出最佳的配置、监视、维护和故障排除实践，并指导您部署完美架构以满足灾难恢复目标和其他要求。...
【DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置
07-31
【DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置.pdf【DATAGUARD】物理dg配置客户端无缝切换 (八.1)--Data Guard Broker 的配置.pdf
DataGuard配置过程_for_oracle11gR2
07-13
在primary节点和standby节点安装操作系统和oracle软件，创建主数据库，创建物理备用库，创建备用日志。。。详细操作过程描述 在primary节点和standby节点安装操作系统和oracle软件。。。
oracle数据库datagard搭建物理备库（主动切换）-详细笔记文档总结
最新发布
06-30
oracle数据库datagard搭建物理备库（主动切换）—详细笔记文档总结
dataguard 中需要配置的参数及其步骤
weixin_50393358的博客
07-11 377
.请说明物理备库的的完整创建过程。 答：一、启用强制日志 force_logging； 二、配置备用重做日志组，数目要比联机重做日志组多 1 组； 三、配置 log_archive_dest_n 参数，确定归档存放的目标位置； 四、配置 log_archive_config 参数，确定传送和接收重做数据的数据库名称； 五、开启主库的 archivelog 归档模式； 六、配置快速恢复区的大小 db_recovery_file_dest_size； 七、使用 RMAN 连接到主库，创建一个包含归档日志的完全备
配置Data Guard涉及到的参数详解
nsj820的专栏
05-20 1186
1.DB_NAME，数据库名字，需要保持同一个Data Guard 中所有数据库DB_NAME相同primary端和standby端相同*.DB_NAME=ora10g*.DB_NAME=ora10g2.DB_UNIQUE_NAME，对应数据库的实例名，每一个数据库需要指定一个唯一的名字primary端：*.DB_UNIQUE_NAME=ora10gstandby端：*.db_unique
oracle 11g dataguard 推荐asm,Oracle 11g Dataguard Duplicate物理备库配置
weixin_39634579的博客
04-03 116
本文采用Oracle 11g Dataguard broker fastfailover测试Oracle 11g Dataguard fast failover配置，需要主备数据库开启闪回功能，闪回功能开启本文略过。闪回开启需要启动到mount状态时，主备库的监听不要随意关闭。1. dgmgrl查看主备库状态$ dgmgrl sys/oracleDGMGRL for Linux: Version ...
Linux下搭建Oracle物理Data guard
weixin_33797791的博客
08-05 98
Linux下搭建Oracle物理Data guard   一、引言     近期利用手头的空余机器搭建了Oracle物理Data guard用于学习，现将过程与大家分享。   二、环境准备 u 硬件     两台主机，当然如果只有一台主机也可以，就是在恢复数据库时需要路径转换，正好手头有两台闲置的主机，所以就省去了许多麻烦。     另，实际环境中也应是不同的主机搭建Data gu...
物理Data Guard的日常维护
weixin_34235135的博客
01-04 75
关闭顺序 1. 首先关闭primary数据库 [oracle@Master admin]$ sqlplus / as sysdba SQL> shutdown immediate 2. 关闭standby数据库 [oracle@Salve ~]$ sqlplus / as sysdba --查看备库是否在应用日志进行恢复 SQL>select process,...
总结11g 物理data guard
08-07 538
郭老师的帖子，咱也凑凑热闹总结一下自己！ 以下均物理data guard 1、Data Guard的核心操作是什么？ redo send、redo receive、redo apply redo send==>log_archive_dest_2;log_archive_dest_state_2=enable;LNSn(lgwr network server process)|LGWR
物理dataguard傻瓜实战
chaxiao的专栏
05-13 666
一、环境 1、环境准备 虚拟机连接方式：注意连接方式虚拟机net，同时点击edit，查看虚拟机网段 操作系统：Red Hat Enterprise Linux Server release 5.1 (Tikanga) 主库地址：192.168.220.3--虚拟机，安装数据库 备库地址：192.168.220.4--虚拟机，未安装数据库，只安装软件 检查数据库版本必须一致： sele
Oracle的Expdp和Impdp之ORA-39151
u013827322的专栏
03-06 8742
Oracle的Expdp和Impdp之ORA-39151
Data Pump的导入和导出数据（ORA-31684）
u013827322的专栏
04-13 3079
Data Pump的导入和导出数据（ORA-31684）
Oracle 19c Data Guard物理备库搭建
06-07
其中，ORCL_DG为Data Guard配置名称，ORCL为备库的DB_UNIQUE_NAME，可以根据实际情况进行修改。 8. 启动Redo Transport 在主库上执行以下命令： ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 在备库上...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值