一、恢复原因
客户的几套Oracle数据库都采用NBU做的备份，这次要求做一次季度的恢复演练，验证一下备份数据的有效性。
所以写下这篇文章，供大家参考，有不对的地方也请指正！
注：以下涉及客户环境真实信息都已做了替换。
二、环境介绍
1、NBU服务端：
CentOS 7.6
NBU Server 7.7.3 （NetBackup_7.7.3_LinuxR_x86_64.tar.gz）
2、异机客户端：
Oracle Linux 7.3
Oracle DB 11.2.0.4（版本跟源库一致）
NBU client 7.7.3 （NetBackup_7.7.3_CLIENTS2.tar.gz）
三、恢复操作
1、服务端要点
要通过NBU做异机恢复要建一个 No.Restrictions 文件，解除异机数据恢复的限制。
[root@nbusrv ~]# mkdir /usr/openv/netbackup/db/altnames
[root@nbusrv ~]#
[root@nbusrv ~]# touch /usr/openv/netbackup/db/altnames/No.Restrictions
[root@nbusrv ~]#
[root@nbusrv ~]# ll /usr/openv/netbackup/db/altnames/No.Restrictions
-rw-r--r-- 1 root root 0 Mar 7 2020 /usr/openv/netbackup/db/altnames/No.Restrictions
2、异机端要点
异机端装好DB软件，建好监听，再建一个空实例，实例名与源库相同。建实例是为了得到参数文件，恢复时不用手动创建。
将数据库关闭再启动到nomount状态：
[oracle@dbtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 15:39:32 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
删除没用的文件：
[oracle@dbtest ~]$ cd $ORACLE_BASE
[oracle@dbtest oracle]$
[oracle@dbtest oracle]$ cd oradata/orcl/
[oracle@dbtest orcl]$ ll
total 15668
-rw-r----- 1 oracle oinstall 9748480 Jul 13 15:39 control01.ctl
-rw-r----- 1 oracle oinstall 20979712 Jul 12 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 13 15:43 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 12 22:03 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 13 08:30 redo03.log
-rw-r----- 1 oracle oinstall 702554112 Jul 13 15:43 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Jul 13 15:43 system01.dbf
-rw-r----- 1 oracle oinstall 78651392 Jul 13 15:43 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 13 15:43 users01.dbf
[oracle@dbtest orcl]$ rm -f ./*
[oracle@dbtest orcl]$ ll
total 0
[oracle@dbtest orcl]$ cd ../../
[oracle@dbtest oracle]$ ll
total 0
drwxr-x--- 5 oracle oinstall 48 Jun 27 21:25 admin
drwxr-xr-x 5 oracle oinstall 45 Mar 7 14:24 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 6 Jun 27 21:27 checkpoints
drwxrwxr-x 11 oracle oinstall 119 Mar 7 12:41 diag
drwxr-x--- 8 oracle oinstall 90 Jun 27 21:25 fast_recovery_area
drwxr-x--- 5 oracle oinstall 48 Jun 27 21:25 oradata
drwxrwxr-x 3 oracle oinstall 19 Mar 7 10:12 product
[oracle@dbtest oracle]$
[oracle@dbtest oracle]$ ll fast_recovery_area/orcl/
total 9520
-rw-r----- 1 oracle oinstall 9748480 Jul 13 15:39 control02.ctl
[oracle@dbtest oracle]$
[oracle@dbtest oracle]$ rm -f fast_recovery_area/orcl/control02.ctl
[oracle@dbtest oracle]$
[oracle@dbtest oracle]$ ll fast_recovery_area/orcl/
total 0
3、异机端列出备份集
回到root用户下操作：
[root@dbtest ~]# cd /usr/openv/netbackup/bin/
[root@dbtest bin]#
[root@dbtest bin]# ./bplist -C 192.168.1.10 -S nbusrv -t 4 -s 07/12/2023 -e 07/13/2023 -l -R /
-rw-rw---- oracle asmadmin 33554432 Jul 13 02:08 /orcl_cntrl_20536_1_1142042924
-rw-rw---- oracle asmadmin 199229440 Jul 13 02:07 /orcl_al_20535_1_1142042868
-rw-rw---- oracle asmadmin 982515712 Jul 13 02:07 /orcl_al_20534_1_1142042868
-rw-rw---- oracle asmadmin 900202496 Jul 13 02:07 /orcl_al_20533_1_1142042868
-rw-rw---- oracle asmadmin 991166464 Jul 13 02:07 /orcl_al_20532_1_1142042823
-rw-rw---- oracle asmadmin 965476352 Jul 13 02:07 /orcl_al_20531_1_1142042822
按需求列出时间点备份集信息，这里只截取部分内容，主要获取控制文件备份集名称 /orcl_cntrl_20536_1_1142042924 。
4、异机端执行恢复
回到Oracle用户下，确认DB是nomount状态：
[oracle@dbtest ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-JUL-2023 16:04:30
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 08-MAR-2023 09:39:23
Uptime 127 days 6 hr. 25 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dbtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest.novalocal)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[oracle@dbtest ~]$
[oracle@dbtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 13 16:08:57 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
恢复控制文件命令：
run {
allocate channel ch00 type 'sbt_tape';
send 'NB_ORA_SERV=nbusrv,NB_ORA_CLIENT=192.168.1.10';
restore controlfile from '/orcl_cntrl_20536_1_1142042924'; ## 这里填写获取的控制文件备份集名称。
sql 'alter database mount';
release channel ch00;
}
登录rman，恢复控制文件：
[oracle@dbtest ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 13 16:11:05 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: orcl (not mounted)
RMAN>
RMAN> run {
allocate channel ch00 type 'sbt_tape';
send 'NB_ORA_SERV=nbusrv,NB_ORA_CLIENT=192.168.1.10';
restore controlfile from '/orcl_cntrl_20536_1_1142042924';
sql 'alter database mount';
release channel ch00;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=156 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
sent command to channel: ch00
Starting restore at 13-JUL-23
channel ch00: restoring control file
channel ch00: restore complete, elapsed time: 00:00:35
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 13-JUL-23
sql statement: alter database mount
released channel: ch00
恢复完，库处于mount状态。
恢复数据文件命令：
run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
send 'NB_ORA_SERV=nbusrv,NB_ORA_CLIENT=192.168.1.10';
set until time = "to_date('2023-07-13 02:00:00','yyyy-mm-dd hh24:mi:ss')"; ## 填写要恢复的时间点。
set newname for database to '/data/orcl/%b';
restore database;
switch datafile all;
recover database;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}
这里碰到一个报错：
RMAN> run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
send 'NB_ORA_SERV=nbusrv,NB_ORA_CLIENT=192.168.1.10';
set until time = "to_date('2023-07-13 02:00:00','yyyy-mm-dd hh24:mi:ss')";
set newname for database to '/data/orcl/%b';
restore database;
switch datafile all;
recover database;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16>
allocated channel: ch00
channel ch00: SID=156 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch01
channel ch01: SID=189 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch02
channel ch02: SID=221 device type=SBT_TAPE
channel ch02: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch03
channel ch03: SID=3 device type=SBT_TAPE
channel ch03: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
sent command to channel: ch00
sent command to channel: ch01
sent command to channel: ch02
sent command to channel: ch03
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 13-JUL-23
Starting implicit crosscheck backup at 13-JUL-23
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/13/2023 16:20:24
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
网上搜到办法，以下处理过程：
RMAN> delete obsolete device type disk;
Starting implicit crosscheck backup at 13-JUL-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=189 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=221 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3 device type=DISK
Crosschecked 3 objects
Crosschecked 3 objects
Crosschecked 4 objects
Crosschecked 3 objects
Finished implicit crosscheck backup at 13-JUL-23
Starting implicit crosscheck copy at 13-JUL-23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Crosschecked 1 objects
Finished implicit crosscheck copy at 13-JUL-23
searching for all files in the recovery area
cataloging files...
no files cataloged
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=189 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=221 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3 device type=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 19772 28-JUN-23
Backup Piece 19772 28-JUN-23 /backup/rman/orcl0628/orcl_20230628_3373971864.199931
Backup Set 19778 28-JUN-23
Backup Piece 19778 28-JUN-23 /backup/rman/orcl0628/orcl_20230628_spfile_gu1vqu9b_1_1.rman
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/backup/rman/orcl0628/orcl_20230628_3373971864.199931 RECID=19772 STAMP=1140683139
Deleted 1 objects
deleted backup piece
backup piece handle=/backup/rman/orcl0628/orcl_20230628_spfile_gu1vqu9b_1_1.rman RECID=19778 STAMP=1140685099
Deleted 1 objects
RMAN>
RMAN> allocate channel for maintenance device type 'sbt_tape';
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: SID=156 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
重新执行恢复：
RMAN> run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
send 'NB_ORA_SERV=nbusrv,NB_ORA_CLIENT=192.168.1.10';
2> 3> 4> 5> set until time = "to_date('2023-07-13 02:00:00','yyyy-mm-dd hh24:mi:ss')";
set newname for database to '/data/orcl/%b';
restore database;
switch datafile all;
recover database;
release channel ch00;
release channel ch01;
release channel ch026> ;
release channel ch03;
}7> 8> 9> 10> 11> 12> 13> 14> 15> 16>
allocated channel: ch00
channel ch00: SID=189 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch01
channel ch01: SID=221 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch02
channel ch02: SID=3 device type=SBT_TAPE
channel ch02: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
allocated channel: ch03
channel ch03: SID=33 device type=SBT_TAPE
channel ch03: Veritas NetBackup for Oracle - Release 7.7.3 (2016051915)
sent command to channel: ORA_MAINT_SBT_TAPE_1
sent command to channel: ch00
sent command to channel: ch01
sent command to channel: ch02
sent command to channel: ch03
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 13-JUL-23
creating datafile file number=9 name=/data/orcl/test_tabspace.397.1038571005
creating datafile file number=12 name=/data/orcl/test_tabspace.951.1038571005
creating datafile file number=23 name=/data/orcl/test_tabspace.387.1038571005
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00015 to /data/orcl/test_tabspace.954.1038571005
channel ch00: reading from backup piece orcl_bk_19792_1_1140213632
channel ch01: starting datafile backup set restore
channel ch01: specifying datafile(s) to restore from backup set
channel ch01: restoring datafile 00041 to /data/orcl/test_tabspace.1150.1108547591
channel ch01: reading from backup piece orcl_bk_19797_1_1140214348
channel ch02: starting datafile backup set restore
channel ch02: specifying datafile(s) to restore from backup set
channel ch02: restoring datafile 00003 to /data/orcl/undotbs1.361.1038565947
channel ch02: restoring datafile 00016 to /data/orcl/test_tabspace.262.1038571005
channel ch02: restoring datafile 00032 to /data/orcl/test_tabspace.1097.1081805359
channel ch02: restoring datafile 00048 to /data/orcl/test_tabspace.1157.1108547687
channel ch02: restoring datafile 00049 to /data/orcl/test_tabspace.1206.1124186593
channel ch02: reading from backup piece orcl_bk_20517_1_1142036125
channel ch03: starting datafile backup set restore
channel ch03: specifying datafile(s) to restore from backup set
channel ch03: restoring datafile 00019 to /data/orcl/test_tabspace.358.1038571005
channel ch03: restoring datafile 00025 to /data/orcl/test_tabspace.993.1039103463
channel ch03: restoring datafile 00035 to /data/orcl/test_tabspace.1143.1099745951
channel ch03: restoring datafile 00045 to /data/orcl/test_tabspace.1154.1108547597
channel ch03: restoring datafile 00052 to /data/orcl/test_tabspace.1220.1138266035
channel ch03: reading from backup piece orcl_bk_20519_1_1142036126
等待恢复任务完成，任务可以在NBU Administration console看到：
恢复完成提示缺少归档，那就recover到提示的scn即可：
channel ch01: restore complete, elapsed time: 00:00:25
archived log file name=/data/arch/3_11171_1038566043.dbf thread=3 sequence=11171
archived log file name=/data/arch/4_10219_1038566043.dbf thread=4 sequence=10219
archived log file name=/data/arch/4_10220_1038566043.dbf thread=4 sequence=10220
archived log file name=/data/arch/1_21099_1038566043.dbf thread=1 sequence=21099
archived log file name=/data/arch/3_11172_1038566043.dbf thread=3 sequence=11172
archived log file name=/data/arch/2_11282_1038566043.dbf thread=2 sequence=11282
unable to find archived log
archived log thread=2 sequence=11283
released channel: ch00
released channel: ch01
released channel: ch02
released channel: ch03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/14/2023 18:35:04
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11283 and starting SCN of 16983905730736
RMAN>
RMAN> recover database until scn 16983905730736; ## 手动恢复到提示的scn点。
Starting recover at 15-JUL-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=221 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=3 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=33 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:08
Finished recover at 15-JUL-23
5、恢复后开库处理
由于异机端存放文件的路径和源库不同，所以要先处理redo。
登录数据库，可以看到redo的路径是源库的路径：
[oracle@dbtest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 15 21:45:20 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> set pagesize 50 linesize 300
col member for a48
select * from v$logfile;SQL> SQL>
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------ ---
3 ONLINE +DATA/orcl/onlinelog/group_3.605.1038566047 NO
2 ONLINE +DATA/orcl/onlinelog/group_2.604.1038566045 NO
1 ONLINE +DATA/orcl/onlinelog/group_1.363.1038566043 NO
10 ONLINE +DATA/orcl/onlinelog/group_10.352.1038566137 NO
11 ONLINE +DATA/orcl/onlinelog/group_11.351.1038566139 NO
12 ONLINE +DATA/orcl/onlinelog/group_12.382.1038566141 NO
7 ONLINE +DATA/orcl/onlinelog/group_7.261.1038566143 NO
8 ONLINE +DATA/orcl/onlinelog/group_8.405.1038566145 NO
9 ONLINE +DATA/orcl/onlinelog/group_9.425.1038566145 NO
4 ONLINE +DATA/orcl/onlinelog/group_4.426.1038566147 NO
5 ONLINE +DATA/orcl/onlinelog/group_5.418.1038566149 NO
6 ONLINE +DATA/orcl/onlinelog/group_6.404.1038566151 NO
rename一下redo，改成异机端的路径：
alter database rename file '+DATA/orcl/onlinelog/group_3.605.1038566047' to '/data/orcl/group_3.605.1038566047';
alter database rename file '+DATA/orcl/onlinelog/group_2.604.1038566045' to '/data/orcl/group_2.604.1038566045';
alter database rename file '+DATA/orcl/onlinelog/group_1.363.1038566043' to '/data/orcl/group_1.363.1038566043';
alter database rename file '+DATA/orcl/onlinelog/group_10.352.103856613' to '/data/orcl/group_10.352.103856613';
alter database rename file '+DATA/orcl/onlinelog/group_11.351.103856613' to '/data/orcl/group_11.351.103856613';
alter database rename file '+DATA/orcl/onlinelog/group_12.382.103856614' to '/data/orcl/group_12.382.103856614';
alter database rename file '+DATA/orcl/onlinelog/group_7.261.1038566143' to '/data/orcl/group_7.261.1038566143';
alter database rename file '+DATA/orcl/onlinelog/group_8.405.1038566145' to '/data/orcl/group_8.405.1038566145';
alter database rename file '+DATA/orcl/onlinelog/group_9.425.1038566145' to '/data/orcl/group_9.425.1038566145';
alter database rename file '+DATA/orcl/onlinelog/group_4.426.1038566147' to '/data/orcl/group_4.426.1038566147';
alter database rename file '+DATA/orcl/onlinelog/group_5.418.1038566149' to '/data/orcl/group_5.418.1038566149';
alter database rename file '+DATA/orcl/onlinelog/group_6.404.1038566151' to '/data/orcl/group_6.404.1038566151';
resetlog开库：
SQL> alter database open resetlogs;
Database altered.
同样处理一下temp路径：
SQL> col name for a50
col tablespace_name for a25
col file_name for a39
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
SQL>
STATUS ENABLED NAME FILE_SIZE
------- ---------- -------------------------------------------------- ----------
ONLINE READ WRITE +DATA/orcl/tempfile/temp.606.1038566051 0
SQL>
SQL> alter tablespace TEMP add tempfile '/data/orcl/temp.606.1038566051' size 500M autoextend on next 100M maxsize 32767M;
Tablespace altered.
SQL>
SQL> alter database tempfile '+DATA/orcl/tempfile/temp.606.1038566051' drop;
Database altered.
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_NAME FILE_SIZE AUT
------------------------- --------------------------------------- ---------- ---
TEMP /data/orcl/temp.606.1038566051 500 YES
SQL>
SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;
STATUS ENABLED NAME FILE_SIZE
------- ---------- -------------------------------------------------- ----------
ONLINE READ WRITE /data/orcl/temp.606.1038566051 500
顺手关掉归档：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4943876096 bytes
Fixed Size 2261688 bytes
Variable Size 989859144 bytes
Database Buffers 3942645760 bytes
Redo Buffers 9109504 bytes
Database mounted.
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /data/arch
Oldest online log sequence 1
Current log sequence 1
最后检查状态：
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL>
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
---------------------------------------------- --------------------
orcl READ WRITE
[oracle@dbtest ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-JUL-2023 22:07:37
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 08-MAR-2023 09:39:23
Uptime 129 days 12 hr. 28 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dbtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbtest.novalocal)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
异机数据恢复到这里就完成了，剩下的交给应用方验证数据。
最后修改时间：2023-07-18 10:42:21
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
