Oracle-Recover A Dropped User Using Flashback

Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state.

Once database is up, we can import the dump. It will need some time( for flashback the database )

PREREQUISITE:

1. Database Must be in Archive log mode

2. Flashback must be enabled for the database.

3.  all the flashback log and archives should be available, from the time, the user is dropped

METHODS:

1. Make sure flashback and archive mode is enable.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback ON;
alter database flashback ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,log_mode from v$database;

FLASHBACK_ON                   LOG_MODE
------------------             ------------
YES                            ARCHIVELOG

2. Let’s drop a user, to test the scenarios:

SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

SQL> drop user ram cascade;

User dropped.
SQL> select table_name from dba_tables where owner='RAM';

no rows selected

SQL> conn ram/ram
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

3. flashback the database to past, when the user was available.

SQL> set time on;

21:08:33 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

21:09:00 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 553648208 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8155136 bytes
Database mounted.

21:09:18 SQL> flashback database to timestamp to_date('11-JAN-2019 21:05:11','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

Elapsed: 00:00:01.31

4. Open the database in read only mode:

21:10:42 SQL> 
21:11:08 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:00.65

21:11:29 SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

Elapsed: 00:00:00.65

We can see the tables are available now.

5. Take export backup of the schema RAM:

6. Now restore the database to current stage:

Though it need some outage in the database, to restore the user, we are able to restore schema, without losing any other data in database.

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 Non-CDB in Oracle Database 12c Release 2 (12.2)

Cloning a remote non-cdb:

Step 1  : Connect to the remote database. Make sure it up and running.

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

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 03:06:02 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

Step 2 : Create a user in the remote database for use with the database link.

SQL> CREATE USER r_user IDENTIFIED BY r_user;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO r_user;

Grant succeeded.

Step 3 : Check the remote non-CDB is archivelog mode. If not make it archivelog mode.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter database close;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL>

Step 4 : In Oracle 12.1  the remote database to read-only mode before continuing, but this is not necessary in Oracle 12.2 provided the source database is in archivelog 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.

[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 03:23:13 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> 
SQL> select db_link from dba_db_links;

DB_LINK
--------------
SYS_HUB

SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

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

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

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

SQL> CREATE DATABASE LINK clone_link CONNECT TO r_user IDENTIFIED BY r_user USING 'orcl';

Database link created.

Step 8 : Create a new PDB in the local database by cloning the remote non-CDB. We are using Oracle Managed Files (OMF), so we don’t need to bother with FILE_NAME_CONVERT parameter for file name conversions. Since there is no PDB to name, we use “DB12C” as the PDB name.

SQL> create pluggable database db12c from dbwr@clone_link;

Pluggable database created

Step 9 : The new PDB has been created, but it is in the MOUNTED state.

column name for a15;

SQL> select name,open_mode from v$pdbs;

NAME                OPEN_MODE
-------------      -----------
DB12C               Mounted

SQL>

Step 10 : This PDB was created as a clone of a non-CDB, before it can be opened we need to run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean it up

ALTER SESSION SET CONTAINER=db12c;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 11 : The PDB can now be opened in read-write mode.

ALTER PLUGGABLE DATABASE db12c OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12C';

NAME                  OPEN_MODE
-------------------- ----------
DB12C                 READ WRITE

SQL>

Thus the cloning PDB has been created from non-cdb.

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

Cloning a Remote Pluggable Database (PDB) in Oracle Database 12c Release 2

Cloning a Remote PDB

Connect to the remote CDB and prepare the remote PDB for cloning.

  1. We need to connect the Database with local connection.Check its up and running.
  2. Need to create user in the remote pluggable database also with the privilege of create session , create pluggable database.
  3. Switch the remote PDB to read only mode.when the source pluggable database prepared with tnsnames and listener as well.
  4. Create Database link in the root container pointing to the user which we create in the remote pluggable database.
  5. We can now clone the pluggable database from the root container using Database link which we created with the user connection.
  6. If we are not using OMF we need to convert the path using FILE_NAME_CONVERT parameter.
  7. Now we can check the pluggable database which we created through the clone.It has created successfully.

 

Step 1 : Database up and running

Step 2 : Required to set the particular pluggable database in read write mode.

Step 3 : 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.

Step 4 : Open the remote PDB in read-only mode.

[oracle@ram ~]$ ps -ef|grep pmon
oracle 4725 4707 0 01:09 pts/0 00:00:00 grep pmon
oracle 55330 1 0 Oct10 ? 00:00:01 ora_pmon_dbwr
oracle 60884 1 0 Oct10 ? 00:00:00 ora_pmon_orcl
[oracle@ram ~]$ export ORACLE_SID=orcl
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 11 11:09:51 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 from v$database;

NAME
---------
ORCL

SQL> conn / as sysdba
Connected.


SQL> show user
USER is "SYS"

SQL> alter session set container=orclpdb;

Session altered.

SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME        OPEN_MODE
----------  ----------
ORCLPDB     READ WRITE


SQL> col name for a15;
SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
--------------   ----------
ORCLPDB          READ WRITE

SQL> create user clone_user identified by clone_user;

User created.

SQL> grant create session,create pluggable database to clone_user;

Grant succeeded.

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

Pluggable database altered.

SQL> alter pluggable database orclpdb open read only;

Pluggable database altered.

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

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.

 

[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin/
[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)
)
)

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

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)
PDB6 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb6.carrierzone.com)
)
)
[oracle@ram admin]$ tnsping ORCLPDB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 11-OCT-2018 11:21:29

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

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


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dbwr.carrierzone.com)))
OK (30 msec)

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

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

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 11 11:22:09 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$pdbs;

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


SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

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

SQL> alter session set container = pdb5;

Session altered.

SQL> alter pluggable database pdb5 open read only;

Pluggable database altered.

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

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> drop database link dblink;
drop database link dblink
*
ERROR at line 1:
ORA-02024: database link not found

SQL> create database link dblink connect to dblink identified by clone_user using 'orclpdb';

Database link created.

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

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

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

SQL>create pluggable database orclpdbnew from  orclpdb@dblink file_name_convert='/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/ oracle/oradata/orcl/orclpdbnew/');

Pluggable database created.

SELECT name, open_mode FROM v$pdbs WHERE name = 'ORCLPDBNEW';

NAME            OPEN_MODE
--------------- ----------
ORCLPDBNEW       MOUNTED

SQL>

ALTER PLUGGABLE DATABASE orclpdbnew OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'ORCLPDBNEW';

NAME                           OPEN_MODE
------------------------------ ----------
ORCLPDBNEW                     READ WRITE

SQL>

Thus the pluggable database created successfully with the remote clone.

Catch Me On:- Hariprasath Rajaram

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

Cloning a Local Pluggable Database (PDB) in Oracle Database 12c Release 2

Clone a Local PDB:

step 1  : Connect to the Container Database

step 2 : Check the Local pluggable Database in that Container Database

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

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 22:12:56 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size 8793448 bytes
Variable Size 1056965272 bytes
Database Buffers 587202560 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.

SQL> sho con_name

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

Step 3 : If that particular Local Pluggable Database not in READ ONLY mode alter that PDB into READ ONLY mode.

Step 4 : With the file_name_convert parameter create cloning pluggable database

Step 5 : Now we can check the Local Pluggable Database through V$PDBS view.We can alter like normal pluggable Database as read only,close,read write modes.

SQL> col name for a15
SQL> /

NAME             OPEN_MODE
------------    ----------
ORCLPDB          MOUNTED
PDB$SEED         READ ONLY

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
-------------  ----------
PDB$SEED        READ ONLY
ORCLPDB         READ WRITE

SQL> alter pluggable database orclpdb close;

Pluggable database altered.

SQL> alter pluggable database orclpdb open read only;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
-----------      ----------
PDB$SEED         READ ONLY
ORCLPDB          READ ONLY

SQL> create pluggable database orclpdb1 from orclpdb file_name_convert=('/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/oracle/oradata/orcl/orclpdb1/');

Pluggable database created.

SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
------------    ----------
PDB$SEED        READ ONLY
ORCLPDB         READ ONLY
ORCLPDB1        READ WRITE

SQL> alter pluggable database orclpdb close;

Pluggable database altered.

SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
------------   ----------
PDB$SEED        READ ONLY
ORCLPDB         READ WRITE
ORCLPDB1        READ WRITE

 

If we need to Delete Local Pluggable Database

  1. Switch to that Pluggable Database to close mode.
  2. Drop the Pluggable Database with Datafiles.

 

SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
-------------    ----------
PDB$SEED         READ ONLY
ORCLPDB          READ WRITE
ORCLPDB1         READ WRITE

SQL> alter pluggable database orclpdb1 close;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME           OPEN_MODE
------------- ----------
PDB$SEED       READ ONLY
ORCLPDB        READ WRITE
ORCLPDB1       MOUNTED

SQL> drop pluggable database orclpdb1 including datafiles;

Pluggable database dropped.

Thus the Pluggable Database dropped 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

Point In Time Recovery (PITR) in PDB in Oracle Database 12c Release 2

Pluggable Database (CDB) Point In Time Recovery (PITR)

Point In Time Recovery (PITR) of a PDB follows as same as regular database. The PDB is closed, restored and recovered to the required point in time, then opened with the resetlogs option.

In this case, the resetlogs option does nothing with the log files themselves, but creates a new PDB incarnation.

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

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 02:04:01 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 Wed Oct 10 02:05:01 2018

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

connected to target database: DBWR (DBID=1322876759)

Taking backup of PDB5 database backup

RMAN> backup pluggable database pdb5; 
Starting backup at 10-OCT-18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 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=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 10-OCT-18 channel ORA_DISK_1: finished piece 1 at 10-OCT-18 piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp tag=TAG20181010T020514 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 10-OCT-18 Starting Control File and SPFILE Autobackup at 10-OCT-18 piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/autobackup/2018_10_10/o1_mf_s_989114740_fvt48wpf_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 10-OCT-18

Connecting user with the PDB5 database

Create table and inserting records in that table with commit.

Checking the scn after giving commit.

Then delete the records in the table with commit.

SQL> conn c##hari@pdb5;
Enter password: 
Connected.
SQL> create table test (id number(5));

Table created.

SQL> insert into test values(&a);
Enter value for a: 1
old 1: insert into test values(&a)
new 1: insert into test values(1)

1 row created.

SQL> /
Enter value for a: 2
old 1: insert into test values(&a)
new 1: insert into test values(2)

1 row created.

SQL> /
Enter value for a: 3
old 1: insert into test values(&a)
new 1: insert into test values(3)

1 row created.

SQL> /
Enter value for a: 4
old 1: insert into test values(&a)
new 1: insert into test values(4)

1 row created.

SQL> /
Enter value for a: 5
old 1: insert into test values(&a)
new 1: insert into test values(5)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

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

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
2196232

SQL> delete from test;

5 rows deleted.

SQL> commit;

Commit complete.

Checking the table records after delete with commit option

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

Connecting the RMAN

inside the run command we need to give as follows

a) particular scn number (which we got before delete records in table)

b) restore the pdb

c) recover the pdb

d) open the pdb with reset logs option

[oracle@ram ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Oct 10 02:40:00 2018

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

connected to target database: DBWR (DBID=1322876759)

RMAN> run 
{
set until scn =2196232;
restore pluggable database pdb5;
recover pluggable database pdb5 auxiliary destination='/u01/app/oracle/oradata/DBWR/';
alter pluggable database pdb5 open resetlogs;
}

executing command: SET until clause

Starting restore at 10-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 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 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 00023 to /u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_fvhgn452_.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_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dbwr/DBWR/77813D4B379738F5E0536501A8C05864/backupset/2018_10_10/o1_mf_nnndf_TAG20181010T020514_fvt4837h_.bkp tag=TAG20181010T020514
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-OCT-18

Starting recover at 10-OCT-18
current log archived
using channel ORA_DISK_1


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

Finished recover at 10-OCT-18

Statement processed

RMAN> exit


Recovery Manager complete.


After recovery log into particular pluggable database and we can check the table records.

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

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 10 02:44:55 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> show con_name

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

SQL> conn c##hari@pdb5
Enter password: 
Connected.

SQL> select * from test;

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


The Deleted records restored successfully in the pluggable database .

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