Rman配置DataGuard使用主库未配置时备份withfilesystem
一、 环境
主机名
创新互联建站是一家专注网站建设、网络营销策划、微信平台小程序开发、电子商务建设、网络推广、移动互联开发、研究、服务为一体的技术型公司。公司成立10年以来，已经为成百上千家成都围栏护栏各业的企业公司提供互联网服务。现在，服务的成百上千家客户与我们一路同行，见证我们的成长；未来，我们一起分享成功的喜悦。 | 数据库版本 | dbname | db_unique_name | IP地址 | 系统版本 |
Jason1(主) |
oracle11204 |
Jason
| jason1 | 192.168.1.99 |
rhel6.6_x86_64 |
jason2(备) | jason2 | 192.168.1.100 |
二、 主库配置
1. 确定主数据库开启强制LOGGING模式
[oracle@jason1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 20:45:33 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
2. 开启归档
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
3 50
2 50
3. 备份主库
创建备份存放目录，使用如下脚本备份主库。
mkdir /data
chown oracle:oinstall /data
run
{
backup database include current controlfile
format '/data/fulldb_%U.bak'
plus archivelog
format '/data/arch_%U.bak';
}
备份结束后手工切换产生归档日志，模拟一天的归档量然后开始配置主库DG参数。
4. 创建standby日志组
1).standby redo log的文件大小与primary 数据库online redo log 文件大小相同
2).standby redo log日志文件组的个数依照下面的原则进行计算
Standby redo log组数公式>=(每个instance日志组个数+1)*instance个数
例如在我的环境中，只有一个节点，这个节点有三组redo，所以
Standby redo log组数公式>=(3+1)*1 == 4
所以需要创建4组Standby redo log
3).每一日志组为了安全起见，可以包含多个成员文件。
查看主数据库的日志组个数与大小，创建standy日志组,大小不能小于在线日志大小。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/JASON/redo03.log
/u01/app/oracle/oradata/JASON/redo02.log
/u01/app/oracle/oradata/JASON/redo01.log
在主数据库创建standby日志组，位置与原日志组相同的路径。
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby01.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/JASON/standby02.log')SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby03.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/JASON/standby04.log')SIZE 50M;
Database altered.
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
------------------------------------------------------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/JASON/redo03.log
2 ONLINE /u01/app/oracle/oradata/JASON/redo02.log
1 ONLINE /u01/app/oracle/oradata/JASON/redo01.log
4 STANDBY /u01/app/oracle/oradata/JASON/standby01.log
5 STANDBY/u01/app/oracle/oradata/JASON/standby02.log
6 STANDBY/u01/app/oracle/oradata/JASON/standby03.log
7 STANDBY/u01/app/oracle/oradata/JASON/standby04.log
7 rows selected.
5. 主库参数文件配置
在主库上修改dataguard配置相关的各个参数，各参数的具体含义可以参考oracle在线文档。
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(JASON1,JASON2)'SCOPE=SPFILE;
System altered.
SQL> alter system set DB_UNIQUE_NAME='JASON1' SCOPE=SPFILE;
System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=SPFILE;
System altered.
SQL> alter system setLOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON1' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=JASON2 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON2' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=spfile;
System altered.
SQL> alter system setLOG_FILE_NAME_CONVERT= '/u01/app/oracle/oradata/JASON','/u01/app/oracle/oradata/JASON' scope=spfile;
System altered.
SQL> alter system set FAL_SERVER='JASON2' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648 bytes
Database Buffers 92274688 bytes
Redo Buffers 4284416 bytes
Database mounted.
Database opened.
6. 配置监听及tnsname
创建监听及tnsname.ora，监听必须使用静态监听，如下：
[oracle@jason1 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = JASON1)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = JASON)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@jason1 admin]$ cat 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.
JASON1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = JASON1)
)
)
JASON2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = JASON2)
)
)
[oracle@jason1 admin]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-201623:06:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jason1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
Start Date 20-JUL-2016 22:50:04
Uptime 0 days 0hr. 16 min. 13 sec
Trace Level off
Security ON:Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jason1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jason1)(PORT=1521)))
Services Summary...
Service "JASON1" has 2 instance(s).
Instance "JASON",status UNKNOWN, has 1 handler(s) for this service...
Instance "JASON",status READY, has 1 handler(s) for this service...
Service "JASONXDB" has 1 instance(s).
Instance "JASON",status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@jason1 admin]$
7. 生成pfile文件，同步相应文件至备库
在主数据库生成pfile文件。
SQL> create pfile from spfile;
File created.
把dbs下的内容同步到standby主机上面，
[oracle@jason1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@jason1 dbs]$ scp initJASON.ora orapwJASON192.168.1.100:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
The authenticity of host '192.168.1.100 (192.168.1.100)' can't beestablished.
RSA key fingerprint is 25:ca:65:90:d3:30:fa:68:ed:11:64:b2:0e:b0:39:a7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.100' (RSA) to the list of knownhosts.
oracle@192.168.1.100's password:
initJASON.ora 100% 1415 1.4KB/s 00:00
orapwJASON 100% 1536 1.5KB/s 00:00
[oracle@jason1 dbs]
三、 备库配置
8. 创建对应目录
备库上创建相关目录
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/adump
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/dpdump
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/pfile
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/archivelog
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/oradata/JASON
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/catbundle
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/dbca/JASON
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/emca
[oracle@jason2 oracle]$ mkdir -p /u01/app/oracle/cfgtoollogs/netca
[oracle@jason2 oracle]$ ll
total 32
drwxr-xr-x 3 oracle oinstall 4096Jul 14 22:27 admin
drwxr-xr-x 2 oracle oinstall 4096Jul 14 22:28 archivelog
drwxr-xr-x 6 oracle oinstall 4096Jul 14 22:32 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 4096Jul 13 23:32 checkpoints
drwxrwxr-x 11 oracle oinstall 4096 Jul 13 23:06 diag
drwxr-xr-x 2 oracle oinstall 4096Jul 14 22:30 fast_recovery_area
drwxr-xr-x 3 oracle oinstall 4096Jul 14 22:28 oradata
drwxr-xr-x 3 oracle oinstall 4096Jul 13 21:37 product
[oracle@jason2 oracle]$
9. 拷贝数据库备份
[oracle@jason2 oracle]#mkdir /data
[oracle@jason2 oracle]#chownoracle:oinstall /data
在节点jason1上拷贝数据库备份至备机。
[oracle@jason1 data]$ scp * 192.168.1.100:/data
oracle@192.168.1.100's password:
arch_01rbevvh_1_1.bak 100% 96MB 23.9MB/s 00:04
arch_04rbf01l_1_1.bak 100% 31KB 31.0KB/s 00:00
fulldb_02rbevvp_1_1.bak 100% 1035MB 16.4MB/s 01:03
fulldb_03rbf01i_1_1.bak 100%9600KB 9.4MB/s 00:00
[oracle@jason1 data]$
10.备库参数文件配置
备库上修改初始参数文件，配置DG所需参数如下。
JASON.__db_cache_size=75497472
JASON.__java_pool_size=4194304
JASON.__large_pool_size=71303168
JASON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
JASON.__pga_aggregate_target=155189248
JASON.__sga_target=255852544
JASON.__shared_io_pool_size=0
JASON.__shared_pool_size=96468992
JASON.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/JASON/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/JASON/control01.ctl','/u01/app/oracle/oradata/JASON/control02.ctl','/u01/app/oracle/oradata/JASON/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='JASON'
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.log_file_name_convert='/u01/app/oracle/oradata/JASON','/u01/app/oracle/oradata/JASON'
*.db_unique_name='JASON2'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JASONXDB)'
*.fal_server='JASON1'
*.log_archive_config='DG_CONFIG=(JASON1,JASON2)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=JASON2'
*.log_archive_dest_2='SERVICE=JASON1 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=JASON1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=411041792
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1
11.配置监听
备库监听必须设置为静态监听
[oracle@jason2 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = JASON2)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = JASON)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@jason2 admin]$ cat 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.
JASON1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = JASON1)
)
)
JASON2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = JASON2)
)
)
12.创建spfile文件
[oracle@jason2 dbs]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 23:07:22 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648 bytes
Database Buffers 92274688bytes
Redo Buffers 4284416 bytes
SQL> create spfile from pfile;
File created.
13.创建备库
将备库启动到nomount状态，然后在备机连接主库进行duplicate操作。
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 310381648 bytes
Database Buffers 92274688 bytes
Redo Buffers 4284416 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testingoptions
[oracle@jason2 ~]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-2016 23:04:56
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jason2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
Start Date 20-JUL-2016 22:50:42
Uptime 0 days 0hr. 14 min. 14 sec
Trace Level off
Security ON:Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jason2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jason2)(PORT=1521)))
Services Summary...
Service "JASON2" has 2 instance(s).
Instance "JASON",status UNKNOWN, has 1 handler(s) for this service...
Instance "JASON",status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@jason2 ~]$ rman target sys/system@JASON1 auxiliarysys/system@JASON2
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 23 23:13:192016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JASON (DBID=2141348976)
connected to auxiliary database: JASON (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
Starting Duplicate Db at 23-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
contents of Memory Script:
{
restore clone standbycontrolfile;
}
executing Memory Script
Starting restore at 23-JUL-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece/data/fulldb_03rbf01i_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data/fulldb_03rbf01i_1_1.baktag=TAG20160723T224513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/JASON/control01.ctl
output file name=/u01/app/oracle/oradata/JASON/control02.ctl
output file name=/u01/app/oracle/oradata/JASON/control03.ctl
Finished restore at 23-JUL-16
contents of Memory Script:
{
sql clone 'alter database mountstandby 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/JASON/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/JASON/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/JASON/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/JASON/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/JASON/users01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/JASON/temp01.dbf incontrol file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-JUL-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backupset
channel ORA_AUX_DISK_1: restoring datafile 00001 to/u01/app/oracle/oradata/JASON/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to/u01/app/oracle/oradata/JASON/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to/u01/app/oracle/oradata/JASON/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to/u01/app/oracle/oradata/JASON/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece/data/fulldb_02rbevvp_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/data/fulldb_02rbevvp_1_1.baktag=TAG20160723T224513
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 23-JUL-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=917998157 filename=/u01/app/oracle/oradata/JASON/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=917998157 filename=/u01/app/oracle/oradata/JASON/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=917998157 filename=/u01/app/oracle/oradata/JASON/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=917998157 filename=/u01/app/oracle/oradata/JASON/users01.dbf
Finished Duplicate Db at 23-JUL-16
RMAN> exit
Recovery Manager complete.
[oracle@jason2 ~]$
14.创建standby日志组
查看主数据库的日志组个数与大小，创建standy日志组,大小不能小于在线日志大小。
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/JASON/redo03.log
/u01/app/oracle/oradata/JASON/redo02.log
/u01/app/oracle/oradata/JASON/redo01.log
在备数据库创建standby日志组，位置与原日志组相同的路径。
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby01.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby02.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby03.log') SIZE 50M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE('/u01/app/oracle/oradata/JASON/standby04.log') SIZE 50M;
Database altered.
SQL>
备注：创建备库后，数据库处于mount状态，因使用未配置的主库创建备库，需创建standby日志组后，打开数据库。打开时将会初始化临时表空间、在线日志、standby日志,接收未传送的日志。
15.开启ADG
将备库置于active dataguard模式下，数据库open时将会传送所有自备份后产生的归档日志至备机。
[oracle@jason2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 14 23:42:40 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using currentlogfile disconnect from session;
Database altered.
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY JASON2
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE
SQL> select status from v$standby_log;
STATUS
----------
ACTIVE
UNASSIGNED
UNASSIGNED
UNASSIGNED
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
-------------------------------------------------------------------------------
3 ONLINE /u01/app/oracle/oradata/JASON/redo03.log
2 ONLINE /u01/app/oracle/oradata/JASON/redo02.log
1 ONLINE /u01/app/oracle/oradata/JASON/redo01.log
4 STANDBY/u01/app/oracle/oradata/JASON/standby01.log
5 STANDBY/u01/app/oracle/oradata/JASON/standby02.log
6 STANDBY /u01/app/oracle/oradata/JASON/standby03.log
7 STANDBY/u01/app/oracle/oradata/JASON/standby04.log
7 rows selected.
SQL>
查看备库数据文件，如下：
[root@jason2 JASON]# ll
total 1744852
-rw-r----- 1 oracle oinstall 9748480 Jul 21 00:11 control01.ctl
-rw-r----- 1 oracle oinstall 9748480 Jul 21 00:11 control02.ctl
-rw-r----- 1 oracle oinstall 9748480 Jul 21 00:11 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:06 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:11 standby01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:09 standby02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:07 standby03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 21 00:07 standby04.log
-rw-r----- 1 oracle oinstall 534781952 Jul 21 00:09 sysaux01.dbf
-rw-r----- 1 oracle oinstall 775954432 Jul 21 00:09 system01.dbf
-rw-r----- 1 oracle oinstall 30416896 Jul 21 00:09 temp01.dbf
-rw-r----- 1 oracle oinstall 73408512 Jul 21 00:09 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 21 00:09 users01.dbf
[root@jason2 JASON]#
主库查看数据库状态
SQL> select open_mode,database_role,db_unique_name from v$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
READ WRITE PRIMARY JASON1
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE
四、 测试
16.switch_over测试
主库切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;
Database altered.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SELECT SWITCHOVER_STATUS FROM V$DATABASE
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 2849
Session ID: 44 Serial number: 27
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes
Variable Size 322964560 bytes
Database Buffers 79691776 bytes
Redo Buffers 4284416 bytes
Database mounted.
Database opened.
SQL> select open_mode,database_role,db_unique_name fromv$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ----------------------------------------------
READ ONLY PHYSICAL STANDBY JASON1
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USINGCURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
SQL>
备库切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITHSESSION SHUTDOWN;
Database altered.
SQL> select open_mode,database_role,db_unique_name fromv$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ---------------- ------------------------------
MOUNTED PRIMARY JASON2
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role,db_unique_name fromv$database;
OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
-------------------- ----------------------------------------------
READ WRITE PRIMARY JASON2
SQL>
五、 报错处理
17. 报错
在备库服务器, 添加静态注册信息到 $GRID_HOME/network/listener.ora文件，
这主要是由于AUXILIARY实例启动到nomount状态时，listener无法注册AUXILIARY实例，listener会标志Auxiliary实例为'blocked'状态，因此duplicate命令就无法通过TNS的方式连接到Auxiliary实例，为了解决这个问题，需要先手动静态注册数据库实例到listener上。当Data Guard配置完成后，就可以删除静态注册的配置信息
[oracle@jason2 dbs]$ rman target sys/system@JASON_PD auxiliarysys/system@JASON_SD
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 14 23:15:312016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JASON (DBID=2141348976)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: allappropriate instances are blocking new connections
设置静态监听后状态
[oracle@jason2 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUL-201623:15:50
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNRfor Linux: Version 11.2.0.4.0 - Production
Start Date 14-JUL-2016 22:41:05
Uptime 0 days 0hr. 34 min. 44 sec
Trace Level off
Security ON:Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jason2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.100)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "JASON2" has 1 instance(s).
Instance "JASON",status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@jason2 dbs]$
六、 创建备库时主备库变化
本次实验中使用主库未配置前的rman备份进行创建DG。模拟备份为前一天的备份。数据库备份结束日志序号为17.然后通过手工切换模拟备份后一天产生的归档日志，最终日志序号为54。以下为主库日志查询结果。
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BYSEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
7 NO
8 NO
9 NO
10 NO
11 NO
12 NO
13 NO
14 NO
15 NO
16 NO
17 NO
SEQUENCE# APPLIED
---------- ---------
18 NO
19 NO
20 NO
21 NO
22 NO
23 NO
24 NO
25 NO
26 NO
27 NO
28 NO
SEQUENCE# APPLIED
---------- ---------
29 NO
30 NO
31 NO
32 NO
33 NO
34 NO
35 NO
36 NO
37 NO
38 NO
39 NO
SEQUENCE# APPLIED
---------- ---------
40 NO
41 NO
42 NO
43 NO
44 NO
45 NO
46 NO
47 NO
48 NO
49 NO
50 NO
SEQUENCE# APPLIED
---------- ---------
51 NO
52 NO
53 NO
54 NO
48 rows selected.
SQL>
在主库传送rman备份至备库中，备份位置必须与主库相同。然后在备库进行恢复数据库。恢复结束后，在备库创建standby日志组，创建结束后执行alter database open命令打开备库。这时从备库日志中可以发现在备库打开时，自备份结束后产生的所有归档日志文件(归档日志必须保证存在)自动传送到备库中。这时再主库再次查看日志会发现日志中显示归档日志已传送，但未应用。
如下为日志变化过程：
SQL> /
SEQUENCE# APPLIED
---------- ---------
7 NO
8 NO
9 NO
10 NO
11 NO
12 NO
13 NO
14 NO
15 NO
16 NO
17 NO
SEQUENCE# APPLIED
---------- ---------
17 NO
18 NO
18 NO
19 NO
19 NO
20 NO
20 NO
21 NO
21 NO
22 NO
22 NO
SEQUENCE# APPLIED
---------- ---------
23 NO
23 NO
24 NO
24 NO
25 NO
25 NO
26 NO
26 NO
27 NO
27 NO
28 NO
SEQUENCE# APPLIED
---------- ---------
28 NO
29 NO
29 NO
30 NO
30 NO
31 NO
31 NO
32 NO
32 NO
33 NO
33 NO
SEQUENCE# APPLIED
---------- ---------
34 NO
34 NO
35 NO
35 NO
36 NO
文章名称：Rman配置DataGuard使用主库未配置时备份withfilesystem
网站链接：http://6mz.cn/article/pjigci.html