『ORACLE』搭建Data Guard（11g）

准备工作：

1、开启归档

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Current log sequence 8

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8

2、打开强制写日志功能

SQL> alter database force logging;

Database altered.

 

确认数据库的快速恢复区处于关闭状态

SQL> show parameter recover

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0

关闭闪回功能

SQL> alter database flashback off;

Database altered.

SQL> select force_logging,flashback_on from v$database;

FOR FLASHBACK_ON
--- ------------------
YES NO

3、创建standby logfile（在主库上创建，组数要大于等于主库的日志组数量。在之后可以直接拷贝到备库中；为之后可能会发生的角色转换做准备）

SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy01.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy02.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy03.log' size 50M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/PROD1/redo_stbdy04.log' size 50M;

Database altered.

SQL> select group#,member,type from v$logfile where type like '%STAND%';　　

SQL> select group#,bytes/1024/1024 as size_m from v$standby_log;

GROUP#    SIZE_M
---------- ----------
4              50
5              50
6              50
7              50

4、生成可编辑的pfile文件
SYS@ENMO> create pfile from spfile;

File created.

[oracle@ENMO admin]$ cd $ORACLE_HOMEs
备份一下二进制文件，防止一会儿改乱，打不开库
[oracle@ENMO dbs]$ cp spfileENMO.ora spfileENMO.ora.bak

进入官方文档找要添加的参数
[oracle@ENMO dbs]$ vi initENMO.ora

PROD1.__db_cache_size=419430400
PROD1.__java_pool_size=4194304
PROD1.__large_pool_size=4194304
PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD1.__pga_aggregate_target=377487360
PROD1.__sga_target=566231040
PROD1.__shared_io_pool_size=0
PROD1.__shared_pool_size=130023424
PROD1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'
*.memory_max_target=943718400
*.memory_target=943718400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

把所有chicago替换成PROD1,把boston替换成SBDB。
%s#chicago#PROD1#g

%s#boston#SBDB#g

DB_UNIQUE_NAME=PROD1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1,SBDB)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/PROD1/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='SBDB','PROD1'
LOG_FILE_NAME_CONVERT='SBDB','PROD1'
STANDBY_FILE_MANAGEMENT=AUTO

把没有的目录建上
[oracle@ENMO dbhome_1]$ mkdir -p /u01/arch1/PROD1/

cd /u01/app/oracle/oradata/PROD1

 

关闭数据库，重新启动检查修改的参数文件是否有问题

[oracle@enmoedu1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 15:47:19 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile ;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes
Database mounted.
Database opened.

将主库的参数文件和口令文件穿到备库里

[oracle@enmoedu1 dbs]$ scp initPROD1.ora orapwPROD1 192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/dbs/
The authenticity of host '192.0.2.12 (192.0.2.12)' can't be established.
RSA key fingerprint is 82:c6:92:1a:f8:4c:ca:93:f2:71:cc:41:de:c4:71:08.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.0.2.12' (RSA) to the list of known hosts.
oracle@192.0.2.12's password: 
initPROD1.ora 100% 1398 1.4KB/s 00:00 
orapwPROD1 100% 1536 1.5KB/s 00:00 
[oracle@enmoedu1 dbs]$

在备库中查看从主库中拷贝过来的参数文件，并改成备库中的实例名

[oracle@enmoedu2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@enmoedu2 dbs]$ ls
hc_EMREP.dat hc_PROD4.dat init.ora initPROD1.ora orapwPROD1
[oracle@enmoedu2 dbs]$ mv initPROD1.ora initSBDB.ora
[oracle@enmoedu2 dbs]$ ls
hc_EMREP.dat hc_PROD4.dat init.ora initSBDB.ora orapwPROD1
[oracle@enmoedu2 dbs]$ mv orapwPROD1 orapwSBDB
[oracle@enmoedu2 dbs]$ ls
hc_EMREP.dat hc_PROD4.dat init.ora initSBDB.ora orapwSBDB

编辑备库中的参数文件，将PROD1和SBDB换过来

:%s#PROD1#AAA#g

:%s#SBDB#PROD1#g

:%s#AAA#SBDB#g

[oracle@enmoedu2 dbs]$ vi initSBDB.ora 
PROD1.__db_cache_size=419430400
PROD1.__java_pool_size=4194304
PROD1.__large_pool_size=4194304
PROD1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD1.__pga_aggregate_target=377487360
PROD1.__sga_target=566231040
PROD1.__shared_io_pool_size=0
PROD1.__shared_pool_size=130023424
PROD1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD1XDB)'
*.memory_max_target=943718400
*.memory_target=943718400
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=SBDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,PROD1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/SBDB/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=PROD1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PROD1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=PROD1
DB_FILE_NAME_CONVERT='PROD1','SBDB'
LOG_FILE_NAME_CONVERT='PROD1','SBDB'
STANDBY_FILE_MANAGEMENT=AUTO

创建备库参数文件中没有的路径

[oracle@enmoedu2 admin]$ mkdir -p /u01/app/oracle/admin/SBDB/adump
[oracle@enmoedu2 admin]$ mkdir -p /u01/app/oracle/oradata/SBDB/
[oracle@enmoedu2 admin]$ mkdir -p /u01/arch1/SBDB/

将数据库启动到nomount状态，来验证备库修改的参数文件是否有问题

[oracle@enmoedu2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 16:29:13 2017

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

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 515902212 bytes
Database Buffers 419430400 bytes
Redo Buffers 4919296 bytes

更改备库的监听

[oracle@enmoedu2 admin]$ vi listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2.example.com)(PORT = 1521))
)
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=SBDB)
(SID_NAME=SBDB)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
)
)

[oracle@enmoedu2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAY-2017 16:55:33

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=enmoedu2.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-MAY-2017 16:55:33
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/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))
Services Summary...
Service "SBDB" has 1 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

SQL>alter system register 

[oracle@enmoedu2 admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 01-MAY-2017 16:56:01

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Welcome to LSNRCTL, type "help" for information.

因为数据库处于nomount状态，所以此时监听处于blocked状态

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=enmoedu2.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 01-MAY-2017 16:55:33
Uptime 0 days 0 hr. 3 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/enmoedu2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=enmoedu2.example.com)(PORT=1521)))
Services Summary...
Service "SBDB" has 2 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
Instance "SBDB", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

配置主库的tnsnames.ora文件

[oracle@enmoedu1 admin]$ vi tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SBDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu2.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = SBDB)
)
)

PROD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = enmoedu1.example.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = PROD1)
)
)

再将tnsnames.ora拷贝到备库的路径下

[oracle@enmoedu1 admin]$ scp tnsnames.ora 192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/network/admin
oracle@192.0.2.12's password: 
tnsnames.ora 100% 578 0.6KB/s 00:00 

使用rman auxiliary 恢复数据库（主库）

[oracle@enmoedu1 admin]$ rman target / auxiliary sys/oracle@SBDB

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 1 17:11:32 2017

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

connected to target database: PROD1 (DBID=2082231315)
connected to auxiliary database: SBDB (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 01-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPROD1' auxiliary format 
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwSBDB' ;
}
executing Memory Script

Starting backup at 01-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 device type=DISK
Finished backup at 01-MAY-17

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/SBDB/control02.ctl' from 
'/u01/app/oracle/oradata/SBDB/control01.ctl';
}
executing Memory Script

Starting backup at 01-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f tag=TAG20170501T173047 RECID=7 STAMP=942859847
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-MAY-17

Starting restore at 01-MAY-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-MAY-17

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/SBDB/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/SBDB/system01.dbf";
set newname for datafile 2 to 
"/u01/app/oracle/oradata/SBDB/sysaux01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/SBDB/undotbs01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/SBDB/users01.dbf";
set newname for datafile 5 to 
"/u01/app/oracle/oradata/SBDB/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/SBDB/system01.dbf" datafile 
2 auxiliary format 
"/u01/app/oracle/oradata/SBDB/sysaux01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/SBDB/undotbs01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/SBDB/users01.dbf" datafile 
5 auxiliary format 
"/u01/app/oracle/oradata/SBDB/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/SBDB/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-MAY-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=/u01/app/oracle/oradata/SBDB/system01.dbf tag=TAG20170501T173054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf tag=TAG20170501T173054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
output file name=/u01/app/oracle/oradata/SBDB/example01.dbf tag=TAG20170501T173054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf tag=TAG20170501T173054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=/u01/app/oracle/oradata/SBDB/users01.dbf tag=TAG20170501T173054
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-MAY-17

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=942859938 file name=/u01/app/oracle/oradata/SBDB/example01.dbf
Finished Duplicate Db at 01-MAY-17

应用日志同步数据（备库）

[oracle@enmoedu2 SBDB]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 1 17:34:02 2017

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.