精华内容
下载资源
问答
  • oracle11g ADG主备切换

    2019-09-24 01:35:44
    oracle11g ADG主备切换 1、主库 SQL> select name,open_mode,switchover_status from v$database; NAME OPEN_MODE SWITCHOVER_STATUS--------- -------------------- --------------------PROD1 READ WRITE .....

    oracle11g ADG主备切换

    1、主库

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

    NAME OPEN_MODE SWITCHOVER_STATUS
    --------- -------------------- --------------------
    PROD1 READ WRITE TO STANDBY

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system archive log current;

    System altered.

    SQL> alter database commit to switchover to physical standby with session shutdown;

    Database altered.

    SQL> shutdown abort
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 849530880 bytes
    Fixed Size 1348244 bytes
    Variable Size 511708524 bytes
    Database Buffers 331350016 bytes
    Redo Buffers 5124096 bytes
    Database mounted.
    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    RECOVERY NEEDED

    SQL> alter database open;

    Database altered.

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY

     

    2、备库

    SQL> alter database commit to switchover to primary with session shutdown;

    Database altered.

    SQL> alter database open;

    Database altered.

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

    NAME OPEN_MODE SWITCHOVER_STATUS
    --------- -------------------- --------------------
    PROD1 READ WRITE TO STANDBY

     

    3、新备库(原主库)启用实时日志应用

     

    SQL> alter database recover managed standby database using current logfile disconnect from session;

     

    Database altered.

     

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

     

    NAME OPEN_MODE SWITCHOVER_STATUS
    --------- -------------------- --------------------
    PROD1 READ ONLY WITH APPLY NOT ALLOWED

     

    参考链接:

    https://blog.csdn.net/kadwf123/article/details/83092607

    http://blog.chinaunix.net/uid-26313110-id-5748048.html

     

    转载于:https://www.cnblogs.com/orcl-2018/p/11565424.html

    展开全文
  • Oracle11.2.0.4数据库ADG主备环境搭建 实现目标 两台X86数据库服务器实现oracle11g的主备adg环境,避免单点故障,满足数据库基本容灾备份需求。其中操作系统版本要求centos7.9,数据库版本要求oracle11.2.0.4+最新...

    Centos7.9+Oracle11.2.0.4数据库ADG主备环境搭建

    1.实现目标

    两台X86数据库服务器实现oracle11g的主备adg环境,避免单点故障,满足数据库基本容灾备份需求。其中操作系统版本要求centos7.9,数据库版本要求oracle11.2.0.4+最新psu201020版本。

    2.环境准备

    主库

    备库

    主机名

    prebilla

    prebillb

    本机ip

    10.1.1.51

    10.1.1.52

    adg服务IP

    10.1.1.53

    数据库名称db_name

    prebill

    数据库db_unique_name

    prebilla

    prebillb

    数据库字符集

    ZHS16GBK

    国家字符集

    AL16UTF16

    root密码

    welcome1

    oracle密码

    welcome1

    sys\system密码

    SYS_Passw0rd

    操作系统

    centos7.9

    数据库版本

    oracle11.2.0.4

    数据库补丁版本

    11.2.0.4.201020

    opatch补丁工具版本

    11.2.0.3.29

    以下为其中一台信息:

    [root@prebillb keepalived]# cat /etc/hosts

    127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

    ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

    #10.1.1.52 prebillb 注意此处注释本机IP和主机名是为了保证adg监听主机名时可以监听服务IP10.1.1.53

    10.1.1.51 prebilla –此处填写另外一台主机名和IP是为了dg broker里边识别对方主机

    主库/etc/hosts:

    备库/etc/hosts:

    [root@prebillb keepalived]# cat /etc/redhat-release

    CentOS Linux release 7.9.2009 (Core)

    [root@prebillb keepalived]# df -h

    Filesystem               Size  Used Avail Use% Mounted on

    devtmpfs                 189G     0  189G   0% /dev

    tmpfs                    189G     0  189G   0% /dev/shm

    tmpfs                    189G   27M  189G   1% /run

    tmpfs                    189G     0  189G   0% /sys/fs/cgroup

    /dev/mapper/centos-root  100G  1.9G   99G   2% /

    /dev/sda1               1014M  153M  862M  16% /boot

    /dev/mapper/centos-home  1.6T  130G  1.4T   9% /home—用于安装oracle数据库

    /dev/loop0               4.4G  4.4G     0 100% /mnt/iso—挂接好iso光盘用于yum安装包

    tmpfs                     38G     0   38G   0% /run/user/0

    [root@prebillb keepalived]# lsblk  --系统安装的目录结构参考如下

    NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT

    sda               8:0    0  1.6T  0 disk

    ├─sda1            8:1    0    1G  0 part /boot

    └─sda2            8:2    0  1.6T  0 part

      ├─centos-root 253:0    0  100G  0 lvm  /

      ├─centos-swap 253:1    0   16G  0 lvm  [SWAP]

      └─centos-home 253:2    0  1.5T  0 lvm  /home

    loop0             7:0    0  4.4G  0 loop /mnt/iso

    [root@prebillb keepalived]# vgs

      VG     #PV #LV #SN Attr   VSize VFree

      centos   1   3   0 wz--n- 1.63t    0

    [root@prebillb keepalived]# lvs

      LV   VG     Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert

      home centos -wi-ao----   1.52t                                                   

      root centos -wi-ao---- 100.00g                                                   

      swap centos -wi-ao----  16.00g                                                   

    [root@prebillb keepalived]# pvs

      PV         VG     Fmt  Attr PSize PFree

      /dev/sda2  centos lvm2 a--  1.63t    0

    [root@prebillb keepalived]# free -g

                  total        used        free      shared  buff/cache   available

    Mem:            377         111         136           0         129         264

    Swap:            15           0          15

    [root@prebillb keepalived]# lscpu

    Architecture:          x86_64

    CPU op-mode(s):        32-bit, 64-bit

    Byte Order:            Little Endian

    CPU(s):                40

    On-line CPU(s) list:   0-39

    Thread(s) per core:    2

    Core(s) per socket:    10

    Socket(s):             2

    NUMA node(s):          2

    Vendor ID:             GenuineIntel

    CPU family:            6

    Model:                 79

    Model name:            Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz

    Stepping:              1

    CPU MHz:               2199.731

    CPU max MHz:           2200.0000

    CPU min MHz:           1200.0000

    BogoMIPS:              4389.97

    Virtualization:        VT-x

    L1d cache:             32K

    L1i cache:             32K

    L2 cache:              256K

    L3 cache:              25600K

    NUMA node0 CPU(s):     0-9,20-29

    NUMA node1 CPU(s):     10-19,30-39

    Flags:                 fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch epb cat_l3 cdp_l3 invpcid_single intel_ppin intel_pt ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm rdt_a rdseed adx smap xsaveopt cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm arat pln pts spec_ctrl intel_stibp

     [root@prebillb keepalived]# sar 1 3

    Linux 3.10.0-1160.el7.x86_64 (prebillb)         07/29/2021      _x86_64_        (40 CPU)

    09:45:30 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle

    09:45:31 AM     all      0.08      0.00      0.13      0.00      0.00     99.80

    09:45:32 AM     all      0.00      0.00      0.03      0.00      0.00     99.97

    09:45:33 AM     all      0.00      0.00      0.00      0.00      0.00    100.00

    Average:        all      0.03      0.00      0.05      0.00      0.00     99.92

    [root@prebillb keepalived]#

    3.实现步骤

    3.1环境配置

    两台修改基本一致

    3.1.1网络配置

    nmtui文字图交互式操作很方便。
    命令修改主机名:hostnamectl set-hostname prebilla
    hostnamectl --static

    3.1.2系统参数

    [root@prebilla keepalived]# cat /etc/sysctl.conf

    # sysctl settings are defined through files in

    # /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.

    #

    # Vendors settings live in /usr/lib/sysctl.d/.

    # To override a whole file, create a new file with the same in

    # /etc/sysctl.d/ and put new settings there. To override

    # only specific settings, add a file with a lexically later

    # name in /etc/sysctl.d/ and put new settings there.

    #

    # For more information, see sysctl.conf(5) and sysctl.d(5).

    fs.file-max = 6815744

    kernel.sem = 250 32000 100 128

    kernel.shmmni = 4096

    kernel.shmall = 1073741824

    kernel.shmmax = 400000000000  #此处比物理内存小一点即可

    kernel.panic_on_oops = 1

    net.core.rmem_default = 262144

    net.core.rmem_max = 4194304

    net.core.wmem_default = 262144

    net.core.wmem_max = 1048576

    net.ipv4.conf.all.rp_filter = 2

    net.ipv4.conf.default.rp_filter = 2

    fs.aio-max-nr = 1048576

    net.ipv4.ip_local_port_range = 9000 65500

    vm.nr_hugepages = 51200 #此处设置内存大页,比sga大一些,且比物理内存小,算法为51200*2/1024=100G,意味着设置SGA的时候小于100G即可,一般可设置80G-100G

    sysctl -p生效

    3.1.3关闭防火墙

    systemctl stop firewalld

    systemctl disable firewalld

    systemctl status firewalld

    3.1.4关闭SELINUX

    vi /etc/selinux/config

    SELINUX=disabled
     

    临时关闭:setenforce 0

    3.1.5时间同步

    手动同步时间:ntpdate xxx.xxx.xxx.xxx

    检查时间和时区是否正确:

    date

    timedatectl status

    timedatectl set-timezone "Asia/Shanghai"

    timedatectl set-time '2015-11-20 16:10:40'
    
    timedatectl set-time '2015-11-20 16:10:40'

     

    启用ntp:

    (也可考虑使用chronyc替换ntp 参考:

    https://blog.csdn.net/jycjyc/article/details/109717197

    1) yum install ntp -y

    2) systemctl start ntpd

    3) systemctl enable ntpd

    4) vi /etc/ntp.conf

    #注释下面4行

    #server 0.centos.pool.ntp.org iburst

    #server 1.centos.pool.ntp.org iburst

    #server 2.centos.pool.ntp.org iburst

    #server 3.centos.pool.ntp.org iburst

    替换成实际时间服务器:

    server xxx.xx.xxx.xxx

    5) vi /etc/sysconfig/ntpd

    # Command line options for ntpd

    SYNC_HWCLOCK=yes 

    OPTIONS="-x"

    6)  systemctl restart ntpd

    7)  systemctl status ntpd

    8)  ntpq -p

    查看ntp服务是否能够被同步,显示为“LOCAL”,表示成功。如果没有任何显示,客户端将无法同步。

    3.1.6配置本地yum源

    挂接ISO:

    mdkir -p /mnt/iso

    mount -t iso9660 -o loop /CentOS-7.iso /mnt/iso

    [root@prebilla /]# df -h

    Filesystem               Size  Used Avail Use% Mounted on

    devtmpfs                 189G     0  189G   0% /dev

    tmpfs                    189G     0  189G   0% /dev/shm

    tmpfs                    189G   19M  189G   1% /run

    tmpfs                    189G     0  189G   0% /sys/fs/cgroup

    /dev/mapper/centos-root  100G  2.0G   99G   2% /

    /dev/sda1               1014M  153M  862M  16% /boot

    /dev/mapper/centos-home  1.6T  131G  1.4T   9% /home

    /dev/loop0               4.4G  4.4G     0 100% /mnt/iso

    tmpfs                     38G     0   38G   0% /run/user/0

    [root@prebilla /]# cat /etc/yum.repos.d/local.repo

    [local]

    name=local

    baseurl=file:///mnt/iso

    enabled=1

    gpgcheck=0

    3.1.7安装软件包

    yum -y install binutils compat-libstdc++-33 gcc gcc-c++ glibc glibc-common  glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat openssh-clients compat-libcap1   xorg-x11-utils xorg-x11-xauth elfutils unixODBC unixODBC-devel libXp elfutils-libelf elfutils-libelf-devel smartmontools psmisc

    3.1.8/etc/security/limits.conf添加内容

    oracle   soft   nofile    10240

    oracle   hard   nofile    65536

    oracle   soft   nproc    16384

    oracle   hard   nproc    16384

    oracle   soft   stack    10240

    oracle   hard   stack    32768

    oracle   hard   memlock    unlimited

    oracle   soft   memlock    unlimited

    3.2用户创建

    3.2.1建用户

    方法1:

    groupadd -g 1001 oinstall 

    groupadd -g 1003 dba 

    useradd -u 1001 -g oinstall -G dba oracle 

    passwd oracle设置oracle密码

    方法2:

    参考生产服务器的用户信息编辑修改:

    cat /etc/passwd

    cat /etc/group

    完成后pwconv

    修改目录属主chown -R oracle:oinstall /home/oracle

    passwd oracle

    3.2.2环境变量:

    [oracle@prebilla ~]$ cat .bash_profile

    # .bash_profile

    # Get the aliases and functions

    if [ -f ~/.bashrc ]; then

            . ~/.bashrc

    fi

    # User specific environment and startup programs

    PATH=$PATH:$HOME/.local/bin:$HOME/bin

    export PATH

    export ORACLE_BASE=/home/oracle/app/oracle

    export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

    export ORACLE_SID=prebill

    export ORACLE_UNQNAME=prebilla  #备库为prebillb,此行可删除

    export NLS_LANG=American_america.zhs16gbk

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

    3.3 软件安装

    两种方式:

    1. 从已有其它生产环境打包tar软件:此次使用的方法

    [oracle@radiusn ~]$ pwd

    /home/oracle

    [oracle@radiusn ~]$ ll

    total 3050528

    drwxr-xr-x 3 oracle oinstall         28 Aug  4  2017 app

    -rw-r--r-- 1 oracle oinstall 2863122012 Jun  1 14:17 app.tar.gz

    [oracle@radiusn ~]$ du -sh app.tar.gz

    2.7G    app.tar.gz

    tar -zcvf app.tar.gz app --exclude=app/oracle/diag --exclude=app/oracle/oradata --exclude=app/oracle/admin --exclude=app/oracle/cfgtoollogs

    主备库创建相同目录路径,scp传输后cd /home/oracle

    然后tar -zxvf app.tar.gz解压即可

    准备两文件:

    注意权限:

    chmod 664 /etc/oratab

    [oracle@prebilla ~]$ cat /etc/oraInst.loc

    inventory_loc=/home/oracle/app/oracle/oraInventory

    inst_group=oinstall

    [oracle@prebillb admin]$ cat /etc/oratab

    #

    # This file is used by ORACLE utilities.  It is created by root.sh

    # and updated by either Database Configuration Assistant while creating

    # a database or ASM Configuration Assistant while creating ASM instance.

    # A colon, ':', is used as the field terminator.  A new line terminates

    # the entry.  Lines beginning with a pound sign, '#', are comments.

    #

    # Entries are of the form:

    #   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

    #

    # The first and second fields are the system identifier and home

    # directory of the database respectively.  The third filed indicates

    # to the dbstart utility that the database should , "Y", or should not,

    # "N", be brought up at system boot time.

    #

    # Multiple entries with the same $ORACLE_SID are not allowed.

    #

    #

    prebillb:/home/oracle/app/oracle/product/11.2.0/db_1:N #主库则为prebilla

    2)新装:由于不能图形化直连,所以可使用静默安装方式

    [oracle@prebilla ~]$ cat /etc/oraInst.loc

    inventory_loc=/home/oracle/app/oracle/oraInventory

    inst_group=oinstall

    ./runInstaller -silent -debug -force -noconfig -IgnoreSysPreReqs \

    FROM_LOCATION=/home/soft/database/stage/products.xml \

    oracle.install.option=INSTALL_DB_SWONLY \

    UNIX_GROUP_NAME=oinstall \

    INVENTORY_LOCATION=/home/oracle/app/oracle/oraInventory \

    ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/db_1 \

    ORACLE_HOME_NAME="Oracle11g" \

    ORACLE_BASE=/home/oracle/app/oracle \

    oracle.install.db.InstallEdition=EE \

    oracle.install.db.isCustomInstall=false \

    oracle.install.db.DBA_GROUP=dba \

    oracle.install.db.OPER_GROUP=dba \

    DECLINE_SECURITY_UPDATES=true

    3.4 数据库创建

    3.4.1建库:

    cd /home/oracle/app/oracle/product/11.2.0/db_1/assistants/dbca/templates

    dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname prebill -sid prebill -sysPassword SYS_Passw0rd -systemPassword SYS_Passw0rd -responseFile NO_VALUE -datafileDestination /home/oracle/app/oracle/oradata -redoLogFileSize 500 -recoveryAreaDestination NO_VALUE -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema false -memoryPercentage 20 -databaseType OLTP -emConfiguration NONE

    3.4.2表空间按需求配置:

    建表空间和加数据文件脚本参考:

    create tablespace bill_ts datafile '/home/oracle/app/oracle/oradata/prebill/bill_ts01.dbf' size 10240M autoextend on next 10M;

    alter tablespace bill_ts add datafile '/home/oracle/app/oracle/oradata/prebill/bill_ts02.dbf' size 10240M autoextend on next 10M;

    create tablespace idr_ts datafile '/home/oracle/app/oracle/oradata/prebill/prebill01.dbf' size 10240M autoextend on next 10M;

    alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill03.dbf' size 10240M autoextend on next 10M;

    alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill04.dbf' size 10240M autoextend on next 10M;

    alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill05.dbf' size 10240M autoextend on next 10M;

    alter tablespace idr_ts add datafile '/home/oracle/app/oracle/oradata/prebill/prebill06.dbf' size 10240M autoextend on next 10M;

    create tablespace INDEX_TS datafile '/home/oracle/app/oracle/oradata/prebill/index_ts01.dbf' size 8192M autoextend on next 10M;

    create tablespace IDRERROR_TS datafile '/home/oracle/app/oracle/oradata/prebill/idrerror_ts01.dbf' size 8192M autoextend on next 10M;

    create tablespace STATIC_TS datafile '/home/oracle/app/oracle/oradata/prebill/static_ts01.dbf' size 1024M autoextend on next 10M;

    create tablespace USER_TS datafile '/home/oracle/app/oracle/oradata/prebill/user_ts01.dbf' size 8192M autoextend on next 10M;

    create tablespace INDX datafile '/home/oracle/app/oracle/oradata/prebill/indx_ts01.dbf' size 1024M autoextend on next 10M;

    查看表空间占用脚本:

    [oracle@prebilla ~]$ pwd

    /home/oracle

    [oracle@prebilla ~]$ cat tbs.sql

    set line 132

    set wrap off

    select t.*

    from (SELECT D.TABLESPACE_NAME,

    SPACE "SUM_SPACE(M)",

    BLOCKS SUM_BLOCKS,

    SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

    ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

    FREE_SPACE "FREE_SPACE(M)"

    FROM (SELECT TABLESPACE_NAME,

    ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

    SUM(BLOCKS) BLOCKS

    FROM DBA_DATA_FILES

    GROUP BY TABLESPACE_NAME) D,

    (SELECT TABLESPACE_NAME,

    ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

    FROM DBA_FREE_SPACE

    GROUP BY TABLESPACE_NAME) F

    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

    UNION ALL --if have tempfile

    SELECT D.TABLESPACE_NAME,

    SPACE "SUM_SPACE(M)",

    BLOCKS SUM_BLOCKS,

    USED_SPACE "USED_SPACE(M)",

    ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

    SPACE - USED_SPACE "FREE_SPACE(M)"

    FROM (SELECT TABLESPACE_NAME,

    ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

    SUM(BLOCKS) BLOCKS

    FROM DBA_TEMP_FILES

    GROUP BY TABLESPACE_NAME) D,

    (SELECT TABLESPACE,

    ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

    FROM V$SORT_USAGE

    GROUP BY TABLESPACE) F

    WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t

    order by "USED_RATE(%)" desc;

    3.4.3建用户

    根据需求创建:

    create user prebill identified by bisys3#$ default tablespace IDR_TS; 

    grant connect,resource,dba to prebill;

    3.4.4日志组优化

    alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/prebill/redo04.log') size 500M;

    创建standby日志为adg使用,比日志组多一组,大小一致。

    alter database add standby logfile thread 1 group 5 ('/home/oracle/app/oracle/oradata/prebill/standby_redo05.log') size 500M;

    alter database add standby logfile thread 1 group 6 ('/home/oracle/app/oracle/oradata/prebill/standby_redo06.log') size 500M;

    alter database add standby logfile thread 1 group 7 ('/home/oracle/app/oracle/oradata/prebill/standby_redo07.log') size 500M;

    alter database add standby logfile thread 1 group 8 ('/home/oracle/app/oracle/oradata/prebill/standby_redo08.log') size 500M;

    alter database add standby logfile thread 1 group 9 ('/home/oracle/app/oracle/oradata/prebill/standby_redo09.log') size 500M;

    3.5 监听配置

    3.5.1监听listener.ora内容:

    方法1:修改监听:

    [oracle@prebilla admin]$ cat listener.ora

    # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (GLOBAL_DBNAME = prebill)

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

          (SID_NAME = prebill)

        )

      )

    LISTENER =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = prebilla)(PORT = 1521)) #备库为prebillb,使用主机名的好处是可以实现监听该主机上的所有IP:对服务IP实现自动监听,避免了数据库切换后应用需要修改数据库连接串的问题。

      )

    ADR_BASE_LISTENER = /home/oracle/app/oracle

    [oracle@prebilla admin]$lsnrctl start

    [oracle@prebilla admin]$lsnrctl status

    方法2:静默建监听:

    netca -silent -responsefile /home/oracle/app/oracle/product/11.2.0/db_1/assistants/netca/netca.rsp

    3.5.2sqlnet.ora设置参考:

    [oracle@prebilla admin]$ more sqlnet.ora

    NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)

    DIAG_ADR_ENABLED=OFF

    DIAG_SIGHANDLER_ENABLED=FALSE

    DIAG_DDE_ENABLED=FALSE

    3.5.3tnsnames.ora配置参考:

    PREBILL=

      (DESCRIPTION=

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

        (CONNECT_DATA=

          (SERVER=DEDICATED)

          (SERVICE_NAME=prebill)

        )

      )

    PREBILLA=

      (DESCRIPTION=

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

        (CONNECT_DATA=

          (SERVER=DEDICATED)

          (SERVICE_NAME=prebill)

        )

      )

    PREBILLB=

      (DESCRIPTION=

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

        (CONNECT_DATA=

          (SERVER=DEDICATED)

          (SERVICE_NAME=prebill)

        )

      )

    3.6 参数优化调整

    alter profile default limit failed_login_attempts unlimited;

    alter profile default limit password_life_time unlimited;

    alter system set audit_trail=none scope=spfile sid='*';

    alter system set recyclebin=off scope=spfile sid='*';

    alter system set sga_target=80G scope=spfile sid='*';--根据物理内存调整

    alter system set pga_aggregate_target=20G sid='*';--根据物理内存调整

    alter system set event='10949 trace name context forever, level 1:28401 trace name context forever, level 1:10795 trace name context forever, level 2'  scope=spfile sid='*';

    alter system set "_use_adaptive_log_file_sync"=false sid='*';

    alter system set "_undo_autotune"=false scope=both sid='*';

    alter system set db_unique_name='prebilla' scope=spfile sid='*';

    alter system set db_files=1000 scope=spfile sid='*';

    3.7 数据库打补丁

    介质如下:

    参考readme,基本步骤如下:(备库不更新数据字典3.7.4和3.7.6,因为主库更新后,再恢复出备库)

    3.7.1替换opatch工具

    opatch工具替换:

    [oracle@prebilla ~]$ mv *.zip ./patch/

    [oracle@prebilla ~]$ cd patch/

    [oracle@prebilla patch]$ ll

    total 551392

    -rw------- 1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip

    -rw------- 1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip

    [oracle@prebilla patch]$ unzip 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip

    Archive:  11.2.0.3.29-p6880880_112000_Linux-x86-64.zip

       creating: OPatch/

    [oracle@prebilla patch]$ pwd

    /home/oracle/patch

    [oracle@prebilla patch]$ ll

    total 551396

    -rw-------  1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip

    drwxr-x--- 16 oracle oinstall      4096 Apr 20 17:38 OPatch

    -rw-------  1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip

    [oracle@prebilla patch]$ cd $ORACLE_HOME/

    [oracle@prebilla db_1]$ mv OPatch OPatch.BAK

    [oracle@prebilla db_1]$ mv /home/oracle/patch/OPatch ./

    [oracle@prebilla db_1]$ opatch -v

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    解压psu201020补丁:

    [oracle@prebilla patch]$ ll

    total 551392

    -rw------- 1 oracle oinstall 123959496 Jul 27 13:31 11.2.0.3.29-p6880880_112000_Linux-x86-64.zip

    -rw------- 1 oracle oinstall 440662115 Jul 27 13:32 psu2020-1020.zip

     [oracle@prebilla patch]$ unzip psu2020-1020.zip

    Archive:  psu2020-1020.zip

       creating: 31720776/

       creating: 31720776/31537677/

    3.7.2停监听和数据库

    lsnrctl stop

    sqlplus / as sysdba

    shutdown immediate

    3.7.3打db补丁

    [oracle@prebilla patch]$ pwd

    /home/oracle/patch

    [oracle@prebilla patch]$ cd 31720776/

    [oracle@prebilla 31720776]$ ll

    total 40

    drwxr-xr-x 30 oracle oinstall  4096 Sep 24  2020 31537677

    drwxr-xr-x  4 oracle oinstall    67 Sep  8  2020 31668908

    -rw-rw-r--  1 oracle oinstall 11566 Oct 19  2020 PatchSearch.xml

    -rw-r--r--  1 oracle oinstall 20741 Oct 20  2020 README.html

    [oracle@prebilla 31720776]$ cd 31537677

    [oracle@prebilla 31537677]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    PREREQ session

    Oracle Home       : /home/oracle/app/oracle/product/11.2.0/db_1

    Central Inventory : /home/oracle/app/oracle/oraInventory

       from           : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc

    OPatch version    : 11.2.0.3.29

    OUI version       : 11.2.0.4.0

    Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-40-59PM_1.log

    Invoking prereq "checkconflictagainstohwithdetail"

    Prereq "checkConflictAgainstOHWithDetail" passed.

    OPatch succeeded.

    [oracle@prebilla 31537677]$ pwd

    /home/oracle/patch/31720776/31537677

    [oracle@prebilla 31537677]$ opatch apply

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    Oracle Home       : /home/oracle/app/oracle/product/11.2.0/db_1

    Central Inventory : /home/oracle/app/oracle/oraInventory

       from           : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc

    OPatch version    : 11.2.0.3.29

    OUI version       : 11.2.0.4.0

    Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-41-32PM_1.log

    Verifying environment and performing prerequisite checks...

    OPatch continues with these patches:   30298532  30670774  31103343  31537677 

    Do you want to proceed? [y|n]

    y

    User Responded with: Y

    All checks passed.

    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

    (Oracle Home = '/home/oracle/app/oracle/product/11.2.0/db_1')

    Is the local system ready for patching? [y|n]

    y

    User Responded with: Y

    Backing up files...

    Applying sub-patch '30298532' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'

    ApplySession: Optional component(s) [ oracle.rdbms.tg4tera, 11.2.0.4.0 ] , [ oracle.rdbms.tg4sybs, 11.2.0.4.0 ] , [ oracle.rdbms.tg4ifmx, 11.2.0.4.0 ] , [ oracle.rdbms.tg4db2, 11.2.0.4.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

    Patching component oracle.rdbms.rsf, 11.2.0.4.0...

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Patching component oracle.rdbms.hsodbc, 11.2.0.4.0...

    Patching component oracle.ldap.rsf, 11.2.0.4.0...

    Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...

    Applying sub-patch '30670774' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'

    Patching component oracle.rdbms.rsf, 11.2.0.4.0...

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Patching component oracle.network.rsf, 11.2.0.4.0...

    Patching component oracle.ldap.rsf, 11.2.0.4.0...

    Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...

    Patching component oracle.swd.oui, 11.2.0.4.0...

    Patching component oracle.ctx, 11.2.0.4.0...

    Applying sub-patch '31103343' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Patching component oracle.rdbms.rsf, 11.2.0.4.0...

    Applying sub-patch '31537677' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Patching component oracle.rdbms.rsf, 11.2.0.4.0...

    Patching component oracle.rdbms.dv, 11.2.0.4.0...

    Patching component oracle.rdbms.rman, 11.2.0.4.0...

    Patching component oracle.ldap.rsf, 11.2.0.4.0...

    Patching component oracle.ldap.rsf.ic, 11.2.0.4.0...

    Patching component oracle.oracore.rsf, 11.2.0.4.0...

    Patching component oracle.rdbms.util, 11.2.0.4.0...

    Patching component oracle.dbdev, 11.2.0.4.0...

    Patching component oracle.ctx, 11.2.0.4.0...

    Patching component oracle.buildtools.rsf, 11.2.0.4.0...

    Composite patch 31537677 successfully applied.

    Log file location: /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-41-32PM_1.log

    OPatch succeeded.

    [oracle@prebilla 31537677]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    PREREQ session

    Oracle Home       : /home/oracle/app/oracle/product/11.2.0/db_1

    Central Inventory : /home/oracle/app/oracle/oraInventory

       from           : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc

    OPatch version    : 11.2.0.3.29

    OUI version       : 11.2.0.4.0

    Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-42-53PM_1.log

    Invoking prereq "checkconflictagainstohwithdetail"

    Prereq "checkConflictAgainstOHWithDetail" passed.

    OPatch succeeded.

    3.7.4更新db数据字典

    [oracle@prebilla 31537677]$ opatch lspatches

    31537677;Database Patch Set Update : 11.2.0.4.201020 (31537677)

    OPatch succeeded.

    [oracle@prebilla 31537677]$ cd $ORACLE_HOME/rdbms/admin

    [oracle@prebilla admin]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 27 13:49:19 2021

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

    Connected to an idle instance.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 8.5516E+10 bytes

    Fixed Size                  2262656 bytes

    Variable Size            1.0737E+10 bytes

    Database Buffers         7.4625E+10 bytes

    Redo Buffers              151117824 bytes

    Database mounted.

    Database opened.

    SQL> @catbundle.sql psu apply

    SQL> set head off

    SQL> select * from registry$history order by id;

    24-AUG-13 12.03.45.119862 PM

    APPLY                          SERVER

    11.2.0.4                                0

    Patchset 11.2.0.2.0

    PSU

    27-JUL-21 01.59.04.290989 PM

    APPLY                          SERVER

    11.2.0.4.201020OJVMPSU                  0

    OJVM PSU post-install

    27-JUL-21 01.59.03.587257 PM

    jvmpsu.sql                     SERVER

    11.2.0.4.201020OJVMPSU                  0

    RAN jvmpsu.sql

    27-JUL-21 10.42.39.670216 AM

    APPLY                          SERVER

    11.2.0.4                           191015

    PSU 11.2.0.4.191015

    PSU

    27-JUL-21 01.49.47.045440 PM

    APPLY                          SERVER

    11.2.0.4                           201020

    PSU 11.2.0.4.201020

    PSU

    27-JUL-21 01.59.04.293219 PM

    APPLY

                                     31668908

    Patch 31668908 applied

    6 rows selected.

    SQL>

    3.7.5打ojvm补丁

    [oracle@prebilla 31668908]$ pwd

    /home/oracle/patch/31720776/31668908

    [oracle@prebilla 31668908]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    PREREQ session

    Oracle Home       : /home/oracle/app/oracle/product/11.2.0/db_1

    Central Inventory : /home/oracle/app/oracle/oraInventory

       from           : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc

    OPatch version    : 11.2.0.3.29

    OUI version       : 11.2.0.4.0

    Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-34PM_1.log

    Invoking prereq "checkconflictagainstohwithdetail"

    Prereq "checkConflictAgainstOHWithDetail" passed.

    OPatch succeeded.

    [oracle@prebilla 31668908]$ opatch apply

    Oracle Interim Patch Installer version 11.2.0.3.29

    Copyright (c) 2021, Oracle Corporation.  All rights reserved.

    Oracle Home       : /home/oracle/app/oracle/product/11.2.0/db_1

    Central Inventory : /home/oracle/app/oracle/oraInventory

       from           : /home/oracle/app/oracle/product/11.2.0/db_1/oraInst.loc

    OPatch version    : 11.2.0.3.29

    OUI version       : 11.2.0.4.0

    Log file location : /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-59PM_1.log

    Verifying environment and performing prerequisite checks...

    OPatch continues with these patches:   31668908 

    Do you want to proceed? [y|n]

    y

    User Responded with: Y

    All checks passed.

    Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

    (Oracle Home = '/home/oracle/app/oracle/product/11.2.0/db_1')

    Is the local system ready for patching? [y|n]

    y

    User Responded with: Y

    Backing up files...

    Applying interim patch '31668908' to OH '/home/oracle/app/oracle/product/11.2.0/db_1'

    ApplySession: Optional component(s) [ oracle.sqlj, 11.2.0.4.0 ] , [ oracle.sqlj.companion, 11.2.0.4.0 ]  not present in the Oracle Home or a higher version is found.

    Patching component oracle.javavm.server, 11.2.0.4.0...

    Patching component oracle.precomp.common, 11.2.0.4.0...

    Patching component oracle.rdbms, 11.2.0.4.0...

    Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

    Patching component oracle.javavm.client, 11.2.0.4.0...

    Patching component oracle.dbjava.jdbc, 11.2.0.4.0...

    Patching component oracle.dbjava.ic, 11.2.0.4.0...

    Patch 31668908 successfully applied.

    Log file location: /home/oracle/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2021-07-27_13-52-59PM_1.log

    OPatch succeeded.

    3.7.6更新ojvm数据字典

    [oracle@prebilla 31668908]$ cd $ORACLE_HOME/sqlpatch

    [oracle@prebilla sqlpatch]$ ls

    31668908

    [oracle@prebilla sqlpatch]$ cd 31668908/

    [oracle@prebilla 31668908]$ pwd

    /home/oracle/app/oracle/product/11.2.0/db_1/sqlpatch/31668908

    [oracle@prebilla 31668908]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 27 13:55:26 2021

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

    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Data Mining

    and Real Application Testing options

    SQL> startup upgrade

    ORACLE instance started.

    Total System Global Area 8.5516E+10 bytes

    Fixed Size                  2262656 bytes

    Variable Size            1.0737E+10 bytes

    Database Buffers         7.4625E+10 bytes

    Redo Buffers              151117824 bytes

    Database mounted.

    Database opened.

    SQL> @postinstall.sql

    SQL> shutdown immediate

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> startup

    ORACLE instance started.

    Total System Global Area 8.5516E+10 bytes

    Fixed Size                  2262656 bytes

    Variable Size            1.0737E+10 bytes

    Database Buffers         7.4625E+10 bytes

    Redo Buffers              151117824 bytes

    Database mounted.

    Database opened.

    SQL> exit

    3.7.7补丁检查:

    opatch lspatches

    opatch lsinventory

    select ACTION_TIME,ID,BUNDLE_SERIES,version from dba_registry_history;

    3.8 adg配置及测试

    3.8.1归档模式:

    SQL> select force_logging from v$database;

    FORCE_LOGGING

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

    NO

    SQL> startup mount

    SQL> alter database archivelog;

    SQL> archive log list;

    Database log mode              Archive Mode

    Automatic archival             Enabled

    Archive destination            /home/oracle/app/oracle/fast_recovery_area

    Oldest online log sequence     12

    Next log sequence to archive   13

    Current log sequence           13

    SQL> alter database open;

    Database altered.

    SQL> alter database force logging;

    Database altered.

    SQL> shutdown immediate

    SQL> startup

    归档日志删除策略设置:

    [oracle@prebillb ~]$ crontab -l

    30 12 * * * /home/oracle/clear_archivelog.sh

    [oracle@prebillb ~]$ cat /home/oracle/clear_archivelog.sh

    #!/bin/sh

    source ~/.bash_profile

    rman target / >> /home/oracle/clear_archive20210727.log << EOF

    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 8';

    exit;

    EOF

    rman主备都配置:

    [oracle@prebillb ~]$ rman target /

    Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 4 13:44:38 2021

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

    connected to target database: PREBILL (DBID=1311192676)

    RMAN> show all;

    using target database control file instead of recovery catalog

    RMAN configuration parameters for database with db_unique_name PREBILLB are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_prebill.f'; # default

    RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    new RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    new RMAN configuration parameters are successfully stored

    RMAN> show all;

    RMAN configuration parameters for database with db_unique_name PREBILLB are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

    CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

    CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

    CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

    CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_prebill.f'; # default

    RMAN>

    3.8.2准备参数文件:

    主库生成参数文件,修改指定内容后拷贝到备库:

    create pfile='/home/oracle/a.txt' from spfile;

    修改a.txt中db_unique_name='prebillb'

    根据参数文件中的路径在备库创建不存在的目录,例如:

    mkdir -p /home/oracle/app/oracle/fast_recovery_area/prebill

    mkdir -p /home/oracle/app/oracle/admin/prebill/adump

    mkdir -p /home/oracle/app/oracle/oradata/prebill

    最终参数文件参考:

    *._undo_autotune=FALSE

    *.archive_lag_target=0

    *.audit_file_dest='/home/oracle/app/oracle/admin/prebill/adump'

    *.audit_trail='NONE'

    *.compatible='11.2.0.4.0'

    *.control_files='/home/oracle/app/oracle/oradata/prebill/control01.ctl','/home/oracle/app/oracle/fast_recovery_area/

    prebill/control02.ctl'

    *.db_block_size=8192

    *.db_domain=''

    *.db_file_name_convert='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill'

    *.db_files=1000

    *.db_name='prebill'

    *.db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area'

    *.db_recovery_file_dest_size=1288490188800

    *.db_unique_name='prebilla'

    *.dg_broker_start=TRUE

    *.diagnostic_dest='/home/oracle/app/oracle'

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

    *.event='10949 trace name context forever, level 1:28401 trace name context forever, level 1:10795 trace name context forever, level 2'

    *.fal_client='prebilla'

    *.fal_server='prebillb'

    *.java_jit_enabled=TRUE

    *.log_archive_config='dg_config=(prebilla,prebillb)'

    *.log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla'

    *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla'

    *.log_archive_dest_2='service="prebillb"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="prebillb" net_timeout=30','valid_for=(all_logfiles,primary_role)'

    *.log_archive_dest_state_2='ENABLE'

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

    *.log_archive_max_processes=4

    *.log_archive_min_succeed_dest=1

    prebill.log_archive_trace=0

    *.log_file_name_convert='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill'

    *.open_cursors=300

    *.pga_aggregate_target=21474836480

    *.processes=2000

    *.recyclebin='OFF'

    *.remote_login_passwordfile='EXCLUSIVE'

    *.sga_target=85899345920

    *.standby_file_management='AUTO'

    *.undo_tablespace='UNDOTBS1'

    3.8.3密码文件拷贝到备库:

    cd $ORACLE_HOME/dbs

    scp orapwprebill 10.1.1.52:/home/oracle/app/oracle/product/11.2.0/db_1/dbs/

    3.8.4测试主备库连通性:

    两节点都操作,确保可连接:

    sqlplus sys/SYS_Passw0rd@prebilla as sysdba

    sqlplus sys/SYS_Passw0rd@prebillb as sysdba

    3.8.5备库恢复操作:

    在备库操作:

    [oracle@dgrac1 ~]$ cat b.sh

    rman target sys/SYS_Passw0rd@prebilla auxiliary sys/SYS_Passw0rd@prebillb log /home/oracle/rman-`date +%Y%m%d-%H%M`.log <<EOF

    run

    {

    allocate channel cl1 type disk;

    allocate auxiliary channel c1 type disk;

    duplicate target database for standby from active database nofilenamecheck;

    release channel c1;

    release channel cl1;

    }

    EOF

    [oracle@dgrac1 ~]$ chmod +x dg.sh

    [oracle@dgrac1 ~]$ nohup ./dg.sh &

    查看日志:tail -f rman-20191119-1310.log

    3.8.6主备库配置dg参数:

    主库:

    alter system set log_archive_config='DG_CONFIG=(prebilla,prebillb)' scope=both;

    alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla' scope=spfile;

    alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebilla scope=spfile;

    alter system set log_archive_dest_2='SERVICE=prebillb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prebillb' scope=both;

    alter system set fal_server='prebillb' scope=both;

    alter system set fal_client='prebilla' scope=both;

    alter system set standby_file_management=AUTO scope=both;

    alter system set DB_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*'; 

    alter system set LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';

    备库:

    alter system set log_archive_config='DG_CONFIG=(prebilla,prebillb)' scope=both;

    alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prebillb' scope=spfile;

    alter system set log_archive_dest_2='SERVICE=prebilla LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prebilla' scope=both;

    alter system set fal_server='prebilla' scope=both;

    alter system set fal_client='prebillb' scope=both;

    alter system set standby_file_management=AUTO scope=both;

    alter system set DB_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*'; 

    alter system set LOG_FILE_NAME_CONVERT='/home/oracle/app/oracle/oradata/prebill','/home/oracle/app/oracle/oradata/prebill' scope=spfile sid='*';

    3.8.7主备库测试同步状态:

    备库

    startup

    启动到实时应用日志状态:

    recover managed standby database using current logfile disconnect from session;

    检查状态视图:

    select process ,client_process , sequence# ,status from v$managed_standby ;

    select database_role ,protection_mode ,protection_level ,open_mode from v$database;

    主库

    startup

    create user jyc identified by jyc;

    grant dba to jyc;

    conn jyc/jyc@prebilla

    create table test (id int);

    insert into values(1);

    commit;

    备库:

    conn jyc/jyc@prebillb

    select * from test;

    3.8.8手动切换dg

    DG主备切换测试

    主库上查询:

    SQL> select status ,gap_status from v$archive_dest_status where dest_id in (1,2);

    STATUS    GAP_STATUS

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

    VALID

    VALID     NO GAP

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS

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

    TO STANDBY

    此处的信息一定要显示是“to standby”

    备库信息:

    SQL> select status,gap_status from v$archive_dest_status where dest_id in (1,2);

    STATUS    GAP_STATUS

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

    VALID

    VALID     NO GAP

    -- 主库的执行:

    SQL> alter database commit to switchover to physical standby with session shutdown;--应该自动关闭库了。

    Database altered.

    SQL> conn / as sysdba

    SQL> shutdown abort;

    ORACLE instance shut down.

    SQL> startup

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS

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

    TO PRIMARY

    --- 备库上操行:

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS

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

    TO PRIMARY

    SQL> alter database commit to switchover to primary with session shutdown;

    Database altered.

    SQL> select open_mode from v$database;

    OPEN_MODE

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

    MOUNTED

    SQL> alter database open;

    Database altered.

    ---- 在之前的主库上执行:

    SQL> select open_mode from v$database;

    OPEN_MODE

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

    READ ONLY

    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

    3.9 dg broker配置及测试

    3.9.1配置命令参考:

    alter system set dg_broker_start=true scope=both sid='*';--启用dg broker,两节点都得设置

    alter system reset log_archive_dest_2 scope=both sid='*';--12c以上需要设置

    以下内容主节点操作即可:

    dgmgrl sys/SYS_Passw0rd@prebilla

    create configuration adg as primary database is prebilla connect identifier is prebilla;

    add database prebillb as CONNECT IDENTIFIER IS prebillb MAINTAINED AS PHYSICAL;

    show CONFIGURATION verbose

    show database verbose prebilla

    show database verbose prebillb

    SWITCHOVER TO prebillb;

    ENABLE CONFIGURATION;

    修改属性参数:StaticConnectIdentifier

    edit database prebilla set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prebilla)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prebill)(INSTANCE_NAME=prebill)(SERVER=DEDICATED)))';

    edit database prebillb set property StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prebillb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prebill)(INSTANCE_NAME=prebill)(SERVER=DEDICATED)))';

    注意此处的service_name和3.5监听配置中的一致。

    避免问题:https://sqlora.blog.csdn.net/article/details/119177917

    3.9.2切换操作

    dgmgrl sys/SYS_Passw0rd@prebilla

    switchover to prebillb

     

    3.10 adg服务IP配置

    主备库都添加,每三秒检测一次。

     

    [root@prebilla keepalived]# crontab -l

    * * * * * /etc/keepalived/ip.sh

    [root@prebilla keepalived]# cat  /etc/keepalived/ip.sh

    step=3

    for ((i = 0; i < 60; i = (i + step))); do

        $(/etc/keepalived/db_ip.sh)

        sleep $step

    done

    exit 0

    [root@prebilla keepalived]# cat /etc/keepalived/db_ip.sh

    #!/bin/bash

    dbstats=`ps -ef | grep ora_smon | grep -v grep | wc -l`

    dgstats=`ps -ef | grep ora_mrp | grep -v grep | wc -l`

    ip=`/usr/sbin/ip a|grep bond0:1|wc -l`

    if [[ "${dbstats}" -gt 0 ]] && [[ "${dgstats}" -eq 0 ]]; then

        if [[ "${ip}" -eq 0 ]]; then

        /usr/sbin/ifconfig bond0:1 10.1.1.53 netmask 255.255.255.0 up

        fi

    else

        if [[ "${ip}" -gt 0 ]]; then

        /usr/sbin/ifconfig bond0:1 down

        fi

    fi

    [oracle@prebilla admin]$ vi tnsnames.ora

    PREBILL =

      (DESCRIPTION =

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

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = prebill)

        )

      )

    "tnsnames.ora" 149L, 3017C written      

    执行3.9.2切换操作测试效果满足要求。

                                                             

    [oracle@prebilla admin]$ sqlplus jyc/jyc@prebill

    SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 28 16:49:42 2021

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

    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

    With the Partitioning, Oracle Label Security, OLAP, Data Mining

    and Real Application Testing options

    SQL> show parameter name;

    NAME                                 TYPE        VALUE

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

    cell_offloadgroup_name               string

    db_file_name_convert                 string      /home/oracle/app/oracle/oradat

                                                     a/prebill, /home/oracle/app/or

                                                     acle/oradata/prebill

    db_name                              string      prebill

    db_unique_name                       string      prebilla

    global_names                         boolean     FALSE

    instance_name                        string      prebill

    lock_name_space                      string

    log_file_name_convert                string      /home/oracle/app/oracle/oradat

                                                     a/prebill, /home/oracle/app/or

    NAME                                 TYPE        VALUE

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

                                                     acle/oradata/prebill

    processor_group_name                 string

    service_names                        string      prebilla

    SQL> exit

    展开全文
  • 查看ADG主备库状态

    千次阅读 2021-07-09 00:58:36
    1 服务器参数文件 2 静态监听 1 主库进程 2 库进程 3 传输速率 4 同步延迟 LGWR ARCH

    1 进程

    进程状态

    IDLE - 进程未执行任何活动
    
    CONNECTED - 建立到主数据库的网络连接
    ALLOCATED - 进程处于活动状态,但当前未连接到主数据库
    
    WRITING - 进程正在将重做数据主动写入已存档的重做日志
    
    CLOSING - 进程已完成存档,正在关闭已存档的重做日志
    WAIT_FOR_LOG - 进程正在等待已存档的重做日志完成
    WAIT_FOR_LOG - 进程正在等待已存档的重做日志完成
    APPLYING_LOG - 进程正在将已存档的重做日志主动应用到备用数据库
    

    1.1 主库进程

    LNS(ASYNC Redo Transport process) - 异步重做传输进程
    DGRD(Generic Oracle Data Guard process)
    ARCH(Archiver process) - 归档进程
    

    在这里插入图片描述
    LGWR
    在这里插入图片描述
    ARCH
    在这里插入图片描述

    1.2 备库进程

    RFS(Remote file server)-接收进程
    MRP0(Detached recovery server process)-恢复进程
    

    在这里插入图片描述

    1.3 传输速率

    在这里插入图片描述

    1.4 同步延迟

    Transport lag: 是一种度量,表示到备用数据库的redo传输滞后于在主数据库上生成redo的程度。如果备用数据库上有一个或多个重做间隔,则计算传输延迟,就像在最早的重做间隔开始之后没有收到重做一样。
    Apply lag:指由于向备用数据库传播和应用redo的延迟,备用数据库中的数据滞后于主数据库中的数据的程度。此值仅与应用实例相关。
    在这里插入图片描述

    2 视图

    • V$MANAGED_STANDBY
      显示与Data Guard环境中的物理备用数据库相关的某些Oracle数据库进程的当前状态信息。实例关闭后,此视图不会持久存在。
    • V$DATAGUARD_STATS
      在备用数据库上查询时,显示有关Oracle Data Guard度量的信息。在主数据库上查询时不返回任何行。
    • V$RECOVERY_PROGRESS
      可以用来跟踪数据库恢复操作,以确保它们不会停止,还可以用来估计完成正在进行的操作所需的时间。

    3 MRP0进程

    查看MRP0进程

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

    关闭MRP进程(停止应用日志)

    alter database recover managed standby database cancel;
    

    开启MRP进程(开启应用日志)

    alter database recover managed standby database using current logfile disconnect from session;
    
    展开全文
  • 3 主库切换归档确保最新数据同步至ADG端 alter system checkpoint; alter system switch logfile; alter system checkpoint; alter system switch logfile; alter system archive log current; alter

    0 检查状态

    备库查询
    select process, pid, status, thread#,sequence#,blocks from v$managed_standby;
    
    主库切归档
    alter system switch logfile;
    
    备库查询
    select process, pid, status, thread#,sequence#,blocks from v$managed_standby;
    

    1 停应用

    2 删除进程

    ps -ef|grep  |grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
    

    3 主库切换归档确保最新数据同步至ADG端

    alter system checkpoint;
    alter system switch logfile;
    alter system checkpoint;
    alter system switch logfile;
    alter system archive log current;
    alter system switch logfile;
    alter system checkpoint;
    alter system switch logfile;
    

    4 主库 -> 备库

    select open_mode,switchover_status,database_role from v$database;
    alter database commit to switchover to physical standby with session shutdown;
    

    主库会关闭!

    5 备库 -> 主库

    select switchover_status from v$database;
    alter database commit to switchover to primary with session shutdown; 
    
    alter database open;
    select open_mode,database_role from v$database;
    

    6 检查状态

    备库查询
    select process, pid, status, thread#,sequence#,blocks from v$managed_standby;
    
    主库切归档
    alter system switch logfile;
    
    备库查询
    select process, pid, status, thread#,sequence#,blocks from v$managed_standby;
    
    展开全文
  • ADG主备库切换 - Failover

    千次阅读 2021-07-10 23:23:25
    主库发生故障时,将库提升为主库。
  • select value from v$dataguard_stats where name=‘apply lag’;
  • oracle11g ADG主备切换1、主库SQL> select name,open_mode,switchover_status from v$database;NAME OPEN_MODE SWITCHOVER_STATUS--------- -------------------- --------------------PROD1 READ WRITE TO ...
  • oracle adg主备手工切换11g和12c通用

    千次阅读 2018-10-16 17:00:11
    主备库之前都正常的情况下,当主库做了上面的操作然后重启到mount阶段时,备库的switchover_status的状态应该是TO PRIMARY状态,此时就可以直接可以执行下面的命令切换成主库。 注意备库的SWITCHOVER_STATUS...
  • 天萃荷净Oracle 11G RAC TO 11G RAC ADG 主备库切换SWITCHOVER过程Oracle 11G RAC主库环境配置准备工作SQL> select inst_id,database_role,OPEN_MODE from gv$database;INST_ID DATABASE_ROLE OPEN_MODE---------...
  • 主备库切换后,归档日志不能自动传送分析 在备库上运行: show parameter log_archive_config --alter system set log_archive_config='DG_CONFIG=(sg02,sg01)' scope=spfile; SQL> SELECT MESSAGE FROM V$...
  • 上篇讲了一起ADG主备切换异常的故障处理,最近又遇到一个ADG的问题,做下分享。事情是这样的,一哥们急急忙忙的跑过来说:“魏大湿,我手上的ADG实时同步死活开不起来!”“之前实时同步是好的么?”“是好的,都...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,107
精华内容 442
关键字:

adg主备