CentOS6.9_X86_64 Oracle 11g r2 DataGuard安装配置

最新推荐文章于 2021-04-06 16:40:09 发布

原创最新推荐文章于 2021-04-06 16:40:09 发布 · 647 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了如何在Oracle 11gR2环境下配置DataGuard，包括安装环境设置、监听器配置、数据库创建、DataGuard配置步骤等关键信息。

Oracle 11g r2 DataGuard安装配置

安装环境

1. ORACLE服务器硬件配置

vmware +oracle 11g r2 11.2.0.4

内存2G，硬盘50G

IP地址&机器名:

192.168.100.100 tjzwdb01 #primary

192.168.100.101 tjzwdb02 #standby

2. Oracle服务器分区

-------------------

分区 SIZE

/boot 200M #主分区

swap 4G #扩展分区

/ 剩下所有空间 #扩展分区

-------------------

3. RPM安装包选择

#安装包

| Desktop Environments

|--- GNOME Desktop Environment #all

|Applications

|---Editors #vi

|---Text-based Internet #all

|Development

|---Development Libraries #all

|---Development Tools #all java nocheck

|---GNOME Software Development #all

|---Legacy Software Development #all

|---X Software Development #all

|Servers

|---Legacy Network Server #all

|---Server Configuration Tools #all

|Base System

|---Administration Tools #all

|---Base #all

|---Legacy Software Support #all

|---System Tools #all

|---X Window System #all

|Languages

|---Chinese Support #

4. 配置图形界面环境（Xmanager）

a. 启动本地X服务器

安装Xmanager以后, 开始菜单 -> 程序 -> Xmanager3 -> Xmanager – Passive

b. 服务器端配置: /etc/ssh/sshd_config

确保有这行: X11Forwarding yes

c. root执行 #export DISPLAY=192.168.100.1:0.0 #本地客户端的IP地址

d. root执行 #xhost +

e. root执行 #xclock #本地应该有图形界面的钟出现

5. rlwrap安装（可选）

安装前准备

#检查OS

[root@tjzwdb01 ~]# grep MemTotal /proc/meminfo

MemTotal: 2038352 kB

[root@tjzwdb01 ~]# grep "model name" /proc/cpuinfo

model name: Intel(R) Core(TM) i7-4500U CPU @ 1.80GHz

[root@tjzwdb01 ~]# grep SwapTotal /proc/meminfo

SwapTotal: 4194300 kB

[root@tjzwdb01 ~]# free

total used free shared buffers cached
Mem: 2038352 292876 1745476 1072 24388 111152
-/+ buffers/cache: 157336 1881016
Swap: 4194300 0 4194300

[root@tjzwdb01 ~]# df -h /dev/shm/

Filesystem Size Used Avail Use% Mounted on

tmpfs 4.0G 72K 4.0G 1% /dev/shm

[root@tjzwdb01 ~]# uname -a
Linux tjzwdb01 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

[root@tjzwdb01 ~]# df -h /tmp

Filesystem Size Used Avail Use% Mounted on
/dev/sda3 45G 18G 26G 42% /

[root@tjzwdb01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 45G 18G 26G 42% /
tmpfs 4.0G 72K 4.0G 1% /dev/shm
/dev/sda1 190M 40M 141M 22% /boot

[root@tjzwdb01 ~]# cat /proc/version
Linux version 2.6.32-696.el6.x86_64 (mockbuild@c1bm.rdu2.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) ) #1 SMP Tue Mar 21 19:29:05 UTC 2017

[root@tjzwdb01 ~]# cat /etc/issue
CentOS release 6.9 (Final)
Kernel \r on an \m

#检查RPM包 #安装缺少的RPM包

mount iso 修改源安装本地源

yuminstall -y binutils-* compat-db* compat-db-* control-center* gcc-* gcc-c++-*glibc-* glibc-common-* gnome-libs-* make-* pdksh* sysstat-* xscreensaver-*make-* gdb-6.1post-* glibc-* glibc-common-* glibc-devel-* glibc-devel-*compat-gcc-* compat-gcc-c++-* compat-libstdc++-* compat-libstdc++-devel-*gnome-libs-* libstdc++-* libstdc++-devel-* openmotif-* sysstat-* setarch-*libaio-* libaio-devel-* libXp* libXp-devel* elfutils-libelf-devel* unixODBC-*unixODBC-devel-* compat-libcap1.x86_64 libcap.so.1 sg3_utils

yuminstall -y compat-libstdc++-33-3.2.3-69.el6.i686g libc-devel-2.12-1.132.el6.i686 libaio-devel-0.3.107-10.el6.i686 libstdc++-4.4.7-4.el6.i686 unixODBC-2.2.14-12.el6_3.i686 unixODBC-devel-2.2.14-12.el6_3.i686 libaio-0.3.107-10.el6.i686

#创建oracle用户

[root@tjzwdb01 ~]# groupadd oinstall

[root@tjzwdb01 ~]# groupadd dba

[root@tjzwdb01 ~]# useradd -g oinstall -G dba oracle

[root@tjzwdb01 ~]# passwd oracle

Changing password for user oracle.

New UNIX password:

Retype new UNIX password:

passwd: all authentication tokens updated successfully.

#修改sysctl.conf

[root@tjzwdb01 ~]# vi /etc/sysctl.conf

#for oracle

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

[root@tjzwdb01 ~]# sysctl -p

[root@tjzwdb01 ~]# vi /etc/security/limits.conf

#for oracle

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle hard stack 10240

#创建目录

# mkdir -p /u01/app/
# chown -R oracle:oinstall /u01/app/
# chmod -R 775 /u01/app/

# mkdir -p /rman/arch

# chown -R oracle:oinstall /rman/

# chmod -R 775 /rman/

#设置环境变量

# su - oracle

$ vi .bash_profile

# For Oracle
export DISPLAY=:0.0
export TMP=/tmp;
export TMPDIR=$TMP;
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_SID=tjzwdb;
export ORACLE_TERM=xterm;
export PATH=/usr/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
exportCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

NLS_LANG=american_america.zhs16gbk;export NLS_LANG

#配置hosts

# vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.100 tjzwdb01
192.168.100.101 tjzwdb02

# echo "redhat-release-5Server-5" > /tmp/.linux_release

# chattr +i /tmp/.linux_release

注：tjzwdb01、tjzwdb02都按照以上步骤执行

开始安装 oracle11gr2 软件

在客户端打开：Xmanager – Passive

Oracle数据库服务器执行以下命令：

# export DISPLAY=192.168.100.1:0.0

[root@platformdb01 ~]# xhost +

access control disabled, clients can connect from any host

[oracle@platformdb01 ~]$ unzip linux.x64_11gR2_database_1of2.zip

$ unzip linux.x64_11gR2_database_2of2.zip

$ cd database/

$ ls

doc install response rpm runInstaller sshsetup stage welcome.html

$ ./runInstaller

注：tail -f /tmp/OraInstall2010-08-24_03-25-30PM/installActions2010-08-24_03-25-30PM.log查看oracle检查安装的log

去掉对号，点击“Next”

点击“Yes”

选择“Install database software only”，点击“Next”

点击“Next”

选择“English”和“Simplified Chinese”，点击“Next”

点击“Next”

选择“Ignore All”，点击“Next”

可以选择保存响应文件

点击“Save”

点击“Finish”

注：可以通过tail -f /u01/oraInventory/logs/installActions2010-08-24_03-25-30PM.log查看oracle安装的log

使用root用户按顺序执行2个脚本，然后点击“OK”

# /u01/oraInventory/orainstRoot.sh

Changing permissions of /u01/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /u01/oraInventory to oinstall.

The execution of the script is complete.

# /u01/app/oracle/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

点击“Close”，完成oracle软件的安装。

注：tjzwdbdb01、tjzwdbdb02都按照以上步骤执行

安装后配置监听、创建oracle数据库（只在tjzwdb01执行）

a. 创建监听

$ netca

点击“Next”

点击“Finish”，完成listener的配置。

注：可以通过lsnrctl status查看监听的状态

[oracle@platformdb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-FEB-2018 15:22:09
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tjzwdb01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 03-FEB-2018 15:21:18
Uptime 0 days 0 hr. 0 min. 51 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/tjzwdb01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tjzwdb01)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

修改listener.ora文件

[oracle@tjzwdb01 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

$ vi listener.ora

-------------------------------------------------

# listener.ora Network Configuration File: /u01/oracle/oracle11g/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = tjzwdb)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = tjzwdb)

)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

-------------------------------------------------

重新启动监听

[oracle@platformdb01 admin]$ lsnrctl stop

[oracle@platformdb01 admin]$ lsnrctl start

b. 创建oracle数据库

[oracle@platformdb01 ~]$ dbca

点击“Next”

输入“Global Database Name”和“SID”，点击“Next”

点击“Next”

输入密码，点击“Next”

选择“Use Common Location for All Database Files”，输入“{ORACLE_BASE}/oradata”点击“Next”

选择“Enable Archiving”，点击“Edit Archive Mode Parameters”

输入“/rman/arch”，点击“OK”，然后点击“Next”

点击“Next”

可以按照服务器配置设置SGA和PGA大小（本文是内存为1000MB的配置），点击“Sizing”

可以根据需要设置“Processes”大小，点击“Character Sets”

选择“ZBK16”，点击“Next”

点击“Next”

选择“Save as a Database Template”和“Generate Database Creation Scripts”，点击“Finish”

点击“OK”

注：可以使用tail -f /u01/app/oracle/cfgtoollogs/dbca/jmcentr/trace.log查看创建数据库log。

点击“Exit”，完成oracle数据库的创建。

备份emkey.ora文件

$ cd /u01/app/oracle/product/11.2.0/db_1/tjzwdb01_tjzwdb/sysman/config/

$ cp emkey.ora emkey.ora.bak

配置 Dataguard

a. 修改tjzwdb01的tnsnames.ora文件

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

$ vi tnsnames.ora

----------------------------------------

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

TJZWDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

----------------------------------------

b. 拷贝tjzwdb01的listener.ora、tnsnames.ora及密码文件orapwtjzwdb到tjzwdb02，并修改listener.ora、tnsnames.ora文件

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

scp listener.ora tjzwdb02:/u01/app/oracle/product/11.2.0/db_1/network/admin/

scp tnsnames.ora tjzwdb02:/u01/app/oracle/product/11.2.0/db_1/network/admin/

$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

scp orapwtjzwdb tjzwdb02:/u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@tjzwdb02 admin]$ vi listener.ora

---------------------------------------

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb02 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = /u01/app/oracle

----------------------------------------

[oracle@tjzwdb02 admin]$ vi tnsnames.ora

-----------------------------------------

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

PD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

TJZWDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tjzwdb02 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = tjzwdb)
)
)

-----------------------------------------

c. 启动tjzwdb02的监听

[oracle@tjzwdb02 admin]$ lsnrctl start

d. 创建oracle目录

# tjzwdb01

[oracle@platformdb01 jmcentr]$ mkdir -p /u01/app/oracle/oradata/tjzwdb --有就不创建

#tjzwdb02

$ mkdir -p /u01/app/oracle/oradata/tjzwdb

$mkdir -p /u01/app/oracle/fast_recovery_area/tjzwdb

$ mkdir -p /u01/app/oracle/fast_recovery_area/TJZWDB/onlinelog

$ mkdir -p /u01/app/oracle/admin/tjzwdb/adump

$ mkdir -p /u01/app/oracle/admin/tjzwdb/dpdump

$ mkdir -p /u01/app/oracle/admin/tjzwdb/pfile

$ mkdir -p /u01/app/oracle/admin/tjzwdb/scripts

创建备库日志文件

SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024

---------- ---------------

1 50

2 50

3 50

SQL>

通过下面的语句可以查询备库Standby日志的大小和组数：

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

创建standby logfile

SQL> alter database add standby logfile group 11 '/u01/app/oracle/oradata/redo11_stb01.log'size 50M;

Database altered.

SQL> alter database add standby logfile group 12 '/u01/app/oracle/oradata/redo12_stb01.log'size 50M;

Database altered.

SQL> alter database add standby logfile group 13 '/u01/app/oracle/oradata/redo13_stb01.log'size 50M;

Database altered.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024

---------- ---------------

11 50

12 50

13 50

e. 配置DataGuard

e.1. 配置primary（ tjzwdb01）数据库的参数

[oracle@tjzwdb01 /]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 3 16:24:18 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
???:
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>

SQL> alter database force logging;

Database altered.

SQL> create pfile from spfile;

File created.

[oracle@tjzwdb01 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs

[oracle@tjzwdb01 dbs]$ cp inittjzwdb.ora inittjzwdb.ora.bak

[oracle@tjzwdb01 dbs]$ cat inittjzwdb.ora
tjzwdb.__db_cache_size=444596224
tjzwdb.__java_pool_size=4194304
tjzwdb.__large_pool_size=8388608
tjzwdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tjzwdb.__pga_aggregate_target=432013312
tjzwdb.__sga_target=641728512
tjzwdb.__shared_io_pool_size=0
tjzwdb.__shared_pool_size=167772160
tjzwdb.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/tjzwdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/tjzwdb/control01.ctl','/u01/app/oracle/fast_recovery_area/tjzwdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='tjzwdb'
*.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=tjzwdbXDB)'
#*.log_archive_dest_1='LOCATION=/rman/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#oracle_dg
*.db_unique_name=PD
*.log_archive_config='DG_CONFIG=(PD,STD)'
*.log_archive_dest_1='LOCATION=/rman/arch noreopen optional VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PD'
*.log_archive_dest_2='SERVICE=STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STD ARCH SYNC REOPEN=10'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.FAL_SERVER='STD'
*.FAL_CLIENT='PD'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.log_file_name_convert='/u01/app/oracle/oradata/tjzwdb/','/u01/app/oracle/oradata/tjzwdb/'

-------------------------------------------

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>CREATE spfile from pfile;

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.

e.2. 拷贝参数文件到standby （tjzwdb02）

[oracle@tjzwdb01 dbs]$ scp inittjzwdb.ora tjzwdb02:/u01/app/oracle/product/11.2.0/db_1/dbs/

e.3. 修改standby（tjzwdb02）参数文件

[oracle@tjzwdb02 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/

[oracle@tjzwdb02 dbs]$ vi inittjzwdb.ora

-----------------------------------------

tjzwdb.__db_cache_size=444596224
tjzwdb.__java_pool_size=4194304
tjzwdb.__large_pool_size=8388608
tjzwdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
tjzwdb.__pga_aggregate_target=432013312
tjzwdb.__sga_target=641728512
tjzwdb.__shared_io_pool_size=0
tjzwdb.__shared_pool_size=167772160
tjzwdb.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/tjzwdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/tjzwdb/control01.ctl','/u01/app/oracle/fast_recovery_area/tjzwdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='tjzwdb'
*.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=tjzwdbXDB)'
#*.log_archive_dest_1='LOCATION=/rman/arch'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1073741824
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#oracle_dg
*.db_unique_name=STD
*.log_archive_config='DG_CONFIG=(PD,STD)'
*.log_archive_dest_1='LOCATION=/rman/arch noreopen optional VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STD'
*.log_archive_dest_2='SERVICE=PD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PD ARCH SYNC REOPEN=10'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.FAL_SERVER='PD'
*.FAL_CLIENT='STD'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.log_file_name_convert='/u01/app/oracle/oradata/tjzwdb/','/u01/app/oracle/oradata/tjzwdb/'

-----------------------------------------

e.4 standby（tjzwdb02）启动到nomount状态

[oracle@tjzwdb02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 3 16:54:45 2018

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inittjzwdb.ora';

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 671089544 bytes
Database Buffers 390070272 bytes
Redo Buffers 5517312 bytes
SQL>

e.5 备份primary主库（tjzwdb01上）

[oracle@tjzwdb01 dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 3 16:56:55 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: TJZWDB (DBID=1923415946)

RMAN> run {

allocate channel d1 device type disk;
backup as compressed backupset
incremental level=0 format='/rman/inc0_%d_%T_%s_%p'
tag='inc0' channel=d1 database;
sql "alter system archive log current";
backup as compressed backupset
format='/rman/arch_%d_%T_%s_%p'
tag='arch' channel=d1 archivelog all delete input;
backup as compressed backupset
format='/rman/ctl_%d_%T_%s_%p'
tag='ctl' channel=d1 current controlfile for standby reuse;
}

e.6 拷贝primary（tjzwdb01）备份文件到standby（tjzwdb02）

Recovery Manager complete.
[oracle@tjzwdb01 dbs]$ cd /rman
[oracle@tjzwdb01 rman]$ ls
arch arch_TJZWDB_20180203_3_1 ctl_TJZWDB_20180203_4_1 inc0_TJZWDB_20180203_1_1 inc0_TJZWDB_20180203_2_1

[oracle@tjzwdb01 rman]$ scp inc0_TJZWDB_20180203_* tjzwdb02:/rman/

[oracle@tjzwdb01 rman]$ scp arch_TJZWDB_20180203_* tjzwdb02:/rman/

[oracle@tjzwdb01 rman]$ scp ctl_TJZWDB_20180203_* tjzwdb02:/rman/

e.7 duplicate primary数据库

RMAN> connect auxiliary sys/orcl@STD

connected to auxiliary database: TJZWDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

e.8 恢复standby数据库（tjzwdb02）

SQL> recover managed standby database disconnect from session;

Media recovery complete.

e.9 standby数据库启动到open read only状态

SQL> recover managed standby database cancel;--取消实时同步

Media recovery complete.

SQL>shutdown immediate;

SQL> startup nomount

SQL>alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.

e.10 测试DataGuard

# primary数据库（tjzwdbdb01）

SQL> alter system archive log current;

System altered.

# tjzwdb02

通过命令查看是否有归档日志恢复

[oracle@tjzwdb02 ~]$ tail -f /u01/app/oracle/diag/rdbms/std/tjzwdb/trace/alert_tjzwdb.log

重启primary和standby数据库

#shutdown

#primary （tjzwdb01）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

#standby （tjzwdb02）

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

#startup

#primary （tjzwdb01）

SQL> startup

ORACLE instance started.

Total System Global Area 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 343934848 bytes

Database Buffers 75497472 bytes

Redo Buffers 4251648 bytes

Database mounted.

Database opened.

#standby （tjzwdb02）

SQL> startup nomount

ORACLE instance started.

Total System Global Area 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 343934848 bytes

Database Buffers 75497472 bytes

Redo Buffers 4251648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

####################################################################

#测试DataGuard数据

#primary

SQL> create table dg_test(sno number,sname varchar(20));

Table created.

SQL> insert into dg_test values(1,'DataGuard');

1 row created.

SQL> insert into dg_test values(2,'primary');

1 row created.

SQL> insert into dg_test values(3,'standby');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

#standby

SQL> select * from dg_test;

SNO SNAME

---------- --------------------

1 DataGuard

2 primary

3 standby

####################################################################

#switchover

a. Primary操作

#停止企业管理器

[oracle@platformdb01 arch]$ emctl stop dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

https://platformdb01:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...

... Stopped.

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE PRIMARY TO STANDBY

注：switchover_status 应为TO STANDBY ，如果不为此状态，就archive log

SQL> select count(*) from v$session where username is not null;

COUNT(*)

----------

注：只有自己

#切换primary为standby

SQL> alter database commit to switchover to physical standby;

Database altered.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 369100672 bytes

Database Buffers 50331648 bytes

Redo Buffers 4251648 bytes

SQL> alter database mount standby database;

Database altered.

b. Standby操作

#切换standby为primary

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ ONLY PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary;

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 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 348129152 bytes

Database Buffers 71303168 bytes

Redo Buffers 4251648 bytes

Database mounted.

Database opened.

c. Primary操作

#primary恢复数据，并且open read only

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

-------------------- -------------------- ----------------

MOUNTED RECOVERY NEEDED PHYSICAL STANDBY

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

-------------------- -------------------- ----------------

MOUNTED NOT ALLOWED PHYSICAL STANDBY

SQL> alter database open read only;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

###########################################################

#failover

#standby操作

a. standby切换为primary

SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;

no rows selected

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> recover managed standby database finish;

Media recovery complete.

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

-------------------- -------------------- ----------------

READ ONLY TO PRIMARY PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

b. 修改参数文件

[oracle@tjzwdb01 dbs]$ vi inittjzwdb.ora

----------------------------------------------

jmcentr.__db_cache_size=75497472

jmcentr.__java_pool_size=4194304

jmcentr.__large_pool_size=4194304

jmcentr.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment

jmcentr.__pga_aggregate_target=163577856

jmcentr.__sga_target=264241152

jmcentr.__shared_io_pool_size=0

jmcentr.__shared_pool_size=167772160

jmcentr.__streams_pool_size=4194304

*.audit_file_dest='/u01/oracle/admin/jmcentr/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/oracle/oradata/jmcentr/control01.ctl','/u01/oracle/oradata/jmcentr/control02.ctl','/u01/oracle/oradata/jmcentr/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='jmcentr'

*.db_recovery_file_dest='/u01/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=jmcentrXDB)'

*.log_archive_dest_1='LOCATION=/rman/arch'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=427819008

*.open_cursors=300

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.undo_tablespace='UNDOTBS1'

#*.db_unique_name=PD

#*.log_archive_config='DG_CONFIG=(PD,STD)'

#*.log_archive_dest_1='LOCATION=/rman/arch noreopen optional VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PD'

#*.log_archive_dest_2='SERVICE=STD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STD LGWR SYNC REOPEN=10'

*.log_archive_dest_state_1=ENABLE

*.log_archive_dest_state_2=ENABLE

#*.FAL_SERVER='STD'

#*.FAL_CLIENT='PD'

#*.STANDBY_FILE_MANAGEMENT='AUTO'

#*.log_file_name_convert='/u01/app/oracle/oradata/tjzwdb/','/u01/app/oracle/oradata/tjzwdb/'

----------------------------------------------

c. 启动数据库

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/inittjzwdb.ora'

ORACLE instance started.

Total System Global Area 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 343934848 bytes

Database Buffers 75497472 bytes

Redo Buffers 4251648 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 425897984 bytes

Fixed Size 2214016 bytes

Variable Size 343934848 bytes

Database Buffers 75497472 bytes

Redo Buffers 4251648 bytes

Database mounted.

Database opened.

SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE

-------------------- -------------------- ----------------

READ WRITE NOT ALLOWED PRIMARY