12c-单机Data Guard搭建--oracle DG
来源：互联网 发布：宁波大学c语言复试 编辑：程序博客网 时间：2024/05/21 07:52
12c单机DG
1 环境
1.1系统：CentOS 7.2# uname -a
Linux dg-p 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
1.2数据库：12.1.0.2.0 - 64bit Production
实例名：主---orcl_p；备---orcl_s；
1.3机器名：dg-p和dg-s；
1.4SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C1 MOUNTED
4 PDB12C2 MOUNTED
从上面可以看出有一个CDB两个PDB，根据自己库的情况来决定后面所需要创建的目录。
1.5host文件
# cat /etc/hosts
127.0.0.1 localhost test
192.168.70.42 dg-p
192.168.70.43 dg-s
1.6主备库的时间
需要将主备库的时间设置为一致，不然有可能导致同步上出现未知问题
查看时间(主备)
# date
如时间不一致，修改
# date -s 时间
2 主库前期工作
2.1 主库启动FORCE LOGGINGForced Logging ---主库设置强制日志，保证所有的操作都记录到日志文件；
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C1 READ WRITE
PDB12C2 READ WRITE
查看force logging是否启动
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
修改为强制日志(force logging)
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
2.2 启动归档模式
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
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 12
Next log sequence to archive 14
Current log sequence 14
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4560M
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest_size=1G;
System altered.
SQL> show parameter recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
SQL> alter database open;
Database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C1 MOUNTED
PDB12C2 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select name ,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB12C1 READ WRITE
PDB12C2 READ WRITE
2.3 主库添加 standby redo logfile
online redo log 和控制文件是保存在CDB中的，PDB中只有运行需要的数据文件；standby redo logfile个数一般是比现有日志组的数量+1；
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select group#, members, bytes from v$log;
GROUP# MEMBERS BYTES
---------- ---------- ----------
1 1 52428800
2 1 52428800
3 1 52428800
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
添加 4(3+1)个standby logfile
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo01.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo02.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo03.log' size 50M;
Database altered.
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stdredo04.log' size 50M;
Database altered.
再次验证是否添加
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/stdredo01.log
/u01/app/oracle/oradata/orcl/stdredo02.log
/u01/app/oracle/oradata/orcl/stdredo03.log
/u01/app/oracle/oradata/orcl/stdredo04.log
2.4 rman备份主库
注意进行rman备份，很多没有说明的是因为他们的库已经存在这个备份文件，但如果是刚建的没有执行过的话是没有的。
创建备份目录，加ho相当于执行系统命令
SQL>ho mkdir /home/oracle/dgback
或
# mkdir /home/oracle/dgback
进入rman模式，并备份数据库
# rman target /
RMAN> configure channel device type disk format '/home/oracle/dgback/%d_%I_%s_%p.bkp';
RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;
3 配置文件
listener.ora---监听配置文件和tnsname.ora---连接端配置文件；listener.ora：监听配置文件，这个文件里面是监听的主要（重要）配置内容，也是监听在服务器端的主文件，这个文件与sqlnet.ora的区别是，这个文件的基础参数是非常重要的，而且是直接关系到整个监听的。
tnsname.ora：连接端配置文件，主要内容是要访问的数据库的连接串的解释。也就是@后面的字符传的解释文件。这个主要出现的访问端（客户端），当然并不是说服务器端没有，因为服务器端也可以是访问端，比如服务器互相访问的dblink,以及服务器自己访问自己
3.1 主备库配置监听listener.ora
切换到监听的目录
$ cd /u01/app/oracle/product/12.1/db1/network/admin
修改为以下内容
$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1/db1/network/admin/listener.ora
# Generated by Oracle configuration tools.
listenner.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/ORACLE_BASE/product/12.1/db1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg-p)(PORT = 1521))---dg-p是主库的，备库的是dg-s
)
)
ADR_BASE_LISTENER = /u01/app/oracle
$ lsnrctl reload
3.2 主备库配置tnsnames.ora
切换到监听的目录
$ cd /u01/app/oracle/product/12.1/db1/network/admin
修改为以下内容
$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL_S = ---这个是使用slqplus访问的数据库名
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.43)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_s)
(UR=A) ---加上这个是因为后面报ORA-12528
)
)
ORCL_P =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.42)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl_p)
(UR=A)
)
)
需要重启监听和数据库
$ lsnrctl stop
$ lsnrctl start
$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup;
验证
$ tnsping orcl_p
$ tnsping orcl_s
$ sqlplus sys/oracle@orcl_p as sysdba
$ sqlplus sys/oracle@orcl_s as sysdba
注：sys/oracle是用户和密码；@orcle_p是tnsnames.ora文件的；
4 在备库创建必要的目录
根据各自的环境创建目录；$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl
$ mkdir -p /u01/app/oracle/admin/orcl/adump
$ mkdir -p /u01/app/oracle/oradata/orcl/pdbseed
$ mkdir -p /u01/app/oracle/oradata/orcl/pdb12c1
$ mkdir -p /u01/app/oracle/oradata/orcl/pdb12c2
5 在主库创建pfile 文件并修改pfile 内容
$ sqlplus / as sysdbaSQL> create pfile from spfile;
File created.
切换到pfile和spfile文件目录
$ cd /u01/app/oracle/product/12.1/db1/dbs
$ ls -l
-rw-rw---- 1 oracle oinstall 1544 Jul 27 17:42 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall 2992 Feb 3 2012 init.ora
-rw-r--r-- 1 oracle oinstall 1004 Jul 28 09:42 initorcl.ora
-rw-r----- 1 oracle oinstall 24 Jul 27 16:57 lkORCL
-rw-r----- 1 oracle oinstall 7680 Jul 27 17:09 orapworcl
-rw-r----- 1 oracle oinstall 17973248 Jul 27 18:02 snapcf_orcl.f
-rw-r----- 1 oracle oinstall 3584 Jul 27 23:04 spfileorcl.ora
这时可以看到生成了initorcl.ora文件；
在initorcl.ora(pfile)中修改内容以及添加内容
$ vi initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=171966464
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=260046848
orcl.__sga_target=494927872
orcl.__shared_io_pool_size=16777216
orcl.__shared_pool_size=289406976
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='dg-p' ---修改为主机的机器名
*.memory_target=720m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#下面的是添加进来的内容
#add for primary dg
*.db_name='orcl'
*.db_unique_name='orcl_p'
*.log_archive_config='dg_config=(orcl_p,orcl_s)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_p'
*.log_archive_dest_2='service=orcl_s valid_for=(online_logfiles,primary_role) db_unique_name=orcl_s'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_s'
如果主备库CDB名称不同，还需要加如下参数：
*.DB_FILE_NAME_CONVERT='orcl','dg-p'
*.LOG_FILE_NAME_CONVERT='orcl','dg-p'
用新参数重启数据库：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup;
ORACLE instance started.
Total System Global Area 754974720 bytes
Fixed Size 2928968 bytes
Variable Size 524291768 bytes
Database Buffers 222298112 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
查看数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
6 将主库的口令文件copy到备库
主备库的CDB实例相同，如果不同，可以使用orapwd命令重建。$ scp /u01/app/oracle/product/12.1/db1/dbs/orapworcl 192.168.70.43: /u01/app/oracle/product/12.1/db1/dbs
7 将主库的参数文件copy到备库并修改
$ scp /u01/app/oracle/product/12.1/db1/dbs/initorcl.ora 192.168.70.43: /u01/app/oracle/product/12.1/db1/dbs修改nitorcl.ora
$ vi initorcl.ora
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=171966464
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=260046848
orcl.__sga_target=494927872
orcl.__shared_io_pool_size=16777216
orcl.__shared_pool_size=289406976
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_pluggable_database=true
*.local_listener='dg-s'
*.memory_target=720m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#下面的是修改为备库的
#add for standby dg
*.db_unique_name='orcl_s'
*.log_archive_config='dg_config=(orcl_p,orcl_s)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl_s'
*.log_archive_dest_2='service=orcl_p valid_for=(online_logfiles,primary_role) db_unique_name=orcl_p'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='orcl_p'
注：控制文件的路径，因这里使用默认的就和主库的一样。
SQL> create spfile from pfile;
File created.
8 用spfile 将备库启动到nomount 状态
SQL> startup nomount;9 Active duplicate
$ rman target sys/oracle@orcl_p auxiliary sys/oracle@orcl_s;注：前面是主库，后面是备库。
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;
注：执行命令后，不管执行成功与否，但备库自动启动到mount状态了。
10 打开备库并并启动apply
10.1 备库启动SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB12C1 MOUNTED
4 PDB12C2 MOUNTED
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C1 MOUNTED
4 PDB12C2 MOUNTED
SQL> alter pluggable database pdb12c1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C1 READ ONLY NO
4 PDB12C2 MOUNTED
SQL> alter pluggable database pdb12c2 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C1 READ ONLY NO
4 PDB12C2 READ ONLY NO
SQL> select log_mode,open_mode ,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ ONLY PHYSICAL STANDBY
查看主库状态
SQL> select log_mode,open_mode ,database_role from v$database;
LOG_MODE OPEN_MODE DATABASE_ROLE
------------ -------------------- ----------------
ARCHIVELOG READ WRITE PRIMARY
备库启动real-time apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
11 验证DG
查看主库的状态SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB12C1 MOUNTED
4 PDB12C2 MOUNTED
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
在主库pdb12c1创建一个test_pdb1表
SQL> alter session set container=pdb12c1;
SQL> show con_name
CON_NAME
------------------------------
PDB12C1
查看数据库状态，这个时候数据库并不是出于open状态，启动到open状态
SQL> select status from v$instance;
SQL> alter database open;
查看是否有这个表
SQL>select count(*) from test_pdb1;
创建表
检验是否创建成功
SQL> select count(*) from test_pdb1;
COUNT(*)
----------
36
SQL> create table test_pdb1 as select * from dba_users;
Table created.
SQL> alter system switch logfile;
alter system switch logfile
ERROR at line 1:
ORA-65040: operation not allowed fromwithin a pluggable database
注意：这里可以看到，对于12c的库，日志的切换只能在CDB中进行，也就是我们前面讲的，DG 是在CDB 级别进行的。
SQL> alter session set container=CDB$ROOT;
SQL> alter system switch logfile;
System altered.
备库查询
SQL> select count(*) from test_pdb1;
select count(*) from test_pdb1
ERROR at line 1:
ORA-00942: table or view does not exist
提示表不存在，因主库是在pdb库创建的，所以备库也需要切换到pdb
SQL> alter session set container=pdb12c1;
SQL> show con_name
CON_NAME
------------------------------
PDB12C1
SQL>select count(*) from test_pdb1;
阅读全文
0 0
- 12c-单机Data Guard搭建--oracle DG
- Oracle DG -Data Guard 搭建
- Oracle 12c Data Guard 搭建手册
- Oracle 12C DG 搭建(RAC-RAC/RAC-单机）
- oracle data guard搭建
- DG(Data Guard)
- 单机搭建oracle dg 实验与总结
- Oracle Data Guard （RAC+DG) 归档删除策略及脚本
- Oracle Data Guard（RAC+DG)归档删除策略
- 虚拟机上安装Oracle 12c 单机到单机的DG
- 使用RMAN duplicate搭建12C的Data Guard环境
- [Data Guard实验]一步一步创建Oracle单机物理Standby
- Oracle RAC + Data Guard 环境搭建
- Oracle RAC + Data Guard 环境搭建
- Oracle RAC + Data Guard 环境搭建
- Oracle RAC + Data Guard 环境搭建
- Oracle RAC + Data Guard 环境搭建
- Oracle RAC + Data Guard 环境搭建
- 经常忘记的小本本
- Win10重命名虚拟桌面名称的操作方法！
- Unity自定义UI组件(八) 颜色拾取器(上)
- Codeforces Round #426 (Div. 2) C
- leetcode 76. Minimum Window Substring
- 12c-单机Data Guard搭建--oracle DG
- 哇，win8.1安装python3.6出现问题0x000007b
- Unity自定义UI组件(九) 颜色拾取器(下)
- [LeetCode]238. Product of Array Except Self
- 【总结】微信小程序
- C++基础之结构体
- Android进阶之路
- 嵌入式ARM处理器有哪些优势？
- CentOS 7 yum安装Zabbix
