两台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 sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 23 10:04:49 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/app/product/11.2/db/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current 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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1048059904 bytes
Fixed Size 2219992 bytes
Variable Size 725614632 bytes
Database Buffers 314572800 bytes
Redo Buffers 5652480 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
YES
SQL> 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 0
REDO LOG 0
0 0
ARCHIVED LOG 0
0 0
FILE_TYPE PERCENT_SPACE_USED
---------------------------------------- ------------------
PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------------
BACKUP PIECE 0
0 0
IMAGE COPY 0
0 0
FLASHBACK LOG .02
0 1
FILE_TYPE PERCENT_SPACE_USED
---------------------------------------- ------------------
PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------------
FOREIGN ARCHIVED LOG 0
0 0
7 rows selected.
SQL> show parameter recover;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest string
/oracle/app/product/11.2/db/db
s/arch
db_recovery_file_dest_size big integer
50G
recovery_parallelism integer
0
（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_
------
NO
SQL> 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.log
SQL> 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.log
7 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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ cd /oracle/app/product/11.2/db/dbs/
[oracle@node1 dbs]$ ll
total 40
drwxr-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.ora
test1.__db_cache_size=310378496
test1.__java_pool_size=4194304
test1.__large_pool_size=4194304
test1.__oracle_base='/oracle/app'#ORACLE_BASE set from environment
test1.__pga_aggregate_target=423624704
test1.__sga_target=629145600
test1.__shared_io_pool_size=0
test1.__shared_pool_size=297795584
test1.__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=test1
log_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=enable
log_archive_dest_state_2=enable
db_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=test2
fal_client=test1
standby_file_management=auto
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
下面是备份库pfile文件内容inittest2.ora
cat
test1.__db_cache_size=310378496
test1.__java_pool_size=4194304
test1.__large_pool_size=4194304
test1.__oracle_base='/oracle/app'#ORACLE_BASE set from environment
test1.__pga_aggregate_target=423624704
test1.__sga_target=629145600
test1.__shared_io_pool_size=0
test1.__shared_pool_size=297795584
test1.__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=test2
log_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=enable
log_archive_dest_state_2=enable
db_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=test1
fal_client=test2
standby_file_management=auto
REMOTE_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 start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-APR-2017 03:25:35
Copyright (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 - Production
System parameter file is /oracle/app/product/11.2/db/network/admin/listener.ora
Log messages written to /oracle/app/diag/tnslsnr/node1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 23-APR-2017 03:25:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/product/11.2/db/network/admin/listener.ora
Listener Log File /oracle/app/diag/tnslsnr/node1/listener/alert/log.xml
Listening 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 sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 23 03:25:58 2017
Copyright (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 bytes
Fixed Size 2219992 bytes
Variable Size 729808936 bytes
Database Buffers 310378496 bytes
Redo Buffers 5652480 bytes
ORA-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 mounted
ORACLE 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 bytes
Fixed Size 2219992 bytes
Variable Size 729808936 bytes
Database Buffers 310378496 bytes
Redo Buffers 5652480 bytes
Database 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 -p
mkdir /oracle/app/oradata/test2 -p
mkdir /oracle/app/admin/test2/adump -p
mkdir /oracle/app/admin/test2/dpdump -p
mkdir /oracle/app/admin/test2/pfile -p
启动备份库到nomount 状态
SQL> startup nomount
SQL> 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 ARCH
ARCH 3680 CONNECTED ARCH
ARCH 3682 CLOSING ARCH
ARCH 3684 CONNECTED ARCH
RFS 3698 IDLE N/A
RFS 3692 IDLE UNKNOWN
RFS 3694 IDLE UNKNOWN
RFS 3696 IDLE LGWR
SQL> 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 ARCH
ARCH 3680 CONNECTED ARCH
ARCH 3682 CLOSING ARCH
ARCH 3684 CONNECTED ARCH
RFS 3698 IDLE UNKNOWN
RFS 3692 IDLE UNKNOWN
RFS 3727 IDLE UNKNOWN
RFS 3696 IDLE LGWR
MRP0 3715 WAIT_FOR_LOG N/A
下面是一些备份库的查询
SQL> select sequence# from v$log;
SEQUENCE#
----------
10
0
0
SQL> show parameter instance_name;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
instance_name string
test2
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
9
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
9
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
10
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
-------------------------------- ----------------------------------------
PHYSICAL STANDBY MOUNTED
SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
下面是住库的一些查看下
Database altered.
SQL> select sequence# from v$log;
SEQUENCE#
----------
10
8
9
SQL> show parameter instance_name;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
instance_name string
test1
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
9
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO STANDBY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
9
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
10
SQL>
SQL>
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
TO STANDBY
SQL> select process,pid,status,client_process from v$managed_standby;
PROCESS PID STATUS CLIENT_PROCESS
------------------ ---------- ------------------------ ----------------
ARCH 3548 CLOSING ARCH
ARCH 3550 CLOSING ARCH
ARCH 3552 CLOSING ARCH
ARCH 3554 CLOSING ARCH
LNS 3556 WRITING LNS
SQL>
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
-------------------------------- ----------------------------------------
PRIMARY READ WRITE
SQL> select status from v$instance;
STATUS
------------------------
OPEN
下面是一些报错 解决方法是，在主库创建密码文件后，将其scp到备份库，然后在启动备份库
orapwd file=orapwtest1 password=oracle entries=10 force=y
ORA-01034: ORACLE not available
PING[ARC2]: Heartbeat failed to connect to standby 'test2'. Error is 1034.
Sun Apr 23 03:37:48 2017
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and 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 standby
PING[ARC2]: Heartbeat failed to connect to standby 'test2'. Error is 16191.
Sun Apr 23 03:40:49 2017
扫一扫
1735
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
