oracle_dataGuard_11G - HelloWorld开发者社区

【李红】--切记_从库只安装实例_不需要 dbca 创建数据库但是 netca 创建监听看个人喜欢，我反正是创建了。
【DataGuard_主数据库的参数配置】
1.启用 force logging 功能
SQL> select force_logging from v$database; //查看数据库有没有开启此功能如果不是 YES 是 NO 的话执行下面一条语句。如果是的话，则不需要执行
SQL> alter database force logging;

2.启用归档
SQL> archive log list;
SQL> select log_mode from v$database;

归档有两个参数如下：
SQL> show parameter log;
文件名格式的参数：log_archive_format
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope = spfile; //修改默认文件参数：log_archive_format

位置：log_archive_dest
SQL> alter system set log_archive_dest_1='location=/u02/oracle/archive'; //设置归档文件路径

SQL> shutdown immediate; //修改配置需要重启数据库实例

SQL> startup mount; //启动到 mount 状态修改归档
SQL> alter database archivelog; //打开数据库的归档模式
SQL> alter database open; //启动数据库

3.设置主库监听
主库：
做静态监听 1522 端口
备库：
做静态监听 1522 端口

[oracle@DBprimary admin]$ pwd
/u01/app/oracle/product/11.2.0.4/db_1/network/admin

[oracle@DBprimary admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@DBprimary admin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1522))
)
)
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(SID_NAME = primary)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@primary ~]$ lsnrctl start listener1 //启动刚刚配置的静态监听

4.配置主库的初始化参数：
SQL> show parameter db_unique_name; //查看自己的服务名
SQL> alter system set db_unique_name = '自己要设置的服务名' scope=spfile; //修改之后需要重启数据库_根据需要，这一步可以不操作
SQL> alter system set log_archive_config='DG_CONFIG=(primary,standby)' scope=both;
SQL> show parameter log_archive_config; //查看设置的 DG_CONFIG
SQL> alter system set log_archive_dest_1='LOCATION=/u02/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=tnsstandby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=both;
SQL> quit;

[oracle@primary admin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@primary admin]$ ls
listener.ora samples shrept.lst tnsnames.ora

[oracle@primary admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

tnsstandby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

tnsprimary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

[oracle@primary admin]$ sqlplus / as sysdba
SQL> show parameter db_file;
db_file_name_convert //设置这个参数:数据文件

SQL> show parameter log;
log_file_name_convert //设置这个参数：重做日志文件

SQL> select name from v$datafile; //查看数据库的数据存放路径
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/primary' scope = spfile; //做对望在前的绑定地址
SQL> select * from v$logfile; //查看 redo 日志
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/standby','/u01/app/oracle/oradata/primary' scope = spfile; //做对望在前的绑定地址
SQL> show parameter standby;
SQL> alter system set standby_file_management = auto;
SQL> show parameter fal;
fal_client //这个参数可以不配置_如果要配置可以看下边的 alter
fal_server //这个参数可以不配置_如果要配置可以看下边的 alter

SQL> alter system set fal_client='tnsprimary' scope = both;
SQL> alter system set fal_server='tnsstandby' scope = both;

【DataGuard_备数据库的参数配置】
1.拷贝密码文件
主库和备库的 oracle 用户的 sys 密码必须一致。
在主库上操作：
[oracle@primary admin]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@primary dbs]$ scp orapwprimary root@备库IP:/u01/app/oracle/product/11.2.0/db_1/dbs

在备库上操作：
[oracle@standby dbs]$ ll
total 16
-rw-rw----. 1 oracle oinstall 1544 Mar 28 15:13 hc_standby.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Mar 15 14:53 lkDBSTANDB
-rw-r----- 1 root root 1536 Mar 29 13:27 orapwprimary

[oracle@standby dbs]$ mv orapwprimary orapwstandby

[oracle@standby dbs]$ ll
total 16
-rw-rw----. 1 oracle oinstall 1544 Mar 28 15:13 hc_standby.dat
-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle oinstall 24 Mar 15 14:53 lkDBSTANDB
-rw-r----- 1 root root 1536 Mar 29 13:27 orapwstandby

[oracle@standby dbs]$ orapwd file=orapwstandby password = //或者这样设置密码也可以
[oracle@standby dbs]$ touch initstandby.ora //创建一个启动文件，因为备库不需要 dbca 来创建数据库，所以指定给文件的形式

[oracle@primary dbs]$ strings spfileprimary.ora //在主库上操作查看这个文件

[oracle@standby dbs]$ vim initstandby.ora //编辑文件类容
db_name='primary'
db_unique_name='standby'
memory_target=1581252608
audit_file_dest='/u01/app/oracle/admin/standby/adump'
compatible='11.2.0.4.0'
control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/fast_recovery_area/standby/control02.ctl'
log_archive_config='DG_CONFIG=(primary,standby)'
log_archive_dest_1='LOCATION=/u02/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
log_archive_dest_2='SERVICE=tnsprimary LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
log_archive_format='%t_%s_%r.arc'
db_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby'
log_file_name_convert='/u01/app/oracle/oradata/primary','/u01/app/oracle/oradata/standby'
fal_client='tnsprimary'
fal_server='tnsstandby'
standby_file_management='AUTO'

[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/standby/adump //创建目录
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/standby
[oracle@standby ~]$ mkdir -p /u01/oracle/archive
[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/standby/

2.创建实例_启动到 mount 状态
[oracle@standby dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile; //创建启动文件

File created.

SQL> startup nomount; //启动到 nomount 状态
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 922750152 bytes
Database Buffers 654311424 bytes
Redo Buffers 7393280 bytes

SQL> show parameter spfile; //查看启动文件的路径地址_可以不看

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfilestandby.ora

SQL> show parameter log; //可以同过 log 日志进行查看_设置的参数

3.配置静态监听_和主库配置是一样的
重要：为什么一定要配置静态监听
nomount 状态下必须使用静态监听才能连接到实例

[oracle@Standby admin]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin/ //进入该目录
[oracle@Standby admin]$ vim listener.ora //编辑文件类容
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1522))
)
)
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(SID_NAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@Standby admin]$ lsnrctl start //启动监听
[oracle@Standby admin]$ lsnrctl start listener1 //启动静态监听

[oracle@Standby admin]$ sqlplus / as sysdba //进入数据库

SQL> select status from v$instance; //查看数据库处于什么样的状态

STATUS
------------
STARTED

4.tns配置
先在主库上操作_如下：
[oracle@Primary admin]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@Primary admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

tnsstandby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

tnsprimary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

后再从库上操作_如下：
[oracle@Standby admin]$ cd /u01/app/oracle/product/11.2.0.4/db_1/network/admin
[oracle@Standby admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby)
)
)

tnsstandby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

tnsprimary =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primary)
)
)

5.测试 tns
主库_从库都做以下操作，通过 OK 才能行
tnsping tnsprimary
tnsping tnsstandby

6.测试连接数据库
主库_从库都做以下操作，通过 OK 才能行在这里我就不写备库的操作了！
[oracle@Primary admin]$ sqlplus sys/oracle@tnsprimary as sysdba //主库上操作

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 30 12:05:05 2019

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>

[oracle@Primary admin]$ sqlplus sys/oracle@tnsstandby as sysdba //主库上操作

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 30 12:06:44 2019

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>

【DataGuard主备库参数检查】
[oracle@Primary admin]$ sqlplus / as sysdba //主库上操作
SQL> show parameter db_unique_name; //检查 db_unique_name 是否一致
SQL> show parameter compa; //查看版本信息是否一致
SQL> show parameter log_archive_config; //查看 DG_config
SQL> show parameter log_archive_dest_1; //查看归档路径_指定详细信息
SQL> show parameter log_archive_dest_2; //查看归档路径_指定详细信息
SQL> show parameter db_file; //查看数据文件存放路径
SQL> show parameter log_file_name_convert; //查看数据LOG存放路径
SQL> show parameter log_archive_format;

[oracle@Standby admin]$ sqlplus / as sysdba //从库上操作
SQL> show parameter db_unique_name; //检查 db_unique_name 是否一致
SQL> show parameter compa; //查看版本信息是否一致
SQL> show parameter log_archive_config; //查看 DG_config
SQL> show parameter log_archive_dest_1; //查看归档路径_指定详细信息
SQL> show parameter log_archive_dest_2; //查看归档路径_指定详细信息
SQL> show parameter db_file; //查看数据文件存放路径
SQL> show parameter log_file_name_convert; //查看数据LOG存放路径
SQL> show parameter log_archive_format;

【从库操作】
[oracle@Standby ~]$ cd $ORACLE_HOME/dbs //发现从库的 db_unique_name 是主库的 db_unique_name 需要更改
[oracle@Standby dbs]$ vim initstandby.ora //编辑文件类容往这个文件里面插入一条新数据——如下：
db_unique_name='standby' //原有的文件内容不变，只是插入一条新的内容进去

[oracle@Standby dbs]$ sqlplus / as sysdba //进入数据库
SQL> shutdown immediate; //关闭数据库
SQL> create spfile from pfile; //重新创建启动文件_因为你修改配置了，需要重新创建一个启动文件
SQL> startup nomount; //启动到 nomount 状态下

【使用 Duplicate 创建物理 standby】备注：主库上操作
[oracle@Primary primary]$ rman target sys/oracle@tnsprimary auxiliary sys/oracle@tnsstandby //这是第一种连接方式_下边的是第二种方式
RMAN> duplicate target database for standby from active database nofilenamecheck;

[oracle@Primary admin]$ rman target sys/oracle@tnsprimary //创建
RMAN> connect auxiliary sys/oracle@tnsstandby //连接辅助实例备库就是辅助实例
RMAN> duplicate target database for standby from active database nofilenamecheck; //主备连接实例

到这里基本 oracle 主从结束了！
小伙伴们动起手来吧！