Oracle >18c Rman Duplication of a PDB To another CDB

Oracle >18c Rman Duplication of a PDB To another CDB ..

Oracle >18c Rman Duplication of a PDB To another CDB

PDB Duplication



source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb) ) )
dest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.209)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb) ) )




#rman target sys/oracle@source auxiliary sys/oracle@dest


tnsping dest

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2022 03:50:51

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.209)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb)))
OK (10 msec)



[oracle@Vihaan admin]$ tnsping source

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2022 03:50:59

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb)))
OK (340 msec)


===========================================


check below 
sqlplus 'sys/oracle@source as sysdba'
sqlplus 'sys/oracle@dest as sysdba'


on 192.168.1.211

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORCL1                          READ WRITE NO  >>>>> create duplicate to ORCLD on 192.168.1.209




on 192.168.1.209 i.e dest

alter system set remote_recovery_file_dest='/u01/app/oracle/Remote_Recovery' scope=both;


Create directory for new PDB:

 mkdir -p /u01/app/oracle/oradata/ORCLCDB/orcld




rman

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 19 04:01:27 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/oracle@source

connected to target database: ORCLCDB (DBID=2780785463)

RMAN> connect auxiliary sys/oracle@dest

connected to auxiliary database: ORCLCDB (DBID=2780785463)

RMAN>



RMAN> connect target sys/oracle@source
RMAN> connect auxiliary sys/oracle@dest



DUPLICATE PLUGGABLE DATABASE ORCL1 as ORCLD TO orclcdb DB_FILE_NAME_CONVERT('orcl1','orcld') FROM ACTIVE DATABASE SECTION SIZE 10M;




                                              [Instance i.e dest] 
DUPLICATE PLUGGABLE DATABASE ORCL1 as ORCLD TO orclcdb    DB_FILE_NAME_CONVERT('orcl1','orcld') FROM ACTIVE DATABASE SECTION SIZE 10M;

RMAN>
RMAN>

Starting Duplicate PDB at 19-SEP-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
current log archived
duplicating Online logs to Oracle Managed File (OMF) location

contents of Memory Script:
{
   set newname for datafile  16 to
 "/u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf";
   set newname for datafile  17 to
 "/u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf";
   set newname for datafile  18 to
 "/u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf";
   set newname for datafile  19 to
 "/u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf";
   restore
   from  nonsparse   section size
 10 m   clone foreign pluggable database
    "ORCL1"
   from service  'source'   ;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 19-SEP-22
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 1 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 2 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 3 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 4 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 5 of 27
.
.
.
.

channel ORA_AUX_DISK_1: restoring section 21 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 22 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 23 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 24 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 25 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-SEP-22
current log archived

contents of Memory Script:
{
   set archivelog destination to  '/u01/app/oracle/Remote_Recovery';
   restore clone force from service  'source'
           foreign archivelog from scn  3145371;
}
executing Memory Script

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 19-SEP-22
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=/u01/app/oracle/Remote_Recovery
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=/u01/app/oracle/Remote_Recovery
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 19-SEP-22

Performing import of metadata...
Finished Duplicate PDB at 19-SEP-22


===============================================================


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORCLD                          READ WRITE NO


set linesize 300
col NAME for a70
select con_id,name from v$datafile where con_id=4;

  CON_ID NAME
---------- ----------------------------------------------------------------------
         4 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
         4 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf
         4 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf
         4 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf





set linesize 300
COLUMN time FORMAT A30
COLUMN name FORMAT A30
COLUMN cause FORMAT A30
COLUMN message FORMAT A30
col ACTION for a70
select name, cause, type, status,action,message,time from pdb_plug_in_violations;


NAME                           CAUSE                          TYPE      STATUS    ACTION                                                                 MESSAGE                        TIME
------------------------------ ------------------------------ --------- --------- ---------------------------------------------------------------------- ------------------------------ ------------------------------
PDB$SEED                       SQL Patch                      ERROR     RESOLVED  Call datapatch to install in the PDB or the CDB                        '19.3.0.0.0 Release_Update 190 31-MAY-19 03.17.50.323190 PM
                                                                                                                                                         4101227' is installed in the C
                                                                                                                                                         DB but no release updates are
                                                                                                                                                         installed in the PDB

RMAN JOBs in Detail and monitoring

define 1='14-09-20 15:19:57'  --- Change based on your requirement 
-- open_time >sysdate -1  --- current status 
-- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill



alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

set lines 1500
set pages 100
col CLI_INFO format a10
col spid 		format a5
col ch 			format a20
col seconds 		format 999999.99
col filename 		format a70
col bfc  		format 9
col "% Complete" 	format 999.99
col event 		format a40
col kill 		for a15
set numwidth 10


select sysdate from dual;
REM gv$session_longops (channel level)
prompt
prompt Channel progress - gv$session_longops:
prompt

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill, CLIENT_INFO ch, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete"
     FROM gv$session_longops o, gv$session s
     WHERE opname LIKE 'RMAN%'
     AND opname NOT LIKE '%aggregate%'
     AND o.sid=s.sid
     AND totalwork != 0
     AND sofar <> totalwork;

REM Check wait events (RMAN sessions) - this is for CURRENT waits only
REM use the following for 11G+
prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt


select ''''||sid ||','|| serial#||',@'||inst_id ||''''  kill, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds from gv$session 
where program like '%rman%' 
and wait_time = 0 
and not action is null;


REM gv$backup_async_io
prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece

prompt if backup_tape_io_slaves=TRUE:
prompt


select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill,  CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid 
--and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') 
and open_time >sysdate -1
order by 2,7;


REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
col FILENAME for a50
set line 400 pagesize 100
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  kill, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,
open_time open, io_count
from gv$backup_sync_io a, gv$session s
where
a.sid=s.sid 
--and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') 
and open_time >sysdate -1
;

dbms_xplan.display_cursor with gather_plan_statistics hint 

gather_plan_statistics hint 

-- Table Create ----

create table teststats as select * from all_objects;

select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where object_id between 100 and 200;


col sql_text for a100 wrap
select inst_id,sql_id,child_number,sql_fulltext FROM gv$sql
WHERE 1=1
and sql_text LIKE '%ANUJTEST%'
and SQL_TEXT not like '%from gv$sql%';

   INST_ID SQL_ID        CHILD_NUMBER SQL_FULLTEXT
---------- ------------- ------------ ----------------------------------------------------------------------------------------------------
         1 5jv62xqkdd1tz            0 SELECT x.*  FROM v$sql s,  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_num
         1 c69d8vn5pdn8t            0 SELECT x.*
                                        FROM v$sql s,
                                             TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch

         1 6wbxyxvnhqfsh            0 SELECT x.*
                                        FROM v$sql s,
                                             TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch

         1 2197z7183vsgc            0 select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where obje  <<<<<<< this one !!!



SELECT x.*  FROM gv$sql s,  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) x 
WHERE 1=1
and s.sql_text LIKE '%ANUJTEST%'
and SQL_TEXT not like '%from gv$sql';



set line 300 pagesize 300
col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc',cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE +PROJECTION +REMOTE +NOTE'));
 




PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2197z7183vsgc, child number 0
-------------------------------------
select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats
where object_id between 100 and 200

Plan hash value: 1317213715

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |       |   392 (100)|          |      1 |00:00:00.11 |    1443 |   1111 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |    13 |            |          |      1 |00:00:00.11 |    1443 |   1111 |
|*  2 |   TABLE ACCESS FULL| TESTSTATS |      1 |     16 |   208 |   392   (1)| 00:00:01 |     96 |00:00:00.01 |    1443 |   1111 |
-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TESTSTATS@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TESTSTATS"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


50 rows selected.

===========================================================================================


or


set linesize 300 pagesize 300
alter session set statistics_level='ALL'; 

col PLAN_TABLE_OUTPUT for a150
select * from table(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc', cursor_child_no => 0,FORMAT =>'allstats')); 


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2197z7183vsgc, child number 0
-------------------------------------
select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats
where object_id between 100 and 200

Plan hash value: 1317213715

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.11 |    1443 |   1111 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.11 |    1443 |   1111 |
|*  2 |   TABLE ACCESS FULL| TESTSTATS |      1 |     16 |     96 |00:00:00.01 |    1443 |   1111 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

  Recover Pluggable Database  ..

Recover Pluggable Database

Performing Point-In-Time Recovery for a Pluggable Database
Restoring and recovering pluggable database using point-in-time recovery

on 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0




full_backup.sh

export DATE=$(date +%y-%m-%d_%H%M%S)

rman target / log=/u01/app/Rman/proddb_${DATE}.log << EOF
run
{
allocate channel ch1 device type disk format '/u01/app/Rman/proddb_full_bkp_%u';
allocate channel ch2 device type disk format '/u01/app/Rman/proddb_full_bkp_%u';
crosscheck backup;
delete noprompt obsolete;
backup database;
backup archivelog all delete input;
release channel ch1;
release channel ch2;
}
EOF


=====



[oracle@DESKTOP-BAS028D ~]$ export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
[oracle@DESKTOP-BAS028D ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 13 14:36:09 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2780785463)

RMAN> LIST BACKUP OF PLUGGABLE DATABASE orcl;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    1008.70M   DISK        00:00:23     13/09/2022 13:27:54
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20220913T132730
        Piece Name: /u01/app/Rman/proddb_full_bkp_0117k7q3
  List of Datafiles in backup set 2
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 2826715    13/09/2022 13:27:31              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
  13      Full 2826715    13/09/2022 13:27:31              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3       Full    470.40M    DISK        00:00:12     13/09/2022 13:28:09
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20220913T132730
        Piece Name: /u01/app/Rman/proddb_full_bkp_0317k7qt
  List of Datafiles in backup set 3
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  9       Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
  12      Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
  15      Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf


====

before start check below 

If the FRA is not configured, then AUXILIARY DESTINATION must be specified ....

show parameter DB_RECOVERY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 12918M

https://docs.oracle.com/en/database/oracle/oracle-database/19/ntdbi/about-fast-recovery-area-and-fast-recovery-area-disk-group.html

RUN {
  ALTER PLUGGABLE DATABASE orcl CLOSE;
  SET UNTIL TIME "TO_DATE('13-SEP-2022 13:27:57','DD-MON-YYYY HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE orcl;
  RECOVER PLUGGABLE DATABASE orcl;
  ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS;
}

=====================

RMAN>  RESTORE pluggable DATABASE orcl UNTIL TIME "TO_DATE('13.09.2022 13:27:57','DD.MM.YYYY HH24:MI:SS')" PREVIEW;

Starting restore at 13/09/2022 13:53:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3       Full    470.40M    DISK        00:00:12     13/09/2022 13:28:09
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20220913T132730
        Piece Name: /u01/app/Rman/proddb_full_bkp_0317k7qt
  List of Datafiles in backup set 3
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  9       Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
  12      Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
  15      Full 2826726    13/09/2022 13:27:57              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    1008.70M   DISK        00:00:23     13/09/2022 13:27:54
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20220913T132730
        Piece Name: /u01/app/Rman/proddb_full_bkp_0117k7q3
  List of Datafiles in backup set 2
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- ------------------- ----------- ------ ----
  10      Full 2826715    13/09/2022 13:27:31              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
  13      Full 2826715    13/09/2022 13:27:31              NO    /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
using channel ORA_DISK_1


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7       451.00K    DISK        00:00:00     13/09/2022 13:28:26
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20220913T132825
        Piece Name: /u01/app/Rman/proddb_full_bkp_0817k7rq

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    24      2825244    13/09/2022 13:06:57 2826756    13/09/2022 13:28:24
recovery will be done up to SCN 2826726
Media recovery start SCN is 2826715
Recovery must be done beyond SCN 2826726 to clear datafile fuzziness    <<<<<<<<<<<<<+1 i.e.  2826727 scn no
Finished restore at 13/09/2022 13:53:04

RMAN>






RUN {
  SET UNTIL scn 2826727;
  RESTORE PLUGGABLE DATABASE orcl;
  RECOVER PLUGGABLE DATABASE orcl;
  ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS;
}
RMAN> 2> 3> 4> 5> 6>

executing command: SET until clause

Starting restore at 13/09/2022 14:48:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/13/2022 14:48:39
ORA-19870: error while restoring backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3
ORA-19573: cannot obtain exclusive enqueue for datafile 10                   <<<<<<<<<<<<<<<<<<<<<<<

RMAN>

RMAN>


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO

SQL> alter session set container=orcl;

Session altered.


SQL> shutdown immediate ;
Pluggable Database closed.

**********************************************

started again after shutdown  the pluggable database  !!!!!!!

RMAN>
RUN {
  SET UNTIL scn 2826727;
  RESTORE PLUGGABLE DATABASE orcl;
  RECOVER PLUGGABLE DATABASE orcl;
  ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS;
}
RMAN> 



executing command: SET until clause

Starting restore at 13/09/2022 14:55:37
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3
channel ORA_DISK_1: piece handle=/u01/app/Rman/proddb_full_bkp_0117k7q3 tag=TAG20220913T132730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0317k7qt
channel ORA_DISK_1: piece handle=/u01/app/Rman/proddb_full_bkp_0317k7qt tag=TAG20220913T132730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13/09/2022 14:56:00

Starting recover at 13/09/2022 14:56:01
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 13/09/2022 14:56:03

Statement processed


===================================================================================

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO


      
      

ORA-65049: Creation of local user or role is not allowed in this container

grant dba to test identified by teast123
*
ERROR at line 1:
ORA-65049: Creation of local user or role is not allowed in this container.

################################################################################################



alter session set "_ORACLE_SCRIPT"=true;


grant dba to test identified by teast123;

Grant succeeded.  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  Created 


col USERNAME for a20
set linesize 300 pagesize 300
select USERNAME,COMMON from dba_users ;


USERNAME                       COM
------------------------------ ---
TEST                           YES



Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.0.0

SQL> create user c##test identified by test123 container=all;  otherwise create c## user 

User created.



USERNAME             COM
-------------------- ---
C##TEST              YES