Category Archives: CDB

Local Users in Oracle Multitenant Databases

December 11, 2015 Larry Catt Leave a comment

In an Oracle 12c multitenant database, Local users are accounts created at the PDB level and they have no rights at the CDB level. This article demonstrate the use of Local user accounts. NOTE: To create a local user you must be connected to that PDB and user is only valid in that PDB. You can have same user name in multiple PDBs in the same

Logon to your server as oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Oct 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to your CDB as an administrator, in this example we logon as CDB admin account c##cdb_dba.

SQL> connect c##cdb_dba/password as sysdba

Connected.

SQL>

Open the PDB pdb1 and pdb2 with the alter pluggable database command.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL>

Connect to the PDB pdb1 to create local user in this single PDB.

SQL> alter session set container=pdb1;

Session altered.

SQL> create user user_pdb identified by password;

User created.

SQL>

Grant resource, create session to user user_pdb.

SQL> grant resource to user_pdb;

Grant succeeded.

SQL> grant create session to user_pdb;

Grant succeeded.

SQL>

Connect to pdb1 as the user user_pdb.

SQL> connect user_pdb/password@pdb1

Connected.

SQL>

Currently user user_pdb is only a local user for the PDB pdb1 not for the CDB CDB$ROOT or the PDB pdb2. This can be show by attempting to connect to both will fail.

SQL> connect user_pdb/password@pdb2

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect user_pdb2/password@cdb1

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> connect user_pdb/password@pdb1

Connected.

SQL>

As an admin user connect to PDB pdb2 and attempt to connect as the user ‘user_pdb’ NOTE: That this fails because no user ‘user_pdb’ exists in this PDB.

SQL> alter session set container=pdb2;

Session altered.

SQL> show user

USER is “C##CDB_DBA”

SQL> connect user_pdb/password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Reconnect as the admin user and create a user ‘user_pdb’ in PDB pdb2

SQL> connect C##CDB_DBA/password

Connected.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container=pdb2;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB2

SQL> create user user_pdb identified by password;

User created.

SQL> grant dba to user_pdb;

Grant succeeded.

SQL> grant create session to user_pdb;

Grant succeeded.

SQL> connect user_pdb/password@pdb2;

Connected.

SQL>

This completes local user creation in PDBs.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Basics of Multitenant Container Database (CDB)

December 6, 2015 Larry Catt Leave a comment

The IT industry has increasing developed server technology with increased levels of memory and processing power which have out grown traditional system setups of one application to one server. Most modern physical servers are very capable of handling multiple applications and even multiple server platforms through Virtualization technologies. This is just as true with RDBMS suites. Oracle has developed Multitenant architecture databases to take advantage of excess server power and reduce the physical hardware foot print. This technology is known as CDB – Container Databases and PDB – Pluggable Databases.

Container Databases (CDB) act as the container where you store all of your application and system databases. It contains and manages all of the initialization parameters, memory resources, processor resource and shared services granted to the RDBMS by the OS and created through the RDBMS. Container Databases should not be used by application data stores to process user requests or perform any action for an outside application. It should only be used to house PDB data stores which do provide data storage and manipulations of applications. CDB’s can hold one to many PDBs.

Pluggable Databases (PDB) act as the application data stores and perform all actions that a traditional Oracle database perform through the use of resources granted by the CDB. It allows us to reduce the overhead of managing multiple Oracle RDBMS Suites and increase the utilization of current hardware resources. PDB’s are housed in a single CDB and can be moved from one CDB to another CDB.

Larry Catt

OCP

12c, backup/recovery, CDB, Multitenant, PDB, RMAN

Recovery of PDB in Oracle 12c Multitenant Database

November 30, 2015 Larry Catt Leave a comment

Oracle 12c multitenant database can use the Recovery Manager utility or EM Cloud control to recover a database from a previously taken backup set. This procedure outlines the general steps to recover a PDB database using the RMAN utility.

Logon to your database server as the oracle software owner.

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:45:07 EST 2015 on pts/1

[oracle@linux2 ~]$

Logon to RMAN utility with sysbackup privileges.

[oracle@linux2 ~]$ rman target ‘”/ as sysbackup”‘

Recovery Manager: Release 12.1.0.2.0 – Production on Thu Dec 1 09:26:07 2015

connected to target database: CDB1 (DBID=898155058)

RMAN>

Place the database in mount mode.

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

RMAN>

Issue the command; RESTORE PLUGGABLE DATABASE <PDB>;

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

Starting restore at 01-DEC-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 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 00008 to /u01/oradata/cdb1/pdb1/system01.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/cdb1/pdb1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00010 to /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oradata/pdb1_users.dbf

channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp

channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp tag=TAG20151201T092735

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 01-DEC-15

RMAN>

Issue the command RESTORE PLUGGABLE DATABASE <PDB>;

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

Starting restore at 01-DEC-15

using channel ORA_DISK_1

skipping datafile 8; already restored to file /u01/oradata/cdb1/pdb1/system01.dbf

skipping datafile 9; already restored to file /u01/oradata/cdb1/pdb1/sysaux01.dbf

skipping datafile 10; already restored to file /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

skipping datafile 15; already restored to file /opt/app/oradata/pdb1_users.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 01-DEC-15

RMAN>

You can remove any database archive redo logs after they are no longer needed using command: RECOVER DATABASE DELETE ARCHIVELOG;

RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

Starting recover at 01-DEC-15

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 01-DEC-15

RMAN>

Examine RMAN logs for errors and if no errors open root and PDBs.

ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

RMAN> ALTER DATABASE OPEN;

Statement processed

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Statement processed

RMAN>

This completes recovery of PDB database.

Larry Catt

OCP

12c, CDB, PDB

Instance Parameter for CDB and PDB

November 10, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases CDB and PDBs use initialization parameters to control their behavior. Initialization Parameters for CDB and PDB have the following characteristics:

CDB will have one SPFILE.
PDB does not have an SPFILE.
PDB inherit initialization parameters from their CDB.
Most initialization parameters can be changed at the PDB level, these parameters that can have a value of TRUE in the column ISPDB_MODIFIABLE column of V$SYSTEM_PARAMETER view.
Changing initialization parameters use the ALTER SYSTEM command with scope of SPFILE or BOTH and they survive system reboot.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Automatic Startup all PDB in a CDB

November 6, 2015 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the PDB must be done after the CDB it is contained in has been started. This procedures covers the implementation of a trigger to startup all PDBs in a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Oct 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

Logon to your CDB where the PDB you wish to start is stored.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

View the available PDBs within the CDB with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

—————————— ———-

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

NOTE: MOUNTED is the closed state for a PDB while it CDB is open.

Create a trigger which executes after the CDB starts with the following create trigger statement.

create trigger PDB_OPEN

after startup on database

begin

execute immediate ‘alter pluggable database all open’;

end;

/SQL> 2 3 4 5 6

Trigger created.

SQL>

Now shutdown the CDB with the command: shutdown immediate.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Restart the CDB with command: startup open.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

Check the status of PDBs with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

Note: All PDBs are now open after restart of CDB without the need to manually issue a command from SQL prompt.
This completes startup of all PDB within its CDB automatically.

Larry Catt

OCP

12c, CDB, dbca

Oracle 12c Create CDB with Database Configuration Assistant in Windows

October 13, 2015 Larry Catt Leave a comment

In Oracle 12c, Container Databases were introduced which allowed for multiple separate database to be placed in a single oracle instance to reduce the amount of resource consumed. This procedure covers the creation of a CDB using Oracle Database Configuration Assistant in a Windows environment.

Logon your windows server as the administrator.

Open Oracle Database Configuration Assistant.

At the Database Operation Page select “Create Database”

At the Creation Mode Page select “Advanced Mode”

At the Database Template Page select the Desired type of database, in This example we select “General Purpose or Transaction Processing”

At the Database Identification Page enter the Global Database Name and SID, then select “Create as Container Database” and “Create an Empty Container Database”

At the Management Options Page, select Configure Enterprise Manger (EM) Database Express if desired.

At the Database Credentials Page, select Use the Same Administrative Password for All Accounts and enter a valid password.

Additionally enter a password the administrative account on the windows system.

On the Network Configuration Page, select the Listener to be used by the new database.

At the Storage Location Page, select the appropriate storage for your database.

At the Database Options page select nothing and Press Next button.

At the Initialization Parameters Page, select the defaults and Press the Next button.

At the Creation Options Page, select Create Database and Generate Database Creation Scripts (giving a valid destination directory) and press Next button.

At the Summary Page, press the Finish button.

The Progress Page, a confirmation of script creation will be displayed, once complete press OK.

The CDB will now be created in Windows.

A confirmation screen will appear once the install is completed and press the Close button to terminate Database Configuration Assistant.

This completes creation of Oracle 12c CDB in Windows environment with Database Configuration Assistant.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, privileges, Users

Privileges of Local Users in Oracle Multitenant Database

October 2, 2015 Larry Catt Leave a comment

In Oracle Multitenant Databases, local users only have access and privileges to a single PDB and not the CDB. You can have the same user name across multiple PDBs housed in the same CDB, but the users share no privileges and only shares a common name. This procedure give a brief overview of local user privileges in Oracle 12c Multitenant Databases. Listed below are the stated restriction on local user privileges.

A privilege granted locally can only be used in that container, even if granted by common user.
Both common and local users can grant privileges locally.
Both common and local users can grant privileges to other common or local users.
Grantor must connect to container and specify CONTAINER=CURRENT clause.
Common and Local user can grant privileges locally if they have appropriate privileges.

A privilege granted locally can only be used in that container, even if granted by common user.

As the common user c##dba logon to pdb1 and pdb2 creating users user_local.

SQL> connect c##dba/password@pdb1

Connected.

SQL> create user user_local identified by password;

User created.

SQL> connect c##dba/password@pdb2

Connected.

SQL> create user user_local identified by password;

User created.

SQL>

Now connect to pdb1 as c##dba and grant the user user_local the DBA role. Connect as user_local and create an additional local user user_test. NOTE: The user user_local can successfully connect to pdb1 and create an additional local user in this PDB.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

Now attempt to connect to pdb2 with local user user_local. NOTE: The user user_local does not even have permission to connect to pdb2, even though the user exists in pdb2.

SQL> connect user_local/password@pdb2

ERROR:

ORA-01045: user USER_LOCAL lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Both common and local users can grant privileges locally.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Both common and local users can grant privileges to other common or local users.

Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL>

Grantor must connect to container and specify CONTAINER=CURRENT clause or specify no CONTAINER clause. CONTAINER=ALL is not available in PDB.

As the user_local grant resource to user_test without a container clause and CONTAINER=ALL clause.

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

SQL> grant resource to user_test container=all;

grant resource to user_test container=all

ERROR at line 1:

ORA-65029: a Local User may not grant or revoke a Common Privilege or Role

SQL>

Common and Local user can grant privileges locally if they have appropriate privileges.

Local user_local can create additional users in PDB pdb1 once the user has been granted the DBA role.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

SQL> connect user_local/password@pdb1

Connected.

SQL> create user user_test identified by password;

User created.

SQL>

This completes the overview of privileges for local users in Oracle 12c Multitenant Database.

Larry Catt

OCP

12c, CDB, Multitenant, privileges, Users

Privileges of Common Users in Oracle Multitenant Database

September 21, 2015 Larry Catt Leave a comment

Privileges for Common Users in Oracle 12c multitenant database architecture have unique constraints allowing for privileges at the CDB level and the PDB levels. Common users may or may not have privileges at all containers in the multitenant database or they may be restricted to only a few. Additionally, privileges maybe restricted to objects in individual containers. The following are general rules governing privileges of common users and we will examine each rule in the below procedures:

A privilege granted commonly can be used in every container and future container.
Only common users can grant common privileges.
Granter of common privileges must connect to root and specify CONTAINER=ALL clause.
Can include system and object privilege.
Privileges should never be given to public.

A privilege granted commonly can be used in every container and future container.

Using the common user C##DBA we will create the user at the CDB level and grant DBA privileges with the CONTAINER clause equal to all.

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> create user c##dba identified by password;

User created.

SQL> grant dba to c##dba container=all;

Grant succeeded.

SQL> connect c##dba/password@cdb1;

Connected.

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

User created.

SQL>

Now alter the session to container PDB1 as the same user and create a user there. NOTE: Permissions are good both in CDB1 and PDB1 for Common users. This is true because container clause was set to ALL.

SQL> show user

USER is “C##DBA”

SQL> alter session set container=PDB1;

Session altered.

SQL> show user

USER is “C##DBA”

SQL> create user pdb1_user identified by password;

User created.

SQL>

Only common users can grant common privileges.

As the common user c##dba grant resource to common user c##user.

SQL> connect c##dba/password@cdb1;

Connected.

SQL> grant resource to c##user;

Grant succeeded.

SQL>

Granter of common privileges must connect to root and specify CONTAINER=ALL clause for privileges to be granted in all containers.

This one is a little tricky because it appears that you can do it, however the key is common privilege, you can have privileges in the CDB and a few PDB and it not be common. This procedure shows an example of this scenario.
Connect to PDB1 as the common user c##dba and grant dba to the user c##user.

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to c##user;

Grant succeeded.

SQL>

Now a user with common privilege of DBA should be able to connect to any PDB in the CDB, however when we try to connect to pdb2, it fails. Further more when we look at the view dba_role_privs, we note that user C##USER does have DBA role, but not commonly.

SQL> connect c##user/password@pdb2

ERROR:

ORA-01045: user C##USER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL> connect c##dba/password@pdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL>

Now logon to the root container as the C##DBA user and grant the DBA role to C##USER. Then view the dba_role_privs table and attempt to connect to PDB2.

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant dba to c##user container=all;

Grant succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

C##USER YES

SQL> connect c##user/password@pdb2

Connected.

SQL>

Can include system and object privilege.

Revoke all rights from user C##USER. If you are following from the above example, the user C##USER has been granted the DBA role twice locally and once commonly. So revoke Common grant at ROOT container and local root grant. Then connect to PDB1 to remove local grant at PDB1.

SQL> connect c##dba/password@cdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

C##USER YES

SQL> revoke dba from c##user container=ALL;

Revoke succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL> revoke dba from c##user container=current;

Revoke succeeded.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE COM

—————————— —

C##USER NO

SQL> connect c##dba/password@pdb1

Connected.

SQL> revoke dba from c##user;

Revoke succeeded.

SQL>

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

no rows selected

SQL>

Now reconnect to C##DBA at the root level and grant connect and select on V$SESSIONS commonly the user C##USER. To test common privilege to objects.

SQL> connect c##dba/password@cdb1

Connected.

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

Grant succeeded.

SQL> grant select on sys.v_$session to c##user container=all;

Grant succeeded.

SQL>

Connect the C##USER and execute a select statement on v$session and v$sql objects to validate access from root container. NOTE: We are able to access object v$session but not v$sql.

SQL> connect c##user/password@cdb1

Connected.

SQL> select count(*) from v$session;

COUNT(*)

———-

SQL> select count(*) from v$sql;

select count(*) from v$sql

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Connect to PDB1 and repeat the above queries to validate common access privileges to objects.

SQL> connect c##user/password@pdb1

Connected.

SQL> select count(*) from v$session;

COUNT(*)

———-

SQL> select count(*) from v$sql;

select count(*) from v$sql

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Privileges should never be given to public from common account.

Once a privilege is granted to public, all users have access to that privilege, which is a huge security risk. However, there is no restrictions built into Oracle to prevent this from occurring. We will demonstrate this by the creation of new user c##dba2, grant the new user no privileges, grant dba role to public, connect to both the CDB and PDB, and finally create a user. This is extremely dangerous, because granting privileges to public grants access to system and object privileges to every user. Finally we will revoke DBA role from public and note that the new user c##dba2 can no longer connect.

SQL> connect / as sysdba

Connected.

SQL> alter session set container=cdb$root;

Session altered.

SQL> create user c##dba2 identified by password;

User created.

SQL> grant dba to public container=all;

Grant succeeded.

SQL> connect c##dba2/password@cdb1

Connected.

SQL> connect c##dba2/password@pdb1

Connected.

SQL> create user testing identified by password;

User created.

SQL> connect / as sysdba

Connected.

SQL> revoke dba from public container=all;

Revoke succeeded.

SQL> connect c##dba2/password@cdb1

ERROR:

ORA-01045: user C##DBA2 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

This completes overview of Privileges for Common Users in Oracle Multitenant Databases.

Larry Catt

OCP

12c, CDB, dbca, PDB

Oracle 12c Create CDB with PDB

September 18, 2015 Larry Catt Leave a comment

In Oracle 12c, Container Databases (CDB) were introduced which allowed for multiple Pluggable database (PDB) to be placed in a single oracle instance to reduce the amount of resource consumed. This procedure covers the creation of a CDB with a PDB using Oracle Database Configuration Assistant in a Windows environment.

Logon your windows server as the administrator.

Open Oracle Database Configuration Assistant.

At the Database Operation Page select “Create Database”

At the Creation Mode Page select “Advanced Mode”

At the Database Template Page select the Desired type of database, in This example we select “General Purpose or Transaction Processing”

At the Database Identification Page enter the Global Database Name and SID, then select “Create as Container Database”, “Create a Container Database with one or more PDBs”, enter Number of PDBs = 1, and Name your PDB.

At the Management Options Page, select Configure Enterprise Manger (EM) Database Express if desired.

At the Database Credentials Page, select Use the Same Administrative Password for All Accounts and enter a valid password. Additionally enter a password the administrative account on the windows system.

On the Network Configuration Page, select the Listener to be used by the new database.

At the Storage Location Page, select the appropriate storage for your database.

At the Database Options page select nothing and Press Next button.

At the Initialization Parameters Page, select the defaults and Press the Next button.

At the Creation Options Page, select Create Database and Generate Database Creation Scripts (giving a valid destination directory) and press Next button.

At the Summary Page, press the Finish button.

The Progress Page, a confirmation of script creation will be displayed, once complete press OK.

The CDB with PDB will now be created in Windows.

A confirmation screen will appear once the install is completed with information about EM configuration, press the Exit button.

Back at the Progress Page, Press the Close button to exit.

This completes creation of Oracle 12c CDB with PDB in a Windows environment with Database Configuration Assistant.

Larry Catt

OCP

12c, backup/recovery, CDB, flashback, Multitenant

Perform Flashback for a CDB in Oracle 12c

August 13, 2015 Larry Catt Leave a comment

Oracle 12c provides the ability to flashback the database to a previous state. This procedures demonstrates the use of this process to flashback the database 15 minutes. NOTE: Four states must exist for flashback to work:

Must be in archivelog mode.
Database must have flashback on.
Control files must contain flashback logs, thus not recreated.
No tablespaces exist with flashback off set.

Logon to your Oracle database server as the Oracle software owner and logon to SQLPLUS.

[root@linux2 etc]# su – oracle

Last login: Thu Jul 1 09:25:23 EST 2015 on pts/1

[oracle@linux2 ~]$

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 1 13:47:01 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Determine SCN of database 15 min ago by using the timestamp_to_scn().

SQL> select timestamp_to_scn(sysdate-1/96) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/96)

——————————

5067892

SQL>

Now using SCN extracted by the previous step, shutdown the database, startup in mount mode and execute the flashback command.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

SQL> FLASHBACK DATABASE TO SCN 5067892;

Flashback complete.

SQL>

Startup the database for normal use with resetlogs option and see that all transactions of the last 15 minutes are gone.

SQL> alter database open resetlogs;

Database altered.

SQL>

NOTE: You should take a full backup of the database as soon after flashback as possible to ensure future recovery.

This completes the use of Flashback in Oracle 12c.

Larry Catt

OCP

Catt Solutions

Category Archives: CDB

Local Users in Oracle Multitenant Databases

Basics of Multitenant Container Database (CDB)

Recovery of PDB in Oracle 12c Multitenant Database

Instance Parameter for CDB and PDB

Automatic Startup all PDB in a CDB

Oracle 12c Create CDB with Database Configuration Assistant in Windows

Privileges of Local Users in Oracle Multitenant Database

Privileges of Common Users in Oracle Multitenant Database

Oracle 12c Create CDB with PDB

Perform Flashback for a CDB in Oracle 12c

Oracle tips and tricks.