OS:CentOS 7
源库:2节点组成的RAC,SID:slnngk1,slnngk2,db_name:slnngk
目的库:单机,sid:slnngkb,db_name:slnngkb
数据库版本:12.2.0.1
注意：目的端的db_name必须与源端保持一致
1.异地机器安装相同版本的数据库软件和启动监听
安装步骤省略,注意只安装软件和启动监听器(没有监听任何服务器),确保监听器已经启动
[oracle@12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-NOV-2023 14:01:22
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=12c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 28-NOV-2023 13:57:11
Uptime 0 days 0 hr. 4 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/12c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=12c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
2.异地机器创建相应的目录
su - oracle
mkdir -p $ORACLE_BASE/oradata/slnngkb/
mkdir -p $ORACLE_BASE/admin/slnngkb/adump
mkdir -p $ORACLE_BASE/admin/slnngkb/dpdump
mkdir -p $ORACLE_BASE/fast_recovery_area/slnngkb
mkdir -p /u01/archivelog
3.异地服务器准备参数文件
可以从主库拷贝进行相应修改
SQL> show parameters spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/SLNNGK/PARAMETERFILE/spf
ile.268.1076389139
SQL>create pfile='/tmp/master_pfile.ora' from spfile;
拷贝到异地机器的dbs目录
scp /tmp/master_pfile.ora oracle@192.168.56.103:/u01/app/oracle/product/12.2.0.1/db_1/dbs/
RAC的参数:
[oracle@12c dbs]$ more master_pfile.ora
slnngk1.__data_transfer_cache_size=0
slnngk2.__data_transfer_cache_size=0
slnngk2.__db_cache_size=1409286144
slnngk1.__db_cache_size=1392508928
slnngk1.__inmemory_ext_roarea=0
slnngk2.__inmemory_ext_roarea=0
slnngk1.__inmemory_ext_rwarea=0
slnngk2.__inmemory_ext_rwarea=0
slnngk1.__java_pool_size=16777216
slnngk2.__java_pool_size=16777216
slnngk1.__large_pool_size=33554432
slnngk2.__large_pool_size=33554432
slnngk1.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
slnngk2.__oracle_base='/u01/oracle/app'#ORACLE_BASE set from environment
slnngk1.__pga_aggregate_target=687865856
slnngk2.__pga_aggregate_target=687865856
slnngk1.__sga_target=2063597568
slnngk2.__sga_target=2063597568
slnngk1.__shared_io_pool_size=100663296
slnngk2.__shared_io_pool_size=100663296
slnngk2.__shared_pool_size=486539264
slnngk1.__shared_pool_size=503316480
slnngk1.__streams_pool_size=0
slnngk2.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/app/admin/slnngk/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/SLNNGK/CONTROLFILE/current.261.1076389005','+REDO/SLNNGK/CONTROLFILE/current.256.1076389005'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='slnngk'
*.db_recovery_file_dest='+REDO'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/u01/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkXDB)'
*.enable_goldengate_replication=TRUE
family:dw_helper.instance_mode='read-only'
slnngk1.instance_number=1
slnngk2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=656m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=1968m
slnngk2.thread=2
slnngk1.thread=1
slnngk2.undo_tablespace='UNDOTBS2'
slnngk1.undo_tablespace='UNDOTBS1'
从库对参数文件重命令
[oracle@slavea dbs]$ cd /u01/app/oracle/product/11.2.0.4/db_1/dbs/
[oracle@slavea dbs]$ mv master_pfile.ora initslnngkb.ora
进行修改,最后的参数如下:
*.audit_file_dest='/u01/app/oracle/admin/slnngkb/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/slnngkb/control01.ctl','/u01/app/oracle/fast_recovery_area/slnngkb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='slnngk'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=slnngkbXDB)'
*.enable_goldengate_replication=TRUE
*.log_archive_dest_1='location=/u01/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=slnngkb'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=1070596096
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3213885440
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='slnngkb'
注意:
*.db_name保留与源库一致不需要修改,否则在启动的时候报如下错误:
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'SLNNGKA' in control file is not 'SLNNGKB'
5.原库备份
su - oracle
mkdir -p /tmp/rmanbak
run{
allocate channel c1 device type disk;
backup as compressed backupset format '/tmp/rmanbak/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '/tmp/rmanbak/archive_log_t%t_s%s_p%p';
backup current controlfile format '/tmp/rmanbak/ctl_%u.bak' tag 'bak_controlfile';
backup spfile format '/tmp/rmanbak/spfile_%u_%T.bak';
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
release channel c1;
}
6.将备份集拷贝到异机
scp archive_log_t1154154949_s41_p1 oracle@192.168.56.103:/u01/rmanbak/
scp ctl_1a2cm0e9.bak oracle@192.168.56.103:/u01/rmanbak/
scp df_t1154154714_s39_p1 oracle@192.168.56.103:/u01/rmanbak/
scp spfile_1b2cm0ec_20231129.bak oracle@192.168.56.103:/u01/rmanbak/
7.异地机器启动到nomont状态
[oracle@slnngkb dbs]$ sqlplus /nolog
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/initslnngkb.ora
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 721423760 bytes
Database Buffers 2466250752 bytes
Redo Buffers 16904192 bytes
8.恢复控制文件(在nomount状态下恢复)
[oracle@slnngkb dbs]$ rman target /
RMAN> restore controlfile from '/u01/rmanbak/ctl_1a2cm0e9.bak';
Starting restore at 27-NOV-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/slnngkb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/slnngkb/control02.ctl
Finished restore at 27-NOV-23
自动会根据启动参数的配置参数*.control_files复制到指定的位置.
9.启动数据库到mount阶段
SQL> connect / as sysdba
Connected.
SQL> alter database mount;
Database altered.
10.注册备份集
[oracle@slnngkb dbs]$ rman target /
RMAN>catalog start with '/u01/rmanbak';
查看备份集:
RMAN> delete expired backupset;
RMAN> list backup of database;
11.还原数据文件
run{
allocate channel c1 device type disk;
set newname for database to '/u01/app/oracle/oradata/slnngkb/%b';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
}
还原后的文件会带上数字串
参数说明:
％b 指定的文件名从目录路径中剥离,只获取数据文件名称.
当然也可以使用set newname的方式
run{
set newname for datafile 1 to '/u01/app/oracle/oradata/slavea/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/slavea/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/slavea/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/slavea/users01.dbf';
restore database;
switch datafile all;
}
12.恢复数据库
恢复到指定的归档日志
run{
set until sequence 104 thread 1;
set until sequence 62 thread 2;
recover database;
}
恢复的序列号为:归档日志号+1
若想恢复某个实例的具体sequence，可以单独指定，系统会自动应用另外节点的sequence
run{
set until sequence 104 thread 1;
recover database;
}
run{
set until sequence 62 thread 2;
recover database;
}
也可以恢复到具体的时间点
run{
set until time "to_date('2023-11-30 02:36:25','YYYY-MM-DD HH24:MI:SS')";
recover database;
}
若该时间的的数据不符合要求,可以将时间往后面推，但不能往前推
13.只读方式打开数据库验证数据
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
若这里恢复不是自己预期的,可以继续使用规定日志进行恢复(注意必须是往后恢复)
SQL>shutdown immediate
SQL>startup nomount pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/initslnngkb.ora
SQL>alter database mount;
RMAN>run{
set until sequence 106 thread 1;
set until sequence 63 thread 2;
recover database;
}
14.打开数据库
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs;
Database altered.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_2_lpfc53y0_.log
/u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_3_lpfc552y_.log
/u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_1_lpfc53x0_.log
/u01/app/oracle/fast_recovery_area/SLNNGK/onlinelog/o1_mf_4_lpfc5526_.log
12C会自动在fast_recovery_area目录生成redo日志,11G的需要重命名.
15.查看各文件路径是否正常
set linesize 1000;
col FILE_NAME format a80;
col status format a10;
col enabled format a16;
select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile
union all
select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile
union all
select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile
union all
select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile;
16.清理redo thread2
SQL> col instance format a8
SQL> select thread#,instance,status,enabled from v$thread;
THREAD# INSTANCE STATUS ENABLED
---------- -------- ---------- --------------------------------
1 slnngkb OPEN PUBLIC
2 slnngk2 CLOSED PUBLIC
SQL> alter database disable thread 2;
Database altered.
SQL> select thread#,instance,status,enabled from v$thread;
THREAD# INSTANCE STATUS ENABLED
---------- -------- ---------- --------------------------------
1 slnngkb OPEN PUBLIC
2 slnngk2 CLOSED DISABLED
17.清理Undo
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
18.删除实例2的log
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 1 209715200 512 1 NO CURRENT 8315295 29-NOV-23 1.8447E+19 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0
3 2 1 209715200 512 1 YES INACTIVE 8315295 29-NOV-23 8315409 29-NOV-23 0
4 2 0 209715200 512 1 YES UNUSED 0 0 0
alter database drop logfile group 3;
alter database drop logfile group 4;
19.重新添加redo log
alter database add logfile group 3 ('/u01/app/oracle/oradata/slnngkb/redo03.log') size 200M;
alter database add logfile group 4 ('/u01/app/oracle/oradata/slnngkb/redo04.log') size 200M; ##大小与之前的保持一致
alter database add logfile group 5 ('/u01/app/oracle/oradata/slnngkb/redo05.log') size 200M;
alter database add logfile group 6 ('/u01/app/oracle/oradata/slnngkb/redo06.log') size 200M;
多执行如下命令进行日志切换,直到日志组的状态为INACTIVE,才能进行删除
alter system switch logfile;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database add logfile group 1 ('/u01/app/oracle/oradata/slnngkb/redo01.log') size 200M;
alter database add logfile group 2 ('/u01/app/oracle/oradata/slnngkb/redo02.log') size 200M;
20.创建spfile
create spfile from pfile='/u01/app/oracle/product/12.2.0.1/db_1/dbs/initslnngkb.ora';