Oracle Database internal interview questions

Oracle Database internal interview questions
1.How the user session established in oracle database?
2.How the user fetching the data from data file in oracle DB?
3.what are sequence of activities happening during insert/update/delete in oracle DB?
4.how the data selection happening in oracle RAC DB?
5.what happen to uncommitted transactions on UNDO in oracle DB?
6.How Database & ASM communicates when you select query run on database?
7.How oracle applies redo changes to physical standby?
8.what is the sequence of steps to generate the archive log in oracle DB?
9.what will happen internal ,when you update the oracle database?
10.what are internal changes will happen,when you do switch over in physical standby?
11.How oracle RAC provides service availability when the nodes fail-over on one of the 2 node RAC?
12.how oracle internally allocates space to system object extension?
13.what will happen when you rebuild index online?
14.what will happen ,when you do re-balance on ASM DISK?
15.what will happen when you kill the flashback process in oracle database?
16.what is the   use of OS kernal parameter in oracle database server?
17.How to allocate memory for the database in oracle database?
18.can flashback database brings dropped datafile in database?
19,what is the object sequence for export/import in oracle database?
20.what will happen when you apply CPU & PSU on oracle home?
21.what is the startup sequence of oracle RAC database?
22.what will happen when you kill the archive process in oracle database?
23.How the user session established in oracle RAC database?
24.what will happen when you try to create object on ASM instance?
24.How do you backup ASM instance?
25.what is the use of begin backup mode in oracle hot backup?
26.will RMAN hot backup put the database to begin backup mode ?

Limitation of oracle database flash back feature

Limitations of Flashback Database

• Flashback Database can only undo changes to a data file made by Oracle Database. It cannot be used to repair media failures, or to recover from accidental deletion of data files.
• You cannot use Flashback Database to undo a shrink data file operation.
• You cannot use Flashback Database alone to retrieve a dropped data file. If you flash back a database to a time when a dropped data file existed in the database, only the data file entry is added to the control file. You can only recover the dropped data file by using RMAN to fully restore and recover the data file.
• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

Prerequisites for Flashback Database and Guaranteed Restore Points

•  database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
• You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

Guaranteed Restore Points

To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater

Note:
There are no special prerequisites to set before using normal restore points.
Using Normal and Guaranteed Restore Points
This section describes the various commands and monitoring capabilities you use with normal and guaranteed restore points.

Creating Normal and Guaranteed Restore Points
To create normal or guaranteed restore points, use the CREATE RESTORE POINT SQL statement, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default).

To create a restore point:

Connect SQL*Plus to a target database.


Run the CREATE RESTORE POINT statement.

The following example shows how to create a normal restore point in SQL*Plus:

SQL> CREATE RESTORE POINT before_upgrade;
This example shows how to create a guaranteed restore point:

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
See Also:
Oracle Database SQL Language Reference for reference information about the SQL CREATE RESTORE POINT statement

"Listing Restore Points" to learn how to list restore point

"Dropping Restore Points" to learn how to delete restore points

Listing Restore Points
You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository. The variations of the command are as follows:

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;
RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The following example shows sample output:

RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
341859           28-JUL-06            28-JUL-06 NORMAL_RS
343690           28-JUL-06 GUARANTEED 28-JUL-06 GUARANTEED_RS
To see a list of all currently defined restore points (normal and guaranteed), use the V$RESTORE_POINT control file view with the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
        FROM V$RESTORE_POINT;


What happens if RVWR cannot write to disk?
A: It depends on the context where the write error occurs:

If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.
If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.



When are the flashback logs deleted?
A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.

If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.
If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.
If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

user creation on oracle 12c database

we have two kind of users available on oracle 12c database

1)local users       --> only for specific PDBS
2)common users --> users on CDB with hass to all PDBS

1)local users creation steps

SQL> alter session set container=pdb1;

Session altered.

SQL> create user user1_pdb1 identified by XXXXX;

User created.

SQL> grant pdb_dba to user1_pdb1;(its a special role for PDB)

Grant succeeded.

SQL> conn user1_pdb1/XXXX@pdb1
connected.

2)common user always start with c###
SQL> conn / as sysdba
SQL> create user c##bala identified by XXXXX;

User created.

SQL> grant dba to  c##bala;

Grant succeeded.

SQL> conn c##bala/XXXX;
Connected.
SQL> create table test_11dec2016_dba_obj as select * from dba_objects;


Table created.

How to enable flahsback on oracle database?

Steps to enable flahsback on oracle database:

connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options

set the recovery file dest location & size & set the flashback retention

SQL> alter system set db_recovery_file_dest='/media/sf_12cR1/flashback' scope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=100g scope=both;

System altered.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /media/sf_12cR1/flashback
db_recovery_file_dest_size           big integer 100G

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

Total System Global Area 2137886720 bytes
Fixed Size                  2290416 bytes
Variable Size            1711279376 bytes
Database Buffers          419430400 bytes
Redo Buffers                4886528 bytes
Database mounted.
SQL>  alter database flashback on;

Database altered.


SQL> alter system set db_flashback_retention_target=6000;

System altered.

SQL>  SELECT NAME, FLASHBACK_ON FROM V$DATABASE;

NAME      FLASHBACK_ON
--------- ------------------
CDB1      YES


SQL> alter database open;


Database altered.

PDB database recovery in oracle 12c

container database (CDB) recovery is same as normal database recovery .

Pluggable Database (PDB) Complete Recovery

There are two ways to restore and recover PDBs. From to root containers, you can restore and recover one or more PDBs using the following script.

$ rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  RESTORE PLUGGABLE DATABASE pdb1 ;
  RECOVER PLUGGABLE DATABASE pdb1 ;
  ALTER PLUGGABLE DATABASE pdb1 OPEN;
}

SQL> alter session set container=pdb1;

Session altered.

SQL> create table test_dba_objec as select * from dba_objects;

Table created.

SQL> select count(*) from test_dba_objec;

  COUNT(*)

----------

     90780

RMAN> BACKUP PLUGGABLE DATABASE pdb1;

Starting backup at 11-DEC-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=275 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=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf

RMAN> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

Statement processed

RMAN> RESTORE PLUGGABLE DATABASE pdb1 ;

Starting restore at 11-DEC-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=270 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 00007 to /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf

channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

channel ORA_DISK_1: reading from backup piece /media/sf_12cR1/backup/0drn72lv_1_1

channel ORA_DISK_1: piece handle=/media/sf_12cR1/backup/0drn72lv_1_1 tag=TAG20161211T135718

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 11-DEC-16

RMAN> RECOVER PLUGGABLE DATABASE pdb1 ;

Starting recover at 11-DEC-16

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 11-DEC-16

RMAN> ALTER PLUGGABLE DATABASE pdb1 OPEN;

Statement processed

SQL> alter session set container=pdb1;

Session altered.

SQL> select count(*) from test_dba_objec;

  COUNT(*)

----------

     90780

Backup container database in oracle 12c

Container Database (CDB) Backup

Backup of a Container Database (CDB) is essentially the same as a non-Container Database. The main thing to remember is, by doing 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.

RMAN> backup database plus archivelog;

PDB database backup

Pluggable Database (PDB) Backup

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

$ rman target=/

RMAN> BACKUP PLUGGABLE DATABASE pdb1;


Starting backup at 11-DEC-16
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=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-DEC-16
channel ORA_DISK_1: finished piece 1 at 11-DEC-16
piece handle=/media/sf_12cR1/backup/0brn71t3_1_1 tag=TAG20161211T134403 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 11-DEC-16

startup/shutdown CDB & PDB in oracle 12c

startup/shutdown CDB & PDB in oracle 12c


Startup and shutdown of the container database is the same as it has always been for regular instances. The SQL*Plus STARTUP and SHUTDOWN commands are available when connected to the CDB as a privileged user. Some typical values are shown below.


SQL> select name,open_mode ,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> select pdb_name from dba_pdbs;

PDB_NAME
--------------------------------------------------------------------------------
PDB$SEED
PDB1
PDB4


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

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2290416 bytes
Variable Size            1711279376 bytes
Database Buffers          419430400 bytes
Redo Buffers                4886528 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode ,cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> select pdb_name,status from dba_pdbs;

PDB_NAME
--------------------------------------------------------------------------------
STATUS
-------------
PDB$SEED
NORMAL

PDB1
NORMAL

PDB4
NORMAL


PDB start/stop

ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;


SQL>
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY FORCE;SQL>

Pluggable database altered.





SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

Pluggable database altered.




SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB4                           READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY FORCE;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ ONLY
PDB4                           READ WRITE

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB4                           READ WRITE

oracle database limits

Here is the  limitation of oracle database

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment.	2 blocks
	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
	Size of a control file	Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILESinitialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier);4 GB (without K/M modifier)
	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier);2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement  Control file can be resized to allow more entries; ultimately an operating system limit
	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	50 KB
	Maximum size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 KB Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	232 (4 GB) blocks
Smallfile (traditional) Tablespaces	Number of blocks	222 (4 MB) blocks

Logical Database Limits

Item	Type			Limit
GROUP BYclause	Maximum length			The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
Indexes	Maximum per table			Unlimited
	total size of indexed column			75% of the database block size minus some overhead
Columns	Per table			1000 columns maximum
	Per index (or clustered index)			32 columns maximum
	Per bitmapped index			30 columns maximum
Constraints	Maximum per column			Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement			Unlimited in the FROM clause of the top-level query  255 subqueries in the WHERE clause
Partitions			Maximum length of linear partitioning key	4 KB - overhead
			Maximum number of columns in partition key	16 columns
			Maximum number of partitions allowed per table or index	64 KB - 1 partitions
Rows			Maximum number per table	Unlimited
Stored Packages			Maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.  See Also: Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit			Maximum value	Operating system-dependent, typically 32
Users and Roles			Maximum	2,147,483,638
Tables			Maximum per clustered table	32 tables
			Maximum per database	Unlimited

Process and Runtime Limits

Item	Type	Limit
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
	Maximum per Backup session	15
Sessions	Maximum per instance	32 KB; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance

---

oracle database block corruption scripts.

script to get corrupted block list.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

check the corrupted block on database level using db verify & rman validate.

RMAN> backup validate check logical datafile  #

dbv userid=system/****** file= file path  blocksize=8192

DBVERIFY - Verification complete




Total Pages
Examined         : 3968000

Total Pages Processed (Data) :
2113698

Total Pages Failing  
(Data) : 0

Total Pages Processed (Index):
1311235

Total Pages Failing  
(Index): 0

Total Pages Processed (Other):
502143

Total Pages Processed
(Seg)  : 3553

Total Pages Failing  
(Seg)  : 0

Total Pages
Empty            : 37371

Total Pages Marked
Corrupt   : 0

Total Pages
Influx           : 0

Highest block
SCN            :
1095073332 (1.1095073332)

Replication methods in oracle and MSSQL server database

In oracle/MSSQL database we can have  multiple replication /standby database available for DR purpose.

oracle to oracle replication methods


1.RAC --Real application cluster for High availability
2.oracle Data guard for standby DR
3.oracle golden gate replication methods
4.we can achieve oracle to oracle replication with streams

oracle to other database replication methods

1.we can do oracle to MSSQL server & IBM DB2  replication using golden gate.
2..we can do oracle to MSSQL server & IBM DB2  replication using gateway server
3.we can do oracle to MSSQL server & IBM DB2  replication using  drivers to support
4.we can do oracle to MSSQL server & IBM DB2  replication using  third party tools like DBmoto

MSQL server to oracle replication methods
1.we can do MSSQL server to oracle DB replication using golden gate
2.we can do MSSQL server to oracle DB replication using native MSSQl server replication method with publisher/ditribuitor/subscriber
3.we can do MSSQL server to oracle DB replication using third party tools like DBmoto
4.we can do MSSQL server to oracle DB replication using gateway server

steps to clone PDB manually in oracle 12c

Clone a Pluggable Database (PDB) Manually

Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause. Make sure the source PDB is open in READ ONLY mode.

Here pdb1 is SOURCE pdb4 as target(new DB )


ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;--source DB needs to be read only mode

CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;

SQL>  select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/pdb2_users01.dbf

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

14 rows selected.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY
*
ERROR at line 1:
ORA-65019: pluggable database PDB1 already open


SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2  CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2


Pluggable database created.

SQL> SQL> SQL>
SQL> ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;
Pluggable database altered.

SQL>

Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE
PDB4                           READ WRITE

 Delete a Pluggable Database (PDB) Manually

When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;  2  SQL> SP2-0734: unknown command beginning "When dropp..." - rest of line ignored.
SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           MOUNTED

SQL> alter PLUGGABLE DATABASE  pdb4  open read write;

Pluggable database altered.

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE