生产环境开发人员误删除包，导致应用系统异常，由于包中涉及代码太多，没有脚本备份，急需恢复。
本来想着尝试闪回查询（undo_retention无guarantee），但为时已晚（故障应第一时间上报处理）
SELECT *
FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2022-04-07 13:59:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE OWNER = 'TEST'
AND NAME = '包名'
ORDER BY LINE;
如果存在，那么恭喜你,否则提示ORA-01555快照过旧。
再继续了解，此库无expdp逻辑备份，只有rman备份。
如果有全库对象备份那么就可以通过impdp include=package:方式快速恢复。
所以除了开发人员重写外，就只能考虑从rman备份做恢复了。
两种方法：
1)duplicate target database to tmpdb from active database SKIP TABLESPACE xxx nofilenamecheck;
https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/DUPLICATE.html#GUID-E13D8A02-80F9-49A2-9C31-92DD3A795CE4
http://blog.itpub.net/26474945/viewspace-2889762/
https://blog.csdn.net/freedompuge/article/details/51353753
https://cloud.tencent.com/developer/article/2005345
2)alter database mount clone database;
set until time “to_date(‘2022-04-07 13:59:00’,‘yyyy-mm-dd hh24:mi:ss’)”;
recover database using backup controlfile until time ‘2022-04-07 13:59:00’;
以下方法(手动TSPITR表空间时间点恢复)非常适合的场景：数据库有rman备份，并且只需要恢复全库或部分表空间的任意对象。
How a Clone Database Is Mounted
A clone database is a specialized copy of a database that can be used for tablespace point-in-time recovery. When you perform tablespace point-in-time recovery, you mount the clone database and recover the tablespaces to the desired time, then export metadata from the clone to the primary database and copy the datafiles from the recovered tablespaces.
See Also:
* Oracle9i Recovery Manager User's Guide
* Oracle9i User-Managed Backup and Recovery Guide
for detailed information about clone databases and tablespace point-in-time recovery
--after 11g
About Duplicating a Database to a Past Point-in-Time
You can use clauses in the DUPLICATE command to duplicate a database to a past point in time.
By default, the DUPLICATE command creates the duplicate database by using the most recent backups of the target database and then performs recovery to the most recent consistent point contained in the incremental backups and archived redo logs. However, you can recover the duplicate database to a past point in time by using one of the following methods:
DUPLICATE … UNTIL command
SET UNTIL command before the DUPLICATE command
See Also:
racle Database Backup and Recovery Reference for an example of duplicating a database to a past point in time
By default, RMAN performs complete recovery. For point-in-time recovery, the best practice is to enter a SET UNTIL command before both the RESTORE and RECOVER commands in a RUN command so that the UNTIL time applies to both commands. If you run SET UNTIL after restoring the database, then you may not be able to recover the database to the target time because the restored files may have timestamps later than the target time.
Note:You must open the database with the RESETLOGS option after incomplete recovery or recovery with a backup control file.
如果有条件，务必将备份集拷贝到测试环境中做不完全恢复即可，避免误操作造成生产事故。
如果在生产环境服务中做恢复，请慎重操作。
以下是在生产服务器中新起实例进行恢复
恢复步骤参考：
1.根据主库准备临时库的参数文件：create pfile='t.txt' from spfile;
db_name不变，增加不同的db_unique_name。
编辑参数保留如下内容，并调整pga,sga大小，创建临时库目录
[oracle@dba rman]$ cat t.txt
*.audit_file_dest='/home/oracle/app/oracle/admin/jyc/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/app/oracle/oradata/jyc/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=1000
*.db_name='db'
*.db_unique_name='jyc'
*.diagnostic_dest='/home/oracle/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=147483648
*.processes=200
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1899345920
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
2.恢复控制文件：
ORACLE_SID和db_unique_name一致。
export ORACLE_SID=jyc
rman target /;
restore controlfile from '/home/oracle/rman/db_incr0_1101388740_29_1';
3.启动到mount clone状态
sql 'alter database mount clone database';
4.备份集路径设定识别
catalog start with '/home/oracle/rman';
yes
5.恢复表空间到指定时间（对象被删之前）
恢复之前需要确认需要恢复的表空间(包是存在system表空间的，所以可以排除用户表空间)在源库中对应的文件号，通过dba_data_files视图核对，日志文件通过v$logfile核对。
run {
allocate channel c1 device type DISK;
allocate channel c2 device type DISK;
set until time "to_date('2022-04-07 13:59:00','yyyy-mm-dd hh24:mi:ss')";
set newname for database to '/home/oracle/app/oracle/oradata/jyc/%b';
restore tablespace system,sysaux,undotbs1;
switch datafile 1;
switch datafile 2;
switch datafile 3;
switch datafile 7;
switch datafile 8;
sql 'alter database datafile 1,2,3,7,8 online';
recover database skip forever tablespace USERS,INDEX_TS,IDR_TS,BILL_TS,IDRERROR_TS,USER_TS,STATIC_TS,INDX,TEMP;
sql "alter database rename file ''/home/oracle/app/oracle/oradata/db/redo01.log'' to ''/home/oracle/app/oracle/oradata/jyc/redo01.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/db/redo02.log'' to ''/home/oracle/app/oracle/oradata/jyc/redo02.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/db/redo03.log'' to ''/home/oracle/app/oracle/oradata/jyc/redo03.log''";
sql "alter database rename file ''/home/oracle/app/oracle/oradata/db/redo04.log'' to ''/home/oracle/app/oracle/oradata/jyc/redo04.log''";
release channel c1;
release channel c2;
}
6.sqlplus连接做恢复，指定所需的归档日志，最后open resetlogs打开。
SQL> recover database using backup controlfile until time '2022-04-07 13:59:00';
ORA-00279: change 420941449509 generated at 04/07/2022 13:45:10 needed for thread 1
ORA-00289: suggestion : /home/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_18_1101374869.dbf
ORA-00280: change 420941449509 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/app/oracle/fast_recovery_area/1_18_1101374869.dbf
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
7.添加临时表空间文件
alter tablespace temp add tempfile '/home/oracle/app/oracle/oradata/jyc/temp01.dbf' size 10M autoextend on next 10M;
模拟测试准备的包、包体
-- 创建包头
create or replace package pkg_jyc is
procedure p_test1(p_1 in varchar2);
function f_test1(p_1 in varchar2) return varchar2;
end pkg_jyc;
/
-- 创建包体(名字必须和包头一样)
create or replace package body pkg_jyc is
v_param1 varchar(20) := 'default';
procedure p_test1(p_1 in varchar2) is
begin
dbms_output.put_line('p_1的值为：'|| p_1);
dbms_output.put_line('全局变量的值为：'||v_param1);
v_param1 := p_1;
dbms_output.put_line('改变后的全局变量值为：'||v_param1);
function f_test1(p_1 in varchar2) return varchar2 is
v_rt varchar2(50);
begin
dbms_output.put_line('获取的全局变量值为：'||v_param1);
v_rt := v_param1||'-'||p_1;
dbms_output.put_line('返回值为：'||v_rt);
return v_rt;
end f_test1;
end pkg_jyc;
/
备份脚本：
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup incremental level 0
format '/home/oracle/rman/db_incr0_%t_%s_%p'
database;
sql 'alter system archive log current';
backup filesperset 10
format '/home/oracle/rman/arch_%t_%s_%p'
archivelog all delete input;
sql 'alter system archive log current';
release channel c1;
release channel c2;
}
导入恢复包和包体：
[oracle@dba rman]$ echo $ORACLE_SID
db
[oracle@dba db]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 12 15:13:16 2022
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, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> desc pkg_jyc;
ERROR:
ORA-04043: object pkg_jyc does not exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@dba db]$ pwd
/home/oracle/app/oracle/fast_recovery_area/db
[oracle@dba db]$ cd /home/oracle/rman
[oracle@dba ~]$ cd rman
[oracle@dba rman]$ ll
total 13099884
-rw-r----- 1 oracle oinstall 203296768 Apr 7 13:19 arch_1101388743_31_1
-rw-r----- 1 oracle oinstall 1399616512 Apr 7 13:19 arch_1101388743_32_1
-rw-r----- 1 oracle oinstall 381206528 Apr 7 13:19 arch_1101388744_33_1
-rw-r----- 1 oracle oinstall 6076620800 Apr 7 13:18 db_incr0_1101388724_27_1
-rw-r----- 1 oracle oinstall 5330362368 Apr 7 13:18 db_incr0_1101388724_28_1
-rw-r----- 1 oracle oinstall 22413312 Apr 7 13:19 db_incr0_1101388740_29_1
-rw-r----- 1 oracle oinstall 98304 Apr 7 13:19 db_incr0_1101388741_30_1
-rw-r----- 1 oracle oinstall 159744 Apr 7 17:14 p1.dmp
-rw-r--r-- 1 oracle oinstall 1102 Apr 7 17:16 p1.log
-rw-r----- 1 oracle oinstall 159744 Apr 7 17:14 p2.dmp
-rw-r----- 1 oracle oinstall 159744 Apr 7 17:04 p.dmp
-rw-r--r-- 1 oracle oinstall 1103 Apr 7 17:04 p.log
-rw-r--r-- 1 oracle oinstall 32 Apr 7 17:15 t1.par
-rw-r--r-- 1 oracle oinstall 2034 Apr 7 13:05 t1.txt
-rw-r----- 1 oracle oinstall 159744 Apr 7 17:16 t.dmp
-rw-r--r-- 1 oracle oinstall 32 Apr 7 17:14 t.par
-rw-r--r-- 1 oracle oinstall 773 Apr 7 16:33 t.txt
[oracle@dba rman]$ impdp jyc/jyc dumpfile=t.dmp logfile=1.log directory=dmp
Import: Release 11.2.0.4.0 - Production on Tue Apr 12 15:14:24 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DMP is invalid
[oracle@dba rman]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 12 15:15:08 2022
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, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> set line 132
SQL> set wrap off
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ----------------------------------------------------------------------
SYS XMLDIR /home/oracle/app/oracle/product/11.2.0/db_1/rdbms/xml
SYS ORACLE_OCM_CONFIG_DIR /home/oracle/app/oracle/product/11.2.0/db_1/ccr/hosts/dba/state
SYS DATA_PUMP_DIR /home/oracle/app/oracle/admin/db/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 /home/oracle/app/oracle/product/11.2.0/db_1/ccr/state
SQL> create directory dmp as '/home/oracle/rman';
Directory created.
SQL> grant read,write on directory dmp to public;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@dba rman]$ impdp jyc/jyc dumpfile=t.dmp logfile=1.log directory=dmp
Import: Release 11.2.0.4.0 - Production on Tue Apr 12 15:16:14 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Master table "JYC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_FULL_01": jyc/******** dumpfile=t.dmp logfile=1.log directory=dmp
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"JYC"."PKG_JYC" created with compilation warnings
Job "JYC"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Apr 12 15:16:16 2022 elapsed 0 00:00:01
[oracle@dba rman]$ sqlplus jyc/jyc
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 12 15:16:24 2022
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, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> desc pkg_jyc
FUNCTION F_TEST1 RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_1 VARCHAR2 IN
PROCEDURE P_TEST1
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_1 VARCHAR2 IN
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@dba rman]$
expdp参数匹配问题：
[oracle@dba rman]$ expdp \'/ as sysdba\' dumpfile=p1.dmp logfile=p1.log SCHEMAS=JYC directory=dmp INCLUDE=PACKAGE:\"like '%JYC%'\"
Export: Release 11.2.0.4.0 - Production on Thu Apr 7 17:11:27 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00911: invalid character
必须把参数INCLUDE=PACKAGE:"LIKE '%JYC%'"写到par文件中调用才行，并且需要注意大写。
[oracle@dba rman]$ cat t.par
INCLUDE=PACKAGE:"LIKE '%JYC%'"
[oracle@dba rman]$ cat t1.par
INCLUDE=PACKAGE:"LIKE '%jyc%'"
[oracle@dba rman]$ expdp \'/ as sysdba\' dumpfile=t1.dmp logfile=p1.log SCHEMAS=JYC directory=dmp parfile=t1.par
Export: Release 11.2.0.4.0 - Production on Thu Apr 7 17:16:00 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=t1.dmp logfile=p1.log SCHEMAS=JYC directory=dmp parfile=t1.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema JYC was not found.
ORA-39168: Object path PACKAGE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 3 error(s) at Thu Apr 7 17:16:02 2022 elapsed 0 00:00:02
[oracle@dba rman]$ expdp \'/ as sysdba\' dumpfile=t.dmp logfile=p1.log SCHEMAS=JYC directory=dmp parfile=t.par
Export: Release 11.2.0.4.0 - Production on Thu Apr 7 17:16:15 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" dumpfile=t.dmp logfile=p1.log SCHEMAS=JYC directory=dmp parfile=t.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/rman/t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Apr 7 17:16:17 2022 elapsed 0 00:00:02
[oracle@dba rman]$ strings t.dmp|grep PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
PKG_JYC
如需避免意外对象删除，除了人员三思后行外，后续技术上可建议：
1.增加expdp的CONTENT=METADATA_ONLY对象备份
2.如果有条件部署dataguard+延迟6小时恢复
3.如果有条件部署dataguard+备库flashback启用
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
