Oracle 19c 单实例adg 一主一备搭建
首次搭建oracle adg，整理了搭建主备的步骤及遇到的问题
主库配置
开启主库监听
lsnrctl start
开启归档模式
--先查看当前的归档模式
archive log list;
--当 database log mode显示为no archive mode即为已经需要开启归档，请按照如下操作步骤开启归档
shutdown immediate
startup mount;
--创建归档日志文件目录
mkdir -p /data3/archivedata
--设置归档文件并开启归档
alter system set log_archive_dest_1='/data3/archivedata';
alter database archivelog;
alter database open;
确保主库 force logging mode
alter database force logging;
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
拷贝主库密码文件到备库
scp $ORACLE_HOME/dbs/orapworcl oracle@103.163.8.155:$ORACLE_HOME/dbs/orapworcldg
配置tnsnames.ora并拷贝到备库
# tnsnames.ora Network Configuration File: /data/oracle/oracle/db/product/19.4/n
etwork/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 103.163.8.157)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.157 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.155 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
--将tnsnames.ora
scp tnsnames.ora传到备库
tnsnames.ora 103.163.8.155:/data/oracle/db/product/19.3/network/admin
到备库后修改LISTENER_ORCL的host值
# tnsnames.ora Network Configuration File: /data/oracle/oracle/db/product/19.4/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 103.163.8.155)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.157 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =103.163.8.155 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
主库中创建pfile文件，并传到备库中
SQL> create pfile='/home/oracle/mespfile.ora' from spfile;
File created.
cd /home/oracle
scp mespfile.ora 103.163.8.155:/home/oracle
在主库中增加参数
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg)' scope=both;
alter system set log_archive_dest_2='SERVICE=orcldg async lgwr valid_for=(online_logfiles,primary_role) db_unique_name=orcldg' scope=both;
alter system set fal_server=orcldg scope=both;
alter system set fal_client=orcl scope=both;
alter system set standby_file_management=auto scope=both;
主库做全备，并拷贝至备库
创建存放备份文件的目录
mkdir -p /data3/backup
rman全备
rman target /
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format '/data3/backup/backdata_%d_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all format '/data3/backup/archlog_%d_%T_%U.bak';
backup current controlfile format '/data3/backup/cntrl_%s_%p_%s.bak';
release channel c1;
release channel c2;
}
执行备份过程中报如下错误
--执行rman备份报RMAN-03009 ORA-19502
RMAN-03009: failure of backup command on c1 channel at 01/09/2024 11:02:54
ORA-19502: write error on file "/data/oracle/oracle/backup/backdata_ORCL_20240109_142g57o9_1_1.bak", block number 3560576 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 3560576
Additional information: 565248
channel c1 disabled, job failed on it will be run on another channel
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 01/09/2024 11:02:57
ORA-19502: write error on file "/data/oracle/oracle/backup/backdata_ORCL_20240109_132g57n6_1_1.bak", block number 5339904 (block size=8192)
ORA-27072: File I/O error
Additional information: 4
Additional information: 5339904
Additional information: 430080
上述问题是由于磁盘空间不够导致rman备份失败。在新的目录创建备份目录并重新执行命令，备份正常
[oracle@jcyjs4 oracle]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 9 11:27:30 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1573120881)
RMAN> run{
2> allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database format '/data3/backup/backdata_%d_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all format '/data3/backup/archlog_%d_%T_%U.bak';
3> 4> 5> 6> 7> backup current controlfile format '/data3/backup/cntrl_%s_%p_%s.bak';
release channel c1;
8> 9> release channel c2;
10> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=3632 device type=DISK
allocated channel: c2
channel c2: SID=3874 device type=DISK
Starting backup at 2024-01-09 11:27:46
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00016 name=/data/oracle/oracle/oradata/ORCL/USERS_1627895937538436.dbf
input datafile file number=00055 name=/minio/data/test19.dbf
channel c1: starting piece 1 at 2024-01-09 11:27:46
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
...
日志省略
...
Starting Control File and SPFILE Autobackup at 2024-01-09 12:09:02
piece handle=/data3/data/ORCL/autobackup/2024_01_09/o1_mf_s_1157803743_lsskpz3q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-09 12:09:04
sql statement: alter system archive log current
Starting backup at 2024-01-09 12:09:04
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5633 RECID=4859 STAMP=1157785804
channel c1: starting piece 1 at 2024-01-09 12:09:04
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=5634 RECID=4860 STAMP=1157798188
input archived log thread=1 sequence=5635 RECID=4861 STAMP=1157803744
channel c2: starting piece 1 at 2024-01-09 12:09:04
channel c2: finished piece 1 at 2024-01-09 12:09:05
piece handle=/data3/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak tag=TAG20240109T120904 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=5636 RECID=4862 STAMP=1157803744
channel c2: starting piece 1 at 2024-01-09 12:09:05
channel c2: finished piece 1 at 2024-01-09 12:09:06
piece handle=/data3/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak tag=TAG20240109T120904 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c1: finished piece 1 at 2024-01-09 12:09:12
piece handle=/data3/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak tag=TAG20240109T120904 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:08
Finished backup at 2024-01-09 12:09:12
Starting backup at 2024-01-09 12:09:13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 2024-01-09 12:09:14
channel c1: finished piece 1 at 2024-01-09 12:09:15
piece handle=/data3/backup/cntrl_55_1_55.bak tag=TAG20240109T120913 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2024-01-09 12:09:15
Starting Control File and SPFILE Autobackup at 2024-01-09 12:09:15
piece handle=/data3/data/ORCL/autobackup/2024_01_09/o1_mf_s_1157803755_lsskqchb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2024-01-09 12:09:16
released channel: c1
released channel: c2
将备份文件传到备库
[root@jcyjs4 data3]# scp -r ./backup/ 103.163.8.155:/data3/backup
Password:
backdata_ORCL_20240109_1i2g5bf9_1_1.bak 100% 20GB 172.5MB/s 01:57
backdata_ORCL_20240109_152g599i_1_1.bak 100% 52GB 172.6MB/s 05:07
archlog_ORCL_20240109_1k2g5bn0_1_1.bak 100% 773MB 173.9MB/s 00:04
cntrl_55_1_55.bak 100% 13MB 123.4MB/s 00:00
backdata_ORCL_20240109_1h2g5bf9_1_1.bak 100% 22GB 173.4MB/s 02:08
backdata_ORCL_20240109_192g59q4_1_1.bak 100% 42GB 170.2MB/s 04:11
archlog_ORCL_20240109_1l2g5bn0_1_1.bak 100% 45MB 172.1MB/s 00:00
backdata_ORCL_20240109_1b2g5a7o_1_1.bak 100% 42GB 169.5MB/s 04:13
backdata_ORCL_20240109_1g2g5b38_1_1.bak 100% 36GB 169.7MB/s 03:37
backdata_ORCL_20240109_1e2g5alb_1_1.bak 100% 42GB 172.2MB/s 04:10
backdata_ORCL_20240109_172g59hh_1_1.bak 100% 23GB 173.3MB/s 02:18
backdata_ORCL_20240109_1d2g5alb_1_1.bak 98% 42GB 171.4MB/s 00:02 ETA
backdata_ORCL_20240109_1d2g5alb_1_1.bak 100% 42GB 171.7MB/s 04:10
backdata_ORCL_20240109_1c2g5a7o_1_1.bak 100% 42GB 171.2MB/s 04:11
backdata_ORCL_20240109_1a2g59q4_1_1.bak 100% 41GB 170.3MB/s 04:09
backdata_ORCL_20240109_182g59hh_1_1.bak 100% 33GB 167.8MB/s 03:22
backdata_ORCL_20240109_1f2g5b38_1_1.bak 100% 41GB 171.4MB/s 04:07
archlog_ORCL_20240109_1m2g5bn1_1_1.bak 100% 4096 21.8MB/s 00:00
backdata_ORCL_20240109_162g599i_1_1.bak 100% 21GB 170.1MB/s 02:03
备库配置
修改mespfile.ora参数文件
修改前的mespfile.ora参数文件
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=46439333888
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=939524096
orcl.__large_pool_size=671088640
orcl.__oracle_base='/data/oracle/oracle/db'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=12884901888
orcl.__sga_target=55834574848
orcl.__shared_io_pool_size=134217728
orcl.__shared_pool_size=6442450944
orcl.__streams_pool_size=1073741824
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/data/oracle/oracle/db/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='19.0.0'
*.control_files='/data/oracle/oracle/oradata/ORCL/control01.ctl','/data/oracle/oracle/oradata/ORCL/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest_size=53687091200
*.db_recovery_file_dest='/data3/data/'
*.diagnostic_dest='/data/oracle/oracle/db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_goldengate_replication=TRUE
*.local_listener='LISTENER_ORCL'
*.log_archive_dest_1='location=/data3/data'
*.memory_max_target=68719476736
*.memory_target=68719476736
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=12884901888
*.processes=4480
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod'
*.sga_max_size=55834574848
*.sga_target=55834574848
*.streams_pool_size=1073741824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
修改的参数包括如下
*.audit_file_dest='/data3/oracle/db/admin/orcldg/adump'
*.control_files='/data3/oracle/oradata/ORCLDG/control01.ctl','/data3/oracle/oradata/ORCLDG/control02.ctl'
*.db_recovery_file_dest='/data3/oracle/archdata/archivelog'
*.log_archive_dest_1='location=/data3/oracle/archdata/archivelog'
新增的参数如下：
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=orcldg
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.fal_server='orcl'
*.fal_client='orcldg'
*.standby_file_management=auto
修改后的正式文件如下
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=46439333888
orcl.__inmemory_ext_roarea=0
orcl.__inmemory_ext_rwarea=0
orcl.__java_pool_size=939524096
orcl.__large_pool_size=671088640
orcl.__oracle_base='/data3/oracle/db'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=12884901888
orcl.__sga_target=55834574848
orcl.__shared_io_pool_size=134217728
orcl.__shared_pool_size=6442450944
orcl.__streams_pool_size=1073741824
orcl.__unified_pga_pool_size=0
*.audit_file_dest='/data3/oracle/db/admin/orcldg/adump'
*.audit_trail='NONE'
*.compatible='19.0.0'
*.control_files='/data3/oracle/oradata/ORCLDG/control01.ctl','/data3/oracle/oradata/ORCLDG/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_recovery_file_dest_size=53687091200
*.db_recovery_file_dest='/data3/oracle/archdata/archivelog'
*.diagnostic_dest='/data3/oracle/db'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.enable_goldengate_replication=TRUE
*.local_listener='LISTENER_ORCL'
*.log_archive_dest_1='location=/data3/oracle/archdata/archivelog'
*.memory_max_target=68719476736
*.memory_target=68719476736
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=12884901888
*.processes=4480
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod'
*.sga_max_size=55834574848
*.sga_target=55834574848
*.streams_pool_size=1073741824
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name=orcldg
*.log_archive_config='DG_CONFIG=(orcl,orcldg)'
*.fal_server='orcl'
*.fal_client='orcldg'
*.standby_file_management=auto
创建文件中对应的路径
mkdir -p /data3/oracle/archdata/archivelog
mkdir -p /data3/oracle/oradata/ORCLDG
mkdir -p /data3/oracle/db/admin/orcldg/adump
备库启动监听
[oracle@jcyjs3 archdata]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JAN-2024 17:10:27
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /data3/oracle/db/product/19.3/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /data3/oracle/db/diag/tnslsnr/jcyjs3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcyjs3)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 09-JAN-2024 17:10:28
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /data3/oracle/db/diag/tnslsnr/jcyjs3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jcyjs3)(PORT=1521)))
The listener supports no services
The command completed successfully
将环境变量中的export ORACLE_SID的值修改为orcldg
export ORACLE_SID=orcldg
--修改后使环境变量生效
source ~/.bash_profile
启动到nomount状态
备库使用mespfile.ora文件启动数据库到nomount状态，创建spfile文件，然后再通过spfile启动到nomount
[oracle@jcyjs3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 08:42:30 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL>
SQL> startup nomount pfile='/home/oracle/mespfile.ora';
ORACLE instance started.
Total System Global Area 5.5835E+10 bytes
Fixed Size 30146136 bytes
Variable Size 7784628224 bytes
Database Buffers 4.7916E+10 bytes
Redo Buffers 104071168 bytes
SQL> SQL>
SQL> create spfile from pfile='/home/oracle/mespfile.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 5.5835E+10 bytes
Fixed Size 30146136 bytes
Variable Size 7784628224 bytes
Database Buffers 4.7916E+10 bytes
Redo Buffers 104071168 bytes
备库恢复控制文件，数据启动到mount状态
[oracle@jcyjs3 backup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 10 08:52:08 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore standby controlfile from '/data3/oracle/backup/cntrl_55_1_55.bak';
Starting restore at 2024-01-10 08:52:33
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5446 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data3/oracle/oradata/ORCLDG/control01.ctl
output file name=/data3/oracle/oradata/ORCLDG/control02.ctl
Finished restore at 2024-01-10 08:52:34
RMAN>
数据库启动到mount状态
alter database mount;
测试主库和备库的连通性
主库连接备库
[oracle@jcyjs4 admin]$ sqlplus hxbtest/hxbtest@orcldg
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 09:16:22 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name: hxbtest
Enter password:
Last Successful login time: Wed Jan 10 2024 09:15:57 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
备库连接主库
[oracle@jcyjs3 ~]$ sqlplus hxbtest/hxbtest@orcl
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 09:02:27 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Jan 10 2024 09:17:11 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
备库恢复数据库
catalog注册备份
RMAN> catalog start with '/data3/oracle/backup';
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 2024-01-10 09:09:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5446 device type=DISK
Crosschecked 52 objects
Finished implicit crosscheck backup at 2024-01-10 09:09:22
Starting implicit crosscheck copy at 2024-01-10 09:09:22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2024-01-10 09:09:22
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /data3/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /data3/oracle/backup/backdata_ORCL_20240109_172g59hh_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1e2g5alb_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1b2g5a7o_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1c2g5a7o_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1a2g59q4_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_152g599i_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1d2g5alb_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_192g59q4_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1h2g5bf9_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1i2g5bf9_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_182g59hh_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1g2g5b38_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1f2g5b38_1_1.bak
File Name: /data3/oracle/backup/cntrl_55_1_55.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /data3/oracle/backup/backdata_ORCL_20240109_172g59hh_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1e2g5alb_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1b2g5a7o_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1c2g5a7o_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1a2g59q4_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1l2g5bn0_1_1.bak
File Name: /data3/oracle/backup/archlog_ORCL_20240109_1m2g5bn1_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_152g599i_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1d2g5alb_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_192g59q4_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1h2g5bf9_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1i2g5bf9_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_182g59hh_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1g2g5b38_1_1.bak
File Name: /data3/oracle/backup/backdata_ORCL_20240109_1f2g5b38_1_1.bak
File Name: /data3/oracle/backup/cntrl_55_1_55.bak
RMAN>
crosscheck核对数据文件备份集
RMAN> crosscheck backup;
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/data/oracle/oracle/db/product/19.4/dbs/c-1573120881-20201123-00 RECID=1 STAMP=1057252431
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/data/oracle/oracle/db/product/19.4/dbs/c-1573120881-20210323-00 RECID=2 STAMP=1067961590
crosschecked backup piece: found to be 'EXPIRED'
...
日志省略
...
backup piece handle=/data3/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak RECID=52 STAMP=1157803744
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data3/oracle/backup/archlog_ORCL_20240109_1k2g5bn0_1_1.bak RECID=54 STAMP=1157879384
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data3/oracle/backup/cntrl_55_1_55.bak RECID=70 STAMP=1157879385
Crosschecked 70 objects
备库恢复数据库
run{
set newname for database to '/data3/oracle/oradata/ORCLDG/%b';
restore database;
switch datafile all;
}
RMAN> run{
set newname for database to '/data3/oracle/oradata/ORCLDG/%b';
restore database;
switch datafile all;
}2> 3> 4> 5>
executing command: SET NEWNAME
Starting restore at 2024-01-10 09:12:08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data3/oracle/oradata/ORCLDG/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00056 to /data3/oracle/oradata/ORCLDG/test20.dbf
channel ORA_DISK_1: reading from backup piece /data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak
channel ORA_DISK_1: piece handle=/data3/oracle/backup/backdata_ORCL_20240109_162g599i_1_1.bak tag=TAG20240109T112746
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:15
...
datafile 47 switched to datafile copy
input datafile copy RECID=59 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test11.dbf
datafile 48 switched to datafile copy
input datafile copy RECID=60 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test12.dbf
datafile 49 switched to datafile copy
input datafile copy RECID=61 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test13.dbf
datafile 50 switched to datafile copy
input datafile copy RECID=62 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test14.dbf
datafile 51 switched to datafile copy
input datafile copy RECID=63 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test15.dbf
datafile 52 switched to datafile copy
input datafile copy RECID=64 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test16.dbf
datafile 53 switched to datafile copy
input datafile copy RECID=65 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test17.dbf
datafile 54 switched to datafile copy
input datafile copy RECID=66 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test18.dbf
datafile 55 switched to datafile copy
input datafile copy RECID=67 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test19.dbf
datafile 56 switched to datafile copy
input datafile copy RECID=68 STAMP=1157886821 file name=/data3/oracle/oradata/ORCLDG/test20.dbf
备库创建standby redo logs
创建redo logs前，需要先核实主库redo log的文件大小，备库的redo logs文件大小必须和主库一直或者大于主库，另外备库的redo logs文件数量也需要多余主库一个
SQL> alter database add standby logfile thread 1 group 11('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo11.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 12('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo12.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 13('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo13.log') size 200M;
SQL> alter database add standby logfile thread 1 group 14('/data3/oracle/oradata/ORCLDG/standby_redo_logs/redo14.log') size 200M;
查看standby 的信息
select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
11 1 0 YES UNASSIGNED
12 1 0 YES UNASSIGNED
13 1 0 YES UNASSIGNED
14 1 0 YES UNASSIGNED
备库开启应用
alter database recover managed standby database using current logfile disconnect for session;
查看相关进程
select process,status ,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 1 0
RFS IDLE 1 5637
MRP0 WAIT_FOR_LOG 1 5637
RFS IDLE 0 0
RFS IDLE 0 0
11 rows selected.
mrp0的状态为WAIT_FOR_LOG
重启数据库
SQL> startup force
;
ORACLE instance started.
Total System Global Area 5.5835E+10 bytes
Fixed Size 30146136 bytes
Variable Size 7784628224 bytes
Database Buffers 4.7916E+10 bytes
Redo Buffers 104071168 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data3/oracle/oradata/ORCLDG/system01.dbf'
上述问题是由于rman的备份集与致日志记录的SCN与控制文件不符，不能完成checkpoint事件，需要进行介质恢复
alter database recover managed standby database cancel;
alter database open read only;
查看一下OPEN_MODE发现此时是READ_ONLY状态
select open_mode from v$database;
再看一下进程，有RFS进程
select process,status ,thread#,sequence# from v$managed_standby;
再开启一下应用
alter database recover managed standby database using current logfile disconnect ;
再看一下相关进程
select process,status ,thread#,sequence# from v$managed_standby;
此时发现MRP进程，且状态为APPLYING_LOG，则搭建成功。
