redhat 6.4 oracle 11g dataguard 搭建（一）

最新推荐文章于 2021-03-12 09:54:11 发布

hzcyhujw

最新推荐文章于 2021-03-12 09:54:11 发布

阅读量265

点赞数

分类专栏： dataguard 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/hzcyhujw/article/details/78921876

版权

dataguard 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

概述：了解oracle先从安装开始，在日常工作中很大一部分也将会是从安装开始；在oracle中的高可用中有RAC（能够做到实例级别的高可用），而dataguard能够做到操作系统、硬件层面的高可用；当然oracle goldengate也是可以实现并还支持异构跨数据库厂商等优势；本次我们将测试dataguard的安装，dataguard的安装有很多种方法，本人列举三种方法：1、使用冷备搭建；2、使用传统的rman搭建，3、使用rman中的duplicate 复制进行；本文使用冷备搭建，后续测试使用2、3的方法进行；

实验简单步骤：

1、主库安装oracle数据库软件并且dbca监控

2、备库安装oracle数据库软件

3、主库归档模式开启

4、主库修改dataguard 数据库参数

5、主备库监听配置

6、主库关库开始备份数据库文件

7、主库mount状态生产standby control file

8、将备份文件、standby control file、参数文件、密码文件拷贝至备库

9、主库open，备库mount开始应用日志

10、查看dataguard状态，测试dataguard是否搭建成功

实验：

主库数据状态查看：

SQL> select INSTANCE_NAME ,STATUS from v$instance;

INSTANCE_NAME STATUS
-------------------------------- ------------------------
CUBE OPEN

SQL> !hostname
cube

备库确定数据库软件已经安装完成：

[root@jakki db_1]# ls
apex csmig dv j2ee md opmn precomp sqlplus xdk
assistants css emcli javavm mesg oracore racg srvm
bin ctx EMStage jdbc mgw oraInst.loc rdbms sysman
ccr cv has jdev network ord relnotes timingframework
cdata dbs hs jdk nls oui root.sh ucp
cfgtoollogs dc_ocm ide jlib oc4j owb scheduler uix
clone deinstall install ldap odbc owm slax usm
config demo instantclient lib olap perl sqldeveloper utl
crs diagnostics inventory log OPatch plsql sqlj wwg
[root@jakki db_1]# pwd
/u01/app/oracle/product/11.2.0/db_1

主库dataguard配置设置：

归档开启

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 16
Current log sequence 18

SQL> show parameter recover

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 524291112 bytes
Database Buffers 310378496 bytes
Redo Buffers 2355200 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

开启强制生成日志模式：

SQL> alter database force logging;
Database altered.

修改dataguard参数
SQL> create pfile from spfile;
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[root@cube ~]# cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[root@cube dbs]# pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[root@cube dbs]# ls
hc_CUBE.dat initCUBE.ora init.ora lkCUBE orapwCUBE spfileCUBE.ora
[root@cube dbs]# vi initCUBE.ora ---添加dataguard配置参数

DB_UNIQUE_NAME=CUBE
LOG_ARCHIVE_CONFIG='DG_CONFIG=(CUBE,JAKKI1)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/fast_recovery_area
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=CUBE'
LOG_ARCHIVE_DEST_2=
'SERVICE=JAKKI1 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=JAKKI1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=JAKKI1
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/JAKKI1/','/u01/app/oracle/oradata/CUBE/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/JAKKI1/','/u01/app/oracle/oradata/CUBE/'
STANDBY_FILE_MANAGEMENT=AUTO

SQL> create spfile from pfile;
File created.

SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.
Database opened.

关闭主库，备份数据库文件；

SQL> col file_name for a60
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- ------------------------------------------------------------
4 /u01/app/oracle/oradata/CUBE/users01.dbf
3 /u01/app/oracle/oradata/CUBE/undotbs01.dbf
2 /u01/app/oracle/oradata/CUBE/sysaux01.dbf
1 /u01/app/oracle/oradata/CUBE/system01.dbf
5 /u01/app/oracle/oradata/CUBE/example01.dbf
6 /u01/app/oracle/oradata/CUBE/cube1_1.dbf
6 rows selected.

[root@cube CUBE]# su - oracle
[oracle@cube ~]$ cd /u01/app/oracle/oradata/CUBE/
[oracle@cube CUBE]$ ls
control01.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
cube1_1.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@cube CUBE]$ tar cvfz /home/oracle/cube.tar.gz *.dbf
cube1_1.dbf
example01.dbf

主库启动到mount状态生产standby control file：

SQL> startup mount;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
Database mounted.

SQL> alter database create standby controlfile as '/home/oracle/stand.ctl';
Database altered.

主备库监听设置：主备库都设置为静态监听

主库监听文件内容：

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=CUBE)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=CUBE))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM=extproc)))

ADR_BASE_LISTENER = /u01/app/oracle

开启监听 lsnrctl start

查看监听状态：

[oracle@cube admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-DEC-2017 16:01:02

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-DEC-2017 15:59:47
Uptime 0 days 0 hr. 1 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/cube/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "CUBE" has 2 instance(s).
Instance "CUBE", status UNKNOWN, has 1 handler(s) for this service...
Instance "CUBE", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[root@cube admin]# vi tnsnames.ora
CUBE=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.66)(PORT=1521))
(CONNECT_DATA=
(SERVER=dedicate)
(SERVICE_NAME=CUBE)))
JAKKI1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521))
(CONNECT_DATA=
(SERVER=dedicate)
(SERVICE_NAME=JAKKI1)))

备库监听配置：

[root@jakki ~]# su - oracle
[oracle@jakki ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@jakki admin]$ ls
samples shrept.lst
[oracle@jakki admin]$ vi listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=JAKKI1)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=JAKKI1))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(PROGRAM=extproc)))

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@jakki admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 28-DEC-2017 16:02:45

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/jakki/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 28-DEC-2017 16:02:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/jakki/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.68)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "JAKKI1" has 1 instance(s).
Instance "JAKKI1", status UNKNOWN, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

将cube.tar.gz，stand.ctl,initCUBE.ora 文件传送至备库

[oracle@cube ~]$ scp cube.tar.gz 192.168.1.68:/home/oracle/
The authenticity of host '192.168.1.68 (192.168.1.68)' can't be established.
RSA key fingerprint is 43:9d:ea:4c:e1:8e:f6:f0:6f:95:e9:17:6e:8d:55:be.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.68' (RSA) to the list of known hosts.
oracle@192.168.1.68's password:
Permission denied, please try again.
oracle@192.168.1.68's password:
cube.tar.gz 100% 335MB 41.8MB/s 00:08
[oracle@cube ~]$ scp stand.ctl 192.168.1.68:/home/oracle/
oracle@192.168.1.68's password:
stand.ctl 100% 9520KB 9.3MB/s 00:00
[oracle@cube ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@cube dbs]$ scp orapwCUBE 192.168.1.68:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwJAKKI1
oracle@192.168.1.68's password:
orapwCUBE 100% 1536 1.5KB/s 00:00
[oracle@cube dbs]$ scp initCUBE.ora 192.168.1.68:/u01/app/oracle/product/11.2.0/db_1/dbs/initJAKKI1.ora
oracle@192.168.1.68's password:
initCUBE.ora 100% 1238 1.2KB/s 00:00
[oracle@cube dbs]$

备库dataguard配置

备库参数文件修改使得数据库能够启动到nomount状态：

[oracle@jakki dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@jakki dbs]$ vi initJAKKI1.ora
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/JAKKI1/control01.ctl','/u01/app/oracle/fast_recovery_area/JAKKI1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='CUBE'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=JAKKI1XDB)'
*.job_queue_processes=1000
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

DB_UNIQUE_NAME=JAKKI1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(JAKKI1,CUBE)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/fast_recovery_area
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=JAKKI1'
LOG_ARCHIVE_DEST_2=
'SERVICE=CUBE ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=CUBE'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

[oracle@jakki dbs]$ mkdir -p /u01/app/oracle/oradata/JAKKI1/
[oracle@jakki dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area
[oracle@jakki dbs]$ mkdir -p /u01/app/oracle/admin/JAKKI1/adump

[oracle@jakki dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 16:44:33 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile ;
File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
SQL>

将cube.tar.gz 解压到相应文件夹中

[oracle@jakki ~]$ tar -xvf cube.tar.gz -C /u01/app/oracle/oradata/JAKKI1/

将stand.ctl 拷贝至对用的文件夹中

[oracle@jakki ~]$ cp stand.ctl /u01/app/oracle/oradata/JAKKI1/control01.ctl
[oracle@jakki ~]$ cp stand.ctl /u01/app/oracle/fast_recovery_area/JAKKI1/control02.ctl

备库启动至mount状态：

SQL> alter database mount;
Database altered.

主库open

SQL> alter database open;

Database altered.

备库开open并开启日志接收：

SQL> recover managed standby database disconnect;
Media recovery complete.

SQL> select open_Mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY WITH APPLY PHYSICAL STANDBY

测试：在主库中cube用户下新建表，切换日志后，查看备库是否有数据更新；

主库：
SQL> create table cube.t2 as select * from scott.dept;
Table created.

备库：

SQL> select * from cube.t2;
select * from cube.t2
*
ERROR at line 1:
ORA-00942: table or view does not exist

主库：

SQL> alter system switch logfile;

System altered.

备库

SQL> select * from cube.t2;

DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

查到数据 ok 完成了

总结：使用冷备份搭建DG完成，各位自行测试吧。明后天会出其他两种方法搭建方法。谢谢