11g R2 LINUX RHEL 5.4 配置dataguard 创建物理standby的过程及遇到错误处理

最新推荐文章于 2021-05-03 01:14:54 发布

cpfengyo322322

最新推荐文章于 2021-05-03 01:14:54 发布

阅读量111

点赞数

文章标签：操作系统网络数据库

主要过程如下
Creating a Physical Standby Database

Reference	Task	Database
Section 3.2.1	Create a Backup Copy of the Primary Database Datafiles	Primary
Section 3.2.2	Create a Control File for the Standby Database	Primary
Section 3.2.3	Prepare an Initialization Parameter File for the Standby Database	Primary
Section 3.2.4	Copy Files from the Primary System to the Standby System	Primary
Section 3.2.5	Set Up the Environment to Support the Standby Database	Standby
Section 3.2.6	Start the Physical Standby Database	Standby
Section 3.2.7	Verify the Physical Standby Database Is Performing Properly	Standby

下面贴出我的创建过程以供参考
初始参数文件设置
primary 端
*.audit_file_dest='/boot/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/boot/u02/oradata/ORCL_1/controlfile/o1_mf_6sot5rdl_.ctl','/boot/u03/oradata/ORCL_1/controlfile/o1_mf_6sot5rlk_.ctl','/boot/u04/oradata/ORCL_1/controlfile/o1_mf_6sot5rrh_.ctl','/boot/u05/oradata/ORCL_1/controlfile/o1_mf_6sot5s15_.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='/boot/u02/oradata'
*.db_create_online_log_dest_1='/boot/u02/oradata'
*.db_create_online_log_dest_2='/boot/u03/oradata'
*.db_create_online_log_dest_3='/boot/u04/oradata'
*.db_create_online_log_dest_4='/boot/u05/oradata'
*.db_domain=''
*.db_file_name_convert='/boot/u02/oradata/ORCL_2/datafile','/boot/u02/oradata/ORCL_1/datafile'
*.db_name='orcl'
*.db_recovery_file_dest='/boot/u03/recovery_area'
*.db_recovery_file_dest_size=8589934592
*.db_unique_name='orcl_1'
*.diagnostic_dest='/boot/u01/app/oracle'
*.fal_client='primary'
*.fal_server='standby1'
*.log_archive_config='dg_config=(orcl_1,orcl_2,orcl_3)'
*.log_archive_dest_1='LOCATION=/boot/u02/oradata'
*.log_archive_dest_2='LOCATION=/boot/u03/oradata'
*.log_archive_dest_3='LOCATION=/boot/u04/oradata'
*.log_archive_dest_4='service=standby1 arch valid_for=(online_logfile,primary_role) db_unique_name=orcl_2'
*.log_archive_dest_5='service=standby2 lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl_3'
*.log_archive_dest_state_4='ENABLE'
*.log_archive_dest_state_5='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/boot/u02/oradata/ORCL_2/onlinelog','/boot/u02/oradata/ORCL_1/onlinelog','/boot/u03/oradata/ORCL_2/onlinelog','/boot/u03/oradata/ORCL_1/onlinelog','/boot/u04/oradata/ORCL_2/onlinelog','/boot/u04/oradata/ORCL_1/onlinelog','/boot/u05/oradata/ORCL_2/onlinelog','/boot/u05/oradata/ORCL_1/onlinelog'
*.memory_target=377487360
*.nls_language='AMERICAN'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

standby端

*.audit_file_dest='/boot/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/boot/u02/oradata/ORCL_2/controlfile/standby01.ctl','/boot/u02/oradata/ORCL_2/controlfile/standby02.ctl','/boot/u02/oradata/ORCL_2/controlfile/standby03.ctl'
*.db_block_size=8192
*.db_create_file_dest='/boot/u02/oradata'
*.db_create_online_log_dest_1='/boot/u02/oradata'
*.db_create_online_log_dest_2='/boot/u03/oradata'
*.db_create_online_log_dest_3='/boot/u04/oradata'
*.db_create_online_log_dest_4='/boot/u05/oradata'
*.db_domain=''
*.db_file_name_convert='/boot/u02/oradata/ORCL_1/datafile','/boot/u02/oradata/ORCL_2/datafile'
*.db_name='orcl'
*.db_recovery_file_dest='/boot/u03/recovery_area'
*.db_recovery_file_dest_size=8589934592
*.db_unique_name='orcl_2'
*.diagnostic_dest='/boot/u01/app/oracle'
*.fal_client='standby1'
*.fal_server='primary'
*.log_archive_config='dg_config=(orcl_1,orcl_2)'
*.log_archive_dest_1='LOCATION=/boot/u02/oradata/ORCL_2/archivelog'
*.log_archive_dest_2='LOCATION=/boot/u03/oradata/ORCL_2/archivelog'
*.log_archive_dest_3='LOCATION=/boot/u04/oradata/ORCL_2/archivelog'
*.log_archive_dest_4='SERVICE=primary ARCH VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_1'
*.log_archive_dest_state_4='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/boot/u02/oradata/ORCL_1/onlinelog','/boot/u02/oradata/ORCL_2/onlinelog','/boot/u03/oradata/ORCL_1/onlinelog','/boot/u03/oradata/ORCL_2/onlinelog','/boot/u04/oradata/ORCL_1/onlinelog','/boot/u04/oradata/ORCL_2/onlinelog','/boot/u05/oradata/ORCL_1/onlinelog','/boot/u05/oradata/ORCL_2/onlinelog'
*.memory_target=377487360
*.nls_language='AMERICAN'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

有些参数用于角色切换，有些是必须的

NET配置（使用net manager）

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

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /boot/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /boot/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl_2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /boot/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl_3)
    )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 8088))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 3525))
    )
)

ADR_BASE_LISTENER = /boot/u01/app/oracle

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

PRIMARY =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.235)(PORT = 8088))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl_1)
    )
)

STANDBY2 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 3525))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl_3)
    )
)

STANDBY1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (INSTANCE_NAME = orcl_2)
    )
)

注意点：注意net manager各个文本框对应的listener。ora和tnsnames。ora中的变量以及对应数据库的初始参数

Oracle Net Manager Field	listener.ora File Parameter	Description
SID	SID_NAME	You can obtain the SID value from the INSTANCE_NAME parameter in the initialization parameter file.
Service Name	GLOBAL_DBNAME	he value for this parameter is typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization parameter file
Oracle Home Directory	ORACLE_HOME	On UNIX, this setting is optional. Use it to specify the Oracle home location of the instance. Without this setting, the listener assumes its Oracle home for the instance. O

Oracle Net Manager Field

listener.ora File Parameter

Description

SID

SID_NAME

You can obtain the SID value from the INSTANCE_NAME parameter in the initialization parameter file.

Service Name

GLOBAL_DBNAME

he value for this parameter is typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters (DB_NAME.DB_DOMAIN) in the initialization parameter file

Oracle Home Directory

ORACLE_HOME

On UNIX, this setting is optional. Use it to specify the Oracle home location of the instance. Without this setting, the listener assumes its Oracle home for the instance.

还有就是net manager中net service name 对应的服务写 db_name 高级选项中有个实例名写对应的instance_name 也就是db_unique_name 千万小心不然主库redolog无法传送具体观察listener.ora he tnsnames.ora文件的各个参数，，本来想自己尝试修改的可是听说格式特别严谨就算了使用图形化的net manager吧但是注意生成的文件吧

SYS@orcl_1/07-APR-11>alter database create standby controlfile as '$ORACLE_BASE/pfile/standby01.ctl';

Database altered.

Elapsed: 00:00:00.64
手动复制到standby初始参数control_files位置即可生成多份为好

[root@localhost ~]# su - oracle
-bash: [oracle=oracle]: command not found
[oracle@localhost ~]$ export ORACLE_SID='orcl_2'
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 7 19:46:35 2011

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

Connected to an idle instance.

ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SYS@orcl_2/07-APR-11>create spfile from pfile='$ORACLE_BASE/pfile/standby.ora';

File created.

Elapsed: 00:00:00.06
SYS@orcl_2/07-APR-11>startup mount;
ORACLE instance started.

Total System Global Area 376635392 bytes
Fixed Size                  1336652 bytes
Variable Size             234883764 bytes
Database Buffers          134217728 bytes
Redo Buffers                6197248 bytes
Database mounted.
SYS@orcl_2/07-APR-11>@ts_file.sql

       TS# TABLESPACE      FILE                                     SIZE
---------- --------------- ---------------------------------------- ----------
         0 SYSTEM          /boot/u02/oradata/ORCL_2/datafile/system 0M
                           01.dbf

         1 SYSAUX          /boot/u02/oradata/ORCL_2/datafile/sysaux 0M
                           01.dbf

         2 UNDOTBS1        /boot/u02/oradata/ORCL_2/datafile/undotb 0M
                           s01.dbf

         4 USERS           /boot/u02/oradata/ORCL_2/datafile/users0 0M
                           1.dbf

4 rows selected.

Elapsed: 00:00:00.03

主库创建备份集然后standby端指定备份集恢复若是控制文件无法识别注册这个备份集即可

SYS@orcl_1/07-APR-11>host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 7 19:45:02 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1275488545)

RMAN> backup database;

Starting backup at 07-APR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/boot/u02/oradata/ORCL_1/datafile/system01.dbf
input datafile file number=00002 name=/boot/u02/oradata/ORCL_1/datafile/sysaux01.dbf
input datafile file number=00003 name=/boot/u02/oradata/ORCL_1/datafile/undotbs01.dbf
input datafile file number=00004 name=/boot/u02/oradata/ORCL_1/datafile/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_nnndf_TAG20110407T194552_6sv8zkf3_.bkp tag=TAG20110407T194552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-APR-11
channel ORA_DISK_1: finished piece 1 at 07-APR-11
piece handle=/boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_ncsnf_TAG20110407T194552_6sv90om2_.bkp tag=TAG20110407T194552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-APR-11

RMAN> exit

Recovery Manager complete.

备库端恢复

RMAN> restore database from tag='TAG20110407T194552';

Starting restore at 07-APR-11
Starting implicit crosscheck backup at 07-APR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 07-APR-11

Starting implicit crosscheck copy at 07-APR-11
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 07-APR-11

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/07/2011 19:56:24
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN> catalog start with '/boot/u03/recovery_area/ORCL_1/backupset/2011_04_07';

searching for all files that match the pattern /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07

List of Files Unknown to the Database
=====================================
File Name: /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_ncsnf_TAG20110407T194552_6sv90om2_.bkp
File Name: /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_nnndf_TAG20110407T194552_6sv8zkf3_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_ncsnf_TAG20110407T194552_6sv90om2_.bkp
File Name: /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_nnndf_TAG20110407T194552_6sv8zkf3_.bkp

RMAN>

RMAN> restore database;

Starting restore at 07-APR-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /boot/u02/oradata/ORCL_2/datafile/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /boot/u02/oradata/ORCL_2/datafile/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /boot/u02/oradata/ORCL_2/datafile/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /boot/u02/oradata/ORCL_2/datafile/users01.dbf
channel ORA_DISK_1: reading from backup piece /boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_nnndf_TAG20110407T194552_6sv8zkf3_.bkp
channel ORA_DISK_1: piece handle=/boot/u03/recovery_area/ORCL_1/backupset/2011_04_07/o1_mf_nnndf_TAG20110407T194552_6sv8zkf3_.bkp tag=TAG20110407T194552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 07-APR-11

RMAN>

然后就是接收主库的日志并应用即可

（至于创建spfile pfile 密码文件 standby 控制文件还有就省略了）

S YS@orcl/2011-04-05 17:30:51>create pfile='$ORACLE_BASE/pfile/orcl_2.ora' from spfile;

SYS@orcl_1/05-APR-11>create spfile from pfile='$ORACLE_BASE/pfile/orcl_2.ora';

SYS@orcl_1/2011-04-06 11:00:18>select force_logging from v$database;

SYS@orcl_1/2011-04-06 11:14:56>alter database force logging ;

Database altered.

Elapsed: 00:00:00.00
SYS@orcl_1/2011-04-06 11:15:22>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /boot/u05/oradata
Oldest online log sequence     8
Next log sequence to archive   11
Current log sequence           11

[oracle@localhost ~]$ orapwd file='$ORACLE_HOME/dbs/orapworcl_1' password=152450 entries=10;
[oracle@localhost ~]$ orapwd file='$ORACLE_HOME/dbs/orapworcl_2' password=152450 entries=10;
[oracle@localhost ~]$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24890594/viewspace-691981/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24890594/viewspace-691981/