Ritu's Weblog

Tuesday, October 7, 2014

Creation of CDB

The following blog explains how to create a CDB named cdbtest containing 1 PDB named pbdtest

export ORACLE_SID=cdbtest
To be authenticated with a password file create a password file
Create the initialization parameter file
Connect to the instance
Create a server parameter file
Start the instance with nomount option
Issue the create database

The create database command has following additional clause to indicate that it is CDB

enable pluggable database
seed file_name_convert=('/u01/app/oracle/oradata/testcdb', '/u01/app/oracle/oradata/pdbseed')

Enable pluggable database clause creates a CDB with a root and a seed

The seed file_name_convert generates file names for the seed's file in /u01/app/oracle/oradata/pbdseed directoru using filenames in the /u01/app/oracle/oradata/testcdb directory.

After you create a CDB it consist of the root and the seed.

Create additional tablespaces if required
Run scripts to build data dictionary views

The following three scripts needs to be run:
catalog.sql
catproc.sql
pubbld.sql

Create PDBs to store user data,.

You can create a PDB from the seed using the create pluggable database statement.

create pluggable database testpdb admin user testpdb identified by test123 file_name_convert=('/test1/bench/pdbseed','/test1/bench/testpdb' );

PDB is now in mount state, modify its mode to OPEN

alter pluggable database testpdb open ;

Set the current session to point to PDB

alter session set container=testpdb ;

Create a local user which will own the schema

create user test identified by test;
grant dba to test;

Clone a pbd from existing pdb.

A PDB namely testpdb exist. We need to clone this pbd and call the cloned PDB as clonePDB

Open source pdb in read-only mode

create pluggable database clonepdb from testpdb file_name_convert=('/testpbd,'clonepdb') ;

; change the mode to open

Is it a CDB or a non-CDB database

select cdb from V$database

What does the various con_id stand for:

con_id        desc
0                    the data pertains to entire PDB
1                     the data pertains to the root (oracle metadata)
2                    the data pertains to the seed
3                    the data pertains to the PDB

How many tablespace do I have after I create a CDB containing 1 PDB:

SQL> select name,con_id from v$tablespace;

NAME                               CON_ID
------------------------------ ----------
SYSTEM                                  1
SYSAUX                                  1
UNDOTBS1                                1
USERS                                   1
TEMP                                    1
SYSTEM                                  2
SYSAUX                                  2
TEMP                                    2
SYSTEM                                  3
SYSAUX                                  3
TEMP                                    3

NAME                               CON_ID
------------------------------ ----------
USERS                                   3

The root ,seed and PDB has system, sysaux and temp tablespace each.

Additionally , the root container has UNDO tablespace . Root and PDB conainters also has user tablespace.

What is my user and con_id and con_name

SQL> show user;
USER is "SYS"
SQL> show con_id;

CON_ID
------------------------------
1
SQL> show con_name

CON_NAME
------------------------------

How many PDBs are exist in a given CDB

cdb_pdbs or dba_pdbs
How many containers does it have:

Getting started with AWR

Do I have license to enable AWR reports:

Enterprise editions have license for AWR and tuning packs. Check Control_management_pack_access parameter to figure out which server manageability pack is active.The following packs are available:

The DIAGNOSTIC pack includes AWR, ADDM, and so on.
The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.

The default value for this parameter in Enterprise Edition is DIAGNOSTIC+TUNING and for all other editions is none.

SQL> show parameters control_management%;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

Is AWR enabled on my server:

Gathering database statistics using the AWR is enabled by default and is controlled by the STATISTICS_LEVEL parameter. The statistics_level paramter should be set to TYPICAL or ALL to enable statistics gathering by the AWR

In the following example, AWR reporting is disabled.

SQL> show parameters statistics_level ;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string BASIC

You can enable it with the following statement :

alter system set statistics_level='ALL';

How often are AWR reports generated:

By default , snapshots are captured once every hour and retained in the database for 8 days.

SQL> SQL> col retention format a20
SQL> col snap_interval format a20
SQL> select dbid, snap_interval, retention from dba_hist_wr_control;

DB Id SNAP_INTERVAL RETENTION
----------- -------------------- --------------------
1622729499 +00000 01:00:00.0 +00008 00:00:00.0

You can adjust the interval and retention of snapshot generation by issuing the following command:

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  interval  =>  60,
  retention =>  20160);


Interval is specified in minutes


The following script manually generates AWR report:



if [[ $# -lt 1 ]]

then

  echo "runawr.sh  <DELAY(RUNTIME)> "

  exit 1

fi



RUNTIME=$1



sqlplus / as sysdba << !

execute dbms_workload_repository.create_snapshot();

!



sleep $RUNTIME





sqlplus / as sysdba << !



var  endSnapshot number ;

var startSnapshot number;

var startSnapshot number;

var myDbid number ;

var myInst      number;







execute select dbms_workload_repository.create_snapshot() into :endSnapshot from dual ;



execute select :endSnapshot - 1  into :startSnapshot from dual;

show user ;

execute select dbid into :myDbid from v\$database;

execute select instance_number into :myInst from v\$instance;



print :endSnapshot ;

print :startSnapshot ;

print :myDbid ;

print :myInst;





spool awr_report.txt



select * from table (dbms_workload_repository.awr_report_text(:myDbid,:myInst,:startSnapshot,:endSnapshot));

!

Wednesday, January 15, 2014

Pluggable Database Concepts

An Oracle Database instance is combination of SGA and group of background processes. An Oracle database is a group of files that contain data. In a single instance db, there is a 1 to 1 relationship between an instance and a database. In a RAC environment, there are many to 1 relationship between instance and DB.

To have multiple DB on the same machine, you need to run multiple DB instance. So, in a single instance DB, if you have 3 mounted DB, you will need 3 DB instances. With PDB, the idea is to remove this duplication of resources and to share one DB instance with multiple DBs now referred to as CDBs.

A container Database (CDB) contains Plugable database (PDB)

A container database consists of following three components each of which is called a container

the root container (CDB$ROOT) which contains metadata for Oracle supplied objects
Seed PDB (PBD$SEED) which contains metadata for user supplied objects
PDBs which are generated by cloning seed PBD and assigned a unique ID upon creation.

At a physical level, the CDB has a database instance and database files, just as a non-CDB does.

The following files exist only in root container:

The redo log files

The redo log files are common for the whole CDB and therefore the information it contains is annotated with the identity of the PDB where a change occurs. All PDBs in a CDB share the ARCHIVELOG mode of the CDB

The control file

The control files are also common for the whole CDB. The control files are updated to reflect any additional tablespaces and datafiles of plugged PDBs.
Add info about undo tbsp ??

Following files exist either in both root container and user container or in one of them(PDBs and seed PDBs together form user container)

Temporary tbsp (required in root ;optional in user container):

A temporary tbsp is required in the root container. This tbsp is optional in PDB. Each PDB can hold its own temporary tbsp for its own local users.

System and SysAux tablespace (required in both containers)

Each container has its own data dictionary stored in its proper SYSTEM tbsp ,containing its own metadata and a SYSAUX tbsp.

User tablespace (only in user container)

The PDBs can create their tbsp according to its application needs

Each datafile is associated with a specific container , named CON_ID.

There are four ways to provision a PDB:

Create a new PDB from seed PDB
Plug in a non-CDB as a PDB
Clone a PDB from another PDB (in same or another CDB)
Plug a unplugged PDB into another CDB. (However, a PDB can be plugged into only one CDB at a time)

You can plug or unplug a PDB from a CDB. A PDB can be plugged into only one CDB at a time.A CDB has a common users which can log in as root and adminster the PDB.You can create a CDB by either DBCA tool or by create database sql.

When you create a CDB, you can specify how many PDBs it will contain. Once the CDB is created, you can use DBCA to plug and unplug PDBs

When you create a CDB using a CREATE DATABASE sql statement, you must enable PDBs (by specifying enable PLUGGABLE DATABASE clause) and specify the names and locations of the seed's files.

Backup and Recovery:

Backup :

RMAN will support backup and recovery of a CDB that contains zero or more PDBs. All RMAN operations will be performed while connected as root of a CDB. An individual PDB is not a valid RMAN target db. New syntax, namely Pluggable Database, is introduced to allow backup of CDB.

Recovery:

On a CDB, the recovery can be done for the entire CDB, for a PDB, a tbsp, a datafile or even a block.

If the files in the root container are lost (namely redo log file, control files, temp tbsp, ...)the only possible recovery is once CDB is in a mounted state.

Wednesday, January 8, 2014

How to create “Cold” RMAN Backup

This blog entry describes how to take rman backup of a database with NOARCHIVELOG mode. The only option you have to take backup of a NOARCHIVELOG mode database is to take consistent whole database backup.

The characteristic of a consistent backup is as under:

All read/write datafiles and control files are checkpointed with the same system change number (SCN)
A consistent backup does not need recovery after it is restored as all files in the backup are guaranteed to contain all changes up to the same SCN.

The steps involved in taking consistent backup as as under:

Make sure the database is shutdown properly.

If the database is not shut down consistently, for example, an instance fails or you issue a “shutdown abort” statement, then the datafiles are always inconsistent and hence your restore will fail. The script is shutting down the database twice as to ensure that the datafiles are in consistent state.

Startup the database in mount option
Configure the location where you need to take the backup

The script is backing up the database on an disk at ‘backupPool/backup’ directory

Configure autobackup of controlfile and specify a location where control file will be backed up.
Backup the complete database

Here is the script to take cold rman backup:

#!/bin/sh
echo "Taking backup of $ORACLE_SID\n"
#rman target / log=backupbench.log << !

shutdown immediate ;
startup pfile=p_build.ora force dba;
shutdown immediate;
startup pfile=p_build.ora mount ;

configure device type disk parallelism 15 backup type to backupset ;
configure channel device type disk format '/backupPool/backup/ora_df%t_s%s_s%p' ;
configure controlfile autobackup on ;
configure controlfile autobackup format for device type disk to '/backupPool/backup/cf_%F' ;

backup database tag 'JGD_create';
alter database open;
exit
!

Wednesday, July 10, 2013

Pluggable Database : An Introduction

What is PDB:

Pluggable database , also referred to as Multitenancy, is the new option of Oracle Database 12c that enables dense database consolidation, rapid provisioning and rapid cloning as well as rapid patching and upgrades. It enables the administrators to manage many databases as one and allows resource management between databases.

In 12c, you can create two types of database: container database and non container database. Container database, referred to as CDB, is the database that can contain several PDBs. Non-container database, referred to as non-CDB, is the regular database that existed prior to 12c.

Multitenacy is just a deployment choice. When you create a database, you choose whether you want to create a CDB or a non-CDB. Once you make that choice and create the database, nothing else changes. The way you connect to database and the way you interact with database remains the same for both CDB and non-CDB.

Business challenges that PDB address:

Prior to 12c , database consolidation would happen through one of the following two ways:

1) Server or OS consolidation: You would start several database instances on a single server. Each instance would be on a server virtualized environment like LDOMs or on an OS virtualized environment such as Solaris Containers.The advantages of this approach is that it provides complete isloation and maximum security. No two distinct database users can ever meddle with each other..as there are usually that many instances as the number of databases. This form of database consolidation cannot provide very dense consolidation as it is not sharing any of the database resources: there are as many copies of Oracle background process and SGA as there are databases so the system very soon exhaust its CPU and memory.

2) Consolidation in the database : You would enable schema based consolidation where each database is represented as a schema in a single monolithic database instance.In this approach, you can consolidate lot of database (schema) into one database instance thereby attaining dense database consolidation. However, the disadvantages are its weak or no name space isolation and very weak security model. A database user can view/change data of another database schema.

Pluggable database provides database consolidation within the database. In a way, it is a supported version of schema based consolidation with all disadvantages of schema based consolidation removed. Additionally, it also provides additional features like pluggability and rapid cloining.

Introduction to Multitenancy architecture:

At a higher level, an Oracle Database consist of Oracle Database Instance and Oracle Database. The instance consist of group of Oracle background process and a shared memory. The database consist of group of data files; some of which contain the user data and some contain Oracle system data. When you start an Oracle database, you essentially have a bunch of Oracle background processes, a bunch of shared memory and data files for storing the data. And this gets replicated for each database that is created. So, each database needs additional shared memory and series of Oracle database process....which quickly consumes the available server resources.

With multitenant architecture you can create an empty container database and then plug in the databases as PDBs. Now you are using just one SGA and one set of Oracle database processing thereby saving the system resources to enable dense database consolidation

By consolidating existing databases as PDBs, administrators can manage many databases as one. For example, tasks such as backup and disaster recovery are performed at the level of the CDB. The multitenant architecture also provides the flexibility to take backups and perform point-in-time recovery at the PDB level, if required.

At a physical level Oracle database consist of a bunch of files: data files as well as bootstrap files like control files, spfile and so on. The physical data files implements the logical entity tablespace which in turn implements tables. Tables hold only three kinds of data

1) metadata that describes the oracle System
2) Metadata that describes the user schema
3) Actual user data .

Prior to 12c, the metadata that describes Oracle system and the metadata that describes user schema were stored collectively in a set of tables that are referred to as data dictionary. This data dictionary tables were stored in dedicated tablespace namely System and Sysaux.

Initially when you create the database, the data dictionary tables only have Oracle system related metadata. Later on when user creates its own data, the data dictionary tables contain user and system metadata. And the user tablespace contains only user data.

Now, if you want to transport a user tablespace from one database to another, you can simply copy the data files containing the tablespace from database location to another . However, you will have to go through the data dictionary view to find out user metadata information and then transport it. If the user metadata information was stored seperately from the Oracle sytem metadata, than you could simply copy user's tablespace metadata from one location to another to transport a tablespace.

Hence , in 12c the data dictionary tables got horizontally partitioned; one partition containing Oracle System metadata is stored in CDB$root container and another partition container user data related metadata is stored in the PDB. Now, transporting a tablespace from one database to another means copy the data files as well as files containing the metadata information of user schema. The user related metadata is stored in SYSTEM and SYSAUX tablespaces of PDB and the Oracle metadata is stored in SYSTEM and SYSAUX tablespaces of CDB$root

In other words, each data dictionary table now occurs twice, once for Oracle System and once for the user schema. Every query against a data dictionary table is now a union between the two occurrences of that table.

The primary physical difference between CDB’s and non-CDB is the system data files. A non-CDB has only one set of system data files. In contrast the root and each PDB in a CDB has its own SYSTEM and SYSAUX tablespaces which has its own complete set of dictionary tables describing the objects in itself.

There are two ways for provisioning new PDBs in multitenant architecture. One is by cloining and another is plug in an previously unplugged PDB. You clone the PDB when you want an extra copy of that PDB and you plug in a PDB when you want to move the PDB from one CDB to another. When you create a new PDB with out explicitly stating the source PDB from which you want to clone, than Oracle creates the new PDB by cloning that PDB from pdb$seed container. In other words, creation of a PDB is internally a clone operation.

These operations are done using SQL commands. which can be executed from any client machine and, hence, access privileges to the server machine are not needed. In addition, the SQL commands can be run in parallel thereby reducing execution times. They can also be programmed in PL/SQL to automate the process of provisioning and cloning.

Thursday, January 17, 2013

Steps to create container for Oracle DB installation

Create a vnic named public1

dladm create-vnic public1 -l net0

Create a zone.template file as under:

create -b

set zonepath=/oraclePool/zones/test

set limitpriv=default,proc_priocntl

set max-shm-memory=50G

add net

set physical=public1

end

Generate sysconfig.xml file for initial zone configuration

There are two ways to generate the sysconfig.xml file

Generate it from scratch by issuing the following command

sysconfig create-profile -o sysconfig.xml

Update the hostname by searching system/identity and ip address by searching network/install on an existing sysconfig.xml file

A typical sysconfig.xml file is as under:

<!DOCTYPE service_bundle SYSTEM "/usr/share/lib/xml/dtd/service_bundle.dtd.1">

<service_bundle type="profile" name="sysconfig">

<property_group type="application" name="root_account">

<propval type="astring" name="password" value="$5$Px97tHB0$X3mcS7MybV7fk

YTB7Z4NEDDwDJDBPEvIA41w.UmbRm."/>

</property_group>

</instance>

</service>

<property_group type="application" name="timezone">

</property_group>

</instance>

</service>

<property_group type="application" name="environment">

</property_group>

</instance>

</service>

<property_group type="application" name="config">

</property_group>

</instance>

</service>

<property_group type="system" name="keymap">

</property_group>

</instance>

</service>

<property_group type="application" name="ttymon">

</property_group>

</instance>

</service>

<property_group type="application" name="netcfg">

</property_group>

</instance>

</service>

<property_group type="application" name="install_ipv4_interface">

</property_group>

<property_group type="application" name="install_ipv6_interface">

</property_group>

</instance>

</service>

<property_group type="application" name="config">

</property_group>

</service>

</service>

<property_group type="application" name="config">

<net_address_list>

<value_node value="130.35.249.41"/>

<value_node value="130.35.249.52"/>

<value_node value="144.20.190.70"/>

</net_address_list>

</property>

<astring_list>

<value_node value="us.oracle.com oraclecorp.com oracle.com"/>

</astring_list>

</property>

</property_group>

</service>

</service_bundle>

Execute the following script to configure, install and boot the zone

zoneName=test
zoneTemplate=zone.template
VNIC=public1
echo "zone template : ${zoneTemplate}

echo "Configuring the zone :${zoneName}
zonecfg -z ${zoneName} -f ${zoneTemplate}
echo "Verify that zone ${zoneName} has been configured"
zoneadm list -icv | grep ${zoneName}

echo " Install the zone : ${zoneName}"
zoneadm -z ${zoneName} install -c /tmp/sysconfig.xml
echo "Verify that zone ${zoneName} has been installed"
zoneadm list -icv | grep ${zoneName}

echo "Wait for a minute before booting the zone"
sleep 60
echo "Boot the zone : ${zoneName} "
zoneadm -z ${zoneName} boot
echo "Verify that zone ${zoneName} is running"
zoneadm list -icv | grep ${zoneName}

Verify if the smf services are up and running on the zone:

Issue : zlogin test ; svcs

If the smf services are not up, login to console for a couple of minutes and check again

zlogin -C test

Verify that zone is up and running

ping <zone-hostname> from global zone
ping <global-hostname> from local zone
Verify network is configured correctly in the local zone
zlogin test
root@dat-zone1:~# dladm show-physroot@dat-zone1:~# dladm show-vnic
LINK                OVER         SPEED MACADDRESS        MACADDRTYPE       VID
public1             ?            1000   2:8:20:af:91:c8   random            0root@dat-zone1:~# ipadm show-addr
ADDROBJ           TYPE     STATE        ADDR
lo0/v4            static   ok           127.0.0.1/8
public1/v4        static   ok           10.129.195.211/24
lo0/v6            static   ok           ::1/128
public1/v6        addrconf ok           fe80::8:20ff:feaf:91c8/10root@dat-zone1:~#

Add asm disk and the directory where the Oracle bits are available to zone and reboot the zone.
Create a file named addtoZone.template

add device
        set match=/dev/rdsk/c2t8d3s2
        set allow-partition=true
        set allow-raw-io=true
end
add fs
   set dir=/installer
set special=/installer
set type=lofs
end


Reconfigure and reboot the zone

zonecfg -z test -f addtoZone.template

zonecfg -z test reboot

Prepare for Oracle installation. Install Oracle and asm

Log on to test container and execute the following script:

asmDisk=/dev/rdsk/c2t8d3s2

groupadd -g 201 dba

groupadd -g 200 oinstall

useradd -g oinstall -G dba -u 200 oracle

mkdir /export/home/oracle

chown -R oracle:oinstall /export/home/oracle

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01

chmod -R 775 /u01

echo "Enter password for Oracle"

passwd oracle

pkg install SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWpool SUNWsprot SUNWtoo SUNWuiu8 SUNWfont-xorg-core SUNWfont-xorg-iso8859-1 SUNWmfrun SUNWxorg-client-programs SUNWxorg-clientlibs SUNWxwfsw pkg://solaris/SUNWxwplt

chown oracle:oinstall ${asmDisk}

chmod 660 ${asmDisk}

Invoke grid installer and db installer to install ASM and Oracle DB.

V2V across servers : From non-global zone on one server to non-global zone on another server

I have a non-global zone, namely db1Zone, on server dat01. The following post explains the steps to migrate this zone to a non-global zone in another server dat02.

Create a archive of db1Zone that needs to be migrated:

zoneadm -z db1Zone shutdown
zoneadm -z db1Zone detach
zonecfg -z db1Zone export > db1Zone.cfg
zfs list -H -o name /zonespool/zones/db1Zone ; zoneroot can be obtained from db1Zone.cfg
zfs snapshot -r zonespool/zones/db1Zone@migr
zfs send -rc zonespool/zones/db1Zone@migr > db1Zone.zfs

Detaching a zone is the first step in moving a zone from one step to anoter. Once the zone is detached,it is left in the configured state. If you try to install or clone to a configured zone that has been detached, you will receive an error message and install or clone process will fail.

Ensure that target server, dat02, is at the same OS patch level as source server, dat01.

Create the zone named db1Zone on target server

Copy db1Zone.cfg and db1Zone.zfs files to dat02 server
Make any required changes to db1Zone.cfg file. The network information will remain the same. Update any disk related info
Create the zone issuing the following command
zonecfg -z db1Zone -f db1Zone.cfg
Attach the source zone to this zone

zoneadm -z db1Zone attach -U -a /export/home/oracle/db1Zone.zfs

Additional info:

http://docs.oracle.com/cd/E23824_01/html/821-1460/gcgnc.html#scrolltoc

Errors

ERROR: Argument db1Zone.zfs is not a regular file

root@dat02:/export/home/oracle# zoneadm -z db2Zone attach -a db1Zone.zfs
ERROR: Argument db1Zone.zfs is not a regular file

Specify the complete path of the archive file. If the problem still exist, verify that target system is on the same OS patch level as the source system.

cannot receive: stream has unsupported feature, feature flags = 0

If the log file has the following info:

Installing: This may take several minutes...
[Tue Jun 5 12:01:14 PDT 2012] gzcat /tmp/db1Zone.zfs.gz | zfs receive -F -u -x zoned rpool/zonespool/zones/db1Zone/installtmp/ds
cannot receive: stream has unsupported feature, feature flags = 0
[Tue Jun 5 12:01:14 PDT 2012] ERROR: Error: Command <zfs receive -F -u -x zoned rpool/zonespool/zones/db1Zone/installtmp/ds> exited with status 1

Verify that the target system is on the same OS patch level as the source system.

Tuesday, October 7, 2014

Creation of CDB

Getting started with AWR

Wednesday, January 15, 2014

Pluggable Database Concepts

Wednesday, January 8, 2014

How to create “Cold” RMAN Backup

Wednesday, July 10, 2013

Pluggable Database : An Introduction

What is PDB:

Business challenges that PDB address:

Introduction to Multitenancy architecture:

Thursday, January 17, 2013

Steps to create container for Oracle DB installation

V2V across servers : From non-global zone on one server to non-global zone on another server

Ritu's Weblog

Labels

Search This Blog

Followers

Blog Archive

About Me