DB-hub Technology Oracle Lab – Data Guard ASM Single Instance 12c
Oracle Lab – Data Guard ASM Single Instance 12c
1.实验环境
请参考12c ASM安装实验。
1.1.环境准备
主机名：dg3
IP地址：192.168.1.183
数据库：OTTER
DB_UNIQUE_NAME: OTTER_PR
主机名：dg4
IP地址：192.168.1.184
数据库：OTTER
DB_UNIQUE_NAME: OTTER_DR
Oracle Database 12.2
OS: CentOS Linux 7.9
vi /etc/hosts
192.168.1.183 dg3 dg3.lab.com
192.168.1.184 dg4 dg4.lab.com
1.2.oracle用户profile
vi /home/oracle/.bash_profile
export ORACLE_SID=OTTER
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/db
export GRID_HOME=#ORACLE_BASE/product/12.2.0.1/gri
export TNS_ADMIN=#ORACLE_HOME/network/admin
export PATH=¥ORACLE_HOME/bin:#GRID_HOME/bin:#PATH
export LD_LIBRARY_PATH=#ORACLE_HOME/lib:#LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export DISPLAY=localhost:10.0
export PS1='[\u@\h:`pwd`]$'
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
2.创建数据库
只需要在dg3创建数据库。
2.1.静默方式
Managing Redo Log Files Using OMF:
When using OMF for redo logs the DB_CREAT_ONLINE_LOG_DEST_n parameters in the “init.ora” file decide on the locations and numbers of logfile members. For example:
DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata'
DB_CREATE_ONLINE_LOG_DEST2 ='/u03/oradata'
Managing Controlfiles Using OMF
During database creation the controlfile names are not specified. Instead, a controlfile is created for each DB_CREATE_ONLINE_LOG_DEST_n specified in the init.ora file. Once the database creation is complete the CONTROL_FILES parameter can be set in the init.ora file using the generated names shown in the V$CONTROLFILE view.
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-responseFile NO_VALUE \
-gdbname OTTER \
-sid OTTER \
-databaseConfigType SINGLE \
-sysPassword oracle \
-systemPassword oracle \
-emConfiguration NONE \
-storageType ASM \
-diskGroupName '+DATA' \
-datafileDestination '+DATA' \
-recoveryGroupName '+FRA' \
-recoveryAreaDestination '+FRA' \
-enableArchive true \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-sampleSchema false \
-automaticMemoryManagement true \
-memoryPercentage 40 \
-dbOptions 'JSERVER:true,ORACLE_TEXT:true,IMEDIA:false,SPATIAL:false,CWMLITE:false,APEX:false,OMS:false,DV:false,SAMPLE_SCHEMA:false' \
-ignorePrereqFailure \
-variables 'DB_NAME=OTTER,DB_UNIQUE_NAME=OTTER_PR,ORACLE_BASE=/oracle/app/oracle,ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/db' \
-initParams 'DB_NAME=OTTER,DB_UNIQUE_NAME=OTTER_PR,nls_language=AMERICAN,nls_territory=AMERICA,db_recovery_file_dest_size=2048M,db_recovery_file_dest=+FRA,processes=150,open_cursors=150'
***-archiveLogDest: If not specified, FRA location will be used***
[WARNING] [DBT-06801] Specified Fast Recovery Area size (2,048 MB) is less than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the database size (1,142 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06801] Specified Fast Recovery Area size (2,048 MB) is less than the recommended value.
CAUSE: Fast Recovery Area size should at least be three times the database size (2,672 MB).
ACTION: Specify Fast Recovery Area Size to be at least three times the database size.
Registering database with Oracle Restart
5% complete
Copying database files
6% complete
7% complete
22% complete
36% complete
Creating and starting Oracle instance
37% complete
39% complete
43% complete
47% complete
51% complete
52% complete
53% complete
56% complete
57% complete
Completing Database Creation
58% complete
60% complete
61% complete
64% complete
67% complete
68% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/OTTER/OTTER.log" for further details.
select name from v#datafile
union
select member from v#logfile
union
select name from v#controlfile
union
select name from v#tempfile;
NAME
-------------------------------------------------
+DATA/OTTER_PR/CONTROLFILE/current.260.1064853955
+DATA/OTTER_PR/DATAFILE/sysaux.257.1064853883
+DATA/OTTER_PR/DATAFILE/system.270.1064853839
+DATA/OTTER_PR/DATAFILE/undotbs1.263.1064853909
+DATA/OTTER_PR/DATAFILE/users.265.1064853909
+DATA/OTTER_PR/ONLINELOG/group_1.266.1064853957
+DATA/OTTER_PR/ONLINELOG/group_2.264.1064853957
+DATA/OTTER_PR/ONLINELOG/group_3.258.1064853957
+DATA/OTTER_PR/TEMPFILE/temp.269.1064853969
+FRA/OTTER_PR/CONTROLFILE/current.267.1064853955
+FRA/OTTER_PR/ONLINELOG/group_1.265.1064853959
+FRA/OTTER_PR/ONLINELOG/group_2.264.1064853959
+FRA/OTTER_PR/ONLINELOG/group_3.259.1064853959
13 rows selected.
/dev/shm问题：
[FATAL] [DBT-11214] Automatic Memory Management is not a feasible option on the system.
CAUSE: There is not enough free space on volume /dev/shm to allocate 1,571MB.
tmpfs 2.0G 640M 1.4G 32% /dev/shm
sudo vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=3G 0 0
sudo mount -o remount /dev/shm
tmpfs 3.0G 640M 2.4G 21% /dev/shm
删除数据库，仅供参考：
dbca -silent -deleteDatabase -sourceDB OTTER \
-sysDBAUserName sys -sysDBAPassword Oracle \
-forceArchiveLogDeletion
2.2.图形方式
请参考 Oracle 安装实验
2.3.配置Listener
[oracle@dg3:/home/oracle]srvctl status listener
PRCN-2044 : No listener exists
[oracle@dg3:/home/oracle]srvctl add listener
[oracle@dg3:/home/oracle]srvctl start listener
[oracle@dg3:/home/oracle]srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dg3
[oracle@dg3:/home/oracle]lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2021 12:18:23
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 18-FEB-2021 12:17:56
Uptime 0 days 0 hr. 0 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/12.2.0.1/grid/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/dg3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.183)(PORT=1521)))
The listener supports no services
The command completed successfully
cd $ORACLE_HOME/network/admin/
listener.ora
已经自动配置动态注册Listener,但是RMAN Duplicate需要静态Listener,因此还需要配置一个静态的Listener,使用不同的端口，如：1525。
LISTENER_DG=
(DESCRIPTION=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=dg3)(PORT=1525)))
)
SID_LIST_LISTENER_DG =
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=OTTER)
(ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/db)
(SID_NAME=OTTER)
)
)
tnsnames.ora
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ---------
service_names string OTTER_PR
lsnrctl status listener_dg
Service "OTTER" has 1 instance(s).
Instance "OTTER", status UNKNOWN, has 1 handler(s) for this service...
OTTER_PR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OTTER)
)
)
OTTER_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OTTER)
)
)
sqlnet.ora
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
ADR_BASE = /oracle/app/oracle
3.配置主库
3.1.开启闪回模式
开启归档
在开启闪回功能之前，必须先开启数据库归档。
默认归档存储路径是USE_DB_RECOVERY_FILE_DEST。
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
不要设置log_archive_dest_1参数，开启闪回的时候，设置db_recovery_file_dest。
开启闪回
alter system set db_recovery_file_dest_size=2g;
alter system set db_recovery_file_dest='+FRA';
alter database flashback on;
alter system set db_flashback_retention_target=1440 scope=spfile;
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 2G
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
3.2.设置force_loging
alter database force logging;
select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
3.3.主库增加Standby Redo Logfile
SRL： Standby Redo Log
ORL： Online Redo Log
- SRL只有在备库中才起作用。备库中SRL相当于主库中的ORL，在主库发生日志切换时，Remote File System（RFS）进程把主库中的ORL写到备库中的SRL，同时备库归档上一个SRL。
- 每个SRL 至少要和主库的ORL 一样大，为了方便管理，Oracle 建议主备库的ORL 设置成一样的大小。
- SRL 至少要比主库的ORL多一组。 可以在主库查询v$log视图，来确定主库有多少组ORL。
公式：nx+1 （n为日志组数，x为节点数） - Oracle 建议在主库也创建SRL，在进行switchover 之后， 主库变为备库，不需要再创建SRL。
SQL> select group#, thread#, bytes/1024/1024 from vlog;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 1 200
alter database add standby logfile size 200M;
alter database add standby logfile size 200M;
alter database add standby logfile size 200M;
alter database add standby logfile size 200M;
set linesize 500
col MEMBER for a50
select group#, status, type, member, IS_RECOVERY_DEST_FILE from vlogfile order by 1;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/OTTER/ONLINELOG/group_1.258.1064833611 NO
1 ONLINE +FRA/OTTER/ONLINELOG/group_1.258.1064833613 YES
2 ONLINE +DATA/OTTER/ONLINELOG/group_2.265.1064833611 NO
2 ONLINE +FRA/OTTER/ONLINELOG/group_2.259.1064833613 YES
3 ONLINE +DATA/OTTER/ONLINELOG/group_3.263.1064833611 NO
3 ONLINE +FRA/OTTER/ONLINELOG/group_3.257.1064833613 YES
4 STANDBY +DATA/OTTER/ONLINELOG/group_4.267.1064840107 NO
4 STANDBY +FRA/OTTER/ONLINELOG/group_4.263.1064840107 YES
5 STANDBY +DATA/OTTER/ONLINELOG/group_5.268.1064840111 NO
5 STANDBY +FRA/OTTER/ONLINELOG/group_5.264.1064840111 YES
6 STANDBY +DATA/OTTER/ONLINELOG/group_6.269.1064840113 NO
6 STANDBY +FRA/OTTER/ONLINELOG/group_6.265.1064840113 YES
7 STANDBY +DATA/OTTER/ONLINELOG/group_7.270.1064840209 NO
7 STANDBY +FRA/OTTER/ONLINELOG/group_7.266.1064840209 YES
14 rows selected.
SQL> select group#, bytes/1024/1024 MB from v$log;
GROUP# MB
---------- ----------
1 50
2 50
3 50
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V¥STANDBY_LOG;
GROUP# MB
---------- ----------
4 50
5 50
6 50
7 50
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
3.4.主库启用最大性能模式
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
3.5.启用远程登录
Data Guard 配置中的数据库要进行互相连接，需要带有口令文件的远程登录。
启用
alter system set remote_login_passwordfile=exclusive scope=spfile;
验证
show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
3.6.主库参数设置
alter system set fal_server='OTTER_DR' scope=spfile;
alter system set fal_client='OTTER_PR' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(OTTER_PR, OTTER_DR)' scope=spfile;
alter system set log_archive_dest_2='SERVICE=OTTER_DR LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=OTTER_DR' scope=spfile;
alter system set log_archive_dest_state_2='enable' scope=spfile;
alter system set log_archive_max_processes=5 scope=spfile;
bounce database
The startup force command is issued when the database is already started, and it consists of two operations. If the startup force command is issues when the instance is running, the following commands are issued serially:
Shutdown abort: Shutdown the database immediately, cancelling any in-flight DML updates.
Startup: This is a normal startup that will roll-back the in-flight transactions from shutdown time, leaving a consistent, non-corrupted database.
不要设置standby_file_management参数，默认值是manual。如果设置成auto，duplicate出错:
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
所有Redo Log 文件都无法复制。
可以在duplicate 完成后设置此参数。
最后，不要忘了重启数据库
4.配置备库
4.1.配置备库Listener,tnsnames
主库，备库配置是一样的。参考2.3
备库从主库拷贝（scp命令）listener.ora, tnsnames.ora, sqlnet.ora 三个文件， 只需要修改listener.ora中的IP地址或者主机名。其余两个文件完全一样。
主库和备库测试：
tnsping OTTER_DR
tnsping OTTER_PR
4.2./etc/oratab
OTTER:/oracle/app/oracle/product/12.2.0.1/db:N
4.3.创建init.ora文件
对于 RMAN duplicate方法而言，init.ora 文件仅需要一个参数：db_name（甚至不必是数据库的真实名称 — 可使用任意名称）。RMAN 将从主数据库复制 spfile，因此仅在复制的第一阶段需要该 init.ora 文件。
db_name = OTTER
4.4.创建PFILE
vi $ORACLE_HOME/dbs/initOTTER.ora
DB_NAME=OTTER
DB_UNIQUE_NAME=OTTER_DR
其余的参数将通过RMAN Duplicate 命令设置。
duplicate命令可以设置spfile参数，但是必须以pfile启动，否则会出错：
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
4.5.拷贝主库密码文件
[oracle@dg4:/oracle/app/oracle/product/12.2.0.1/db/dbs]scp oracle@dg3:/oracle/app/oracle/product/12.2.0.1/db/dbs/orapwOTTER .
4.6.创建相应的目录
[oracle@dg4:/oracle]mkdir -p /oracle/app/oracle/admin/OTTER_DR/adump
[oracle@dg4:/oracle]mkdir -p /oracle/app/oracle/audit
5.创建备库
5.1.备库启动到nomount
startup nomount pfile=?/dbs/initOTTER.ora
select instance_name, status, database_status from v$instance;
Make sure you exit sqlplus after starting the database in nomount. The duplicate procedure will restart the database and an open connection can cause the process to fail.
5.2.运行rman
rman
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Feb 18 17:45:42 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN>
RMAN> connect target sys/oracle@OTTER_PR
connected to target database: OTTER (DBID=504562627)
RMAN> connect auxiliary sys/oracle@OTTER_DR
connected to auxiliary database: OTTER (not mounted)
5.3.运行duplicate命令
run {
allocate channel cl1 type disk;
allocate channel cl2 type disk;
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database
for standby
from active database
dorecover
spfile
set DB_UNIQUE_NAME='OTTER_DR'
set audit_file_dest='/oracle/app/oracle/admin/OTTER_DR/adump'
set fal_server='OTTER_PR'
set fal_client='OTTER_DR'
set log_archive_config='dg_config=(OTTER_PR,OTTER_DR)'
set log_archive_dest_2='service=OTTER_PR LGWR async valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=OTTER_PR'
nofilenamecheck;
release channel c1;
release channel c2;
release channel cl1;
release channel cl2;
}
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
Cause:
It was not possible to convert ASM Oracle Managed Files names using DB_FILE_NAME_CONVERT parameter. RMAN changed these invalid names to the converted disk group name instead.
Action:
No action is required. This is an informational message only.
If the automatic change is incorrect, use one of the following options instead of using DB_FILE_NAME_CONVERT for ASM Oracle Managed Files:
1) use RMAN command SET NEWNAME for each Oracle Managed File.
2) set DB_CREATE_FILE_DEST initialization parameter in auxiliary instance and not specify DB_FILE_NAME_CONVERT.
The source filenames are in ASM OMF format, e.g., +DATA/datafile/xxxx.xx,
then RMAN will generate new OMF names.
This is because ASM OMF filenames are unique and cannot be specified by the user or using DB_FILE_NAME_CONVERT.
i.e., it is generated by ASM code itself. Therefore, restored filenames will be different from the source filename, and only the diskgroup name is honored in the filename conversion.
For example, suppose original filename is:
+DATA/OTTER_PR/DATAFILE/system.270.1064853839
DB_FILE_NAME_CONVERT=(‘+DATA’, ‘+STDBY_DATA’)
After restore, the filename would not be
+STDBY_DATA/OTTER_DR/DATAFILE/system.270.1064853839
but will be a newly created and OMF
select name from v$datafile
union
select member from v#logfile
union
select name from v#controlfile
union
7 select name from v#tempfile;
NAME
-------------------------------------------------------------
+DATA
+DATA/OTTER_DR/CONTROLFILE/current.277.1064865203
+DATA/OTTER_DR/DATAFILE/sysaux.268.1064865211
+DATA/OTTER_DR/DATAFILE/system.269.1064865211
+DATA/OTTER_DR/DATAFILE/undotbs1.267.1064865211
+DATA/OTTER_DR/DATAFILE/users.266.1064865213
+DATA/OTTER_DR/ONLINELOG/group_1.261.1064865243
+DATA/OTTER_DR/ONLINELOG/group_2.265.1064865245
+DATA/OTTER_DR/ONLINELOG/group_3.264.1064865245
+DATA/OTTER_DR/ONLINELOG/group_4.276.1064865245
+DATA/OTTER_DR/ONLINELOG/group_5.275.1064865245
+DATA/OTTER_DR/ONLINELOG/group_6.274.1064865247
+DATA/OTTER_DR/ONLINELOG/group_7.273.1064865247
+FRA/OTTER_DR/CONTROLFILE/current.257.1064865203
+FRA/OTTER_DR/ONLINELOG/group_1.271.1064865245
+FRA/OTTER_DR/ONLINELOG/group_2.270.1064865245
+FRA/OTTER_DR/ONLINELOG/group_3.264.1064865245
+FRA/OTTER_DR/ONLINELOG/group_4.263.1064865245
+FRA/OTTER_DR/ONLINELOG/group_5.262.1064865247
+FRA/OTTER_DR/ONLINELOG/group_6.261.1064865247
+FRA/OTTER_DR/ONLINELOG/group_7.260.1064865249
21 rows selected.
SQL>
5.4.STANDBY_FILE_MANAGEMENT
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
5.5.查看备库状态
SQL> select open_mode, database_role, switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
6.测试
6.1.启动和停止Real Time Apply
SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
alter database recover managed standby database cancel;