Oracle安装和dataguard

1 准备工作

	Host IP	DB_NAME	DB_UNIQUE_NAME	Archive_Dest
主库	172.20.20.150	orcl	pdunq	/data/oracle/archivelog/
备库	172.20.20.151	orcl	pdunq_dg	/data/oracle/archivelog/

保护模式：默认最大性能模式(MAXIMUM PERFORMANCE)

在primary搭建数据库软件，建立lsnrctl监听，采用dbca搭建实例，在standby上搭建数据库软件，建立监听，但是不需要采用dbca建立实例。

2 数据库安装

主机名修改

#/etc/sysconfig/network   

#/etc/hosts

解决系统依赖关系

yum -y install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc* ksh glibc-common glibc-devel gcc gcc-c++ libaio-devel libaio libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh numactl-devel glibc-headers

创建用户

groupadd oinstall

groupadd dba

useradd -g oinstall -G dba oracle

配置系统环境变量（source /home/oracle/.bash_profile)

#vi /home/oracle/.bash_profile

export ORACLE_BASE=/data/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1

export ORACLE_SID=orcl

export ORACLE_OWNER=oracle

export ORACLE_TERM=vt100

export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

export PATH=$ORACLE_HOME/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

export CLASSPATH

PATH=$PATH:/usr/sbin; export PATH

PATH=$PATH:/usr/bin; export PATH

ORA_NLS33=$ORACLE_HOME/nls/admin/data

修改系统内核参数（sysctl -p）

#vi /etc/sysctl  

kernel.sem =   100

fs.file-max = 6815744

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.wmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_max = 1048576

fs.aio-max-nr = 1048576

net.ipv4.tcp_fin_timeout = 10

kernel.threads-max = 143360

vi /etc/security/limits.conf

oracle soft nproc 204700

oracle hard nofile 65536

* soft memlock 41943040

* hard memlock 41943040

* soft nofile 265535

* hard nofile 265535

两种安装方式

-新建dbca文件静默安装

-图形界面xmanager远程

yum groupinstall "X Window System" "Desktop" -y

DISPLAY=10.255.1.6:0.0; export DISPLAY

./data/soft/database/runInstaller

下一步提示安装

数据库建库（dbca）----->备库不需要

建立监听（netca）

具体参考链接 http://www.jb51.net/article/53769.htm

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

3 dataguard配置

主库打开Forced Logging 模式

强制归档 alter database force logging;

添加standby文件

alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20M;

alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20M;

alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20M;

alter database drop standby logfile group 4;

alter database drop standby logfile group 5;

alter database drop standby logfile group 6;

select * from v$logfile order by 1;

准备参数文件，生成pfile

SQl>create pfile from spfile;

File created.

SQL>shutdown immediate

修改pfile,先备份文件

cp /data/oracle/product/11.2/db_1/dbs/initorcl.ora /data/oracle/product/11.2/db_1/dbs/initorcl.orabak

--->主要修改如下红色地方

orcl.__db_cache_size=1459617792

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=1476395008

orcl.__sga_target=2197815296

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=671088640

orcl.__streams_pool_size=0

*.audit_file_dest='/data/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/data/oracle/oradata/orcl/control01.ctl','/data/oracle/flash_recovery_area/orcl/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='pdunq'

*.diagnostic_dest='/data/oracle'

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

*.FAL_CLIENT='pdunq'

*.FAL_SERVER='pdunq_dg'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pdunq_dg,pdunq)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/data/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdunq'

*.LOG_ARCHIVE_DEST_2='service=pdunq_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

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

*.memory_max_target=4000000000

*.memory_target=3660944384

*.open_cursors=300

*.processes=3000

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

生成spfile，配置生效

create spfile from pfile;

启动

SQL>startup

修改监听文件

vi /data/oracle/product/11.2/db_1/network/admin/listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = KM1818-databasebak)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /data/oracle

SID_LIST_LISTENER =

  (SID_LIST =

                (SID_DESC =

                  (GLOBAL_DBNAME = orcl)

                  (ORACLE_HOME =/data/oracle/product/11.2/db_1)

                  (SID_NAME = orcl)

                )

  )

修改tns配置文件

vi /data/oracle/product/11.2/db_1/network/admin/tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = KM1818-databasebak)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

pdunq  =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)( HOST = 172.20.20.150 )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

pdunq_dg  =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)( HOST = 172.20.20.151 )(PORT = 1521))

    )

    (CONNECT_DATA =

        (SERVER = DEDICATED)

        (SERVICE_NAME = orcl)

    )

  )

监听服务重启

lsnrctl stop/start/status

primary上配置最大可用模式：

SQL>startup

SQL>alter database set standby database to maximize availability;

创建主库的密码文件，没有则建立。可以OS验证的方式登录

 orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=iamwangnc entries=5

备份数据库

backup database plus archivelog;

backup current controlfile for standby;

exit;

备份结束后会在闪回区产生备份文件（ /data/oracle/flash_recovery_area/ORCL）

主库上建立备库控制文件（控制文件通常需要有多份，手工将文件复制几份）

$ sqlplus '/as sysdba'

SQL> alter database create standby controlfile as '/data/oracle/stdby_control01.ctl';

 cp sdtby_control01.ctl stdby_control02.ctl

拷贝文件到备机上（包括密码文件；控制文件；监听文件；rman备份文件）

scp -r ./* 172.20.20.151:/data/oracle/flash_recovery_area/

scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/dbs/

scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/network/admin/

scp -r * 172.20.20.151:/data/oracle/product/11.2/db_1/dbs/orapworcl

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

备机上操作

listener.ora  tnsnames.ora(配置如下)

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)( HOST = i-rzc0r96p )(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /data/oracle

SID_LIST_LISTENER =

  (SID_LIST =

                (SID_DESC =

                  (GLOBAL_DBNAME = orcl)

                  (ORACLE_HOME =/data/oracle/product/11.2/db_1)

                  (SID_NAME = orcl)

                )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = i-rzc0r96p)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

pdunq =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.20.150)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

pdunq_dg =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.20.151)(PORT = 1521))

    )

    (CONNECT_DATA =

        (SERVER = DEDICATED)

        (SERVICE_NAME = orcl)

    )

  )

修改参数文件

orcl.__db_cache_size=1493172224

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=1476395008

orcl.__sga_target=2197815296

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=637534208

orcl.__streams_pool_size=0

*.audit_file_dest='/data/oracle/admin/orcl/adump'

*.audit_trail= 'db'  （如果备库将来要打开成只读模式，需要确认audit_trail参数不是含db，应该设成os或none）

*.compatible='11.2.0.0.0'

*.control_files='/data/oracle/stdby_control01.ctl','/data/oracle/stdby_control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

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

*.db_recovery_file_dest_size=4070572032

*.DB_UNIQUE_NAME='pdunq_dg'

*.diagnostic_dest='/data/oracle'

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

*.FAL_CLIENT='pdunq'

*.FAL_SERVER='pdunq_dg'

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pdunq_dg,pdunq)'

*.log_archive_dest_1='location=/data/oracle/archivelog'

*.LOG_ARCHIVE_DEST_2='service=pdunq_dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdunq_dg'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'

*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

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

*.memory_max_target=4000000000

*.memory_target=3660944384

*.open_cursors=300

*.processes=3000

*.remote_login_passwordfile='EXCLUSIVE'

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

监听重启，查看状态

重启备库生效

create spfile from pfile;

启动物理备库

SQL> startup nomount

SQL> alter database mount standby database;

备库做rman恢复

$ rman target /       (要求主备库rman备份文件的存放路径和文件名一致)

RMAN> restore database;

##RMAN> restore archivelog all;

介质恢复后，rman 自动将standby 数据库打开到mount 状态。

启动redo 应用

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

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

查看同步情况

首先连接到primary 数据库

SQL> show parameter instance_name;

NAME TYPE VALUE

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

instance_name string jssweb

SQL> alter system switch logfile;

系统已更改。

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

              22

连接到standby 数据库

SQL> show parameter instance_name;

NAME TYPE VALUE

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

instance_name string jsspdg

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

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

22

常用维护SQL命令

启动Data Guard 后，查看同步情况：：

SQL> select error from v$archive_dest;

用SQL 查看了一下同步正常：

SQL> select sequence#,applied from v$archived_log;

查看备库接收、应用redo数据的过程

select message from v$dataguard_status；

主库归档：

SQL>ALTER SYSTEM SWITCH LOGFILE;    --对单实例数据库或RAC中的当前实例执行日志切换

SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;    --对数据库中的所有实例执行日志切换

V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息

SELECT* FROM V$STANDBY_LOG;

备库启动归档日志应用

alter database recover managed standby database disconnect from session;

备库停止归档日志应用

alter database recover managed standby database cancel;

查看备库是否和主库同步，查询V$archive_dest_status视图

select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;

监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少

select* from v$archive_gap;

查看当前主机的运行状态

select switchover_status,database_role,protection_mode from v$database

备库端查看RFS(RemoteFile Service)接收日志情况和MRP应用日志同步主数据库的情况(Physical Standby Database Only)  记录当前备库的一些进程情况和进程ID

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;