备案控制台
探索云世界
开发者社区 数据库 文章 正文

ORACLE 11.2.0.4 dg搭建及对DDL的支持验证

2016-09-01 979
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：     今天，有同事问ORACLE 11.2.0.4 dataguard是否对DDL支持；由于，我工作中oracle 11g的dataguard环境很少，所以对她的研究不是很多；因此，对于有疑问的知识，最好的办法就是用实验数据进行验证了。
    今天，有同事问ORACLE 11.2.0.4 dataguard是否对DDL支持；由于，我工作中oracle 11g的dataguard环境很少，所以对她的研究不是很多；因此，对于有疑问的知识，最好的办法就是用实验数据进行验证了。
    首先，先说明下实验结论：ORACLE 11.2.0.4的dataguard对DDL是支持的。
    服务器环境：
主库
[oracle@oradbs ~]$ uname -a
Linux oradbs 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oradbs ~]$
备库
[oracle@oratest ~]$ uname -a
Linux oratest 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oratest ~]$
    数据库版本：
主库
[oracle@oradbs ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oradbs ~]$
备库
[oracle@oratest ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.4.0 Production
[oracle@oratest ~]$ 
    搭建好的11.2.0.4 oracle dataguard:
主库：
[oracle@oradbs ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 09:07:11 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
select name,database_role from v$database;
NAME  DATABASE_ROLE
--------- ----------------
ORADB  PRIMARY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS  GAP_STATUS
--------- ------------------------
VALID  RESOLVABLE GAP

备库：
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 08:17:36 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name,database_role from v$database;
NAME  DATABASE_ROLE
--------- ----------------
ORADB  PHYSICAL STANDBY
SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID =2;
STATUS  GAP_STATUS
--------- ------------------------
VALID  NO GAP
SQL> COL NAME FOR A30
SQL> COL VALUE FOR A40
SQL> SET LINESIZE 1000
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS
NAME                        VALUE                                DATUM_TIME
------------------------------ ---------------------------------------- ------------------------------
transport lag              +00 00:00:00 08/31/2016 22:52:56
apply lag                   +00 00:00:00 08/31/2016 22:52:56
apply finish time
estimated startup time       5
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES

    oracle 11.2.0.4 dataguard对DDL支持测试：
验证用户创建语句：
主库：
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /oradata/arch
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence       23
SQL> create user zhul identified by zhul;
User created.
SQL>   alter system switch logfile;
System altered.

备库：
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES
22 31-AUG-16 31-AUG-16 YES
SQL> select username from dba_users where username='ZHUL';
no rows selected
SQL> /
USERNAME
------------------------------
ZHUL

注意：主库创建完用户，备库立即查询是查不到的，原因是当前的DATAGUARD是最大性能模式（如下图），需要主库切换归档并且备库应用完归档后，主库的用户创建才能查询到。

验证表空间创建语句：
主库：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> create tablespace test datafile '/home/oracle/oracle/oradata/oradb/test.dbf' size 100m;
Tablespace created.
SQL> alter system switch logfile;
System altered.

备库：
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oracle/oradata/oradb/users01.dbf
/home/oracle/oracle/oradata/oradb/undotbs01.dbf
/home/oracle/oracle/oradata/oradb/sysaux01.dbf
/home/oracle/oracle/oradata/oradb/system01.dbf
/home/oracle/oracle/oradata/oradb/test.dbf

    ORACLE 11.2.0.4 dg搭建及对DDL的支持验证最终结论是： ORACLE 11.2.0.4 对DDL是支持的。

本次测试环境的搭建过程：
主备节点主机：RHEL5.5
数据库版本：ORACLE 11.2.0.4
备库创建方法：可以使用RMAN备份主库到备库主机进行恢复，也可以使用duplicate直接在主库主机复制主库到备机生成备库，本次实验采用duplicate复制主库到备机生成备库的方法
前提：在相同平台操作系统下安装相同版本oracle11.2.0.4的数据库软件，不创建数据库
声明：本次DATAGURAD的搭建，仅仅是对DDL支持的验证实验，数据库部署环境及参数设置均不是合理的
调整主备机的监听及TNS解析文件
1、主库
[oracle@oradbs admin]$ cat listener.ora 
# listener.ora Network Configuration File: /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oradbs)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_listener=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=oradb)
      (SID_NAME=oradb)
   (ORACLE_HOME=/home/oracle/oracle/product/11.2.0.3/db)))
ADR_BASE_LISTENER = /home/oracle/oracle
[oracle@oradbs admin]$ 
[oracle@oradbs admin]$ cat tnsnames.ora 
beiku =(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )
zhuku =  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )
[oracle@oradbs admin]$ 
[oracle@oradbs ~]$ cat .bash_profile 
# .bash_profile
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/home/oracle/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/db
export ORACLE_SID=oradb
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@oradbs ~]$ 

2、备库
[oracle@oratest admin]$ cat listener.ora 
# listener.ora Network Configuration File: /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oratest)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_listener=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=oradb)
      (SID_NAME=oradb)
   (ORACLE_HOME=/oradata/oracle/product/11.2.0.4/db)))
ADR_BASE_LISTENER = /oradata/oracle
[oracle@oratest admin]$ 
[oracle@oratest admin]$ cat tnsnames.ora 
beiku =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.3)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )
zhuku =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )
[oracle@oratest admin]$ 
[oracle@oratest ~]$ cat .bash_profile 
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_BASE=/oradata/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db
export ORACLE_SID= oradb
export ORACLE_UNQNAME=beiku
export NLS_LANG=
export PATH=$PATH:$ORACLE_HOME/bin

主库的主要操作
1、主库设置归档模式，启动强日志模式
SQL> select log_mode,force_logging from v$database;
LOG_MODE     FOR
------------ ---
ARCHIVELOG   NO
SQL> alter database force logging;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE     FOR
------------ ---
ARCHIVELOG   YES
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /home/oracle/oracle/product/11.2.0.3/db/dbs/arch
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence       7
2、主库修改归档路径
SQL> alter system set log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb';
System altered.
SQL> alter system set log_archive_dest_2='SERVICE= beiku  async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= oradb'; 
System altered.
SQL> show parameter log_archive_dest_2
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2     string service=beiku async valid_for=
(online_logfile,primary_role)
db_unique_name=oradb
SQL> show parameter log_archive_dest_1
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1     string LOCATION=/oradata/arch VALID_F
OR=(ALL_LOGFILES,ALL_ROLES) DB
_UNIQUE_NAME=oradb

3、主库添加dg相关日志组
SQL>select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
3   ONLINE  /home/oracle/oracle/oradata/oradb/redo03.log
2   ONLINE  /home/oracle/oracle/oradata/oradb/redo02.log
1   ONLINE  /home/oracle/oracle/oradata/oradb/redo01.log
3  rows selected.
SQL>  alter database add standby logfile thread 1 group 4 ('/home/oracle/oracle/oradata/oradb/redo04.log') size 50M;
 alter database add standby logfile thread 1 group 5 ('/home/oracle/oracle/oradata/oradb/redo05.log') size 50M;
 alter database add standby logfile thread 1 group 6 ('/home/oracle/oracle/oradata/oradb/redo06.log') size 50M;
 alter database add standby logfile thread 1 group 7 ('/home/oracle/oracle/oradata/oradb/redo07.log') size 50M;
Database altered.
SQL> 
Database altered.
SQL> 
Database altered.
SQL> 
Database altered.
SQL> select group#,status,type,member from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
3   ONLINE  /home/oracle/oracle/oradata/oradb/redo03.log
2   ONLINE  /home/oracle/oracle/oradata/oradb/redo02.log
1   ONLINE  /home/oracle/oracle/oradata/oradb/redo01.log
4   STANDBY /home/oracle/oracle/oradata/oradb/redo04.log
5   STANDBY /home/oracle/oracle/oradata/oradb/redo05.log
6   STANDBY /home/oracle/oracle/oradata/oradb/redo06.log
7   STANDBY /home/oracle/oracle/oradata/oradb/redo07.log
7 rows selected.
4、主库修改归档最大进程数
SQL> show parameter log_archive_max
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes     integer 4
SQL> alter system set log_archive_max_processes=30;
System altered.
SQL> show parameter log_archive_max
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes     integer 30
5、主库修改dg日志控制参数
SQL> alter system set log_archive_config='dg_config=(oradb,beiku)';
System altered.
SQL> alter system set fal_server=beiku;
System altered.
SQL> alter system set fal_client=oradb;
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL> show parameter log_archive_config
NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config     string dg_config=(oradb,beiku)
6、主库生成启动备库的pfile参数文件
SQL> create pfile from spfile;
File created.
7、主库生成数据库的密码文件
[oracle@oradbs dbs]$ orapwd file=orapworadb password=oracle 
8、传送pfile和密码文件到备机
[oracle@oradbs dbs]$ scp orapworadb 192.168.56.3:/oradata/oracle/product/11.2.0.4/db/dbs/
oracle@192.168.56.3's password: 
orapworadb                                                                             100% 1536     1.5KB/s   00:00    
[oracle@oradbs dbs]$ cd ..
[oracle@oradbs ~]$ scp pfile.ora 192.168.56.3:/home/oracle/
oracle@192.168.56.3's password: 
pfile.ora                                                                              100% 1182     1.2KB/s   00:00    
[oracle@oradbs ~]$
9、确认主库监听启动
[oracle@oradbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 01-SEP-2016 11:03:44
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradbs)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                31-AUG-2016 23:01:26
Uptime                    0 days 12 hr. 2 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File         /home/oracle/oracle/diag/tnslsnr/oradbs/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradbs)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 2 instance(s).
  Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oradbs ~]$

备库的主要操作
1、修改/home/oracle/pfile.ora文件，
DB_UNIQUE_NAME=oradb
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=BEIKU
FAL_CLIENT=ORADB
STANDBY_FILE_MANAGEMENT=AUTO
2、备库使用pfile启动到nomount
[oracle@oratest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:32:46 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size    2257880 bytes
Variable Size  683674664 bytes
Database Buffers  150994944 bytes
Redo Buffers    2355200 bytes
3、备库监听启动
[oracle@oratest admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-AUG-2016 22:11:38
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Starting /oradata/oracle/product/11.2.0.4/db/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Log messages written to /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oratest)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                31-AUG-2016 22:11:38
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oradata/oracle/product/11.2.0.4/db/network/admin/listener.ora
Listener Log File         /oradata/oracle/diag/tnslsnr/oratest/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oratest)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradb" has 1 instance(s).
  Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4、监听及TNS解析服务可用性测试
[oracle@oratest admin]$ sqlplus sys/oracle@zhuku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 31 22:12:39 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$ 
[oracle@oratest ~]$ sqlplus sys/oracle@beiku as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 1 10:36:28 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>   quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oratest admin]$ 

主库duplicate备库到备机
1、主库使用rman连接到备库实例
[oracle@oradbs admin]$  rman target sys/oracle@zhuku auxiliary sys/oracle@beiku
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 31 23:26:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORADB (DBID=2674606220)
connected to auxiliary database: ORADB (not mounted)
RMAN> 
2、主库执行duplicate
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 31-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/home/oracle/oracle/product/11.2.0.3/db/dbs/orapworadb' auxiliary format 
 '/oradata/oracle/product/11.2.0.4/db/dbs/orapworadb'   ;
}
executing Memory Script
Starting backup at 31-AUG-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
Finished backup at 31-AUG-16
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/home/oracle/oracle/oradata/oradb/control01.ctl';
   restore clone controlfile to  '/home/oracle/oracle/oradata/oradb/control02.ctl' from 
 '/home/oracle/oracle/oradata/oradb/control01.ctl';
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/oracle/product/11.2.0.3/db/dbs/snapcf_oradb.f tag=TAG20160831T232714 RECID=3 STAMP=921367635
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 31-AUG-16
Starting restore at 31-AUG-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 31-AUG-16
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 
 "/home/oracle/oracle/oradata/oradb/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/home/oracle/oracle/oradata/oradb/system01.dbf";
   set newname for datafile  2 to 
 "/home/oracle/oracle/oradata/oradb/sysaux01.dbf";
   set newname for datafile  3 to 
 "/home/oracle/oracle/oradata/oradb/undotbs01.dbf";
   set newname for datafile  4 to 
 "/home/oracle/oracle/oradata/oradb/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/home/oracle/oracle/oradata/oradb/system01.dbf"   datafile 
 2 auxiliary format 
 "/home/oracle/oracle/oradata/oradb/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/home/oracle/oracle/oradata/oradb/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/home/oracle/oracle/oradata/oradb/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/oracle/oradata/oradb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/oracle/oradata/oradb/system01.dbf
output file name=/home/oracle/oracle/oradata/oradb/system01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
output file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
output file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/oracle/oradata/oradb/users01.dbf
output file name=/home/oracle/oracle/oradata/oradb/users01.dbf tag=TAG20160831T232721
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oradata/arch/1_19_921341455.dbf" auxiliary format 
 "/oradata/arch/1_19_921341455.dbf"   ;
   catalog clone archivelog  "/oradata/arch/1_19_921341455.dbf";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 31-AUG-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=19 RECID=16 STAMP=921367665
output file name=/oradata/arch/1_19_921341455.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-AUG-16
cataloged archived log
archived log file name=/oradata/arch/1_19_921341455.dbf RECID=1 STAMP=921364551
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=921364551 file name=/home/oracle/oracle/oradata/oradb/users01.dbf
contents of Memory Script:
{
   set until scn  984934;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 31-AUG-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 19 is already on disk as file /oradata/arch/1_19_921341455.dbf
archived log file name=/oradata/arch/1_19_921341455.dbf thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-AUG-16
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/oracle/oradata/oradb/redo01.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/home/oracle/oracle/oradata/oradb/redo02.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/home/oracle/oracle/oradata/oradb/redo03.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 4 thread 1: '/home/oracle/oracle/oradata/oradb/redo04.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 5 thread 1: '/home/oracle/oracle/oradata/oradb/redo05.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 6 thread 1: '/home/oracle/oracle/oradata/oradb/redo06.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 1: '/home/oracle/oracle/oradata/oradb/redo07.log'
RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 31-AUG-16
RMAN> quit
Recovery Manager complete.

备库创建dg相关的日志
SQL>  alter database add standby logfile thread 1 group 12 ('/home/oracle/oracle/oradata/oradb/redo12.log') size 50M;
 alter database add standby logfile thread 1 group 11 ('/home/oracle/oracle/oradata/oradb/redo11.log') size 50M;
 alter database add standby logfile thread 1 group 10 ('/home/oracle/oracle/oradata/oradb/redo10.log') size 50M;
 alter database add standby logfile thread 1 group 9 ('/home/oracle/oracle/oradata/oradb/redo09.log') size 50M;
Database altered.
SQL> 
Database altered.
SQL> 
Database altered.
SQL> 
Database altered.
备库将数据库置为只读模式
SQL> alter database open read only;
Database altered.
备库开启同步
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
查看是否有gap日志中断
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
主库切换日志
SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /oradata/arch
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence       20
SQL>  alter system switch logfile;
System altered.
SQL>  alter system switch logfile;
System altered.
SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /oradata/arch
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence       22
备库查看日志应用同步
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
19 31-AUG-16 31-AUG-16 YES
20 31-AUG-16 31-AUG-16 YES
21 31-AUG-16 31-AUG-16 YES

查看主库告警日志，发现如下内容也说明dg搭建成功
Wed Aug 31 23:44:49 2016
ARC2: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:49 2016
ARCf: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
Thread 1 advanced to log sequence 22 (LGWR switch)
  Current log# 1 seq# 22 mem# 0: /home/oracle/oracle/oradata/oradb/redo01.log
Wed Aug 31 23:44:52 2016
Archived Log entry 18 added for thread 1 sequence 21 ID 0x9f6aea8c dest 1:
Wed Aug 31 23:44:52 2016
ARC4: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:44:52 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LNS: Archival destination is a Primary RAC instance: 'beiku'
Wed Aug 31 23:48:38 2016
Thread 1 advanced to log sequence 23 (LGWR switch)
备库查看告警日志有如下内容：
Media Recovery Waiting for thread 1 sequence 20
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Aug 31 22:52:54 2016
RFS[1]: Assigned to RFS process 11189
RFS[1]: Opened log for thread 1 sequence 20 dbid -1620361076 branch 921341455
Archived Log entry 2 added for thread 1 sequence 20 rlc 921341455 ID 0x9f6aea8c dest 2:
Wed Aug 31 22:52:54 2016
Media Recovery Log /oradata/arch/1_20_921341455.dbf
Media Recovery Waiting for thread 1 sequence 21
Wed Aug 31 22:52:57 2016
RFS[2]: Assigned to RFS process 11193
RFS[2]: Opened log for thread 1 sequence 21 dbid -1620361076 branch 921341455
Archived Log entry 3 added for thread 1 sequence 21 rlc 921341455 ID 0x9f6aea8c dest 2:

如果实验中有不对的地方，敬请看客指正！
至此，ORACLE 11.2.0.4 DATAGUARD的实验环境搭建完成！







    
文章标签：
云原生数据仓库AnalyticDB MySQL版
数据库备份
日志服务
关系型数据库
Oracle
SQL
数据库
网络协议
测试技术
OLAP
Linux
安全
关键词：
Oracle数据库一键上云ddl
Oracle数据库一键上云验证
Oracle数据库一键上云支持
Oracle数据库一键上云dg
相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库，在RDS-MySQL中完成对学生成绩的详情查询，执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库，高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准，可以对海量数据进行即时的多维分析透视和业务探索，快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
kissweety
目录
相关文章
lemotree
oracle-故障-在dg备库使用DB LINK跨库查询时遇到ORA-16000
lemotree
1337 0
靖宇ring
|
5月前
|
Oracle 关系型数据库 数据库
oracle导入时IMP-00010: 不是有效的导出文件, 头部验证失败
oracle导入时IMP-00010: 不是有效的导出文件, 头部验证失败
靖宇ring
126 1
翎野君
|
11月前
|
SQL Oracle 安全
Oracle DDL+DML+DCL实例
Oracle DDL+DML+DCL实例
翎野君
87 0
QGS
|
Oracle 关系型数据库 Linux
Centos7安装oracle客户端并验证oracle客户端
记Centos7安装oracle客户端并验证oracle客户端
QGS
428 0
Centos7安装oracle客户端并验证oracle客户端
YourBatman
|
Oracle 架构师 Java
【方向盘】Oracle Java SE Support Roadmap：支持JDK 8到2030年
Java的发展史，也伴随着JVM的变迁史
YourBatman
321 0
【方向盘】Oracle Java SE Support Roadmap：支持JDK 8到2030年
bqospzg5rfs7g
|
存储 SQL Oracle
10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记
快速学习10 PostgreSQL 表级复制-物化视图篇,支持异地,异构如 Oracle 到 pg 的物化视图
bqospzg5rfs7g
422 0
10 PostgreSQL 表级复制-物化视图篇, 支持异地,异构如 Oracle 到 pg 的物化视图|学习笔记
lemotree
|
数据库
oracle-排查dg环境文件不开Auto同步导致的错误
lemotree
89 0
lemotree
|
Oracle 关系型数据库
oracle-rman-如何在oracle dg端进行rman备份
lemotree
390 0
中间件兴趣圈
|
canal SQL Oracle
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步
中间件兴趣圈
3468 0
新的增量数据同步工具闪亮登场，完美支持Oracle增量同步
再见孙悟空_
|
Oracle 安全 关系型数据库
出现身份验证错误,要求的函数不受支持(这可能是由于CredSSP加密Oracle修正)
出现身份验证错误,要求的函数不受支持(这可能是由于CredSSP加密Oracle修正)
再见孙悟空_
284 0

热门文章

最新文章

  • 1
    Oracle 面试题及答案整理，最新面试题
  • 2
    Seata常见问题之oracle 数据库 报 just support mysql如何解决
  • 3
    PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
  • 4
    Dataphin实时集成Oracle CDC相关问题排查
  • 5
    【Oracle】玩转Oracle数据库（一）：装上去，飞起来！
  • 6
    【Oracle】玩转Oracle数据库（二）：体系结构、存储结构与各类参数
  • 7
    【Oracle】玩转Oracle数据库（七）：RMAN恢复管理器
  • 8
    struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Emp实体类和对应的配置信息
  • 9
    oracle基本笔记整理及案例分析1
  • 10
    struts+hibernate+oracle+easyui实现lazyout组件的简单案例——EmpDao层代码
  • 1
    服务器Centos7 静默安装Oracle Database 12.2
    71
  • 2
    SpringBoot整合Mybatis连接Oracle数据库
    52
  • 3
    避坑，Oracle中rownum与order by的执行顺序
    24
  • 4
    避坑，Oracle中left join 与 (+) 的区别
    19
  • 5
    Oracle临时表详解
    17
  • 6
    Oracle insert数据时字符串中有‘单引号问题
    25
  • 7
    【Bug记录】Oracle中小数点前面的0不显示的问题
    37
  • 8
    Oracle中merge Into的用法
    17
  • 9
    Flink CDC产品常见问题之使用cdc-Oracle连接器报错如何解决
    48
  • 10
    Oracle系列十八：Oracle RAC
    46

    • 相关课程

    更多
  • 如何在 PolarDB-X 中进行 Online DDL
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • PolarDB的Online DDL功能验证实验
  • PolarDB数据库维护
  • 如何在PolarDB-X中进行Online DDL
  • 使用DAS实现数据库自动SQL限流
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群

    • 推荐镜像

    更多
  • oracle
  • postgresql
  • mysql
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4