centos7 系统 oracle 12c dataguard 安装
访问量:1351
创建时间:2021-12-29
oracle 集群 dataguard容灾(1+1，一主一备)；oracle (2+1，rac+单机DG)；oracle (2+2，rac+rac(DG)),MAA最高可用架构。前两种使用较多。
- Oracle日志
Redo\Undo\ArchivedRedoLog
Oracle的日志(Log)是记载数据库修改信息的数据结构,日志文件是日志记录的汇集。
日志完整记录了数据库所有活动,其中不仅包含有数据的历史记录:新建(Insert)、更新(Update)和删除(Delete),也包含有事务的历史记录:开始(Begin)、提交(Commit)和回滚(Rollback)。
日志有两种类型,重做(Redo)和撤销(Undo)。
撤销日志对应于即时的数据库修改。所有的数据修改操作直接作用在原表上,日志则记录了修改的过程,当要撤销某个操作时,通过把日志中的历史记录写回到原表,就可以撤销该操作,把数据库回退之前的状态。
重做日志则通过推迟数据库的修改,来实现相同的功能。通过把事务的所有实际操作推迟到事务提交后才进行,在事务提交后根据日志记录中对数据库数据的修改信息对数据文件进行写入,从而实现事务的原子性。如果事务在未提交之前发生了故障,则当前事务所做的修改只写到了日志文件中而没有被写到数据文件中,这时数据库恢复子系统会直接忽略日志中的信息,不去修改数据文件。12另一方面,如果在事务提交后发生了故障,可能事务已经执行完成,但是只写了日志文件而没有写数据文件,这时恢复子系统从日志中可以判断那些动作需要重做,然后利用日志中记录的对应信息进行数据文件的重写。
Oracle日志文件组成Oracle的日志系统是基于重做日志的,分为两类:联机日志(OnlineRedoLog)和归档日志(ArchivedRedoLog)。联机日志是每个Oracle数据库必须有的日志,它支持着数据库的正常运作。一旦相关联的数据库发生了变更,那些记录就会被写入联机日志。其中既有变更前的旧数据又有变更后的新数据。联机日志由两个以上的文件组成,它们循环地记录着数据库当前的变更,当一个日志文件写满后,日志管理进程会使用另一个日志文件,直到所有的日志文件都写了一遍后,再重新写第一个文件,开始新的一轮循环。通过配置Oracle数据库,可以使联机日志文件在写满后以归档的形式保存起来,这就是归档日志。在归档日志中通过日志序列号LSN(LogSequenceNumber)来唯一确定每一个联机日志。归档日志记录的是变更记录的历史,联机日志记录的是当前最近的变更记录。通过合理使用这两类的日志文件,可以把Oracle数据库恢复到任何一个历史状态。
Oracle日志系统运行机制Oracle在运行时会把数据文件以块(Block)为单位加载到缓存,所有的操作都发生在缓存中,当操作完毕后再以块的形式写到文件中。Oracle重做日志记录了所有的发生在缓存中的变化,其中包括有数据、索引和回滚段的变更。而且不论这个变更提交与否,都会写重做日志。一个日志实体包含一组变更向量,它们记录了对数据库的原子操作。Oracle的后台进程LGWR(LogWriterProcess)管理着日志的保存。
它每隔三秒或是在遇到事务递交的时候,把在缓存中生成的日志。
#通过v$log视图查询在线日志的总体信息
SQL> SELECT * FROM v$log;
#通过v$logfile视图查询在线日志文件信息：
SQL> SELECT * FROM v$logfile ORDER BY group#;
Alert log files--系统报警日志，Trace files--跟踪日志(用户和进程) ,这里不做介绍。
dataguard 通过在主节点和备份节点间通过日志同步来保证数据的同步。dataguard分为物理standby和逻辑standby。物理standby通过块拷贝方式同步。逻辑standby通过sql语句方式同步，逻辑standby接收primary库的redo log并转换成sql语句，在standby库上执行sql apply实现同步。物理standby接收并应用primary数据库的redo log以介质恢复的方式redo apply实现同步。
oracle dataguard 搭建信息
| ip--OS | Hostname | 角色 | DB_NAME | DB_UNIQUE_NAME | Instance_Name | 安装软件 |
|---|---|---|---|---|---|---|
| 192.168.0.57--centos7 | oracle57 | primary | prod | orcl_p | orcl_p(实例名可以相同) | 软件\监听\数据库实例 |
| 192.168.0.58--centos7 | oracle58 | standby | prod | orcl_s | orcl_s(实例名可以相同) | 软件\监听 |
数据库基本安装
192.168.0.57
###配置主机名(一个机器oracle57 一个配置为orale58,)
[root@oracle57 ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.57 oracle57
192.168.0.58 oracle58
[root@oracle57 ~]# hostname
oracle57
###安装依赖包
[root@oracle57 ~]# yum -y install rlwrap binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*i686 compat-libstdc++-33 gcc gcc-c++ glibc glibc*.i686 glibc-devel glibc-devel*.i686 ksh libaio libaio*.i686 libaio-devel libgcc libgcc*.i686 libstdc++ libstdc++*.i686 libstdc++-devel libXi libXi*.i686 libXtst sysstat unixODBC unixODBC*.i686 unixODBC-devel unixODBC-devel*.i686
###添加用户，用于安装与运行数据库
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
useradd -u 54321 -g oinstall -G dba,oper oracle
passwd oracle
###修改内核参数，不修改安装验证会失败
[root@oracle57 ~]# vim /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096 #最小的segment的大小，页大小
kernel.shmall = 3145728 # 共享内存总页数12 ，shmmax/shmmni=shmall
kernel.shmmax = 12884901888 # 内存大小的80-90%，单个段允许的大小，12G内存是12*1024*1024*1024
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
[root@oracle57 ~]# sysctl -p
###配置limit
[root@oracle57 ~]# cat >> /etc/security/limits.conf << EOF
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft stack 102400
oracle hard stack 327680
oracle hard memlock 134217728
oracle soft memlock 134217728
EOF
### 关闭透明大页(透明大页动态分配内存会造成性能故障，标准大页内存在启动时预先分配，并且运行时不会改变)(标准Huge pages与AMM冲突，memory_target=SGA+PGA;11g以后可以配置memory_target来动态配置sga和pga,但是这个功能和标准大页冲突，开启标准大页不使用AMM功能，建议单独设置sga和pga;标准大页与ASMM[Auto Shared Memory management]可以一起使用，可以使用ipcs命令查看)
```shell
#查看系统已经开启透明大页
[root@oracle57 u01]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
#查看透明大页大小
[root@oracle57 u01]# cat /proc/meminfo | grep AnonHugePages
AnonHugePages: 8192 kB
#添加transparent_hugepage=never
[root@oracle57 u01]# vim /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto spectre_v2=retpoline rd.lvm.lv=centos/root rd.lvm.lv=centos/swap rhgb quiet transparent_hugepage=never"
[root@oracle57 u01]# grub2-mkconfig -o /boot/grub2/grub.cfg
##重启系统或者执行下面命令
[root@oracle57 u01]# echo "never" > /sys/kernel/mm/transparent_hugepage/enabled
[root@oracle57 u01]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
配置标准大页
#配置内存锁定，允许oracle用户使用的最大内存，单位kb
[root@oracle57 u01]# vim /etc/security/limits.conf
oracle hard memlock 12582912
oracle soft memlock 12582912
#配置shm,可改为与内存一样大
[root@oracle57 u01]# df -h | grep shm
tmpfs 7.8G 0 7.8G 0% /dev/shm
[root@oracle57 ~]# vim /etc/fstab
tmpfs /dev/shm/ tmpfs defaults,size=12g 0 0
#重新挂载
[root@oracle57 ~]# mount -o remount /dev/shm
##/etc/security/limits.conf的memlock 12582912/2048=6144
[root@oracle57 ~]# echo "vm.nr_hugepages = 6144" > /etc/sysctl.d/97-oracledatabase-sysctl.conf
##重启系统
[root@oracle57 ~]# reboot
[root@oracle57 ~]# cat /proc/meminfo
HugePages_Total: 6144
HugePages_Free: 6144
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
###ipcs用于查看系统用户使用的Shared Memory Segments情况(当数据库运行时)
[root@oracle57 ~]# ipcs -m
创建oracle安装目录
mkdir -p /u01/app/oracle/oradata
chmod -R 775 /u01
chown -R oracle:oinstall /u01
配置环境变量
[root@oracle57 ~]# vim /etc/profile
export TMP=/tmp
export TMPDIR=$TMP
#export ORACLE_HOSTNAME=oracle57 #主机名
#export ORACLE_UNQNAME=prod
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1
export ORACLE_SID=prod
export PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
#export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
[root@oracle57 ~]# source /etc/profile
vnc安装
###安装vnc
yum install tigervnc tigervnc-server -y
###进入oracle设置vnc 和密码
[root@oracle58 ~]# su - oracle
Last login: Wed Dec 29 10:59:09 CST 2021 on pts/0
###开启编号为:1的桌面
[oracle@oracle58 ~]$ vncserver :1
You will require a password to access your desktops.
Password:
Verify:
Would you like to enter a view-only password (y/n)? n
A view-only password is not used
New 'oracle58:1 (oracle)' desktop is oracle58:1
Creating default startup script /home/oracle/.vnc/xstartup
Creating default config /home/oracle/.vnc/config
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/oracle58:1.log
#####关闭编号为:1的vncserver ,这一步不需要执行 (vncserver -kill :1)
登陆远程桌面
解压
cd /u01/
mv /root/linuxx64_12201_database.zip /u01/
unzip linuxx64_12201_database.zip
chown -R oracle:oinstall /u01/
图形化安装
As a root user, execute the following script(s):
1. /u01/app/oracle/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/12.2.0.1/db_1/root.sh
在root用户下运行上面提示的2个脚本(执行完这两个脚本才能点ok)
[root@oracle58 ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@oracle58 ~]# /u01/app/oracle/product/12.2.0.1/db_1/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/12.2.0.1/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] :
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.2.0.1/db_1/install/root_oracle58_2021-12-29_11-26-41-996977025.log
Finished installing Oracle Trace File Analyzer (TFA)
配置监听和dbca安装数据库
监听安装配置
dbca安装数据库
192.168.0.58 只安装数据库软件和监听，过程省略
主库配置
开启强制日志
###开启强制日志
[oracle@oracle57 ~]$ sqlplus / as sysdba
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select name,log_mode,force_logging from v$database;
NAME LOG_MODE FORCE_LOGGING
--------- ------------ ---------------------------------------
PROD NOARCHIVELOG YES
主库开启归档
[oracle@oracle57 ~]$ mkdir /u01/arch
[oracle@oracle57 ~]$ sqlplus / as sysdba
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u01/arch' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 8103395328 bytes
Fixed Size 8639856 bytes
Variable Size 1442843280 bytes
Database Buffers 6643777536 bytes
Redo Buffers 8134656 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
配置主库初始化参数
alter system set db_unique_name='orcl_p' scope=spfile; 需要重启数据库
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_p,orcl_s)' scope=both; DG_CONFIG=(orcl_p,orcl_s) 是主备库的db_unique_name,这个不需要重启
alter system set log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_p' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=tnsorcl_s LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_s' scope=both ; 这个是主库要把日志传到备库的路径,SERVICE=tnsorcl_s这个后面配置，LGWR SYNC AFFIRM用于实时同步的参数
alter system set standby_file_management=AUTO scope=both ; 自动管理，当主库添加文件，备库自动添加文件
alter system set fal_client='tnsorcl_p' scope=both ; 本地tns,拉取日志，在备库上面设置fal_client，FAL_SERVER
alter system set FAL_SERVER='tnsorcl_s' scope=both ; 对端的tns
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl_p/','/u01/app/oracle/oradata/orcl_s/' scope=spfile sid='*'; 用于主备切换的参数，不切换可以不设置
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl_p/','/u01/app/oracle/oradata/orcl_s/' scope=spfile sid='*'; 用于主备切换的参数，不切换可以不设置
SQL> shutdown immediate;
SQL> startup
配置tns
[oracle@oracle57 ~]$ cd $ORACLE_HOME/network/admin
[oracle@oracle57 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@oracle57 admin]$ vim tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
TNSORCL_P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle57)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_p)
)
)
TNSORCL_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle58)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle57)(PORT = 1521))
######测试
[oracle@oracle57 admin]$ tnsping tnsorcl_p
[oracle@oracle57 admin]$ tnsping tnsorcl_s
备库参数设置
###密码文件cp
[oracle@oracle57 dbs]$ scp orapwprod oracle58:/u01/app/oracle/product/12.2.0.1/db_1/dbs/
###配置tns
[root@oracle58 dbs]# cd $ORACLE_HOME/network/admin
[oracle@oracle58 admin]$ vim tnsnames.ora
TNSORCL_P =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle57)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_p)
)
)
TNSORCL_S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle58)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod)
)
)
LISTENER_PROD =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle58)(PORT = 1521))
配置静态监听
[oracle@oracle58 admin]$ vim listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle58)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle58)(PORT = 1522))
)
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod)
(SID_NAME = prod)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
)
)
[oracle@oracle58 admin]$ lsnrctl start listener1
pfile
[oracle@oracle58 dbs]$ touch initprod.ora
db_name='prod'
sga_target=7728m
pga_aggregate_target=2575m
db_unique_name='orcl_s'
compatible='12.2.0'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_p,orcl_s)'
audit_file_dest='/u01/app/oracle/admin/orcl_s/adump'
control_files='/u01/app/oracle/oradata/prod/control01.ctl','/u01/app/oracle/oradata/prod/control02.ctl'
log_archive_dest_1='LOCATION=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_s';
LOG_ARCHIVE_DEST_2='SERVICE=tnsorcl_p LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_p'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/prod'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/prod'
standby_file_management='AUTO'
remote_login_passwordfile='EXCLUSIVE'
fal_client='tnsorcl_s'
FAL_SERVER='tnsorcl_p'
##创建spfile
[oracle@oracle58 dbs]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount
tns测试
##在两台机器上测试
tnsping tnsorcl_s
tnsping tnsorcl_p
sqlplus sys/oracle123@tnsorcl_s as sysdba
sqlplus sys/oracle123@tnsorcl_p as sysdba
###可以用select status from v$instance;查看登陆的机器
使用duplicate在线做主从
[oracle@oracle57 ~]$ rman target sys/oracle123@tnsorcl_p auxiliary sys/oracle123@tnsorcl_s
RMAN> duplicate target database for standby from active database nofilenamecheck;
如果失败的话，要把实例重新启动到nomount状态，备库的数据文件/u01/app/oracle/oradata/prod/下都删除
添加standby日志组，并开启同步
###在主库增加standby redologfile文件(如果redo log有N组，standby redo log则需要N+1组；standby redo log的文件大小与primary 数据库online redo log 文件大小相同；
[oracle@oracle58 ~]$ sqlplus / as sysdba
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 13 209715200 512 1 NO INACTIVE 1942002 26-DEC-21 1992782 27-DEC-21 0
2 1 14 209715200 512 1 NO INACTIVE 1992782 27-DEC-21 2052748 28-DEC-21 0
3 1 15 209715200 512 1 NO CURRENT 2052748 28-DEC-21 1.8447E+19 0
SQL> select * from v$logfile;
###备库增加standbylogfile：
alter database add standby logfile group 4 '/u01/app/oracle/oradata/prod/stdredo4.log' size 200m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/prod/stdredo5.log' size 200m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/prod/stdredo6.log' size 200m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/prod/stdredo7.log' size 200m;
###查看standy日志组信息
SQL> select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
GROUP# SEQUENCE# STATUS BYTES/1024/1024
---------- ---------- ---------- ---------------
4 0 UNASSIGNED 200
5 0 UNASSIGNED 200
6 0 UNASSIGNED 200
7 0 UNASSIGNED 200
在备库开启同步
SQL> alter database open;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
##开启redo应用(实时同步，commit就同步)在备库操作
##在12.1版本以后，USING CURRENT LOGFILE字句弃用
##弃用此命令，这个是11g的alter database recover managed standby database using current logfile disconnect from session;
##弃用此命令，这个是11g的alter database recover managed standby database using current logfile disconnect；
##oracle 12c开启实时同步 alter database recover managed standby database disconnect;
##oracle 12c停止实时同步 alter database recover managed standby database cancel;
###开启redo应用(日志切换才会同步,主库日志切换了，才同步到备库，主库可以执行alter system switch logfile;切换日志)
SQL> alter database recover managed standby database disconnect;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> set linesize 8000
SQL> select name,open_mode,database_role,protection_mode,protection_level from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- -------------------- --------------------
PROD READ ONLY WITH APPLY PHYSICAL STANDBY物理 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE最大性能
在主库查询
SQL> select name,open_mode,database_role,protection_mode,protection_level from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- -------------------- --------------------
PROD READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
更改保护模式
alter database set standby database to maximize performance;最大性能，
alter database set standby database to maximize availability;最大可用,优先同步保证数据，一般生产也是最大可用
alter database set standby database to maximize protection;最大保护
##主从都执行，改为最大可用。
alter database set standby database to maximize availability;
##最大可用要配合log_archive_dest_2的参数
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=tnsorcl_s LGWR SYNC AF
FIRM VALID_FOR=(ONLINE_LOGFIL
ES,PRIMARY_ROLE) DB_UNIQUE_NAM
E=orcl_s
测试同步是否正常
##主库
SQL> create table t(id int);
SQL> insert into t values(1);
SQL> commit;
###在备库查询
SQL> select * from t;
ID
----------
1
DG启停顺序
启动--先备库后主库
关闭--先主库，后备库
故障处理
检查主备节点配置 tns配置 fal_client,fal_server 备库出于实时应用模式 查看相关视图 v$managed_standby(备库查看) RFS 日志传输进程(备库查看) MRPO:日志应用进程(备库查看)
v$archive_dest (主库)status为valid有效的，error字段错误信息 v$archive_dest_status(主库)
switchover主备互换
#2个角色primary和standby
#主库
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
#备库
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
- 切换，switchover主备互换角色，不会丢失数据；failover故障转移，主库不可用了，把备库变成主库，最大保护模式和最大可用模式下，可以保证数据不丢失。
在主库检查配置文件，增加standby redo log配置
###查看配置信息
SQL> select * from v$standby_log;
###在主库同样添加standby redo log配置，为主库切换为备库准备。
alter database add standby logfile group 4 '/u01/app/oracle/oradata/prod/stdredo4.log' size 200m;
alter database add standby logfile group 5 '/u01/app/oracle/oradata/prod/stdredo5.log' size 200m;
alter database add standby logfile group 6 '/u01/app/oracle/oradata/prod/stdredo6.log' size 200m;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/prod/stdredo7.log' size 200m;
如果主备的数据库安装路径不一致需要设置
###查看参数，目前主库的参数为空，只有备库的这两个参数配置了，这里主从切换，主库也要配置，为切换为备库使用
SQL> show parameter DB_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
pdb_file_name_convert string
SQL> show parameter LOG_FILE_NAME_CONVERT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string
###前面的路径是对面数据库的路径，后面是本地的路径，如果有多个路径不同要设置多对。(db_file_name_convert数据文件路径，log_file_name_convert联机重做日志路径)
alter system set db_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/prod/' scope=spfile ;
alter system set log_file_name_convert='/u01/app/oracle/oradata/prod/','/u01/app/oracle/oradata/prod/' scope=spfile ;
##需要重启oracle数据库
SQL> shutdown immediate;
SQL> startup
###fal_server 是对方的，fal_client是自己；这个也是在主库设置(备库之前已经配置了,与主库相反)
SQL> show parameter fal;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string tnsorcl_p
fal_server string tnsorcl_s
主库通过视图检查，TO STANDBY表示可以做切换；session active状态表示有会话连接(也可以做切换)。其他表示有故障，要先处理故障。
###主库查询
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PRIMARY TO STANDBY
备库是NOT ALLOWED是正常，表示备库现在不允许操作，要先在主库切换，再切换备库。
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PHYSICAL STANDBY NOT ALLOWED
验证是否有gap,还可以通过验证insert数据来看数据同步是否有间隙，dest_id=2是连接备库的路径。
SQL> select status,gap_status from v$archive_dest_status where dest_id=2;
STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP
开始切换
###主库执行
###12c新语法,orcl_s 是对面数据库的db_unique_name；verify是检查能否切换
alter database switchover to orcl_s verify;
#下面这个如果切换不了可以加force强制切换，11g老语法主库备库都要执行切换命令，12c只在主库执行即可
alter database switchover to orcl_s ;
alter database switchover to orcl_s force;
切换完后主库是shutdown状态。备库是mount状态。
##老的备库的操作
[oracle@oracle57 ~]$ sqlplus / as sysdba
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
SQL> set linesize 8000
SQL> select name,open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- -------------------- --------------------
PROD READ WRITE PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY TO STANDBY
####老的主库操作
[oracle@oracle57 ~]$ sqlplus / as sysdba
SQL> startup
SQL> set linesize 8000
SQL> select name,open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- -------------------- --------------------
PROD READ ONLY PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY RECOVERY NEEDED
###老主库打开应用日志
SQL> alter database recover managed standby database disconnect;
####状态变为READ ONLY WITH APPLY
SQL> select name,open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- -------------------- --------------------
PROD READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY NOT ALLOWED
测试
###新主库
SQL> insert into t values(23);
1 row created.
SQL> commit;
Commit complete.
###新备库
SQL> select * from t;
ID
----------
1
3233
23
切换至此已经完成了，下面讲一下11g旧的语法。
###主库操作
###with session shutdown 是11g的语法；在12c中可以使用去掉with session shutdown的语法来切换，11g的老语法在12c要变化使用，或者直接使用12c的语法。
alter database commit to switchover to physical standby with session shutdown;(会话连接着,11g的老语法)
#执行完了实例关闭。
alter database commit to switchover to physical standby ;(12c可以用此语法)
#启动
startup
#查询状态
select name,open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
#开启日志实时应用
alter database recover managed standby database disconnect;
###备库切成主库
##检查状态
select name,open_mode,database_role,protection_mode,protection_level,switchover_status from v$database;
#切换
alter database commit to switchover to primary with session shutdown;(11g的老语法)
alter database commit to switchover to primary;(12c可以用此语法)
##重启
shutdown immediate
startup
生产环境切换时间可能很长
先刷缓存
alter flush buffer_cache;
alter system checkpoint;
shutdown immediate
重启实例做切换。这样切换时间应该很短。业务应用停掉。
登陆评论:
使用GITHUB登陆