devbox
黑客灵魂
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

oracle12c dataguard搭建及切换_oracle12c dg 切换

作者：黑客灵魂 | 2024-08-13 20:28:13

赞

踩

oracle12c dg 切换


准备工作：
主节点(centos7.9 + oracle 12.2): 安装数据库软件，建库(实例名 lo666)
从节点(centos7.9 + oracle 12.2): 安装数据库软件，不建库 (后续从主库同步，实例名stdlo777)


1. 创建用户和用户组（两个节点，root用户执行）

  1. groupadd oinstall
  2. groupadd dba
  3. groupadd oper
  4. useradd -g oinstall -G dba,oper oracle
  5. echo 'oracle' | passwd --stdin oracle

2. 创建数据库安装目录和数据存储目录（两个节点，root用户执行）

  1. mkdir -p /u01/oracle
  2. chown -R oracle.oinstall /u01
  3. chmod -R 775 /u01
  4. mkdir -p /data/arch
  5. chown -R oracle.oinstall /data

3. 安装依赖包（两个节点，root用户执行）

yum install libstdc++ libstdc++-devel gcc ksh glibc-devel libaio libaio-devel gcc-c++ compat-libcap1 sysstat smartmontools binutils rlwrap unzip 

4. 设置oracle用户的环境变量
vi .bash_profile  主节点,oracle用户添加如下内容

  1. export ORACLE_BASE=/u01
  2. export ORACLE_HOME=$ORACLE_BASE/oracle
  3. export ORACLE_SID=lo666
  4. export PATH=$ORACLE_HOME/bin:$PATH
  5. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  6. export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
  7. stty erase ^H
  8.  
  9. alias sysdba='rlwrap sqlplus / as sysdba'
  10. alias rmant='rlwrap rman target /'


vi .bash_profile  从节点，oracle用户添加如下内容

  1. export ORACLE_BASE=/u01
  2. export ORACLE_HOME=$ORACLE_BASE/oracle
  3. export ORACLE_SID=stdlo777
  4. export PATH=$ORACLE_HOME/bin:$PATH
  5. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  6. export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
  7. stty erase ^H
  8.  
  9. alias sysdba='rlwrap sqlplus / as sysdba'
  10. alias rmant='rlwrap rman target /'

5. 设置swap（两个节点，root用户执行）

  1. dd if=/dev/zero of=/opt/swap.file bs=1G count=10
  2. mkswap /opt/swap.file
  3. swapon /opt/swap.file

设置swap开机自动挂载（两个节点，root用户执行）

  1. chmod +x /etc/rc.d/rc.local
  2. swapon /opt/swap.file

6. 配置hosts（两个节点，root用户执行）

  1. vi /etc/hosts
  2. 192.168.1.225 dg225
  3. 192.168.1.226 dg226

7. 配置sysctl.conf和limits.conf （两个节点，root用户执行）

  1. vi /etc/sysctl.conf
  2. kernel.sem=250 32000 100 128
  3. fs.aio-max-nr = 1048576
  4. fs.file-max = 6815744
  5. kernel.shmall = 2097152
  6. kernel.shmmax = 6208434176
  7. kernel.shmmni = 4096
  8. kernel.sem = 250 32000 100 128
  9. net.ipv4.ip_local_port_range = 9000 65500
  10. net.core.rmem_default = 262144
  11. net.core.rmem_max = 4194304
  12. net.core.wmem_default = 262144
  13. net.core.wmem_max = 1048576

  1. vi /etc/security/limits.conf
  2. oracle  soft    nofile  65536
  3. oracle  hard    nofile  65536
  4. oracle  soft    nproc   2047
  5. oracle  hard    nproc   16384
  6. oracle  soft    stack   10240

8. 重启系统（两个节点，root用户执行）
reboot

9. 解压数据库（两个节点，root用户执行）
cd /opt
unzip Oracle_12C_liunx_64.zip
chown oracle.oinstall database -R

10. 两节点静默安装数据库（两个节点，oracle用户执行）
/opt/database/runInstaller -silent -responseFile /opt/12c_only_dbsw.rsp
cat /opt/12c_only_dbsw.rsp

  1. oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0
  2. oracle.install.option=INSTALL_DB_SWONLY
  3. UNIX_GROUP_NAME=oinstall
  4. INVENTORY_LOCATION=/u01/tmp/oraInventory
  5. ORACLE_HOME=/u01/oracle
  6. ORACLE_BASE=/u01
  7.  
  8. oracle.install.db.InstallEdition=EE
  9. oracle.install.db.OSDBA_GROUP=dba
  10. oracle.install.db.OSOPER_GROUP=oper
  11. oracle.install.db.OSBACKUPDBA_GROUP=dba
  12. oracle.install.db.OSDGDBA_GROUP=dba
  13. oracle.install.db.OSKMDBA_GROUP=dba
  14. oracle.install.db.OSRACDBA_GROUP=dba
  15. oracle.install.db.rac.configurationType=
  16. oracle.install.db.CLUSTER_NODES=
  17. oracle.install.db.isRACOneInstall=false
  18. oracle.install.db.racOneServiceName=
  19. oracle.install.db.rac.serverpoolName=
  20. oracle.install.db.rac.serverpoolCardinality=0
  21. oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
  22. oracle.install.db.config.starterdb.globalDBName=
  23. oracle.install.db.config.starterdb.SID=
  24. oracle.install.db.ConfigureAsContainerDB=false
  25. oracle.install.db.config.PDBName=
  26. oracle.install.db.config.starterdb.characterSet=
  27. oracle.install.db.config.starterdb.memoryOption=false
  28. oracle.install.db.config.starterdb.memoryLimit=
  29. oracle.install.db.config.starterdb.installExampleSchemas=false
  30. oracle.install.db.config.starterdb.password.ALL=
  31. oracle.install.db.config.starterdb.password.SYS=
  32. oracle.install.db.config.starterdb.password.SYSTEM=
  33. oracle.install.db.config.starterdb.password.DBSNMP=
  34. oracle.install.db.config.starterdb.password.PDBADMIN=
  35. oracle.install.db.config.starterdb.managementOption=DEFAULT
  36. oracle.install.db.config.starterdb.omsHost=
  37. oracle.install.db.config.starterdb.omsPort=0
  38. oracle.install.db.config.starterdb.emAdminUser=
  39. oracle.install.db.config.starterdb.emAdminPassword=
  40. oracle.install.db.config.starterdb.enableRecovery=false
  41. oracle.install.db.config.starterdb.storageType=
  42. oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=
  43. oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
  44. oracle.install.db.config.asm.diskGroup=
  45. oracle.install.db.config.asm.ASMSNMPPassword=
  46. MYORACLESUPPORT_USERNAME=
  47. MYORACLESUPPORT_PASSWORD=
  48. SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
  49. DECLINE_SECURITY_UPDATES=true
  50. PROXY_HOST=
  51. PROXY_PORT=
  52. PROXY_USER=
  53. PROXY_PWD=
  54. COLLECTOR_SUPPORTHUB_URL=

主节点，静默创建数据库（主节点，oracle用户执行）

  1. [oracle@dg225 opt]$ dbca -silent -createDatabase -responseFile 12c_dbca_lo666.rsp
  2.  
  3. [oracle@dg225 opt]$ cat 12c_dbca_lo666.rsp
  4. responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
  5. gdbName=lo666
  6. sid=lo666
  7. databaseConfigType=SI
  8. RACOneNodeServiceName=
  9. policyManaged=false
  10. createServerPool=false
  11. serverPoolName=
  12. cardinality=
  13. force=false
  14. pqPoolName=
  15. pqCardinality=
  16. createAsContainerDatabase=false
  17. numberOfPDBs=0
  18. pdbName=
  19. useLocalUndoForPDBs=true
  20. pdbAdminPassword=
  21. nodelist=
  22. templateName=/u01/oracle/assistants/dbca/templates/General_Purpose.dbc
  23. sysPassword=
  24. systemPassword=
  25. serviceUserPassword=
  26. emConfiguration=DBEXPRESS
  27. emExpressPort=5500
  28. runCVUChecks=false
  29. dbsnmpPassword=
  30. omsHost=
  31. omsPort=0
  32. emUser=
  33. emPassword=
  34. dvConfiguration=false
  35. dvUserName=
  36. dvUserPassword=
  37. dvAccountManagerName=
  38. dvAccountManagerPassword=
  39. olsConfiguration=false
  40. datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
  41. datafileDestination=/data/oradata/{DB_UNIQUE_NAME}/
  42. recoveryAreaDestination=
  43. storageType=FS
  44. diskGroupName=
  45. asmsnmpPassword=
  46. recoveryGroupName=
  47. characterSet=ZHS16GBK
  48. nationalCharacterSet=AL16UTF16
  49. registerWithDirService=false
  50. dirServiceUserName=
  51. dirServicePassword=
  52. walletPassword=
  53. listeners=
  54. variablesFile=
  55. variables=DB_UNIQUE_NAME=lo666,ORACLE_BASE=/u01,PDB_NAME=,DB_NAME=lo666,ORACLE_HOME=/u01/oracle,SID=lo666
  56. initParams=undo_tablespace=UNDOTBS1,processes=480,nls_language=AMERICAN,pga_aggregate_target=1184MB,sga_target=3552MB,dispatchers=(PROTOCOL=TCP) (SERVICE=lo666XDB),db_block_size=8192BYTES,diagnostic_dest={ORACLE_BASE},audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,nls_territory=AMERICA,local_listener=LISTENER_LO666,compatible=12.2.0,control_files=("/data/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/data/oradata/{DB_UNIQUE_NAME}/control02.ctl"),db_name=lo666,audit_trail=db,remote_login_passwordfile=EXCLUSIVE,open_cursors=300
  57. sampleSchema=true
  58. memoryPercentage=40
  59. databaseType=MULTIPURPOSE
  60. automaticMemoryManagement=false
  61. totalMemory=0


配置主节点： 

  1. [oracle@dg225 ~]$ alias sysdba
  2. alias sysdba='rlwrap sqlplus / as sysdba'
  3. [oracle@dg225 ~]$ sysdba
  4.  
  5. SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 22 21:16:38 2024
  6. Copyright (c) 1982, 2016, Oracle.  All rights reserved.
  7. Connected to an idle instance.
  8.  
  9. SQL> startup mount
  10. ORACLE instance started.
  11.  
  12. Total System Global Area 3724541952 bytes
  13. Fixed Size                  8627296 bytes
  14. Variable Size             939527072 bytes
  15. Database Buffers         2768240640 bytes
  16. Redo Buffers                8146944 bytes
  17. Database mounted.
  18. SQL> alter database archivelog;         -- 修改归档模式
  19. Database altered.
  20.  
  21. SQL> alter database open;       -- 打开数据库
  22. Database altered.
  23.  
  24. SQL> alter database force logging;       -- 打开force logging; 
  25. Database altered.
  26.  
  27. SQL> alter database add standby logfile ('/data/oradata/lo666/stdredo01.log') size 200M;         -- 添加standby redo log
  28. Database altered.
  29.  
  30. SQL> alter database add standby logfile ('/data/oradata/lo666/stdredo02.log') size 200M;         -- 添加standby redo log
  31. Database altered.
  32.  
  33. SQL> alter database add standby logfile ('/data/oradata/lo666/stdredo03.log') size 200M;         -- 添加standby redo log
  34. Database altered.
  35.  
  36. SQL> alter database add standby logfile ('/data/oradata/lo666/stdredo04.log') size 200M;         -- 添加standby redo log
  37. Database altered.
  38.  
  39. SQL> col MEMBER for a50
  40. SQL> set linesize 200
  41. SQL> select group#,type,member  from v$logfile where type='STANDBY';
  42.     GROUP# TYPE    MEMBER
  43. ---------- ------- --------------------------------------------------
  44.          4 STANDBY /data/oradata/lo666/stdredo01.log
  45.          5 STANDBY /data/oradata/lo666/stdredo02.log
  46.          6 STANDBY /data/oradata/lo666/stdredo03.log
  47.          7 STANDBY /data/oradata/lo666/stdredo04.log
  48.  
  49. SQL> select GROUP#, DBID,THREAD#,SEQUENCE#,STATUS from v$standby_log;
  50.     GROUP# DBID                                        THREAD#  SEQUENCE# STATUS
  51. ---------- ---------------------------------------- ---------- ---------- ----------
  52.          4 UNASSIGNED                                        0          0 UNASSIGNED
  53.          5 UNASSIGNED                                        0          0 UNASSIGNED
  54.          6 UNASSIGNED                                        0          0 UNASSIGNED
  55.          7 UNASSIGNED                                        0          0 UNASSIGNED
  56.  
  57.  
  58. SQL> alter system set log_archive_config='DG_CONFIG=(lo666,stdlo777)' scope=spfile;          --- 修改dg参数
  59. System altered.
  60.  
  61. SQL> alter system set log_archive_dest_1='LOCATION=/data/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lo666' scope=both;
  62.  
  63. System altered.
  64.  
  65. SQL> alter system set log_archive_dest_2='SERVICE=stdlo777 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdlo777' scope=spfile;
  66. System altered.
  67.  
  68. SQL> alter system set db_file_name_convert='/data/oradata/stdlo777/','/data/oradata/lo666/' scope=spfile;
  69. System altered.
  70.  
  71. SQL> alter system set log_file_name_convert='/data/oradata/stdlo777/','/data/oradata/lo666/' scope=spfile;
  72. System altered.
  73.  
  74. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=enable scope=spfile;
  75. System altered.
  76.  
  77. SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=spfile;
  78. System altered.
  79.  
  80. SQL> alter system set FAL_CLIENT=lo666 scope=spfile;
  81. System altered.
  82.  
  83. SQL> alter system set FAL_SERVER=stdlo777 scope=spfile;
  84. System altered.
  85.  
  86. SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;
  87. System altered.
  88.  
  89. SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
  90. System altered.
  91.  
  92. SQL> create pfile from spfile;       --- 创建pfile
  93. File created.
  94.  
  95. SQL> 
  96.  
  97. [oracle@dg225 dbs]$ cat /u01/oracle/dbs/initlo666.ora
  98. lo666.__data_transfer_cache_size=0
  99. lo666.__db_cache_size=2768240640
  100. lo666.__inmemory_ext_roarea=0
  101. lo666.__inmemory_ext_rwarea=0
  102. lo666.__java_pool_size=16777216
  103. lo666.__large_pool_size=50331648
  104. lo666.__oracle_base='/u01'#ORACLE_BASE set from environment
  105. lo666.__pga_aggregate_target=1241513984
  106. lo666.__sga_target=3724541952
  107. lo666.__shared_io_pool_size=201326592
  108. lo666.__shared_pool_size=671088640
  109. lo666.__streams_pool_size=0
  110. *.audit_file_dest='/u01/admin/lo666/adump'
  111. *.audit_trail='db'
  112. *.compatible='12.2.0'
  113. *.control_files='/data/oradata/lo666/control01.ctl','/data/oradata/lo666/control02.ctl'
  114. *.db_block_size=8192
  115. *.db_file_name_convert='/data/oradata/stdlo777/','/data/oradata/lo666/'
  116. *.db_name='lo666'
  117. *.diagnostic_dest='/u01'
  118. *.dispatchers='(PROTOCOL=TCP) (SERVICE=lo666XDB)'
  119. *.fal_client='LO666'
  120. *.fal_server='STDLO777'
  121. *.log_archive_config='DG_CONFIG=(lo666,stdlo777)'
  122. *.log_archive_dest_1='LOCATION=/data/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lo666'
  123. *.log_archive_dest_2='SERVICE=stdlo777 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdlo777'
  124. *.log_archive_dest_state_1='ENABLE'
  125. *.log_archive_dest_state_2='ENABLE'
  126. *.log_archive_format='%t_%s_%r.arc'
  127. *.log_file_name_convert='/data/oradata/stdlo777/','/data/oradata/lo666/'
  128. *.nls_language='AMERICAN'
  129. *.nls_territory='AMERICA'
  130. *.open_cursors=300
  131. *.pga_aggregate_target=1184m
  132. *.processes=480
  133. *.remote_login_passwordfile='EXCLUSIVE'
  134. *.sga_target=3552m
  135. *.standby_file_management='AUTO'
  136. *.undo_tablespace='UNDOTBS1'

拷贝pfile至从节点
 

[oracle@dg225 dbs]$ scp initlo666.ora 192.168.1.226:/u01/oracle/dbs/initstdlo777.ora

 主节点配置监听

  1. [oracle@dg225 admin]$ cat listener.ora
  2. # listener.ora Network Configuration File: /u01/oracle/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.
  4.  
  5. LISTENER =
  6.   (DESCRIPTION_LIST =
  7.     (DESCRIPTION =
  8.       (ADDRESS = (PROTOCOL = TCP)(HOST = dg225)(PORT = 1521))
  9.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  10.     )
  11.   )
  12.  
  13. SID_LIST_LISTENER =
  14.   (SID_LIST =
  15.     (SID_DESC =
  16.       (GLOBAL_DBNAME = lo666)
  17.       (ORACLE_HOME = /u01/oracle)
  18.       (SID_NAME = lo666)
  19.     )
  20.   )
  21.  
  22. ADR_BASE_LISTENER = /u01/
  23.  
  24. [oracle@dg225 admin]$ cat tnsnames.ora
  25. lo666 =
  26.   (DESCRIPTION =
  27.     (ADDRESS = (PROTOCOL = TCP)(HOST = dg225)(PORT = 1521))
  28.     (CONNECT_DATA =
  29.       (SERVER = DEDICATED)
  30.       (SERVICE_NAME = lo666)
  31.     )
  32.   )
  33.  
  34. stdlo777 =
  35.   (DESCRIPTION =
  36.     (ADDRESS = (PROTOCOL = TCP)(HOST = dg226)(PORT = 1521))
  37.     (CONNECT_DATA =
  38.       (SERVER = DEDICATED)
  39.       (SID = stdlo777)
  40.     )
  41.   )
  42.  
  43. ADR_BASE_LISTENER = /u01/


从节点配置参数文件：
修改 参数文件

  1. vi /u01/oracle/dbs/initstdlo777.ora
  2.  
  3. lo666.__data_transfer_cache_size=0
  4. lo666.__db_cache_size=2768240640
  5. lo666.__inmemory_ext_roarea=0
  6. lo666.__inmemory_ext_rwarea=0
  7. lo666.__java_pool_size=16777216
  8. lo666.__large_pool_size=50331648
  9. lo666.__oracle_base='/u01'#ORACLE_BASE set from environment
  10. lo666.__pga_aggregate_target=1241513984
  11. lo666.__sga_target=3724541952
  12. lo666.__shared_io_pool_size=201326592
  13. lo666.__shared_pool_size=671088640
  14. lo666.__streams_pool_size=0
  15. *.audit_file_dest='/u01/admin/stdlo777/adump'
  16. *.audit_trail='db'
  17. *.compatible='12.2.0'
  18. *.control_files='/data/oradata/stdlo777/control01.ctl','/data/oradata/stdlo777/control02.ctl'
  19. *.db_block_size=8192
  20. *.db_file_name_convert='/data/oradata/lo666/','/data/oradata/stdlo777/'
  21. *.db_name='lo666'
  22. *.diagnostic_dest='/u01'
  23. *.dispatchers='(PROTOCOL=TCP) (SERVICE=stdlo777XDB)'
  24. *.fal_server='LO666'
  25. *.fal_client='STDLO777'
  26. *.log_archive_config='DG_CONFIG=(stdlo777,lo666)'
  27. *.log_archive_dest_1='LOCATION=/data/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdlo777'
  28. *.log_archive_dest_2='SERVICE=lo666 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lo666'
  29. *.log_archive_dest_state_1='ENABLE'
  30. *.log_archive_dest_state_2='ENABLE'
  31. *.log_archive_format='%t_%s_%r.arc'
  32. *.log_file_name_convert='/data/oradata/lo666/','/data/oradata/stdlo777/'
  33. *.nls_language='AMERICAN'
  34. *.nls_territory='AMERICA'
  35. *.open_cursors=300
  36. *.pga_aggregate_target=1184m
  37. *.processes=480
  38. *.remote_login_passwordfile='EXCLUSIVE'
  39. *.sga_target=3552m
  40. *.standby_file_management='AUTO'
  41. *.undo_tablespace='UNDOTBS1'
  42. *.DB_UNIQUE_NAME=stdlo777

从节点配置监听

  1. [oracle@dg226 admin]$ cat listener.ora
  2. LISTENER =
  3.   (DESCRIPTION_LIST =
  4.     (DESCRIPTION =
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = dg226)(PORT = 1521))
  6.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  7.     )
  8.   )
  9.  
  10. SID_LIST_LISTENER =
  11.   (SID_LIST =
  12.     (SID_DESC =
  13.       (GLOBAL_DBNAME = stdlo777)
  14.       (ORACLE_HOME = /u01/oracle)
  15.       (SID_NAME = stdlo777)
  16.     )
  17.   )
  18.  
  19. ADR_BASE_LISTENER = /u01/
  20.  
  21. [oracle@dg226 admin]$ cat tnsnames.ora
  22. lo666 =
  23.   (DESCRIPTION =
  24.     (ADDRESS = (PROTOCOL = TCP)(HOST = dg225)(PORT = 1521))
  25.     (CONNECT_DATA =
  26.       (SERVER = DEDICATED)
  27.       (SERVICE_NAME = lo666)
  28.     )
  29.   )
  30.  
  31. stdlo777 =
  32.   (DESCRIPTION =
  33.     (ADDRESS = (PROTOCOL = TCP)(HOST = dg226)(PORT = 1521))
  34.     (CONNECT_DATA =
  35.       (SERVER = DEDICATED)
  36.       (SID = stdlo777)
  37.     )
  38.   )
  39.  
  40. ADR_BASE_LISTENER = /u01/
  41.  
  42. [oracle@dg226 admin]$ lsnrctl start

从节点配置必要的目录, 启动至nomount

  1. [oracle@dg226 ~]$ mkdir -p /u01/admin/stdlo777/adump
  2. [oracle@dg226 ~]$ mkdir -p /data/oradata/stdlo777
  3. [oracle@dg226 ~]$ mkdir -p /data/arch
  4.  
  5.  
  6. [oracle@dg226 ~]$ sysdba
  7. SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 22 22:33:22 2024
  8. Copyright (c) 1982, 2016, Oracle.  All rights reserved.
  9. Connected to an idle instance.
  10.  
  11. SQL> startup nomount             --- 使用参数文件开启数据库到nomount状态
  12. ORACLE instance started.
  13. Total System Global Area 3724541952 bytes
  14. Fixed Size                  8627296 bytes
  15. Variable Size             939527072 bytes
  16. Database Buffers         2768240640 bytes
  17. Redo Buffers                8146944 bytes
  18. SQL>

主节点 duplicate数据库至从节点：

  1. [oracle@dg225 ~]$ scp /u01/oracle/dbs/orapwlo666 192.168.1.226:/u01/oracle/dbs/orapwstdlo777   --- 拷贝密码文件
  2.  
  3.  
  4. [oracle@dg225 admin]$ rlwrap rman target sys/Oracle123@lo666 AUXILIARY sys/Oracle123@stdlo777  
  5.  
  6. Recovery Manager: Release 12.2.0.1.0 - Production on Sun Jun 23 17:14:01 2024
  7. Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
  8. connected to target database: LO666 (DBID=645469506)
  9. connected to auxiliary database: LO666 (not mounted)
  10.  
  11. RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;   --- 将主库duplicate到从库
  12.  
  13. Starting Duplicate Db at 23-JUN-24
  14. using target database control file instead of recovery catalog
  15. allocated channel: ORA_AUX_DISK_1
  16. channel ORA_AUX_DISK_1: SID=128 device type=DISK
  17. current log archived
  18. contents of Memory Script:
  19. {
  20.    backup as copy reuse
  21.    targetfile  '/u01/oracle/dbs/orapwlo666' auxiliary format
  22.  '/u01/oracle/dbs/orapwstdlo777'   ;
  23. }
  24. executing Memory Script
  25. Starting backup at 23-JUN-24
  26. allocated channel: ORA_DISK_1
  27. channel ORA_DISK_1: SID=134 device type=DISK
  28. Finished backup at 23-JUN-24
  29. contents of Memory Script:
  30. {
  31.    restore clone from service  'lo666' standby controlfile;
  32. }
  33. executing Memory Script
  34. Starting restore at 23-JUN-24
  35. using channel ORA_AUX_DISK_1
  36. channel ORA_AUX_DISK_1: starting datafile backup set restore
  37. channel ORA_AUX_DISK_1: using network backup set from service lo666
  38. channel ORA_AUX_DISK_1: restoring control file
  39. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
  40. output file name=/data/oradata/stdlo777/control01.ctl
  41. output file name=/data/oradata/stdlo777/control02.ctl
  42. Finished restore at 23-JUN-24
  43. contents of Memory Script:
  44. {
  45.    sql clone 'alter database mount standby database';
  46. }
  47. executing Memory Script
  48. sql statement: alter database mount standby database
  49. contents of Memory Script:
  50. {
  51.    set newname for tempfile  1 to
  52.  "/data/oradata/stdlo777/temp01.dbf";
  53.    switch clone tempfile all;
  54.    set newname for datafile  1 to
  55.  "/data/oradata/stdlo777/system01.dbf";
  56.    set newname for datafile  3 to
  57.  "/data/oradata/stdlo777/sysaux01.dbf";
  58.    set newname for datafile  4 to
  59.  "/data/oradata/stdlo777/undotbs01.dbf";
  60.    set newname for datafile  7 to
  61.  "/data/oradata/stdlo777/users01.dbf";
  62.    restore
  63.    from  nonsparse   from service
  64.  'lo666'   clone database
  65.    ;
  66.    sql 'alter system archive log current';
  67. }
  68. executing Memory Script
  69. executing command: SET NEWNAME
  70. renamed tempfile 1 to /data/oradata/stdlo777/temp01.dbf in control file
  71. executing command: SET NEWNAME
  72. executing command: SET NEWNAME
  73. executing command: SET NEWNAME
  74. executing command: SET NEWNAME
  75. Starting restore at 23-JUN-24
  76. using channel ORA_AUX_DISK_1
  77. channel ORA_AUX_DISK_1: starting datafile backup set restore
  78. channel ORA_AUX_DISK_1: using network backup set from service lo666
  79. channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  80. channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/oradata/stdlo777/system01.dbf
  81. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
  82. channel ORA_AUX_DISK_1: starting datafile backup set restore
  83. channel ORA_AUX_DISK_1: using network backup set from service lo666
  84. channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  85. channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/oradata/stdlo777/sysaux01.dbf
  86. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
  87. channel ORA_AUX_DISK_1: starting datafile backup set restore
  88. channel ORA_AUX_DISK_1: using network backup set from service lo666
  89. channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  90. channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/oradata/stdlo777/undotbs01.dbf
  91. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  92. channel ORA_AUX_DISK_1: starting datafile backup set restore
  93. channel ORA_AUX_DISK_1: using network backup set from service lo666
  94. channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
  95. channel ORA_AUX_DISK_1: restoring datafile 00007 to /data/oradata/stdlo777/users01.dbf
  96. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
  97. Finished restore at 23-JUN-24
  98. sql statement: alter system archive log current
  99. current log archived
  100. contents of Memory Script:
  101. {
  102.    restore clone force from service  'lo666'
  103.            archivelog from scn  1847821;
  104.    switch clone datafile all;
  105. }
  106. executing Memory Script
  107. Starting restore at 23-JUN-24
  108. using channel ORA_AUX_DISK_1
  109. channel ORA_AUX_DISK_1: starting archived log restore to default destination
  110. channel ORA_AUX_DISK_1: using network backup set from service lo666
  111. channel ORA_AUX_DISK_1: restoring archived log
  112. archived log thread=1 sequence=7
  113. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  114. channel ORA_AUX_DISK_1: starting archived log restore to default destination
  115. channel ORA_AUX_DISK_1: using network backup set from service lo666
  116. channel ORA_AUX_DISK_1: restoring archived log
  117. archived log thread=1 sequence=8
  118. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  119. channel ORA_AUX_DISK_1: starting archived log restore to default destination
  120. channel ORA_AUX_DISK_1: using network backup set from service lo666
  121. channel ORA_AUX_DISK_1: restoring archived log
  122. archived log thread=1 sequence=9
  123. channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
  124. Finished restore at 23-JUN-24
  125. datafile 1 switched to datafile copy
  126. input datafile copy RECID=1 STAMP=1172423729 file name=/data/oradata/stdlo777/system01.dbf
  127. datafile 3 switched to datafile copy
  128. input datafile copy RECID=2 STAMP=1172423729 file name=/data/oradata/stdlo777/sysaux01.dbf
  129. datafile 4 switched to datafile copy
  130. input datafile copy RECID=3 STAMP=1172423729 file name=/data/oradata/stdlo777/undotbs01.dbf
  131. datafile 7 switched to datafile copy
  132. input datafile copy RECID=4 STAMP=1172423729 file name=/data/oradata/stdlo777/users01.dbf
  133. contents of Memory Script:
  134. {
  135.    set until scn  1847958;
  136.    recover
  137.    standby
  138.    clone database
  139.     delete archivelog
  140.    ;
  141. }
  142. executing Memory Script
  143. executing command: SET until clause
  144. Starting recover at 23-JUN-24
  145. using channel ORA_AUX_DISK_1
  146. starting media recovery
  147. archived log for thread 1 with sequence 7 is already on disk as file /data/arch/1_7_1172347332.arc
  148. archived log for thread 1 with sequence 8 is already on disk as file /data/arch/1_8_1172347332.arc
  149. archived log for thread 1 with sequence 9 is already on disk as file /data/arch/1_9_1172347332.arc
  150. archived log file name=/data/arch/1_7_1172347332.arc thread=1 sequence=7
  151. archived log file name=/data/arch/1_8_1172347332.arc thread=1 sequence=8
  152. archived log file name=/data/arch/1_9_1172347332.arc thread=1 sequence=9
  153. media recovery complete, elapsed time: 00:00:02
  154. Finished recover at 23-JUN-24
  155. Finished Duplicate Db at 23-JUN-24
  156. RMAN>
  157.  
  158. 从库：
  159. [oracle@dg226 admin]$ sysdba
  160. SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 23 17:16:56 2024
  161. Copyright (c) 1982, 2016, Oracle.  All rights reserved.
  162. Connected to:
  163. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
  164.  
  165. SQL> select status from v$instance;
  166. STATUS
  167. ------------
  168. MOUNTED
  169.  
  170. SQL> alter database open read only;                 --- 从库以只读的方式打开
  171. Database altered.
  172.  
  173. SQL> alter database recover managed standby database using current logfile disconnect from session;          --- 开启从库的日志应用
  174. Database altered.
  175. SQL>
  176.  
  177. SQL> alter database recover managed standby database cancel;       --- 可停止从库的日志应用
  178. Database altered.

---------------dg 已搭建完成，数据已可自动同步到备节点 --------------------------------------------


--------------- 开始切换switchover ----------------------------------------------------

主节点检查:

  1. SYS@lo666> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;  -- 角色为PRIMARY，可切换
  2. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  3. --------- -------------------- -------------------- ---------------- ----------- --------------------
  4. LO666     READ WRITE           MAXIMUM PERFORMANCE  PRIMARY            645608162 TO STANDBY
  5.  
  6. SYS@lo666> select process,status,client_process,sequence#,block# from v$managed_standby;   -- LGWR 在写 WRITING 
  7. PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK#
  8. --------- ------------ -------- ---------- ----------
  9. ARCH      CLOSING      ARCH             24       2048
  10. DGRD      ALLOCATED    N/A               0          0
  11. DGRD      ALLOCATED    N/A               0          0
  12. ARCH      CLOSING      ARCH             24          1
  13. ARCH      CLOSING      ARCH             22     251904
  14. ARCH      CONNECTED    ARCH              0          0
  15. DGRD      ALLOCATED    N/A               0          0
  16. LGWR      WRITING      LGWR             25        365

备节点检查：

  1. SYS@stdlo777> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;  -- 角色为PHYSICAL STANDBY ，可切换
  2. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  3. --------- -------------------- -------------------- ---------------- ----------- --------------------
  4. LO666     READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY   645608162 NOT ALLOWED
  5.  
  6. SYS@stdlo777> select process,status,client_process,sequence#,block# from v$managed_standby;       -- 备库MRP0在应用日志 APPLYING_LOG
  7. PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK#
  8. --------- ------------ -------- ---------- ----------
  9. ARCH      CONNECTED    ARCH              0          0
  10. DGRD      ALLOCATED    N/A               0          0
  11. DGRD      ALLOCATED    N/A               0          0
  12. ARCH      CONNECTED    ARCH              0          0
  13. ARCH      CONNECTED    ARCH              0          0
  14. RFS       IDLE         Archival          0          0
  15. ARCH      CLOSING      ARCH             24       2048
  16. RFS       IDLE         UNKNOWN           0          0
  17. RFS       IDLE         LGWR             25        414
  18. MRP0      APPLYING_LOG N/A              25        416
  19. 10 rows selected.

主节点切换：

  1. SYS@lo666> alter database commit to switchover to physical standby with session shutdown ;    -- 将主节点切换为备，关闭主节点
  2. Database altered.

备节点接管：

  1. SYS@stdlo777> select process,status,client_process,sequence#,block# from v$managed_standby;   -- 此时MRP0 为等待日志
  2. PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK#
  3. --------- ------------ -------- ---------- ----------
  4. ARCH      CLOSING      ARCH             25          1
  5. DGRD      ALLOCATED    N/A               0          0
  6. DGRD      ALLOCATED    N/A               0          0
  7. ARCH      CONNECTED    ARCH              0          0
  8. ARCH      CONNECTED    ARCH              0          0
  9. ARCH      CLOSING      ARCH             24       2048
  10. MRP0      WAIT_FOR_LOG N/A              26          0
  11. 7 rows selected.
  12.  
  13. SYS@stdlo777> alter database commit to switchover to primary with session shutdown;           -- 切换为主primary
  14. Database altered.
  15.  
  16. SYS@stdlo777> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;
  17. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  18. --------- -------------------- -------------------- ---------------- ----------- --------------------
  19. LO666     MOUNTED              MAXIMUM PERFORMANCE  PRIMARY                    0 NOT ALLOWED
  20.  
  21. SYS@stdlo777>  alter database open;
  22. Database altered.
  23.  
  24. SYS@stdlo777> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;   -- 角色已经切换为主
  25. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  26. --------- -------------------- -------------------- ---------------- ----------- --------------------
  27. LO666     READ WRITE           MAXIMUM PERFORMANCE  PRIMARY            645973944 FAILED DESTINATION

原主节点，现启动作为备节点：

  1. SYS@lo666> startup mount
  2. ORACLE instance started.
  3.  
  4. Total System Global Area 3724541952 bytes
  5. Fixed Size                  8627296 bytes
  6. Variable Size             939527072 bytes
  7. Database Buffers         2768240640 bytes
  8. Redo Buffers                8146944 bytes
  9. Database mounted.
  10. SYS@lo666> alter database open read only;       -- read only 打开数据库，作为备库准备接收日志
  11. Database altered.
  12.  
  13. SYS@lo666> set line 200
  14. SYS@lo666> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;  -- 角色为备库，需要recovery
  15. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  16. --------- -------------------- -------------------- ---------------- ----------- --------------------
  17. LO666     READ ONLY            MAXIMUM PERFORMANCE  PHYSICAL STANDBY           0 RECOVERY NEEDED
  18.  
  19. SYS@lo666> alter database recover managed standby database using current logfile disconnect from session;     -- 开始应用日志
  20. Database altered.
  21.  
  22. SYS@lo666>  select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;
  23. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  24. --------- -------------------- -------------------- ---------------- ----------- --------------------
  25. LO666     READ ONLY WITH APPLY MAXIMUM PERFORMANCE  PHYSICAL STANDBY   645973944 NOT ALLOWED
  26.  
  27. SYS@lo666>

新主节点已切换成功，并将日志同步给备节点：

  1. SYS@stdlo777> select name,open_mode,protection_mode,database_role, switchover#,switchover_status from v$database;   -- 新主节点状态
  2.  
  3. NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE    SWITCHOVER# SWITCHOVER_STATUS
  4. --------- -------------------- -------------------- ---------------- ----------- --------------------
  5. LO666     READ WRITE           MAXIMUM PERFORMANCE  PRIMARY            645973944 TO STANDBY
  6.  
  7.  
  8. SYS@stdlo777> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;   -- 检查日志是否被备库应用   
  9.  SEQUENCE# APPLIED
  10. ---------- ---------
  11.          7 YES
  12.          8 YES
  13.          9 YES
  14.         10 YES
  15.         11 YES
  16.         12 YES
  17.         13 YES
  18.         13 YES
  19.         14 YES
  20.         14 YES
  21.         15 YES
  22.  
  23.  SEQUENCE# APPLIED
  24. ---------- ---------
  25.         15 YES
  26.         16 YES
  27.         16 YES
  28.         17 YES
  29.         17 YES
  30.         18 YES
  31.         18 YES
  32.         19 YES
  33.         19 YES

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/黑客灵魂/article/detail/976418
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号