Large Tables In Oracle database

Large Tables In Oracle database 

creating a table with 10 million records

ORACLE> sqlplus '/as sysdba'

SQL> alter session set workarea_size_policy=manual;
SQL> alter session set sort_area_size=1000000000;

SQL> create table QADER_T1 as select rownum as id, 'Just Some Text' as textcol, mod(rownum,5) as numcol1, mod(rownum,1000) as numcol2 , 5000 as numcol3, to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e7;

Insert Another 10 Million Records to the Table

ORACLE> sqlplus '/as sysdba'

SQL> insert into QADER_T1 select rownum as id, 'Just Some Text' as textcol, mod(rownum,5) as numcol1, mod(rownum,1000) as numcol2, 5000 as numcol3,
to_date ('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2018' ,'dd.mm.yyyy') as time_id from dual connect by level<=1e7;
SQL> COMMIT;

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

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

1. 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 target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Note:

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

2. Root Container Backup

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

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

$ rman target=/

RMAN> BACKUP DATABASE ROOT;

Note:

The datafiles associated with the CBD (cdb1) are included, but all the PDBs (pdb1, pdb2, pdb$seed) are not included in the backup.

3. 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, pdb2;

Note:
You can see this includes the datafiles for both referenced PDBs.

Alternatively, connect to a specific PDB and issue the following command.

$ rman target=sys@pdb1

RMAN> BACKUP DATABASE;

4. Tablespace and Datafile Backups

Multiple PDBs in the same CDB can have a tablespace with the same name, for example SYSTEM, SYSAUX and USERS. One way to remove that ambiguity is connect to the appropriate PDB. Once RMAN is connected to the PDB, the tablespace backup commands is unchanged compared to previous versions.

$ rman target=sys@pdb1

RMAN> BACKUP TABLESPACE system, sysaux, users;
Alternatively, you can remove the ambiguity by qualifying the PDB name with the tablespace name when connected to the root container.

$ rman target=sys@cdb1

RMAN> BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;
Datafiles have unique file numbers and fully qualified names, so they can be backed up from the root container or the individual PDB.

$ rman target=/

# Or

$ rman target=sys@pdb1

RMAN> BACKUP DATAFILE 8, 9, 10;
If you are connecting to a PDB, only the files belonging to that PDB can be backed up. So for example, when connected as PDB1, we get an error if we try to backup the SYSTEM datafile from the root container.

RMAN> BACKUP DATAFILE 1;

Starting backup at 23-DEC-13
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

12c Pluggable Databases FAQ's

12c Pluggable Databases FAQ's

1.Can Point-In-Time recovery be conducted at Pluggable Database (PDB) level?
Yes, it will be possible.

2. Can database services be created inside a Pluggable Database (PDB)?
Of course you can have many services within a PDB. Each will denote the PDB within which it is defined as the initial current container. Use the normal methods to create, maintain, and drop additional services in a PDB. A PDB’s default service must not be dropped. Of course, service names should be unique across all the databases that are registered with a particular listener.

3. Can I have local TEMPORARY tablespace at Pluggable Database (PDB) level?
In spite, during the PDB creation only SYSTEM and SYSAUX tablespaces created, you can create local temporary tablespace later for every PDB. UNDO tablespace can NOT be local and stays on the CDB level.

4. Is conventional database mode will be still available in 12c database and later versions?
Yes, on Oracle 12c you will have two options of creating a usual old-fashioned Oracle database (non-CDB) or creating a Container Database (CDB) that will hold all your Pluggable Databases (PDB) that you will create or plug later. However, based on my information non-CDB mode will be available only in 12c release 1 for compatibility reasons and will be discontinued in 12cR2.

5. Will Oracle 12c database have only one container?
Yes and No! In fact, Oracle considers a CDB as well as all PDBs as containers. Every container has own name. For example a single Container Database (CDB) has a container name CDB$Root. Every Pluggable database has a container name similar to the PDB name. On CDB level by default your current container is CDB$Root but you can switch between PDBs’ containers also. On CDB level you can change current container with following SQL:

alter session set container = my_cdb

or verify current container with below SQL:

select Sys_Context('Userenv', 'Con_Name') "current container" from dual;

6. Is cloning a PDB from one in a different CDB supported?
I have not seen it in 12c Beta 2, but Oracle intends to support it in the final version. You’ll identify the source CDB by creating a database link to it from the CDB in which you will create the clone.

7.Is online PDB cloning supported?
Not in 12c Beta2, but Oracle intents to remove this restriction in the final version by using the same approach that allows online RMAN backup: by re-constructing any blocks that change during the time it takes to copy the source PDB’s datafiles.

8.Why switching PDB Open_Mode/Restricted modes requires 2 SQL commands?
Not sure why, but in final 12c release it will be possible to go from any one Open_Mode/Restricted combination to another with a single use of the alter pluggable database statement.

9.Will it be possible to allocate/restrict resources (CPU/Memory) of PDB’s (Pluggable)?
Yes, it will be possible using new functionality of Oracle 12c Resource Manager.
Using 12c Resource manager you will be able control CPU, Exadata I/O, sessions and parallel servers. A new 12c CDB Resource Manager Plan will use so-called “Shares” (resource allocations) to specify how CPU is distributed between PDBs. A CDB Resource Manager Plan also can use “utilization limits” to limit the CPU usage for a PDB. With a default directive, you do not need to modify the resource plan for each PDB plug and unplug. However I’m not aware of a possibility to restrict memory of PDBs, since they will share one single SGA

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB)

Startup and Shutdown Container Databases (CDB) and Pluggable Databases (PDB) 

1. Container Database (CDB)
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.

STARTUP [NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]
SHUTDOWN [IMMEDIATE | ABORT]

2. Pluggable Database (PDB)
Pluggable databases can be started and stopped using SQL*Plus commands or the ALTER PLUGGABLE DATABASE command.

SQL*Plus Commands
The following SQL*Plus commands are available to start and stop a pluggable database, when connected to that pluggable database as a privileged user.
STARTUP FORCE;
STARTUP OPEN READ WRITE [RESTRICT];
STARTUP OPEN READ ONLY [RESTRICT];
STARTUP UPGRADE;
SHUTDOWN [IMMEDIATE];
Some examples are shown below.

STARTUP FORCE;
SHUTDOWN IMMEDIATE;

STARTUP OPEN READ WRITE RESTRICT;
SHUTDOWN;

STARTUP;
SHUTDOWN IMMEDIATE;
ALTER PLUGGABLE DATABASE
The ALTER PLUGGABLE DATABASE command can be used from the CDB or the PDB.

The following commands are available to open and close the current PDB when connected to the PDB as a privileged user.

ALTER PLUGGABLE DATABASE OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE CLOSE [IMMEDIATE];
Some examples are shown below.

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

ALTER PLUGGABLE DATABASE OPEN READ WRITE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
The following commands are available to open and close one or more PDBs when connected to the CDB as a privileged user.

ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ WRITE [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN READ ONLY [RESTRICTED] [FORCE];
ALTER PLUGGABLE DATABASE <pdb-name-clause> OPEN UPGRADE [RESTRICTED];
ALTER PLUGGABLE DATABASE <pdb-name-clause> CLOSE [IMMEDIATE];
The <pdb-name-clause> clause can be any of the following:

One or more PDB names, specified as a comma-separated list.
The ALL keyword to indicate all PDBs.
The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.

Some examples are shown below.

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

ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE IMMEDIATE;

12c Database Commands

12c Database Commands

1. Create and open a new Oracle 12c Pluggable database (PDB)

Create a pdb using the seed:

Now we will create and open a new Pluggable database (PDB) called My_PDB.
Every CDB has a standard template of a PDB whose name is PDB$Seed. We actually create a new PDB by cloning this Seed. See below a SQL example :

Pre-requisite
connect to root with CREATE PLUGGABLE DATBASE privileges.
configure a storage location (and ensure disk space) for the new database’s files.
select a unique name for the new database.

create pluggable database My_PDB admin user App_Admin identified by pass file_name_convert = ('/pdbseed/', '/my_pdb/');

Ex:
create pluggable database lub admin user pdbmqm identified by oracle file_name_convert=('/u02/app/oracle/oradata/orcl/pdbseed', '/u02/app/oracle/oradata/orcl/lub');

The file_name_convert clause specifies how names for the new files are derived from those of the seed using the same scheme as you know from RMAN. During the PDB creation Oracle copies just 2 database files for System and Sysaux tablespaces to the new location of our PDB. The rest of database files for undo, redo, and so on is global for the whole CDB and belongs to the special container called CDB$Root.

The admin user clause is mandatory. In an extended form, it lets you grant privileges and roles to the new user. This user is able to start a new session only within My_PDB.

On completion of a create pluggable database command, the new PDB will be in MOUNTED mode. Before you can start a session in the new PDB, it must be open. So, let’s open our PDB using below SQL :

alter pluggable database My_PDB open;

2. Check Container Database (CDB) and Pluggable database (PDB) files

select con_id, tablespace_name, file_name from cdb_data_files where file_Name like '%/cdb1/pdbseed/%' or file_Name like '%/cdb1/my_pdb/%' order by 1, 2;

CON_ID Tablespace_Name File_Name
------ ------------ -------------------------------------------------
2 SYSAUX /opt/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
2 SYSTEM /opt/oracle/oradata/cdb1/pdbseed/system01.dbf
3 SYSAUX /opt/oracle/oradata/cdb1/My_PDB/sysaux01.dbf
3 SYSTEM /opt/oracle/oradata/cdb1/My_PDB/system01.dbf

3. Open all Oracle 12c Pluggable Databases (PDB)

Each PDB within a CDB has its own Open_Mode and Restricted status in each instance of a RAC. The possible values Open_Mode are MOUNTED, READ ONLY, and READ WRITE; and the possible values for the Restricted status are YES and NO when the PDB is open and otherwise null.

Starting an instance (which opens the entire CDB) does not cause PDBs to be opened. The alter pluggable database statement is used to set the Open_Mode. You give in SQL either the name of a particular PDB or use the keyword all, thus:

alter pluggable database all open;

4. Close all Oracle 12c Pluggable Databases (PDB) in CDB
This SQL statement closes all the PDBs in the CDB:

alter pluggable database all close;

Show the open time of PDBs

select substr(name,1,10) name, con_id, open_mode, to_char(open_time,'DD-MON-YYYY') open_time, total_size from v$pdbs;

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME   OPEN_MODE
---------- ------------------------------ ----------
 2 PDB$SEED   READ ONLY
 3 ORCL   READ WRITE
 4 PROD   READ WRITE

or

show pdbs

How to set the pdb database:

SQL> alter session set container=PROD;
SQL> show con_name

CON_NAME
------------------------------
PROD
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/cdb1/prod/system01.dbf
/home/oracle/app/oracle/oradata/cdb1/prod/sysaux01.dbf

How to close the PDB database:
alter pluggable database devpdb1 close immediate;

How to set the container database:
alter session set container = <db_name>;

How to check the container database:
show con_name

How to drop the PDB database:
drop pluggable database My_PDB keep datafiles;

How to Tell If the Database Is a CDB
select name, cdb, con_id from v$database;

Viewing Information About Containers
select name, con_id, db_id from v$containers;

Viewing Information About the PDBs
select pdb_id, pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB
select name, open_mode, restricted, open_time from v$pdbs;

Viewing the History of PDBs
select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property;

Viewing all the tablespaces in a CDB
select tablespace_name, con_id from cdb_tablespaces;</em>

Note:

1. After creating a PDB database open it atleast once in read-write mode to complete the integration with the CDB.

2. Memory and processes are required at container database level only.

Components of the CDB are as follows

Components of the CDB are as follows

* One or more PDBs

* CDB$ROOT, the keeper of all the PDBs that are part of the collection

* PDB$SEED, a template from which new PDBs can be created