Before restoration and recovery of PDB (Pluggable database), we need to ensure that valid rman backup available. We have already discussed the same in another post. Please check here for more information regarding RMAN Backup.
Start the database with nomount.
$sqlplus / as sysdba
SQL>startup nomount pfile='/home/oracle/ora12c/dbs/orcl.pfile';
RMAN> restore controlfile from '/backup01/rmanbkp/*****';
SQL>alter database mount;
Now we will restore the database.
$RMAN target sysbackup/*****@PDBORCL1
Connected to target database: ORA12CP (DBID=1429060559, not open)
RMAN> run {
allocate channel 'dev_0' type 'sbt_tape'
parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=TESTdb.com)';
restore pluggable database PDBORCL1;
recover pluggable database PDBORCL2;
}
Note:- If datafile destination different as compare to source database. Use SET NEWNAME FOR DATAFILE and then use SWITCH DATAFILE ALL. Pls check here for more information.
Before execute resetlogs , need to update redologfiles and tempfiles. For more info check here.
SQL> alter database open resetlogs;
Run below to check the status of Pluggable Database(PDB).
SQL> select name,open_mode from v$pdbs;
To open all pluggabel database.
SQL>alter pluggable database all open;
In this article we will discuss the 'script to delete concurrent program and executable from backend'.
------------------------------------------------------------------------------
delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
-- delete_program (program_short_name, application_short_name)
-- delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------
DECLARE
lv_prog_short_name VARCHAR2(240);
lv_appl_short_name VARCHAR2(240);
BEGIN
-- set the variables first
lv_prog_short_name := 'XX_BOE_DTLS';-- concurrent program(XX BOE Details Reports) short name
lv_appl_short_name := 'EXE_BOE_DTLS';-- application short name
-- see if the program exists. if found, delete the program
IF fnd_program.program_exists (lv_prog_short_name, lv_appl_short_name) AND
fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)
THEN
fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);
fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);
COMMIT;
DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');
-- if the program does not exist in the system
ELSE
DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
END;
---------------------------------------------------------------------------------
Script for deleting the data definition.
------------------------------------------------>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('INV','NRGINTR');
END;
--------------------------------------------------------
--------------------------------------------------------
Script for deleting the template.
------------------------------------------------------>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('INV','NRGINTR');
END;
-----------------------------------------------------------------------------------------------------------
We can also use it as below.
Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;
If we have concurrent program 'XX_BOE_Details_Reports' with short name 'XX_BOE_DTLS' and we have executable name 'EXE_BOE_DTLS' of concurrent program in apps schema then we can just use it to 'delete concurrent program from backend' as below.
Begin
fnd_program.delete_program('XX_BOE_DTLS','APPS');
fnd_program.delete_executable('EXE_BOE_DTLS','APPS');
commit;
End;
Note:- The same concurrent program can be disable through front-end if you decide not to use it.
How to fix ORA-19809 limit exceeded for recovery files.
In this article we will discuss 'how to resolve ORA-19809 and ORA-19804' error. This has very common issue while doing RMAN backup, we have faced ORA-19809 and ORA-19804 error due to recovery destination full. In this scenario we will have to remove old archivelog (expired or unwanted) or increase the size of db_recovery_file_dest_size .
RMAN> backup tablespace users;
Starting backup at 24-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 09/24/2019 14:38:54
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 67108864 bytes disk space from 5033164800 limit
Check the current value of db_recovery_file_dest_size.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /data01/12cdatabase/oracle/fast_recovery_area
db_recovery_file_dest_size big integer 4800M
Now we will check the space used .
SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from v$recovery_file_dest;
SPACE_USED(GB) SPACE_LIMIT(GB)
-------------- ---------------
4.68073177 4.6875
After that we need to increase the db_recovery_file_dest_size
SQL>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=10g SCOPE=SPFILE;
SQL> shut immediate;
SQL> Startup;
If you don't want to shutdown database.You may delete the expired archive log online. We should 'delete files from recovery area using rman' on regular basis.
RMAN> delete expired archivelog all;
RMAN>delete noprompt expired backup;
RMAN>delete noprompt expired archivelog all;
RMAN>delete noprompt obsolete recovery window of 7 days;
Verify the recovery_file_dest size.
SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from v$recovery_file_dest;
SPACE_USED(GB) SPACE_LIMIT(GB)
-------------- ---------------
4.80296659 10
Now we can start the backup as below.
[oracle@prod ~]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Tue Sep 24 16:32:38 2019
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: GCDB1 (DBID=1045104251)
RMAN> backup tablespace users;
Starting backup at 24-SEP-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=305 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/data01/12cdatabase/oracle/oradata/gcdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-SEP-19
channel ORA_DISK_1: finished piece 1 at 24-SEP-19
piece handle=/data01/12cdatabase/oracle/fast_recovery_area/GCDB1/backupset/2019_09_24/o1_mf_nnndf_TAG20190924T163242_grmxymnv_.bkp tag=TAG20190924T163242 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-SEP-19
Starting Control File and SPFILE Autobackup at 24-SEP-19
piece handle=/data01/12cdatabase/oracle/fast_recovery_area/GCDB1/autobackup/2019_09_24/o1_mf_s_1019838764_grmxyplb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 24-SEP-19
Now we can see in RMAN Backup logs no 'ORA-19809 limit exceeded for recovery files' showing.
