PDB point in time recovery by Franck Pachot

Task
Source database has multiply PDBs. User wants to restore in specific point in time only one of them
Here is the Franck’s answer and script

There is no way other than point-in-time duplicate to an auxiliary CDB and unplug to put wherever you want. You can skip pluggable databases so that you restore only CDB$ROOT and your PDB. This process is automated by RMAN when restoring in-place (a temporary auxiliary database is created for CDB$ROOT) but not for your case.
Here is a script I used to demo it once on a lab:

---# create an init.ora  
cat > /tmp/CDB_TEMP.ora <<'CAT'  
db_name=CDB_TEMP  
# I set a temporary domain to avoid any conflict  
db_domain=[temp.dbi-services.com](http://temp.dbi-services.com/)  
enable_pluggable_database=true  
compatible=19.0.0.0  
db_block_size=8192  
db_files=200  
sga_target=1024M  
processes=150  
db_create_file_dest=/u02/oradata  
db_recovery_file_dest=/u90/fast_recovery_area  
db_recovery_file_dest_size=1G  
#_clone_one_pdb_recovery=true # this is used by automated in-place PDBPITR so it may be cool ;)  
#_system_trig_enabled=false  
CAT  
---# Start the instance  
ORACLE_SID=CDB_TEMP sqlplus / as sysdba <<<"startup force nomount pfile='/tmp/CDB_TEMP.ora';"  
---## RMAN duplicate  
---# RMAN connect to target and auxiliary  
ORACLE_SID=CDB_TEMP rman  
set echo on  
connect target sys/manager@//localhost:1521/[CDB1.it.dbi-services.com](http://cdb1.it.dbi-services.com/)  
alter system archive log current  
/  
connect auxiliary /  
---# list PDBs to exclude (remove the one you want to keep!)  
select listagg(pdb_name,',')within group(order by pdb_name) from dba_pdbs  
/  
---# duplicate skip PDBs(use no-open just to show funny name)  
duplicate database CDB1_SITE1 to CDB_TEMP skip pluggable database CDB1PDB02,CDB1PDB03 until restore point 'DEMO_OOP_PDB_PITR' noopen;  
quit;  
---# Open it (because we did a noopen duplicate)  
ORACLE_SID=CDB_TEMP sqlcl / as sysdba  
show pdbs  
alter database open resetlogs  
/  
show pdbs  

  

Cleanup:

  

---# remove the temporary CDB  
ORACLE_SID=CDB_TEMP rman target /  
startup dba mount force;  
drop database noprompt;  
quit;  
rm -rf /u??/?*/CDB_TEMP $ORACLE_BASE/diag/rdbms/cdb_temp $ORACLE_HOME/rdbms/log/cdb_temp* $ORACLE_HOME/rdbms/audit/CDB_TEMP* $ORACLE_HOME/dbs/?*CDB_TEMP* $ORACLE_BASE/admin/CDB_TEMP  

I doubt there will ever be another solution because you need a CDB$ROOT at same point-in-time in order to open the PDB and unplug it.

Note that, as an alternative, if you have a standby you may stop apply, flashback the pdb, convert to snapshot standby, clone the pdb, and get all back to physical standby.

DOP downgrade

Для версии 12 (по крайней мере 2) как написано тут DOP downgrade reason можно посмотреть в SQL Monitor в Other column for PX coordinator shows the downgrade reason, а сами причины расшифровать запросом

select qksxareasons, indx 
from x$qksxa_reason 
where qksxareasons like '%DOP downgrade%';

DOP downgrade due to adaptive DOP 351
DOP downgrade due to resource managermax DOP 352
DOP downgrade due to insufficient number of processes 353
DOP downgrade because slaves failed to join 354

В 11.2 такой таблички я не нашел, поэтому там придется пользоваться старым добрым

alter session set "_px_trace"=high,all;

который прекрасно описан тут

Execute as sysdba

One can use os_command package to create and execute script from database server as sysdba user. Oracle white paper about this package
Steps to implement:

1. Create directory to store file and grant permission to user

create or replace directory &tmpdir as '&tmppath';
grant read, write, execute on directory &tmpdir to &username;

2. Grant execute permissions

begin
  dbms_java.grant_permission
      (upper('&username'),
       'java.io.FilePermission',
       '<<ALL FILES>>',
       'read,write,execute');
  dbms_java.grant_permission
      (upper('&username'),
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor');
end;
/

3. Write file content with SQL or PL/SQL commands

--run sqlplus script
begin
  lob_writer_plsql.write_clob(
    '&tmpdir', '&scriptname',
q'[#!/bin/bash

ORACLE_SID=&dbname
ORACLE_HOME=/data/oracle/product/&dbver.&subver
PATH=/bin:/sbin:/usr/bin:${PATH}
export ORACLE_SID ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -l -s / as sysdba <<!!!
--WRITE HERE WHAT TO DO

--example
SELECT * FROM sys.metaview\$;

--example (notice how to wrap handle "/" char)
BEGIN
  NULL;
END;
]'||q'[/

exit
!!!

exit 0
]'
  );
end;
/

&dbver and &subver can be extracted by query

column dbver new_value dbver
select regexp_substr(banner, 'Release (\d+.\d+.\d+.\d+).\d+', 1, 1, '', 1) dbver
from v$version
where banner like 'Oracle Database%';

4. Execute script

declare
  c clob;
begin
  c := os_command.exec_CLOB('chmod 755 &tmppath/&scriptname');
  c := os_command.exec_CLOB('&tmppath/&scriptname');
  dbms_output.put_line(c);
  c := os_command.exec_CLOB('rm &tmppath/&scriptname');
end;
/