Oracle 12c-Multitenant Database Backup Using Rman

With multi tenant feature introduced in oracle 12c, New commands are there for taking rman backup of pluggable database and root container database.

Backup of complete container( ROOT + ALL PDBS )

Taking backup of particular pluggable database:(backup pluggable database PDB5)

Backup of tablespace of pluggable database

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Migration:

DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database.

Step 1 : Check the database its properly up and running.shutdown the non-CDB and start it in read-only mode.

[oracle@ram ~]$ export ORACLE_SID=hari
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 22:54:03 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME         OPEN_MODE
--------- --------------------
HARI         READ WRITE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

In the non-DBC database using the DBMS_PDB_DESCRIBE procedure  creates an XML file in the same way that the unplug operation does for a PDB.

Step 2 : Shutdown the non-CDB database.

SQL> begin dbms_pdb.describe(pdb_descr_file => '/u01/app/oracle/oradata/HARI/tmp/hari.xml');
2 end;
3 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 3 : Connect to an existing CDB

Step 4 : Create a new PDB using that xml file which we described already in  the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

[oracle@ram ~]$ export ORACLE_SID=Petonas
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 23:46:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database pdb5new using '/u01/app/oracle/oradata/HARI/tmp/hari.xml'
2 copy file_name_convert = ('/u01/app/oracle/oradata/HARI/datafile/','/u01/app/oracle/oradata/PETONAS/datafile/pdb5new/');


Pluggable database created.

Step 5 : Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> alter session set container=pdb5new;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

step 6 : Startup the PDB and check the open mode

SQL> alter session set container=pdb5new;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select name ,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB5NEW
READ WRITE


SQL> col name for a15
SQL> /

NAME             OPEN_MODE
--------------- ----------
PDB5NEW          READ WRITE


The non-cdb database has been converted to CDB now successfully.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Connecting to a Container Database (CDB)

Connecting to the root of a container database is the same as  database instance.

On the database server you can use OS Authentication.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:10:26 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL>

You can connect to other common users in similar way.

SQL> conn system/oracle
Connected.
SQL>

The v$services views can be used to display available services from the database.

SQL> col name for a20
SQL> col pdb for a20
SQL> select name , pdb from v$services order by name;

NAME                     PDB
--------------------   --------------
SYS$BACKGROUND           CDB$ROOT
SYS$USERS                CDB$ROOT
dbwr.carrierzone.com     CDB$ROOT
dbwrXDB                  CDB$ROOT
pdb1.carrierzone.com     PDB1
pdb5.carrierzone.com     PDB5
pdb6.carrierzone.com     PDB6

7 rows selected.

The lsnrctl utility allows you to display the available services from the command line

[oracle@ram ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-OCT-2018 02:19:58

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.localdomain.com)(PORT=1521)))
Services Summary...
Service "777d95e41a572d82e053dd971d40e224.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77813d4b379738f5e0536501a8c05864.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77f4b206457338a1e0536501a8c0ed42.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwr.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwrXDB.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ram.localdomain.com, pid: 32509>
(ADDRESS=(PROTOCOL=tcp)(HOST=ram.localdomain.com)(PORT=58780))
Service "pdb1.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb5.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb6.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully.

Connections using services

[oracle@ram admin]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:30:30 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/oracle@dbwr
Connected.
SQL>

The connection using a TNS requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr.carrierzone.com)
)
)
Displaying the Current Container

The show con_name and show con_id commands in SQL*Plus display the current container name and ID respectively.

SQL> sho con_name

CON_NAME
-----------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------
1
SQL>

They can also be retrieved using the sys_context function.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT

SQL> select sys_context('userenv','con_id') from dual;

SYS_CONTEXT('USERENV','CON_ID')
---------------------------------------------------
1

SQL>
Switching Between Containers

When logged in to the CDB as an appropriately privileged user, the alter session command can be used to switch between containers within the container database.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container = pdb5;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
PDB5

SQL> alter session set container =cdb$root;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made by using a service. Each pluggable database automatically registers a service with the listener.

This is how any application will connect to a pluggable database, as well as administrative connections.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:47:52 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn / as sysdba
Connected.
SQL> conn system/oracle@pdb5
Connected.
SQL>

The connection using a TNS alias requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file, such as the one shown below.

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB.

All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the create session privilege to enable connections.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Manage Users and Privileges:

When connected to a multitenant database the management of users and privileges is a little different to traditional Oracle environments. In multitenant environments there are two types of user.

  • Common User : The user is present in all containers (root and all PDBs).
  • Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

  • Common Role : The role is present in all containers (root and all PDBs).
  • Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Some DDL statements have a container clause added to allow them to be directed to the current container or all containers.

Create Common Users :

While creating a common user the following requirements must all be met.

  • Must be connected to a common user with the create user privilege.
  • The current container must be the root container.
  • The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • Username must be unique across all containers.
  • The default tablespace, temporary tablespace , quota and profile must all reference objects that exist in all containers.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.

now we can see how to create common users with and without the container clause from the root container.

[oracle@ram ~]$ ps -ef | grep pmon
oracle 37524 34096 0 18:42 pts/0 00:00:00 grep pmon
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 18:42:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.

Create the common user using the CONTAINER clause

SQL> create user c##user1 identified by password1 container=all;

User created.

SQL> grant create session to c##user1 container=all;

Grant succeeded.

Create the common user using the default CONTAINER setting

SQL> create user c##user2 identified by password2;

User created.

SQL> grant create session to c##user2;

Grant succeeded.
Create Local Users:

While creating a local user the following requirements must all be met.

  • Must be connected to a user with the create user privilege.
  • Username for the local user must not be prefixed with “C##” or “c##”.
  • Username must be unique within the PDB.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is a PDB.
Switch container while connected to a common user
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container =pdb5;

Session altered.
Create the local user using the CONTAINER clause
SQL> create user user3 identified by password3 container=current;

User created.

SQL> grant create session to user3 container=current;

Grant succeeded.
Connect to a privileged user in the PDB
SQL> conn system/oracle@pdb5 
Connected.
Create the local user using the default CONTAINER setting
SQL> create user user4 identified by password4;

User created.

SQL> grant create session to user4;

Grant succeeded.
Create Common Roles:

Roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are must.

  • You must be connected to a common user with create role and the set container privileges granted commonly.
  • The current container must be the root container.
  • The role name for the common role must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • The role name must be unique across all containers.
  • The role is created with the container=all clause

Now we can see how to create a common role and grant it to a common and local user.

Create the common role
SQL> conn / as sysdba
Connected.
SQL> create role c##role1;

Role created.

SQL> grant create session to c##role1;

Grant succeeded.
Grant it to a common user
SQL> conn / as sysdba
Connected.
SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL> alter pluggable database all open;

Pluggable database altered.
Grant it to a local user
SQL> alter session set container =pdb5;

Session altered.

SQL> grant c##role1 to user3;

Grant succeeded.

SQL>
Create Local Roles:

Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions are must.

  • Must be connected to a user with the create role privilege.
  • If you are connected to a common user, the container must be set to the local PDB.
  • Role name for the local role must not be prefixed with “C##” or “c##”.
  • Role name must be unique within the PDB.

we can see now how to create local a role and grant it to a common user and a local user.

SQL> conn / as sysdba
Connected.
SQL>
Switch container
SQL> alter session set container = pdb5;

Session altered.

Alternatively , we can connect pluggable database through local  or common user  with PDB service.

Create the common role
SQL> create role role1;

Role created.

SQL> grant create session to role1;

Grant succeeded.
Grant it to a common user
SQL> grant role1 to c##user1;

Grant succeeded.
Grant it to a local user
SQL> grant role1 to user3;

Grant succeeded.
Granting Roles and Privileges to Common and Local Users:

The basic difference between a local and common grant is the value used by the container clause.

Common grants
SQL> conn / as sysdba
Connected.
SQL> grant create session to c##user1 container=all;

Grant succeeded.

SQL> grant create session to c##role1 container=all;

Grant succeeded.

SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL>
Local grants
SQL> conn system/oracle@pdb5
Connected.
SQL> grant create session to user3;

Grant succeeded.

SQL> grant create session to role1;

Grant succeeded.

SQL> grant role1 to user3;

Grant succeeded.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Enable/Disable Flashback Database

Pre-request:

1.Must be in archivelog mode

2.Before enable flashback database we need to make sure the database is in archivelog mode.

3.Must do this from the root container.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 00:53:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> conn / as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

We can now enable/disable flashback database with the open mode in 12c.

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

We can check the flashback status from v$database.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

with db_flashback_retention_target parameter we can set flashback retention time.

SQL> sho parameter retention

NAME                                   TYPE          VALUE
------------------------------------ ----------- ----------------------
db_flashback_retention_target          integer        1440
undo_retention                         integer        900

SQL> alter system set db_flashback_retention_target=10080 scope=both;

System altered.

SQL>
Creating Restore Points
  • Creating restore point is just like same in normal database.
  • Its alias for a SCN.
  • Guaranteed restored point prevents the database from removing any flashback logs between that point and the current time.
  • Always remove unwanted guaranteed restore point.
Normal restore point:
SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL>
Guaranteed restore point:
SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

SQL>

Creating PDB level restore point and Guarantee restore point as follows.

SQL> conn / as sysdba
Connected.

SQL> alter session set container=pdb5;

Session altered.

SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

From the root container also we can create PDB level restore point and Guarantee restore point

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp for pluggable database pdb5;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Guarantee restore point from root container:

SQL> 
SQL> create restore point rsp for pluggable database pdb5 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Clean restore points can be created while connected to the PDB

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 02:09:44 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.
SQL> col name for a15;
SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            MOUNTED
PDB6            MOUNTED
PDB1            MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            READ WRITE
PDB6            READ WRITE
PDB1            READ WRITE

SQL> alter session set container=pdb5;

Session altered.


SQL> shutdown;
Pluggable Database closed.


SQL> create clean restore point crsp;
create clean restore point crsp
*
ERROR at line 1:
ORA-39891: Clean PDB restore point cannot be created when CDB is in local undo
mode.

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME
----------------------------------------------------------------------PROPERTY_VALUE
-----------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
  • The container database to be running in local undo mode, but flashback PDB does not depend on it.
  • If the CDB is running in shared undo mode, it is more efficient to flashback to clean restore points.
  • These are restore points taken when the pluggable database is down, with no outstanding transactions.
SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> alter database local undo off;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> col property_name for a20;
SQL> col property_value for a20;

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME          PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED     FALSE

SQL>
SQL> create clean restore point crsp;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL>
SQL> create clean restore point crsp guarantee flashback database;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL> startup;


ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size		    8795904 bytes
Variable Size		  671090944 bytes
Database Buffers	 1828716544 bytes
Redo Buffers		    7979008 bytes
Database mounted.
Database opened.

They can also be created from the root container.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> conn / as sysdba 
Connected. 

SQL> 

SQL> create clean restore point rsp for pluggable database pdb5; 
Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. 

SQL>

SQL> create clean restore point rsp for pluggable database pdb5 guarantee flashback database; 

Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped.

 SQL>
Flashback CDB and  PDB:
SQL> select * from t1;

ID
----------
1
2
3
4
5

SQL> create restore point rsp; 

Restore point created.

SQL> desc v$restore_point
Name                                        Null?      Type
-----------------------------------      -------- --------------------
SCN                                                 NUMBER
DATABASE_INCARNATION#                               NUMBER
GUARANTEE_FLASHBACK_DATABASE                        VARCHAR2(3)
STORAGE_SIZE                                        NUMBER
TIME                                                TIMESTAMP(9)
RESTORE_POINT_TIME                                  TIMESTAMP(9)
PRESERVED                                           VARCHAR2(3)
NAME                                                VARCHAR2(128)
PDB_RESTORE_POINT                                   VARCHAR2(3)
CLEAN_PDB_RESTORE_POINT                             VARCHAR2(3)
PDB_INCARNATION#                                    NUMBER
CON_ID                                              NUMBER

SQL> select name,scn from v$restore_point;

NAME                                          SCN
--------------------                       ----------
RSP                                           3096868
RSP1                                          3098148
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> conn / as sysdba
Connected.


SQL> flashback database to restore point rsp;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database to scn 3096868;

Flashback complete.

SQL> flashback database to before scn 3096868;

Flashback complete.

SQL> flashback database to restore point rsp1;

Flashback complete.

Flashback of a PDB differ on whether local undo mode or not.

SQL> conn / as sysdba 
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL>flashback pluggable database pdb5 to restore point rsp;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Database altered.

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database pdb5 to scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to before scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to restore point rsp1;

Flashback complete.
Flashback a Pluggable Database:
  • create a restore point.
  • connect inside the PDB and make changes.
  • Flashback the PDB to the restore point.
  • Check the table content is missing.
SQL> startup 
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> conn / as sysdba
Connected.

SQL> create restore point rsp2 for pluggable database pdb5;

Restore point created.

SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 

ID 
---------- 
1
2 
3 
4 
5

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> flashback pluggable database pdb5 to restore point rsp2;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Pluggable database altered. 


SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 
select * from t1 
* 
ERROR at line 1: 
ORA-00942: table or view does not exist


 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Hot Clone a Remote PDB in Oracle Database 12c Release 2 (12.2)

Hot Clone a Remote Pluggable Database

In 12.1 remote cloning was the prerequisite of placing the source PDB or non-CDB into read-only mode before initiating the cloning process.

This made this feature useless for cloning production systems, as that level of down-time is typically unacceptable.

Oracle Database 12c Release 2 (12.2) removes this prerequisite, which enables hot cloning of PDBs and non-CDBs for the first time.

Step 1 : Connect to the remote CDB and prepare the remote PDB for cloning.

[oracle@ram admin]$ export ORACLE_SID=dbwr
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 01:09:04 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho con_name

CON_NAME
----------------
CDB$ROOT


Step 2 : Create a user in the remote database for use with the database link. In this case, we will use a local user in the remote PDB

SQL> CREATE USER c##remote_user IDENTIFIED BY remote_user CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_user CONTAINER=ALL;

Grant succeeded.

Step 3 : Check the remote CDB is in local undo mode and archivelog mode.

SQL> conn / as sysdba
Connected.

SQL> col property_name for a30
SQL> col property_value for a30

SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                   PROPERTY_VALUE
--------------------------- -----------------------
LOCAL_UNDO_ENABLED                 TRUE


SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Step 4 : Because the remote CDB is in local undo mode and archivelog mode, we don’t need to turn the remote database into read-only mode.

Step 5 : Switch to the local server and create a “tnsnames.ora” entry pointing to the remote database for use in the using clause of the database link.

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)

Step 6 : Connect to the local database to initiate the clone.

[oracle@ram ~]$ export ORACLE_SID=Petonas
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 00:44:25 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho con_name

CON_NAME
------------
CDB$ROOT

Step 7 : Create a database link in the local database, pointing to the remote database.

SQL> drop database link pdb5_link;

Database link dropped.

SQL> CREATE DATABASE LINK pdb5_link CONNECT TO c##remote_user IDENTIFIED BY remote_user USING 'pdb5';

Database link created.

Step 8 : Create a new PDB in the local database by cloning the remote PDB. In this case we are using Oracle Managed Files (OMF), so we don’t need to bother with FILE_NAME_CONVERT parameter for file name conversions.

SQL> CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@pdb5_link;


Pluggable database created.

We can see the new PDB has been created, but it is in the MOUNTED state.

SQL> COLUMN name FORMAT A30
SQL> select name,open_mode from v$pdbs where name='PDB5NEW';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5NEW                        MOUNTED

SQL>

Step 9 : The PDB is opened in read-write mode to complete the process.

SQL> alter pluggable database pdb5new open;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

NAME                  OPEN_MODE
--------------------- ----------
PDB5NEW               READ WRITE

SQL>

Thus the remote clone Pluggable Database cloned successfully.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Backup of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c Release 2

RMAN CONNECTIONS:
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 9 02:56:50 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> !
[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 02:57:12 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN>
Container Database (CDB) Backup:

A full backup of the CDB you are also doing a full backup of all PDBs.

Connect to RMAN using OS authentication and take a full backup using the following command. This means you are connecting to the root container with “AS SYSDBA” privilege.

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 02:57:12 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN>

The datafiles associated with the CBD  and all the PDBs (pdb5, pdb6, pdb$seed) are included in the backup.

RMAN> backup database plus archivelog;


Starting backup at 09-OCT-18
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=1 STAMP=989032088
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_annnn_TAG20181009T030809_fvqml1tn_.bkp tag=TAG20181009T030809 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting backup at 09-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqml3k6_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmp18w_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmqsfz_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T030811_fvqmrlvp_.bkp tag=TAG20181009T030811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 09-OCT-18

Starting backup at 09-OCT-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=2 STAMP=989032324
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_annnn_TAG20181009T031204_fvqmsdc7_.bkp tag=TAG20181009T031204 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989032325_fvqmsgbb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Root Container Backup

A backup of the root container is a backup of the CDB, excluding any of the PDBs.

Connect to RMAN using OS authentication and backup the root container using the following command. This means you are connecting to the root container with “AS SYSDBA” privilege.

RMAN>rman target /

RMAN> backup database root;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T031649_fvqn2b13_.bkp tag=TAG20181009T031649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989032666_fvqn42vl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN>
Pluggable Database (PDB) Backup

There are two ways to backup pluggable databases. When connected to RMAN as the root container, you can backup one or more PDBs

  1. backup pluggable database pdb5,pdb6;
  2. backup database; (Alternatively, connect to a specific PDB)
RMAN> rman target /

RMAN> backup pluggable database pdb5,pdb6;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032310_fvqng76b_.bkp tag=TAG20181009T032310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032310_fvqnh1n5_.bkp tag=TAG20181009T032310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:31
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989033048_fvqnj0n4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN> 


Alternative way,

SQL> alter session set container = pdb5;

Session altered.

SQL> !
[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 03:26:42 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> backup database;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 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=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:50
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00023 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnpr4p_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
input datafile file number=00016 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnqk88_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnrbgm_.bkp tag=TAG20181009T032653 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:30
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989033344_fvqns92k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

RMAN>

Tablespace and Datafile Backups

Multiple PDBs in the same CDB can have a tablespace with the same name, for example SYSTEM, SYSAUX and USERS.

we can remove that through directly connect to the appropriate PDB.

Once RMAN is connected to the PDB, the tablespace backup commands is unchanged like previous versions.

[oracle@ram admin]$ rman target=sys/pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:21:25 2018

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

connected to target database: DBWR (DBID=1322876759)
RMAN> BACKUP TABLESPACE system, sysaux, users;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 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=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp tag=TAG20181009T182505 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:52
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087157_fvs9bxpr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Either way is we can connect to the CDB and take directly backup of the particular PDB.

[oracle@ram admin]$ rman target=sys/dbwr

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:28:37 2018

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

connected to target database: DBWR (DBID=1322876759)
RMAN> backup tablespace pdb5:system,pdb5:sysaux,pdb5:users,pdb6:system;

Starting backup at 09-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=83 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=00022 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
input datafile file number=00021 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
input datafile file number=00024 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp tag=TAG20181009T183030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:30
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00013 name=/u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777D95E41A572D82E053DD971D40E224/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9nf4n_.bkp tag=TAG20181009T183030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087476_fvs9nwwy_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

Datafiles have unique file numbers and fully qualified names, so they can be backed up from the root container or the individual PDB.

[oracle@ram admin]$ rman target =sys/pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 18:36:26 2018

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

connected to target database: DBWR (DBID=1322876759)

Datafiles have unique file numbers  so they can be backed up from the root container or the individual PDB.

RMAN> backup datafile 5,6,7;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/777BDA91F244116BE053DD971D400082/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvs9zy5o_.bkp tag=TAG20181009T183709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-18
channel ORA_DISK_1: finished piece 1 at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp tag=TAG20181009T183709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-OCT-18

Starting Control File and SPFILE Autobackup at 09-OCT-18
piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_09/o1_mf_s_989087859_fvsb0w26_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-OCT-18

If you are connecting to a PDB, only the files belonging to that PDB can be backed up.

So when connected as PDB5, we get an error if we try to backup the SYSAUX datafile from the root container.

RMAN> backup datafile 2;

Starting backup at 09-OCT-18
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/09/2018 18:42:39
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: 2

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Recovery of a Container Database (CDB) and a Pluggable Database (PDB) in Oracle Database 12c Release 2

Container Database (CDB) Full Recovery

Restoring a CDB is similar to restoring a non-CDB database, but remember restoring a whole CDB will restore not only the root container, but all the PDBs also.

Same in a Point In Time Recovery (PITR) of the whole CDB will bring all PDBs back to the same point in time.

Connect to RMAN using OS authentication and restore the whole CDB using the following restore script. This means you are connecting to the root container with “AS SYSDBA” privilege.

[oracle@ram admin]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 19:45:41 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> RUN {
SHUTDOWN IMMEDIATE; 
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 2516582400 bytes

Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes

Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

skipping datafile 5; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgr1m0c_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgr1m08_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgr1m0f_.dbf
skipping datafile 13; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
skipping datafile 14; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
skipping datafile 15; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
skipping datafile 16; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
skipping datafile 21; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
skipping datafile 22; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
skipping datafile 24; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
skipping datafile 23; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
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 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp tag=TAG20181009T032653
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00001 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp tag=TAG20181009T182505
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp tag=TAG20181009T183709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-OCT-18

Starting recover at 09-OCT-18
using channel ORA_DISK_1

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

Finished recover at 09-OCT-18

Statement processed


Root Container Full Recovery

Instead of recovering the whole CDB, including all PDBs, the root container can be recovered in isolation.

Connect to RMAN using OS authentication and restore the root container using the following restore script.

It means you are connecting to the root container with “AS SYSDBA” privilege.

 

[oracle@ram admin]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 20:09:10 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> RUN {
SHUTDOWN IMMEDIATE; 
STARTUP MOUNT;
RESTORE DATABASE ROOT;
RECOVER DATABASE ROOT;
ALTER DATABASE OPEN;
}

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 2516582400 bytes

Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes

Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 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 00004 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvgqyx60_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqno5rt_.bkp tag=TAG20181009T032653
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00001 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvgqv1n6_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvgqxhq9_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T182505_fvs999th_.bkp tag=TAG20181009T182505
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:27
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 00007 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvgqyy96_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183709_fvsb0to6_.bkp tag=TAG20181009T183709
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-OCT-18

Starting recover at 09-OCT-18
using channel ORA_DISK_1

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

Finished recover at 09-OCT-18

Statement processed

It is not a good to restore and recover just the root container without doing the same for the PDBs.

If there any difference in metadata between the two could create problem.so better avoid this.

 

Pluggable Database (PDB) Recovery Scenario

There are two ways to restore and recover PDBs.

1.Connect to root containers, you can restore and recover one or more PDBs.

2.Connect directly to a PDB, you can restore and recover the current PDB using a local user with the SYSDBA privilege.

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 20:23:47 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> RUN {
ALTER PLUGGABLE DATABASE pdb5, pdb6 CLOSE;
RESTORE PLUGGABLE DATABASE pdb5, pdb6;
RECOVER PLUGGABLE DATABASE pdb5, pdb6;
ALTER PLUGGABLE DATABASE pdb5, pdb6 OPEN;
}2> 3> 4> 5> 6>

using target database control file instead of recovery catalog
Statement processed

Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK

skipping datafile 13; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_system_fvgzcl9q_.dbf
skipping datafile 14; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_sysaux_fvgzclb4_.dbf
skipping datafile 15; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_undotbs1_fvgzclb5_.dbf
skipping datafile 16; already restored to file /u01/app/oracle/oradata/DBWR/777D95E41A572D82E053DD971D40E224/datafile/o1_mf_users_fvgzd8cs_.dbf
skipping datafile 21; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
skipping datafile 22; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
skipping datafile 24; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
skipping datafile 23; already restored to file /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 09-OCT-18

Starting recover at 09-OCT-18
using channel ORA_DISK_1

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

Finished recover at 09-OCT-18

Statement processed

Alternate way,

[oracle@ram ~]$ sqlplus sys@pdb5 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 9 20:27:27 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE USER prasath IDENTIFIED BY prasath;

User created.

SQL> GRANT CREATE SESSION, PDB_DBA, SYSDBA TO prasath;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@ram ~]$ rman target=prasath@pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 20:28:54 2018

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

target database Password: 
connected to target database: DBWR:PDB5 (DBID=2599175923)

RMAN> SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;
using target database control file instead of recovery catalog
database closed

RMAN> 
Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 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 00023 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnpr4p_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T032653_fvqnpr4p_.bkp tag=TAG20181009T032653
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00021 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp tag=TAG20181009T183030
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 09-OCT-18

RMAN> 
Starting recover at 09-OCT-18
using channel ORA_DISK_1

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

Finished recover at 09-OCT-18
Tablespace and Datafile Recovery

Restoring a tablespace must be connected to the PDB.

But Datafile restoring can connected to the container or directly to the PDB.

[oracle@ram ~]$ rman target=sys@pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 20:48:06 2018

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

target database Password: 

connected to target database: DBWR:PDB5 (DBID=2599175923)

RMAN> RUN {
ALTER TABLESPACE users OFFLINE;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
ALTER TABLESPACE users ONLINE;
}

using target database control file instead of recovery catalog
Statement processed

Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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 00024 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp tag=TAG20181009T183030
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-OCT-18

Starting recover at 09-OCT-18
using channel ORA_DISK_1
applied offline range to datafile 00024
offline range RECID=69 STAMP=989095658

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

Finished recover at 09-OCT-18

Statement processed

Datafile Recovery:

[oracle@ram ~]$ rman target=sys@pdb5

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Oct 9 20:51:24 2018

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

target database Password: 
connected to target database: DBWR:PDB5 (DBID=2599175923)

RMAN> RUN {
ALTER DATABASE DATAFILE 24 OFFLINE;
RESTORE DATAFILE 24;
RECOVER DATAFILE 24;
ALTER DATABASE DATAFILE 24 ONLINE;
}

using target database control file instead of recovery catalog
Statement processed

Starting restore at 09-OCT-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 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 00024 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_09/o1_mf_nnndf_TAG20181009T183030_fvs9mgjy_.bkp tag=TAG20181009T183030
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-OCT-18

Starting recover at 09-OCT-18
using channel ORA_DISK_1
applied offline range to datafile 00024
offline range RECID=69 STAMP=989095658

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

Finished recover at 09-OCT-18

Statement processed.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Manage Tablespaces in a Container Database (CDB) and Pluggable Database (PDB) in Oracle Database 12c Release 2

Manage Tablespaces in a CDB:

Management of tablespaces in a container database (CDB) is same as a non-CDB database. Provided you are logged in as a privileged user and pointing to the root container, the usual commands are all available.

SQL> show con_name
CON_NAME
-----------
CDB$ROOT

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
--------------- --------------------------------------------
USERS		/u01/app/oracle/oradata/DBWR/datafile/o1_mf_
		users_fvgqyy96_.dbf

UNDOTBS1	/u01/app/oracle/oradata/DBWR/datafile/o1_mf_
		undotbs1_fvgqyx60_.dbf

SYSTEM		/u01/app/oracle/oradata/DBWR/datafile/o1_mf_
		system_fvgqv1n6_.dbf

SYSAUX		/u01/app/oracle/oradata/DBWR/datafile/o1_mf_
		sysaux_fvgqxhq9_.dbf

Create tablespace & adding Datafile in CDB:

create tablespace as like as same non-cdb and adding datafile also the same.

SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs01' size 5m;

Tablespace created.

SQL> alter tablespace tbs01 add datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs02' size 5m;

Tablespace altered.

SQL> drop tablespace tbs01 including contents and datafiles;
Tablespace dropped.
Manage Tablespaces in a PDB

Same tablespace management commands are available from a pluggable database (PDB), provided you are pointing to the correct container. You can connect using a common user then switch to the correct container.

SQL> conn / as sysdba
Connected.

SQL> alter session set container = pdb5;
Session altered.

SQL> show con_name
CON_NAME
-----------------
PDB5
SQL>

Creating Tablespace & Datafile in PDB:

create Tablespace and Datafile as like same in non-cdb.

SQL> create tablespace tbs05 datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs05' size 15m; 

Tablespace created. 

SQL> alter tablespace tbs05 add datafile '/u01/app/oracle/oradata/DBWR/datafile/tbs06' size 15m; 

Tablespace altered. 

SQL> drop tablespace tbs05 including contents and datafiles; 

Tablespace dropped.
Temporary Tablespaces

Management of the temporary tablespace in a CDB is unchanged from that of a non-CDB database.

A PDB can either have its owner temporary tablespace, or if it is created without a temporary tablespace, it can share the temporary tablespace with the CDB.

SQL> select name from v$tempfile;

NAME
-------------
/u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf

Creating , Selecting and dropping Temporary Tablespace as like as non-cdb

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/DBWR/temp1.dbf' size 3m;

Tablespace created.


SQL> select name from v$tempfile;

NAME
-----------
/u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf
/u01/app/oracle/oradata/DBWR/temp1.dbf


SQL> drop tablespace temp1 including contents and datafiles;

Tablespace dropped.

SQL>
Undo Tablespaces

Management of the undo tablespace in a CDB is unchanged from that of a non-CDB database.

In contrast, a PDB cannot have an undo tablespace. Instead, it uses the undo tablespace belonging to the CDB. If we connect to a PDB, we can see no undo tablespace is visible.

SQL> show con_name

CON_NAME
-----------------------
PDB5

SQL> Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
-----------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

But we can see the datafile associated with the CDB undo tablespace.

SQL> SELECT name FROM v$datafile;
NAME
----------------------------------------------------------------
/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_fvhgn44z_.dbf
/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_fvhgn44v_.dbf
/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.dbf
/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_fvhgn454_.dbf


SQL> SELECT name FROM v$tempfile;
NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/DBWR/datafile/temp012018-10-06_00-55-51-380-AM.dbf

SQL>
Default Tablespaces

The default tablespace and default temporary tablespace for a CDB is unchanged compared to a non-CDB database.

There are a two ways to set the default tablespace and default temporary tablespace for a PDB. The ALTER PLUGGABLE DATABASE command is the recommended way.

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE users;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE temp;
ALTER DATABASE DEFAULT TABLESPACE users;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

but both methods, you should be pointing to the appropriate container.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2

Container Database (CDB):

Startup and Shutdown in container Database is same like normal instances only.The sql*plus startup and shutdown commands are available when CDB connected as a privileged user.

While normally dbwr database with the command of startup nomount

command:  startup nomount

command: shutdown immediate

command: startup force

command: startup 

Pluggable Database (PDB):

Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

step 1:

view all pluggable database name and modes with the command

select name, open_mode from v$pdbs;

step 2:

now we closing all pluggable database with the command

alter pluggable database all close immediate;

alter pluggable database all open; [setting up all PDB open mode]

Trying to close except particular PDB6 with the command

alter pluggable database all except pdb6 close immediate;

Startup with open read write mode with SQL*PLUS command

startup open read write

To set particular PDB5 open with the command

alter pluggable database pdb5 open;

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba