Oracle adg主从同步

原创已于 2022-11-18 21:22:36 修改 · 3k 阅读

8 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle #数据库

于 2022-11-17 18:22:35 首次发布

Oracle 专栏收录该内容

1 篇文章

订阅专栏

本文档详细介绍了如何配置Oracle数据库的Active Data Guard（ADG）主从同步。内容涵盖系统环境设置，包括软件安装、用户组与目录创建及资源限制；Oracle数据库的安装过程，涉及环境变量配置、监听创建、数据库创建与参数调整；以及Oracle ADG的具体配置，如从库安装、主库文件复制、初始化参数文件调整和从库启动。在部署过程中，还特别提到了ORA-01119等错误的解决方法。

一、系统环境基本设置

1.1、安装相关软件

## 安装常用软件
yum install -y vim tree net-tools unzip
## 安装Oracle依赖包
yum install -y  compat-libstdc++-33 elfutils-libelf-devel gcc* glibc-* ksh libaio libaio-devel libstdc++-devel unixODBC unixODBC-devel sysstat

1.2、创建用户组和目录

##创建用户和组
/usr/sbin/groupadd -g 1000 oinstall
/usr/sbin/groupadd -g 1031 dba
/usr/sbin/useradd -u 1101 -g oinstall -G dba oracle
passwd oracle
##创建目录
mkdir -p /data/u01/oraInventory 
mkdir -p /data/u01/app/oracle 
mkdir -p /data/oradata/primary
mkdir -p /data/oradata/primarytbs
mkdir -p /data/oradata/active
chown -R oracle.oinstall /data/u01/app/oracle 
chown -R oracle.oinstall /data/u01 
chmod -R 775 /data/u01/app/oracle 
chmod -R 775 /data/u01 
chown -R oracle.oinstall /data 
chmod -R 775 /data

1.3、系统资源限制

## 修改内核参数文件
cat >> /etc/sysctl.conf << EOF
## add for oracle
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
EOF
## 使其生效
/sbin/sysctl -p
##系统资源限制
cat >> /etc/security/limits.d/20-nproc.conf <<EOF
* soft nproc 4096
root soft nproc unlimited
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
oracle hard memlock 134217728
oracle soft memlock 134217728
EOF

二、Oracle数据库的安装

2.1、修改oracle环境变量，安装Oracle

## 添加环境变量
 # add for oracle
export ORACLE_BASE=/data/u01/app/oracle
export ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=primary
export GDBNAME=primary
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH:. export PATH=$ORACLE_HOME/bin:$PATH:.
## 切换用户
su - oracle
##解压oracle压缩包
unzip linux.x64_11gR2_database_1of2.zip 
unzip linux.x64_11gR2_database_2of2.zip  ##用oracle用户解压
## 开始安装数据库
cd /homa/oacle/datebase/
vim response/db_install.rsp				##安装数据库软件时用到的文件，这里我已经把空行注释行除去了，把文件贴出来。
######################db_install.rsp#############################
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=primary								##主机名
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/data/u01/app/oracle/oraInventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/data/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.CLUSTER_NODES=
oracle.install.db.config.starterdb.type=
oracle.install.db.config.starterdb.globalDBName=
oracle.install.db.config.starterdb.SID=
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
oracle.install.db.config.starterdb.password.ALL=
oracle.install.db.config.starterdb.password.SYS=
oracle.install.db.config.starterdb.password.SYSTEM=
oracle.install.db.config.starterdb.password.SYSMAN=
oracle.install.db.config.starterdb.password.DBSNMP=
oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.dbcontrol.emailAddress=
oracle.install.db.config.starterdb.dbcontrol.SMTPServer=
oracle.install.db.config.starterdb.automatedBackup.enable=false
oracle.install.db.config.starterdb.automatedBackup.osuid=
oracle.install.db.config.starterdb.automatedBackup.ospwd=
oracle.install.db.config.starterdb.storageType=
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
oracle.install.db.config.asm.diskGroup=
oracle.install.db.config.asm.ASMSNMPPassword=
MYORACLESUPPORT_USERNAME=
MYORACLESUPPORT_PASSWORD=
SECURITY_UPDATES_VIA_MYORACLESUPPORT=
DECLINE_SECURITY_UPDATES=true
PROXY_HOST=
PROXY_PORT=
PROXY_USER=
PROXY_PWD=
#################################################################
## 执行安装命令
./runInstaller -silent -force -responseFile /home/oracle/database/response/db_install.rsp -ignorePrereq
## 安装成功后执行root.sh脚本
cd /data/u01/app/oracle/product/11.2.0/db_1/
su root
sh root.sh
## 切换oracle用户增加环境变量
su - oracle
vim .bash_profile
#new
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/JRE/lib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export LIBPATH=${CLASSPATH}:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
export ORACLE_OWNER=oracle
export SPFILE_PATH=$ORACLE_HOME/dbs
export ORA_NLS10=$ORACLE_HOME/nls/dat

2.2、Oracle数据库创建监听

## 配置监听
netca /silent /responsefile /home/oracle/database/response/netca.rsp
## netca.rsp 文件  需要修改端口的可以更改文件，如果不需要默认即可
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
## 查看监听状态
ss -tpln  | grep 1521

2.3创建数据库

/data/u01/app/oracle/product/11.2.0/db_1/bin/dbca \
-silent \
-createdatabase \
-templatename
/data/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/General_Purpose.dbc \
-sid primary \
-gdbname primary \
-syspassword oracle \
-systempassword oracle \
-emconfiguration LOCAL \
-sysmanpassword oracle \
-dbsnmppassword oracle \
-characterset al32utf8 \
-nationalCharacterSet al6utf16 \
-datafileDestination /data/oradata \
-redoLogFileSize 1024 \
-recoveryAreaDestination /data/fast_recovery_area \
-sampleSchema true

2.3、调整参数

## 修改参数
SQL> alter system set processes = 1024 scope = spfile;
SQL> alter system set open_cursors = 1024 scope = spfile;
## 开启强制归档日志
SQL> alter database force logging;
SQL> select FORCE_LOGGING from v$database;
SQL> alter system set standby_file_management=AUTO;
## 增加redo日志组 
SQL> alter database add logfile
group 4 ('/data/oradata/primary/redo04.log') size 1024M, 
group 5 ('/data/oradata/primary/redo05.log') size 1024M;
## 增加standby日志
SQL> alter database add standby logfile
group 21 ('/data/oradata/primary/staredo21.log') size 1024M, 
group 22 ('/data/oradata/primary/staredo22.log') size 1024M, 
group 23 ('/data/oradata/primary/staredo23.log') size 1024M, 
group 24 ('/data/oradata/primary/staredo24.log') size 1024M, 
group 25 ('/data/oradata/primary/staredo25.log') size 1024M, 
group 26 ('/data/oradata/primary/staredo26.log') size 1024M;
## 创建表空间
CREATE TABLESPACE primarytbs DATAFILE 
'/data/oradata/primarytbs/primarytbs0002.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 
UNLIMITED, 
'/data/oradata/primarytbs/primarytbs0005.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 
UNLIMITED, 
'/data/oradata/primarytbs/primarytbs0004.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 
UNLIMITED, 
'/data/oradata/primarytbs/primarytbs0003.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 
UNLIMITED, 
'/data/oradata/primarytbs/primarytbs0001.dbf' SIZE 10M AUTOEXTEND ON NEXT 100M MAXSIZE 
UNLIMITED 
LOGGING 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
BLOCKSIZE 8K 
SEGMENT SPACE MANAGEMENT AUTO 
FLASHBACK ON;
## 生成standby控制文件
alter database create standby controlfile as '/data/oradata/primary/standby.ctl';
## 关闭数据库
shutdown immediate;

2.4、init文件修改导出

## 导出当前数据库参数文件并修改
 create spfile from pfile='/data/u01/app/oracle/product/11.2.0/db_1/dbs/initprimary.ora';
 #################initprimary.ora############################
primary.__db_cache_size=6576668672
primary.__java_pool_size=67108864
primary.__large_pool_size=67108864
primary.__oracle_base='/data/u01/app/oracle'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=5435817984
primary.__sga_target=8053063680
primary.__shared_io_pool_size=0
primary.__shared_pool_size=1275068416
primary.__streams_pool_size=0
*.audit_file_dest='/data/u01/app/oracle/admin/primary/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/primary/control01.ctl','/data/fast_recovery_area/primary/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=13262389248
*.diagnostic_dest='/data/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.memory_target=13425967104
*.open_cursors=1024
*.processes=1024
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='primary'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=/data/oradata/active'
*.log_archive_dest_2='service="standby"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='/data/oradata/primary/','/data/oradata/primary/','/data/oradata/primarytbs/','/data/oradata/primarytbs/','/data/oradata/njrq/','/data/oradata/njrq/'
*.LOG_FILE_NAME_CONVERT='/data/oradata/primary/','/data/oradata/primary/'
*.standby_file_management='auto'

##########重点################
*.log_archive_dest_2='service="standby"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="standby" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
以上几行参数是解决同步延迟的配置之一

2.5、配置监听和访问服务

## 修改listenr.ora文件
# listener.ora Network Configuration File: /data/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.254.1.245)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /data/u01/app/oracle
###########下面是新增的
SID_LIST_LISTENER=
 (SID_LIST=
   (SID_DESC=
     (PROGRAM=extproc)
     (SID_NAME=PLSExtProc)
     (ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/db_1)
   )
   (SID_DESC=
     (GLOBAL_DBNAME=primary)
     (SID_NAME=primary)
     (ORACLE_HOME=/data/u01/app/oracle/product/11.2.0/db_1)
   )

  )
  ######################注意######################################
  sid和gdbname要和环境变量中保持一致

## 修改tnsnames.ora文件
#增加主库配置
PRIMARY =
    (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP) (HOST = 10.254.1.245) (PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = primary)
    )
    )
#增加从库配置
STANDBY=
    (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP) (HOST = 10.254.1.246) (PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = standby)
    )
    )
LISTENER_PRIMARY =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
  )
#################################测试监听########################
## 停止服务
SQL>shutdown immediate;
SQL>startup mount;
## alter database open是数据库处于mount状态，从mount状态打开数据库的命令。
SQL>alter database open;
lsnrctl stop
lsnrctl start
## 测试配置是否正确
SQL>sqlplus sys/oracle@primary as sysdba
## 测试成功关闭主库
SQL>shutdown immediate;

三、Oracle ADG 配置

3.1、从库Standby的安装

步骤和主库一样创建同样的目录，配置同样的环境，这里不多赘述
下面列出需要注意的几项

1、sid需要修改，此文档的备库sid为standby
2、db_install.rsp文件中主机名需要修改（从库只需安装数据库文件）
3、目录必须所属正确

3.2、主库相应文件复制到从库

## 创建相应目录
mkdir -p /data/oradata/primary
mkdir -p /data/oradata/primarytbs
mkdir -p /data/oradata/njrq
mkdir -p /data/fast_recovery_area/primary
## 复制文件（redo日志和standby日志），在主库操作
[oracle@primary primary]$ pwd
/data/oradata/primary
[oracle@primary primary]$ scp * oracle@10.254.1.246:/data/oradata/primary ##执行scp命令 把文件全部复制到从库
[oracle@primary primarytbs]$ pwd
/data/oradata/primarytbs
[oracle@primary primary]$ scp * oracle@10.254.1.246:/data/oradata/primary ##执行scp命令 把文件全部复制到从库
## 复制文件，密码文件和init文件
spc orapwprimary  initprimary.ora  oracle@10.254.1.246:/data/u01/app/oracle/product/11.2.0/db_1/dbs/
### 需要注意，从库需要将密码文件重命名。
## 移动从库文件 从库操作
## 刚才从主库复制过来的有standby.ctl(从库控制文件）
## 将控制文件重命名并复制到相应目录
[oracle@standby primary]$ pwd
/data/oradata/primary
[oracle@standby primary]$ cp standby.ctl control01.ctl   ## 直接覆盖原有的控制文件
[oracle@standby primary]$ cp  standby.ctl /data/fast_recovery_area/primary/control02.ctl           ## 直接覆盖原有的控制文件

## 配置从库监听
直接将主库文件复制过来，然后修改sid以及IP
[oracle@primary admin]$ pwd
/data/u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@primary admin]$ scp listener.ora  tnsnames.ora oracle@10.254.1.245:/data/u01/app/oracle/product/11.2.0/db_1/network/admin/		## 复制到从库然后修改SID和IP
## 启动监听
lsnrctl start

3.3、initprimay.ora文件

primary.__db_cache_size=6576668672
primary.__java_pool_size=67108864
primary.__large_pool_size=67108864
primary.__oracle_base='/data/u01/app/oracle'#ORACLE_BASE set from environment
primary.__pga_aggregate_target=5435817984
primary.__sga_target=8053063680
primary.__shared_io_pool_size=0
primary.__shared_pool_size=1275068416
primary.__streams_pool_size=0
*.audit_file_dest='/data/u01/app/oracle/admin/primary/adump'		## 确保这个目录存在
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/oradata/primary/control01.ctl','/data/fast_recovery_area/primary/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primary'					
*.db_recovery_file_dest='/data/fast_recovery_area'
*.db_recovery_file_dest_size=13262389248
*.diagnostic_dest='/data/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.memory_target=13425967104
*.open_cursors=1024
*.processes=1024
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='standby'
*.fal_client='standby'
*.fal_server='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='LOCATION=/data/oradata/active'
*.log_archive_dest_2='service="primary"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="primary" net_timeout=30','valid_for=(all_logfiles,primary_role)'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.DB_FILE_NAME_CONVERT='/data/oradata/primary/','/data/oradata/primary/','/data/oradata/primarytbs/','/data/oradata/primarytbs/','/data/oradata/njrq/','/data/oradata/njrq/'
*.LOG_FILE_NAME_CONVERT='/data/oradata/primary/','/data/oradata/primary/'
*.standby_file_management='auto'
################################################################
## 对比于主库文件我们需要修改
*.db_unique_name='standby'
*.log_archive_dest_2='service="primary"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="primary" net_timeout=30','valid_for=(all_logfiles,primary_role)'

3.4、启动从库配置从库

## 使用新参数文件建立从库实例
SQL> startup pfile='/data/u01/app/oracle/product/11.2.0/db_1/dbs/initprimary.ora' nomount;
SQL> create spfile from pfile='/data/u01/app/oracle/product/11.2.0/db_1/dbs/initprimary.ora';
SQL>shutdown immediate;
## 启动备用数据库为挂载备库模式
SQL>startup nomount;
SQL>alter database mount standby database;
## 在备用服务器上启动日志传送服务
SQL>alter database recover managed standby database disconnect from session;
SQL>shutdown immediate;
SQL>startup;
## 开启主库
SQL>startup;
## 验证配置
查看从库状态
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY
查看主库状态
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

部署过程中遇到的问题以及常见问题解决

1、ORA-01119,ORA-27038，ORA-01111
报错日志

在这里插入图片描述

日志目录：/data/u01/app/oracle/diag/rdbms/standby/standby/trace
问题原因：文件目录没有转换过来
这个问题也会导致数据库无法打开
解决办法：修改未识别的数据库文件目录

SQL> alter database create datafile '/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/data/oradata/primarytbs/primarytbs0002.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data/oradata/primary/system01.dbf
/data/oradata/primary/sysaux01.dbf
/data/oradata/primary/undotbs01.dbf
/data/oradata/primary/users01.dbf
/data/oradata/primary/example01.dbf
/data/oradata/primarytbs/primarytbs0002.dbf
/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007
/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008
/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00009
/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00010

10 rows selected.

SQL> alter database create datafile '/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00007' as '/data/oradata/primarytbs/primarytbs0005.dbf';

Database altered.

SQL> alter database create datafile '/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00008' as '/data/oradata/primarytbs/primarytbs0004.dbf';

Database altered.

SQL> alter database create datafile '/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00009' as '/data/oradata/primarytbs/primarytbs0003.dbf';

Database altered.


SQL> alter database create datafile '/data/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00011' as '/data/oradata/njrq/njrq01.dbf';

Database altered.
SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/data/oradata/primary/system01.dbf
/data/oradata/primary/sysaux01.dbf
/data/oradata/primary/undotbs01.dbf
/data/oradata/primary/users01.dbf
/data/oradata/primary/example01.dbf
/data/oradata/primarytbs/primarytbs0002.dbf
/data/oradata/primarytbs/primarytbs0005.dbf
/data/oradata/primarytbs/primarytbs0004.dbf
/data/oradata/primarytbs/primarytbs0003.dbf
/data/oradata/primarytbs/primarytbs0001.dbf
/data/oradata/njrq/njrq01.dbf
/data/oradata/primary/njrq01.dbf

12 rows selected.



SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.3429E+10 bytes
Fixed Size		    2217992 bytes
Variable Size		 6845106168 bytes
Database Buffers	 6576668672 bytes
Redo Buffers		    4661248 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1.3429E+10 bytes
Fixed Size		    2217992 bytes
Variable Size		 6845106168 bytes
Database Buffers	 6576668672 bytes
Redo Buffers		    4661248 bytes
Database mounted.
Database opened.
## 常用语句
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME  from V$DATAGUARD_STATS;		##查看同步延迟时间
select process, status, thread#,sequence#, block#, blocks from V$MANAGED_STANDBY where process in ('MRP0','RFS') order by process;            ##查看MRP进程
select name,open_mode,database_role,dataguard_broker,guard_status,switchover_status from v$database;   ##查看主从状态

###################如果还是有延迟需要配置##################################################
#解决办法	利用 using current logfile 开启实时传输
alter database recover managed standby database cancel; 
alter database recover managed standby database using current logfile disconnect ;