场景说明:
1.本文档的环境为同平台、同版本(操作系统版本可以不同,但数据库版本需相同),源机器和目标机器部分控制文件和ORACLE_BASE目录不一样。
2.目标机器只需要安装oracle只安装oracle数据库软件,不创建数据库(no netca dbca)。
3.第一次利用备份恢复测试环境,之后从源机器拷贝备份到目标机器并在控制文件中注册,再进行恢复测试。
主要过程:
将参数文件备份、控制文件备份、数据文件备份、密码文件以及归档备份拷贝到目标主机进行rman恢复。
注意事项:
当使用rman nocatalog恢复时,数据库必须是处于“mount”状态的。而Oracle startup mount的前提条件是control必须存在。因此,你必须在恢复datafile之前先恢复controlfile。
特别说明:
源数据库和新数据库控制文件目录和ORACLE_BASE目录不同,其他目录相同。解决办法:更改参数文件中控制文件目录和ORACLE_BASE目录为新数据库位置。
环境描述
源数据库环境
操作系统版本 : CentOS Linux release 7.3.1611 (Core) 内存:128G
数据库版本 : Oracle 11.2.0.4 x64
数据库名 : syxk
数据库SID : syxk
db_unique_name : syxk
instance_name : syxk
归档模式:开启
IP : 172.19.146.149
目标数据库环境
操作系统版本 : Red Hat Enterprise Linux Server release 6.9 (Santiago) 内存:32G
数据库版本 : Oracle 11.2.0.4 x64 (只安装oracle数据库软件,no netca dbca)
数据库名 : syxk
数据库SID : syxk
db_unique_name: syxk
instance_name : syxk
IP:172.19.146.176
源数据库服务器
查看源数据库DBID、控制文件、数据文件、redo日志文件的存储位置和归档:
--查看源数据库的DBID:
sys@SYXK 2024-01-25 17:21:58> select dbid from v$database;
DBID
----------
2492862311
--查看控制文件:
sys@SYXK 2024-01-25 17:22:01> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/topsoft/oracle/oradata/syxk/control01.ctl
/topsoft/oracle/app/oracle/fast_recovery_area/syxk/control02.ctl
--查看数据文件:
sys@SYXK 2024-01-25 17:22:52> set linesize 999
sys@SYXK 2024-01-25 17:22:19> select status,name from v$datafile;
STATUS NAME
------- --------------------------------------------------------------------------------
SYSTEM /topsoft/oracle/oradata/syxk/system01.dbf
ONLINE /topsoft/oracle/oradata/syxk/sysaux01.dbf
ONLINE /topsoft/oracle/oradata/syxk/undotbs01.dbf
ONLINE /topsoft/oracle/oradata/syxk/users01.dbf
ONLINE /topsoft/oracle/oradata/syxk/syxk01.dbf
ONLINE /topsoft/oracle/oradata/syxk/saisi01.dbf
6 rows selected.
--查看日志文件:
sys@SYXK 2024-01-25 17:22:52> set linesize 999
sys@SYXK 2024-01-25 17:23:55> col member for a70
sys@SYXK 2024-01-25 17:24:05> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
3 ONLINE /topsoft/oracle/oradata/syxk/redo03.log NO
2 ONLINE /topsoft/oracle/oradata/syxk/redo02.log NO
1 ONLINE /topsoft/oracle/oradata/syxk/redo01.log NO
--查看归档模式
sys@SYXK 2024-01-25 17:24:06> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /topsoft/oracle/oradata/archivelog
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
数据量查看
-- 查看数据库字符集
select * from nls_database_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8
select userenv('language') from dual;
--查看用户
set lin1000 pagesize 999
select username,default_tablespace,temporary_tablespace from dba_users where username not in ('SYS','SYSTEM','HR','OUTLN','MGMT_VIEW','FLOWS_FILES','MDSYS','ORDSYS','EXFSYS','DBSNMP','WMSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','ORDDATA','CTXSYS','ANONYMOUS','SYSMAN','XDB','ORDPLUGINS','OWBSYS','SI_INFORMTN_SCHEMA','OLAPSYS','SCOTT','ORACLE_OCM','XS$NULL','BI','PM','MDDATA','IX','SH','DIP','OE','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR');
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ENTSERVICE ENTSERVICE ENTSERVICETEMP
--查看数据量
SQL> select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
size(G)
----------
11.8505859
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('SYXK','SAISI')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------ ----------
SAISI 437
SYXK 1524
--每个业务用户下的各个对象类别的数量校验
SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('SYXK','SAISI') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
SAISI INDEX VALID 253
SAISI LOB VALID 75
SAISI SEQUENCE VALID 1
SAISI TABLE VALID 107
SAISI VIEW VALID 1
SYXK INDEX VALID 824
SYXK LOB VALID 67
SYXK SEQUENCE VALID 11
SYXK TABLE VALID 651
SYXK VIEW VALID 4
rman备份源数据库
通过rman进行一次全备:
vi rman_bak_L0.sh
#************************************************************************
#*** rman_bak_L0.sh ***
#************************************************************************
#!/bin/bash
source /home/oracle/.bash_profile
rq=`date +%Y%m%d`
bakdir=/topsoft/rmanbak/${rq}
autobak=/topsoft/rmanbak/autobackup
if [ ! -d ${bakdir} ];
then mkdir -p ${bakdir}
fi
if [ ! -d ${autobak} ];
then mkdir -p ${autobak}
fi
cd $ORACLE_HOME/bin
./rman target / log=$bakdir/rmanfull_${rq}.log <<EOF
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '${autobak}/%F';
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
sql 'alter system archive log current';
backup as compressed backupset incremental level 0 database tag 'dbfull' format '${autobak}/backlv0_%d_%T_%t_%s_%p.bak';
sql 'alter system archive log current';
backup as compressed backupset archivelog all tag 'arch' format '${autobak}/arch_%d_%T_%t_%s_%p.bak';
backup current controlfile format '${autobak}/ctl_%d_%T_%t_%s_%p.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}
report obsolete;
crosscheck backup;
crosscheck archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary;
exit;
EOF
【注意:控制文件一定要最后备份,由于没做归档目录数据库,rman备份信息都放在控制文件里面,要保证备份过程的信息内容都进入到控制文件,保证数据文件和控制文件是一致备份】
分别列出参数文件备份,控制文件备份,数据文件备份,以及归档备份的名字:
a.参数文件备份如下:
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.36M DISK 00:00:00 25-JAN-24
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20240125T180503
Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06
SPFILE Included: Modification time: 25-JAN-24
SPFILE db_unique_name: SYXK
b.控制文件备份如下:
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 9.36M DISK 00:00:00 25-JAN-24
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20240125T180503
Piece Name: /topsoft/rmanbak/autobackup/c-2492862311-20240125-06
Control File Included: Ckp SCN: 2895135 Ckp time: 25-JAN-24
c.数据文件备份如下:
RMAN> list backup of database;
d.列出归档备份如下:
RMAN> list backup of archivelog all;
备份参数文件
create pfile='/topsoft/rmanbak/initsyxk20240125.ora' from spfile;
备份密码文件
cp /topsoft/oracle/app/oracle/product/11.2.0/db/dbs/orapwsyxk /topsoft/rmanbak/
将rman备份文件、参数文件和密码文件copy至目标机器
--拷贝rman备份文件
scp -P 58595 -r /topsoft/rmanbak/autobackup oracle@172.19.146.176:/topsoft/newbak/
--拷贝参数文件
scp -P 58595 /topsoft/rmanbak/initsyxk20240125.ora oracle@172.19.146.176:/topsoft/newbak/
--拷贝密码文件
scp -P 58595 /topsoft/rmanbak/orapwsyxk oracle@172.19.146.176:/topsoft/newbak/
目标数据库服务器
修改参数文件
修改目录
参数文件中
syxk.__oracle_base为$ORACLE_BASE目录
.audit_file_dest为$ORACLE_BASE目录/admin/syxk/adump
.control_files中control02.ctl路径为$ORACLE_BASE目录/fast_recovery_area/syxk/control02.ctl
.diagnostic_dest为$ORACLE_BASE目录
.db_recovery_file_dest为$ORACLE_BASE目录/fast_recovery_area
--备份已备份的参数文件,避免改的有问题重新从源库服务器传输到目标库服务器
cp /topsoft/newbak/initsyxk20240125.ora /topsoft/newbak/initsyxk20240125.ora_bak
--更改参数文件中的oracle_base目录为目标库的oracle_base目录
vi /topsoft/newbak/initsyxk20240125.ora
:%s#/topsoft/oracle/app/oracle#/topsoft/app/oracle#g
更改参数文件中的控制文件路径/topsoft/oracle/app/oracle/fast_recovery_area/syxk/control02.ctl为
参数文件中的
/topsoft/app/oracle/fast_recovery_area/syxk/control02.ctl
语法说明:
:%s#源目录#新目录#g
修改内存相关参数
由于目标服务器32G和源服务器内存128G差异大,需要更改内存参数。
目标服务器上创建相应的目录
ORACLE_BASE目录不要创建,因为数据库软件已安装。源库和目标库ORACLE_BASE目录不同采用更改目标库参数文件中ORACLE_BASE目录的方法。
窗口1:查看参数文件
cat /topsoft/newbak/initsyxk20240125.ora
窗口2:创建参数文件中的目录
mkdir -p /topsoft/app/oracle/admin/syxk/adump
mkdir -p /topsoft/app/oracle/fast_recovery_area/syxk
mkdir -p /topsoft/oracle/oradata
mkdir -p /topsoft/oracle/oradata/archivelog
恢复参数文件,将数据库启动到nomount
export ORACLE_SID=syxk
[oracle@orcl dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 12 20:39:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/topsoft/rmanbak/initsyxk20240125.ora'
需要对拷贝过来的pfile进行修改【由于源控制路径和ORACLE_BASE目录不一样,要手工修改到目标数据库的相关路径,并建立相关数据库目录】,不然会提示找不到adump udump等目录路径文件的。
====================================================
[root@syxk:/topsoft/newbak]$ cat /topsoft/newbak/initsyxk20240125.ora
syxk.__db_cache_size=36104568832
syxk.__java_pool_size=939524096
syxk.__large_pool_size=939524096
syxk.__oracle_base='/topsoft/app/oracle'#ORACLE_BASE set from environment
syxk.__pga_aggregate_target=10871635968
syxk.__sga_target=43218108416
syxk.__shared_io_pool_size=0
syxk.__shared_pool_size=4966055936
syxk.__streams_pool_size=0
*._b_tree_bitmap_plans=FALSE
*._cleanup_rollback_entries=2000
*._datafile_write_errors_crash_instance=FALSE
*._index_partition_large_extents='FALSE'
*._memory_imm_mode_without_autosga=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_null_aware_antijoin=FALSE
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*.audit_file_dest='/topsoft/app/oracle/admin/syxk/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=31
*.control_files='/topsoft/oracle/oradata/syxk/control01.ctl','/topsoft/app/oracle/fast_recovery_area/syxk/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/topsoft/oracle/oradata'
*.db_domain=''
*.db_files=5000
*.db_name='syxk'
*.db_recovery_file_dest='/topsoft/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=13576962048
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/topsoft/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=syxkXDB)'
*.enable_ddl_logging=TRUE
*.event='28401 trace name context forever,level 1','10949 trace name context forever,level 1'
*.log_archive_dest_1='location=/topsoft/oracle/oradata/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=1000
*.parallel_max_servers=64
*.pga_aggregate_target=10778312704
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resource_manager_plan='force:'
*.sec_case_sensitive_logon=FALSE
*.session_cached_cursors=300
*.sga_max_size=43115347968
*.sga_target=43115347968
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
用pfile生成spfile文件(勿忘记)
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup nomount; --启动到nomount状态
恢复控制文件
源库和目标库的控制文件路径虽然不一样,但是在更改参数文件的参数文件位置后,恢复控制文件过程中会把控制文件恢复到已更改的参数文件指定的控制文件路径。
[oracle@syxk:/home/oracle]$ export ORACLE_SID=syxk
[oracle@syxk:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 25 19:43:46 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SYXK (not mounted)
RMAN> set DBID=2492862311;
executing command: SET DBID
RMAN> restore controlfile from '/topsoft/newbak/autobackup/c-2492862311-20240125-06';
Starting restore at 25-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2271 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/topsoft/oracle/oradata/syxk/control01.ctl
output file name=/topsoft/app/oracle/fast_recovery_area/syxk/control02.ctl
Finished restore at 25-JAN-24
--启库到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
恢复数据文件
该文档源库和目标库数据文件路径相同,不涉及目录转换;如果源库和目标库数据文件路径不同,该文档步骤不适用,需将控制文件中记录的数据文件位置进行转换。
[oracle@syxk:/home/oracle]$ export ORACLE_SID=syxk
[oracle@syxk:/home/oracle]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jan 25 19:50:34 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SYXK (DBID=2492862311, not open)
# 将备份集信息重新导入到当前控制文件中(一定要用“/”结尾,不然可能会找不到真实的路径)
RMAN> catalog start with '/topsoft/newbak/autobackup/';
RMAN> restore database; --还原数据文件
RMAN> recover database; --还原归档日志 提示如下报错:
....
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/topsoft/oracle/oradata/archivelog/1_17_1155666473.dbf thread=1 sequence=17
unable to find archived log
archived log thread=1 sequence=18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2024 19:54:41
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 2895113
--进行不完全恢复
RMAN> recover database until sequence 18;
Starting recover at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2271 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-JAN-24
--恢复完成后,将库启动到read only模式,查询一下数据是否正常
SQL> alter database open read only;
Database altered.
扩展:源库和目标库数据库文件路径不一致
如果源库和目标库数据文件路径不同,需将控制文件中记录的数据文件位置进行转换
参数文件修改
修改参数文件中数据目录
.control_files中有个1个control01.ctl指向的数据目录
.db_create_file_dest指向的数据目录
.log_archive_dest_1有可能指向的数据目录,如果归档目录和数据目录独立可不修改该参数
数据文件位置转换
查看文件位置
su - oracle
export ORACLE_SID=dhh
sqlplus / as sysdba
--查看数据文件存放位置
col name for a60
select name from v$datafile;
或
col name for a60
select * from v$dbfile;
--查看临时文件位置
col name for a50
select file#,name from v$tempfile;
--查看控制文件位置
select name from v$controlfile;
--查看日志文件位置
select member from v$logfile;
查看历史redo日志 select * from v$log_history;
--查看归档文件位置
select name from v$archived_log;
还原
--加载备份目录下的备份集
catalog start with '/home/oracle/rmanbak/';
--更改控制文件中数据文件和临时文件的名称,还原数据库
run{
set newname for datafile '/u01/app/oracle/oradata/dhh/system01.dbf' to '/data/u01/app/oracle/oradata/dhh/system01.dbf';
set newname for datafile '/u01/app/oracle/oradata/dhh/sysaux01.dbf' to '/data/u01/app/oracle/oradata/dhh/sysaux01.dbf';
set newname for datafile '/u01/app/oracle/oradata/dhh/undotbs01.dbf' to '/data/u01/app/oracle/oradata/dhh/undotbs01.dbf';
set newname for datafile '/u01/app/oracle/oradata/dhh/users01.dbf' to '/data/u01/app/oracle/oradata/dhh/users01.dbf';
set newname for datafile '/u01/app/oracle/oradata/dhh/example01.dbf' to '/data/u01/app/oracle/oradata/dhh/example01.dbf';
set newname for datafile '/u01/app/oracle/oradata/dhh/fuwa01.dbf' to '/data/u01/app/oracle/oradata/dhh/fuwa01.dbf';
set newname for tempfile '/u01/app/oracle/oradata/dhh/temp01.dbf' to '/data/u01/app/oracle/oradata/dhh/temp01.dbf';
set newname for tempfile '/u01/app/oracle/oradata/dhh/fuwatmp01.dbf' to '/data/u01/app/oracle/oradata/dhh/fuwatmp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
--恢复归档至指定路径
run{
crosscheck archivelog all;
set archivelog destination to '/data/oradata/archivelog/dhh';
restore archivelog all;
}
select name from v$archived_log;
--在alter database open resetlogs之前,更改contorlfile中redo的路径
alter database rename file '/u01/app/oracle/oradata/dhh/redo01.log' to '/data/oradata/dhh/redo01.log';
alter database rename file '/u01/app/oracle/oradata/dhh/redo02.log' to '/data/oradata/dhh/redo02.log';
alter database rename file '/u01/app/oracle/oradata/dhh/redo03.log' to '/data/oradata/dhh/redo03.log';
select member from v$logfile;
--先open read only 比对数据,数据无问题后再open resetlogs
SQL> alter database open read only;
Database altered.
比对数据步骤参照以上步骤。
--对比数据量无问题后
SQL> shutdown immediate;
--resetlogs方式打开
SQL> alter database open resetlogs;
Database altered.
--查看数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
恢复密码文件
将拷贝过来的密码文件恢复至数据库对应目录下:
[oracle@syxk:/topsoft/]$ cp /topsoft/newbak/orapwsyxk /topsoft/app/oracle/product/11.2.0/db/dbs/orapwsyxk
或者手动创建密码文件:
orapwdfile='/topsoft/app/oracle/product/11.2.0/db/dbs/orapworcl' password=oracle entries=30 force=y
数据量查看
--查看数据库
SQL> select sum(bytes)/1024/1024/1024 as "size(G)" from dba_data_files;
size(G)
----------
11.8505859
--每个业务用户下的总对象数量校验
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('SYXK','SAISI')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
OWNER COUNT(1)
------------------------------ ----------
SAISI 437
SYXK 1524
--每个业务用户下的各个对象类别的数量校验
SQL> select OWNER,OBJECT_TYPE,status,count(OBJECT_NAME) from dba_objects where owner in ('SYXK','SAISI') group by OBJECT_TYPE,owner,status order by 1,3,2;
OWNER OBJECT_TYPE STATUS COUNT(OBJECT_NAME)
------------------------------ ------------------- ------- ------------------
SAISI INDEX VALID 253
SAISI LOB VALID 75
SAISI SEQUENCE VALID 1
SAISI TABLE VALID 107
SAISI VIEW VALID 1
SYXK INDEX VALID 824
SYXK LOB VALID 67
SYXK SEQUENCE VALID 11
SYXK TABLE VALID 651
SYXK VIEW VALID 4
恢复归档日志(可选)
此次恢复不涉及该步骤。
可能在恢复的过程中归档没有及时生成,可以在最后再备份一下归档日志,或者将新产生的归档拷贝到归档的相应目录下,然后进行如下的恢复操作步骤:
可能在恢复的过程中归档没有及时生成,可以在最后再备份一下归档日志,或者将新产生的归档拷贝到归档的相应目录下,然后进行如下的恢复操作步骤:
RMAN> alter database mount; --接上面
RMAN> catalog start with '/topsoft/oracle/oradata/archivelog/';
RMAN> recover database;
resetlogs方式打开
使用resetlogs选项,会把当前的日志序号(log sequence number)重设为1,并抛弃所有日志信息。在以下条件时需要使用resetlogs选项:
在不完全恢复(介质恢复);
使用备份控制文件。
使用resetlogs打开数据库后,务必要完整地进行一次数据库备份。
--对比数据量无问题后
SQL> shutdown immediate;
--resetlogs方式打开
SQL> alter database open resetlogs;
Database altered.
--查看数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
问题处理
ORA-27102: out of memory
--问题描述
SQL> startup nomount pfile='/topsoft/newbak/initsyxk20240125.ora';
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
Additional information: -939524096
Additional information: 1
--问题原因
由于目标服务器32G和源服务器内存128G差异大,需要更改内存参数。
参数文件中sga pga参数太大
--解决办法
--sga和pga配置的最佳实践
对于OLTP系统来说,oracle建议是sga=系统内存*0.8*0.8 单位字节
pga=系统内存*0.8*0.2
对于DSS系统来说,oracle建议是sga=系统内存*0.8*0.5 单位字节
pga=系统内存*0.8*0.5
更改参数文件中的关于内存的参数
recover database提示unable to find archived log
--问题描述
还原归档文件时间报错
RMAN> recover database;
Starting recover at 25-JAN-24
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: reading from backup piece /topsoft/newbak/autobackup/arch_SYXK_20240125_1159207499_60_1.bak
channel ORA_DISK_1: piece handle=/topsoft/newbak/autobackup/arch_SYXK_20240125_1159207499_60_1.bak tag=ARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/topsoft/oracle/oradata/archivelog/1_16_1155666473.dbf thread=1 sequence=16
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /topsoft/newbak/autobackup/arch_SYXK_20240125_1159207499_61_1.bak
channel ORA_DISK_1: piece handle=/topsoft/newbak/autobackup/arch_SYXK_20240125_1159207499_61_1.bak tag=ARCH
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/topsoft/oracle/oradata/archivelog/1_17_1155666473.dbf thread=1 sequence=17
unable to find archived log
archived log thread=1 sequence=18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/25/2024 19:54:41
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 2895113
--解决办法
--进行不完全恢复
RMAN> recover database until sequence 18;
Starting recover at 25-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2271 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-JAN-24
--恢复完成后,将库启动到read only模式,查询一下数据是否正常
SQL> alter database open read only;
Database altered.
--对比数据量无问题后
SQL> shutdown immediate;
--resetlogs方式打开
SQL> alter database open resetlogs;
Database altered.
--查看数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
参考链接:Oracle 11G RMAN备份恢复到异机数据库_oracle 11g rman 备份异机恢复-CSDN博客