两台oracle未安装ARC,俺是小白，占时不会玩arc。ADB高手在于分享，俺小白将
下面是Data Guard 主备环境配置过程分享给大家。备份是物理Data Guard
一，
系统：centos 6.4
IP 主：192.168.153.131 oracle 企业 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 备：192.168.153.134 oracle 企业 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
（1）在主库上打开archive log
[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 23 10:04:49 2017Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /oracle/app/product/11.2/db/dbs/archOldest online log sequence 3Next log sequence to archive 5Current log sequence
（2） 在主库上设置闪回大小 SQL> alter system set db_recovery_file_dest_size=50g scope=spfile;System altered.（3）在主库上设置闪回路径及打开闪回SQL> alter system set db_recovery_file_dest='/oracle/app/product/11.2/db/dbs/arch' scope=spfile;System altered.SQL> alter database flashback on;alter database flashback on*ERROR at line 1:ORA-38706: Cannot turn on FLASHBACK DATABASE logging.ORA-38709: Recovery Area is not enabled.SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1048059904 bytesFixed Size 2219992 bytesVariable Size 725614632 bytesDatabase Buffers 314572800 bytesRedo Buffers 5652480 bytesDatabase mounted.SQL> alter database flashback on;Database altered.SQL> select flashback_on from v$database;FLASHBACK_ON------------------------------------YESSQL> alter database open;Database altered.（4）在主库上查看闪回SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;FILE_TYPE PERCENT_SPACE_USED---------------------------------------- ------------------PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------------------- ---------------CONTROL FILE 0 0 0REDO LOG 0 0 0ARCHIVED LOG 0 0 0FILE_TYPE PERCENT_SPACE_USED---------------------------------------- ------------------PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------------------- ---------------BACKUP PIECE 0 0 0IMAGE COPY 0 0 0FLASHBACK LOG .02 0 1FILE_TYPE PERCENT_SPACE_USED---------------------------------------- ------------------PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES------------------------- ---------------FOREIGN ARCHIVED LOG 0 0 07 rows selected.SQL> show parameter recover;NAME TYPE------------------------------------ ----------------------VALUE------------------------------db_recovery_file_dest string/oracle/app/product/11.2/db/dbs/archdb_recovery_file_dest_size big integer50Grecovery_parallelism integer0（4）在主库上创建表空间做测试SQL> create tablespace shanghai datafile '/oracle/app/oradata/test1/shanghai01.dbf' size 100m autoextend on;Tablespace created.SQL> create user shanghai identified by shanghai default tablespace shanghai;User created.SQL> grant connect,resource to shanghai;Grant succeeded.SQL> grant dba to shanghai;Grant succeeded.（5）在主库上打开强制写日志SQL> select force_logging from v$database;FORCE_------NOSQL> alter database force logging;Database altered.（6）在主库上创建standby 日志，通过redo与备库进行同步SQL> select group#,member from V$logfile; GROUP#----------MEMBER-------------------------------------------------------------------------------- 3/oracle/app/oradata/test1/redo03.log 2/oracle/app/oradata/test1/redo02.log 1/oracle/app/oradata/test1/redo01.logSQL> alter database add standby logfile group 11 '/oracle/app/oradata/test1/standby_redo11.log' size 50m;Database altered.SQL> alter database add standby logfile group 12 '/oracle/app/oradata/test1/standby_redo12.log' size 50m;Database altered.SQL> alter database add standby logfile group 13 '/oracle/app/oradata/test1/standby_redo13.log' size 50m;Database altered.SQL> alter database add standby logfile group 14 '/oracle/app/oradata/test1/standby_redo14.log' size 50m;Database altered.SQL> select group#,member from v$logfile; GROUP#----------MEMBER-------------------------------------------------------------------------------- 3/oracle/app/oradata/test1/redo03.log 2/oracle/app/oradata/test1/redo02.log 1/oracle/app/oradata/test1/redo01.log GROUP#----------MEMBER-------------------------------------------------------------------------------- 11/oracle/app/oradata/test1/standby_redo11.log 12/oracle/app/oradata/test1/standby_redo12.log 13/oracle/app/oradata/test1/standby_redo13.log GROUP#----------MEMBER-------------------------------------------------------------------------------- 14/oracle/app/oradata/test1/standby_redo14.log7 rows selected.（7）在主库创建pfile文件，后面住库启动的时候要用到pfile文件，将主库file文件复制为备库文件，我这里是没有修改前复制，也可以是修改后复制，将其scp到备份相应目录 cp -rq inittest1.ora inittest2.ora SQL> create pfile from spfile;File created.SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@node1 ~]$ cd /oracle/app/product/11.2/db/dbs/[oracle@node1 dbs]$ lltotal 40drwxr-xr-x 3 oracle oinstall 4096 Apr 23 2017 arch-rw-rw---- 1 oracle oinstall 1544 Apr 23 2017 hc_DBUA0.dat-rw-rw---- 1 oracle oinstall 1544 Apr 23 2017 hc_test1.dat-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora-rw-r--r-- 1 oracle oinstall 1524 Apr 23 02:37 inittest1.ora-rw-r--r-- 1 oracle oinstall 906 Apr 23 02:24 inittest1.ora.bak-rw-r--r-- 1 oracle oinstall 1524 Apr 23 02:37 inittest2.ora-rw-r----- 1 oracle oinstall 24 Apr 23 2017 lkTEST1-rw-r----- 1 oracle oinstall 1536 Apr 23 2017 orapwtest1-rw-r----- 1 oracle oinstall 2560 Apr 23 2017 spfiletest1.ora下面是主库pfile修改后的内容inittest1.ora[oracle@node1 dbs]$ cat inittest1.oratest1.__db_cache_size=310378496test1.__java_pool_size=4194304test1.__large_pool_size=4194304test1.__oracle_base='/oracle/app'#ORACLE_BASE set from environmenttest1.__pga_aggregate_target=423624704test1.__sga_target=629145600test1.__shared_io_pool_size=0test1.__shared_pool_size=297795584test1.__streams_pool_size=0*.audit_file_dest='/oracle/app/admin/test1/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/oracle/app/oradata/test1/control01.ctl','/oracle/app/oradata/test1/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='test1'*.db_recovery_file_dest_size=53687091200*.db_recovery_file_dest='/oracle/app/product/11.2/db/dbs/arch'*.diagnostic_dest='/oracle/app'*.dispatchers='(PROTOCOL=TCP) (SERVICE=test1XDB)'*.memory_target=1050673152*.open_cursors=300*.processes=1500*.remote_login_passwordfile='EXCLUSIVE'*.sessions=1655*.undo_tablespace='UNDOTBS1'下面是修改的的内容db_unique_name=test1log_archive_config='dg_config=(test1,test2)'log_archive_dest_1='location=/oracle/app/product/11.2/db/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=test1'log_archive_dest_2='service=test2 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=test2'log_archive_dest_state_1=enablelog_archive_dest_state_2=enabledb_file_name_convert='/oracle/app/oradata/test2','/oracle/app/oradata/test1'log_file_name_convert='/oracle/app/oradata/test2','/oracle/app/oradata/test1'fal_server=test2fal_client=test1standby_file_management=autoREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE下面是备份库pfile文件内容inittest2.oracat test1.__db_cache_size=310378496test1.__java_pool_size=4194304test1.__large_pool_size=4194304test1.__oracle_base='/oracle/app'#ORACLE_BASE set from environmenttest1.__pga_aggregate_target=423624704test1.__sga_target=629145600test1.__shared_io_pool_size=0test1.__shared_pool_size=297795584test1.__streams_pool_size=0*.audit_file_dest='/oracle/app/admin/test2/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/oracle/app/oradata/test2/control01.ctl','/oracle/app/oradata/test2/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='test1'*.db_recovery_file_dest_size=53687091200*.db_recovery_file_dest='/oracle/app/product/11.2/db/dbs/arch'*.diagnostic_dest='/oracle/app'*.dispatchers='(PROTOCOL=TCP) (SERVICE=test1XDB)'*.memory_target=1050673152*.open_cursors=300*.processes=1500*.remote_login_passwordfile='EXCLUSIVE'*.sessions=1655*.undo_tablespace='UNDOTBS1'db_unique_name=test2log_archive_config='dg_config=(test1,test2)'log_archive_dest_1='location=/oracle/app/product/11.2/db/dbs/arch valid_for=(all_logfiles,all_roles) db_unique_name=test2'log_archive_dest_2='service=test1 lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=test1'log_archive_dest_state_1=enablelog_archive_dest_state_2=enabledb_file_name_convert='/oracle/app/oradata/test1','/oracle/app/oradata/test2'log_file_name_convert='/oracle/app/oradata/test1','/oracle/app/oradata/test2'fal_server=test1fal_client=test2standby_file_management=autoREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE（8）在主库上创建控制文件SQL> alter database create standby controlfile as '/oracle/app/oradata/test1/control03.ctl';Database altered.（9）配置监听，将主备库监听都起来，lsnrctl start 测试主备是否通 tnsping test2 tnsping test1主库[oracle@node1 admin]$ cat listener.ora# listener.ora Network Configuration File: /oracle/app/product/11.2/db/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test1) (ORACLE_HOME = /oracle/app/product/11.2/db) (SID_NAME = test1) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521)) )ADR_BASE_LISTENER = /oracle/app[oracle@node1 admin]$ [oracle@node1 admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /oracle/app/product/11.2/db/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TEST2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.153.134)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test2) ) )TEST1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.153.131)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test1) ) )[oracle@node1 admin]$ 备份库[root@node2 admin]# cat listener.ora# listener.ora Network Configuration File: /oracle/app/product/11.2/db/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test2) (ORACLE_HOME = /oracle/app/product/11.2/db) (SID_NAME = test2) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521)) )ADR_BASE_LISTENER = /oracle/app[root@node2 admin]# [root@node2 admin]# cat tnsnames.ora# tnsnames.ora Network Configuration File: /oracle/app/product/11.2/db/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TEST2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.153.134)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test2) ) )TEST1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.153.131)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = test1) ) )[root@node2 admin]# SQL> SQL> exit Disconnected[oracle@node1 dbs]$ [oracle@node1 dbs]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-APR-2017 03:25:35Copyright (c) 1991, 2009, Oracle. All rights reserved.Starting /oracle/app/product/11.2/db/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionSystem parameter file is /oracle/app/product/11.2/db/network/admin/listener.oraLog messages written to /oracle/app/diag/tnslsnr/node1/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.1.0 - ProductionStart Date 23-APR-2017 03:25:35Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /oracle/app/product/11.2/db/network/admin/listener.oraListener Log File /oracle/app/diag/tnslsnr/node1/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))Services Summary...Service "test1" has 1 instance(s). Instance "test1", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully[oracle@node1 dbs]$ [oracle@node1 dbs]$ (10)用之前创建的pfile启动主库[oracle@node1 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 23 03:25:58 2017Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> startup pfile='/oracle/app/product/11.2/db/dbs/inittest1.ora';ORACLE instance started.Total System Global Area 1048059904 bytesFixed Size 2219992 bytesVariable Size 729808936 bytesDatabase Buffers 310378496 bytesRedo Buffers 5652480 bytesORA-00214: control file '/oracle/app/oradata/test1/control01.ctl' version 772 这里是1和3控制文件不一样报错，也是pfile文件里面加了一个103的控制文件路径，将路径去掉inconsistent with file '/oracle/app/oradata/test1/control03.ctl' version 768 SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> SQL> SQL> startup pfile='/oracle/app/product/11.2/db/dbs/inittest1.ora';ORACLE instance started.Total System Global Area 1048059904 bytesFixed Size 2219992 bytesVariable Size 729808936 bytesDatabase Buffers 310378496 bytesRedo Buffers 5652480 bytesDatabase mounted.Database opened.（11）创建spfile文件，住库原先是有spfile文件，但是原先的spfile文件不是dg环境的文件，如果将住库关闭，在刺用startup启动时，会有错误SQL> create spfile from pfile;.关闭主库shutdown immediate;
先在备份库创建完相应的目录复制数据文件，控制文件，redo文件，注意控制文件和文件的属性，这里是复制上面创建的控制文件不是主库源先的控制文件。scp *.log oracle@192.168.153.134:/oracle/app/oradata/test2/scp *.dbf oracle@192.168.153.134:/oracle/app/oradata/test2/scp control03.ctl oracle@192.168.153.134:/oracle/app/oradata/test2/ 二,下面是备份库的操作
(1)创建pfile文件的相应目录mkdir /oracle/app/product/11.2/db/dbs/arch -pmkdir /oracle/app/oradata/test2 -pmkdir /oracle/app/admin/test2/adump -pmkdir /oracle/app/admin/test2/dpdump -p mkdir /oracle/app/admin/test2/pfile -p
启动备份库到nomount 状态SQL> startup nomountSQL> alter database mount atandby database 这个步骤做完后会有RFS进程，如果没有请查看日志SQL> select process,pid,status,client_process from v$managed_standby;PROCESS PID STATUS CLIENT_PROCESS------------------ ---------- ------------------------ ----------------ARCH 3678 CONNECTED ARCHARCH 3680 CONNECTED ARCHARCH 3682 CLOSING ARCHARCH 3684 CONNECTED ARCHRFS 3698 IDLE N/ARFS 3692 IDLE UNKNOWNRFS 3694 IDLE UNKNOWNRFS 3696 IDLE LGWRSQL> alter database recover managed standby database disconnect from session; 这个步骤做完后会有MRP0进程，如果没有请看日志QL> select process,pid,status,client_process from v$managed_standby;PROCESS PID STATUS CLIENT_PROCESS------------------ ---------- ------------------------ ----------------ARCH 3678 CONNECTED ARCHARCH 3680 CONNECTED ARCHARCH 3682 CLOSING ARCHARCH 3684 CONNECTED ARCHRFS 3698 IDLE UNKNOWNRFS 3692 IDLE UNKNOWNRFS 3727 IDLE UNKNOWNRFS 3696 IDLE LGWRMRP0 3715 WAIT_FOR_LOG N/A下面是一些备份库的查询SQL> select sequence# from v$log; SEQUENCE#---------- 10 0 0SQL> show parameter instance_name;NAME TYPE------------------------------------ ----------------------VALUE------------------------------instance_name stringtest2SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 9SQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------NOT ALLOWEDSQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------NOT ALLOWEDSQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 9SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 10SQL> select database_role,open_mode from v$database;DATABASE_ROLE OPEN_MODE-------------------------------- ----------------------------------------PHYSICAL STANDBY MOUNTEDSQL> select status from v$instance;STATUS------------------------MOUNTED下面是住库的一些查看下Database altered.SQL> select sequence# from v$log; SEQUENCE#---------- 10 8 9SQL> show parameter instance_name;NAME TYPE------------------------------------ ----------------------VALUE------------------------------instance_name stringtest1SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 9SQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO STANDBYSQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 9SQL> alter system switch logfile;System altered.SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)-------------- 10SQL> SQL> SQL> select switchover_status from v$database;SWITCHOVER_STATUS----------------------------------------TO STANDBYSQL> select process,pid,status,client_process from v$managed_standby;PROCESS PID STATUS CLIENT_PROCESS------------------ ---------- ------------------------ ----------------ARCH 3548 CLOSING ARCHARCH 3550 CLOSING ARCHARCH 3552 CLOSING ARCHARCH 3554 CLOSING ARCHLNS 3556 WRITING LNSSQL> SQL> select database_role,open_mode from v$database;DATABASE_ROLE OPEN_MODE-------------------------------- ----------------------------------------PRIMARY READ WRITESQL> select status from v$instance;STATUS------------------------OPEN
下面是一些报错 解决方法是，在主库创建密码文件后，将其scp到备份库，然后在启动备份库orapwd file=orapwtest1 password=oracle entries=10 force=y
ORA-01034: ORACLE not availablePING[ARC2]: Heartbeat failed to connect to standby 'test2'. Error is 1034.Sun Apr 23 03:37:48 2017Error 1017 received logging on to the standby------------------------------------------------------------Check that the primary and standby are using a password fileand remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191------------------------------------------------------------Errors in file /oracle/app/diag/rdbms/test1/test1/trace/test1_arc2_3311.trc:ORA-16191: Primary log shipping client not logged on standbyPING[ARC2]: Heartbeat failed to connect to standby 'test2'. Error is 16191.Sun Apr 23 03:40:49 2017
