Tag Archives: CREATE

Sharding Database in Oracle

Leave a reply

Sharding Database in Oracle

Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in such a configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB). Oracle Sharding is implemented based on the Oracle Database partitioning feature.

SHARDED DATABASE:
Database is host independently with all resources like CPU, disk, memory as standalone server. This database is called shared.
All together make a single logical database called sharded database.
SHAREDED TABLE:
Splitting a table across shards so that each shard contains the table with the same columns but a different subset of rows.
A table split up in this manner is also known as a sharded table.
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Note: Oracle Database supports scaling up to 1000 shards.

Terms Used in Shard Environment

Sharded database (SDB) – A SDB is collection of shards represent as a single logical Oracle Database.
Shards – independent physical Oracle databases
Global service – database services that provide access to data in an SDB
Shard catalog – Stored SDB configuration data and manage centralized of sharded database. shard catalog also contains the master copy of all duplicated tables in an SDB. The shard catalog uses materialized views to automatically replicate changes to duplicated tables in all shards.
Shard directors – network listeners that enable high performance connection routing based on a sharding key.
Connection pools – at runtime, act as shard directors by routing database requests across pooled connections
Management interfaces – GDSCTL (command-line utility) and Oracle Enterprise Manager (GUI)

Defining and creating process of Objects in Shared

Shared table
A sharded table is a table that is partitioned into smaller and more manageable pieces among multiple databases, called shards.

Example
creates a sharded table having horizontally partitioning the table across shards based on sharding key cust_id:


CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, region VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET ts1;

Note: Global index is not supported in Shared.
Oracle Sharding creates and manages tablespaces as a unit called a tablespace set.
The PARTITIONS AUTO clause specifies that the number of partitions should be automatically determined.

CHUNK
The unit of data migration between shards is a chunk. A chunk is a set of tablespaces that store corresponding partitions of all tables in a table family.

Table Family
parent-child relationship between database tables with a referential constraint (foreign key). A set of such tables is referred to as a table family. A
table in a table family that has no parent is called the root table.
In this example customer table is parent and order table is child

CREATE SHARDED TABLE Customers
( CustNo NUMBER NOT NULL
, Name VARCHAR2(50)
, Address VARCHAR2(250)
, region VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
)
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;
CREATE SHARDED TABLE Orders
( OrderNo NUMBER
, CustNo NUMBER NOT NULL
, OrderDate DATE
)
PARENT Customers
PARTITION BY CONSISTENT HASH (CustNo)
PARTITIONS AUTO
TABLESPACE SET ts1
;

Duplicate Table
A table with the same contents in each shard is called a duplicated table. It simple presents on all the database(shards)
Oracle Sharding synchronizes the contents of duplicated tables using Materialized View Replication. A duplicated table on each shard is represented by a materialized view.
Note: refresh frequency of all duplicated tables is controlled by the database initialization parameter SHRD_DUPL_TABLE_REFRESH_RATE. Default value is 60 seconds

CREATE DUPLICATED TABLE Products
( StockNo NUMBER PRIMARY KEY
, Description VARCHAR2(20)
, Price NUMBER(6,2))
);

Advertisements

Manages PDB databases on Oracle RAC Environment

Leave a reply

Manages PDB databases on RAC instances

Container database is started then all PDB’s databases are in mounted state. If we tried to connect with PDB database then we got the following error:
ORA-01033: Oracle initialization or Shutdown in progress.
So we need to open the database in all RAC instances.

Open and Close the PDB database in RAC Environment

Check the status of all PDB

select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 MOUNTED
1 PDB2 MOUNTED
2 PDB2 MOUNTED

Open specific pluggable database on current instance which is connected:

alter pluggable database PDB2 open;
Verify:
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 MOUNTED
1 PDB2 READ WRITE
2 PDB2 MOUNTED

Open specific pluggable database on specific instance:

alter pluggable database pdb2 open instances = ( 'node2');
Verify:
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 MOUNTED
1 PDB2 READ WRITE
2 PDB2 READ WRITE

Open all pluggable database on all instances:

alter pluggable database all open instances=ALL;
Verify:
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 READ WRITE
2 PDB1 READ WRITE
1 PDB2 READ WRITE
2 PDB2 READ WRITE

Close the PDB database on current instance which is connected:

alter pluggable database PDB1 close;
Verify:
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 READ WRITE
1 PDB2 READ WRITE
2 PDB2 READ WRITE

Close the specific pdb on specific instance:

alter pluggable database pdb1 close instances=('node2');
Verify:
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 MOUNTED
1 PDB2 READ WRITE
2 PDB2 READ WRITE

close all the pluggable database on all instances:

alter pluggable database all close instances = ALL;
select inst_id,name,open_mode from gv$pdbs order by name,inst_id;
INST_ID NAME OPEN_MODE
------- -------- ---------
1 PDB$SEED READ ONLY
2 PDB$SEED READ ONLY
1 PDB1 MOUNTED
2 PDB1 MOUNTED
1 PDB2 MOUNTED
2 PDB2 MOUNTED

Relocate the PDB database by close on current instance and open on other:

alter pluggable database PDB2 close relocate to 'node2';

Manage Service for PDB database in RAC Environment
It is dynamic service we are created, default service is the service which is created when pdb database is created, it name as same pdb name or pdb name plus domain name.

Create a Service for PDB database
Note: you create it with policy automatic then you donot need to start the pdb database everytime when instance started

srvctl add service -db contdb -pdb pdb1 -service pdb1srv -serverpool sp1 -cardinality singleton -policy manual

serctl add service -db contdb -pdb pdb2 -service pdb2srv -serverpool sp1 -cardinality uniform -policy automatic
automatic is default

Start the service of PDB database:

srvctl start service -db contdb -service pdb1srv

Check the Status of the Service

srvctl status service -db contdb
Service pdb1srv is running on nodes: NODE1
Service pdb2srv is running on nodes: NODE1,NODE2

Check Service from sqlplus

select name, con_id from cdb_services;

Remove the service

srvctl remove service -db contdb -service pdb1srv

Manages Services in RAC Environment

Leave a reply

Managed Services in RAC Oracle 12c

Services is used for managed the workload in Oracle database and simplified the way of connection with application and group of application.
Divide the workload with help of services like OLTP , Batch , Reporting to simplified the bottleneck when issued occurred.
srvctl command is used to managed the services.Services integrated with resource manager
Services also control parallel operation in rac , by default all instance is used for parallel operations in RAC.

Characteristics for service
Service Name
Service Edition
Service management policy – Manual when service not start when database is start , Automatic when database start service automatic start
Database role
Instance preference – Specify the instance preference
Server pool Assignment
Load balancing advisory – Run time connection load balancing to choose the
Connection load balancing goal –

Note: Default database name service identified by db_unique_name if not set then db_name or pdb_name is as default service.

Creating and Managing the Services in RAC Oracle 12c

Create a new Service for ORCL database.

srvctl add service -db orcl -service GL -preferred NODE1,NODE2,NODE3

Check the Configuration of the Service created.

srvclt config service -db orcl -service GL

Create a service with available and preferred options

srvctl add service -db orcl -service AL -preferred NODE1,NODE2 -available NODE3

Create a service “ETL” with server pool with singleton option so that it will work only on one instance. Policy is manual means it doesnot start or stop automatically adminstrator do it manually to stop and start service

srvctl add service -db orcl -service ETL -serverpool SP1 -cardinality SINGLTON -policy MANUAL

Create a service “TRAN” with uniform cardinality so that it run all the node present in serverpool. Choose policy to automatic so it automatic start or stop with database

srvctl add service -db orcl -service TRAN -serverpool SP2 -cardinality UNIFORM -policy AUTOMATIC

Start the service in RAC

srvctl start service -db orcl -service GL
srvctl start service -db orcl -service AL

Check the status of the service in RAC

srvctl status service -db orcl -service AL
Service AL is running on instance(s) node1

Stop the Service in RAC

srvctl stop service -db orcl -service AL
-- Stop service on particular instance only
srvctl stop service -db orcl -service GL -instance NODE1

Disable the service in RAC environment

srvctl disable service -db orcl -service AL -instance NODE1

Changing the preferred and available instance

srvctl modify service -db orcl -service AL -modifyconfig -perferred NODE1, NODE2 -available NODE3

Relocate the service from NOde1 to Node3

srvctl relocate service -db orcl -service AL -oldinst NODE1 -newinst Node3

Manage Service for PDB database in RAC Environment
It is dynamic service we are created, default service is the service which is created when pdb database is created, it name as same pdb name or pdb name plus domain name.

Create a Service for PDB database
Note: you create it with policy automatic then you donot need to start the pdb database everytime when instance started

srvctl add service -db contdb -pdb pdb1 -service pdb1srv -serverpool sp1 -cardinality singleton -policy manual

serctl add service -db contdb -pdb pdb2 -service pdb2srv -serverpool sp1 -cardinality uniform -policy automatic
automatic is default

Start the service of PDB database:

srvctl start service -db contdb -service pdb1srv

Check the Status of the Service

srvctl status service -db contdb
Service pdb1srv is running on nodes: NODE1
Service pdb2srv is running on nodes: NODE1,NODE2

Check Service from sqlplus

select name, con_id from cdb_services;

Remove the service

srvctl remove service -db contdb -service pdb1srv

Restricted Service Registration
Restricted Service Registration is only for local ip address/ network. (subnet address is same). valid node checking is enabled by default.

Check the default setting for SCAN Listener when configured

srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

Restricted Service to registered nodes on scan listener:

srvctl modify scan_listener -update -invitednodes host1,host2,host3

Restricted Service with Registered subnet on Scan Listener:

srvclt modify scan_listener -update -invitedsubnets 192.168.0.0/24,10.10.10.0/24

Check again the Scan listener setting:

#srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

Note: when you add register nodes and subnet then listener.ora file is updated with parameters REGISTRATION_INVITED_NODES_LISTENER_SCAN

Automatic Storage Management (ASM) in Oracle

Leave a reply

ASM (Automatic Storage Management) in Oracle

ASM is used by Oracle to manage the diskgroups and diskgroups use to store to the data files. It will help to manage the disk groups and
provide the feature of ASM for striping and mirroring to provide balanced performance and security to the storage. ASM is managed with ASM instance.
ASM instance is like the oracle database instance, it only allocate memory structure in RAM area it does not have its own database,it will use information from its PFILE or SPFILE.
When you add or remove disks from a disk group, Oracle ASM automatically redistributes the data.

Few points for configure ASM:
1. All ASM disks in a disk group have similar storage performance and availability. If one disk is slow caused I/O bottleneck.
2. ALL ASM disks in a disk group have the same capacity to maintain balance because its distribute data according to capacity.

In 11g, ASM introduced two compatibility attributes that determine the version of the ASM and database software that can use specific disk groups
COMPATIBLE.ASM – minimum version of the ASM software that can access the disk group default setting is 10.1.
COMPATIBLE.RDBMS – minimum COMPATIBLE database initialization parameter setting for any database instance that uses the disk group default setting is 10.1.

Check compatible as following:

SELECT group_number, name, compatibility, database_compatibility FROM v$asm_diskgroup;
SELECT software_version ,compatible_version FROM v$asm_client;

PFILE Parameters:
INSTANCE_TYPE: ASM or RDBMS, let you know instance is of ASM or Database
DB_UNIQUE_VALUE: +ASM ,Global unique name for the database. but in this case it name of automatic storage management
ASM_POWER_LIMIT: value from 1 to 11, used for rebalance operation after storage configuration changes, such as when you add, drop, or resize disks. Default value is 1, it’s represent the speed with which rebalancing operations occur.
ASM_DISKGROUPS: diskgroup1,diskgroup2 List of diskgroups which is mounted automatic at startup. you can set manually after instance startup “ALTER SYSTEM SET ASM_DISKGROUPS = DISKGRP1, DISKGRP2;”
ASM_DISKSTRING: initialization parameter specify its discovery strings like ASM_DISKSTRING = ‘/dev/rdsk/*disk3’, ‘/dev/rdsk/*disk4’ wildcard character is used for discovery disk. Only disks that match one of the strings are discovered.
Default value is NULL, NULL value causes Oracle ASM to search a default path for all disks in the system to which the Oracle ASM instance has read and write access.

Start the ASM instance:
1. You have the pfile for start the asm

create spfile from pfile;

2. Start the ASM in nomount state as database
Note: ASM instance is started

startup nomount

3. For mount the ASM diskgroup which is present in ASM_DISKGROUPS parameter in spfile or pfile.

alter database mount;

Stop the ASM instance:
Shutdown the instance as normal database with shutdown command.

SHUTDOWN
--The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
SHUTDOWN iMMEDIATE
--The ASM instance waits for any SQL transactions to complete then shuts down. It doesn't wait for sessions to exit.

Disks group is configured with CREATE or ALTER DISKGROUP statement,you have following option to choose the redundancy level of diskgroups:
EXTERNAL REDUNDANCY means no mirroring for the disks. They may be protected at hardware level mirroring RAID or customer no need of mirroring
NORMAL REDUNDANCY means two way mirroring, required two failure group for configure disk in normal redundancy mode.
HIGH REDUNDANCY means three-way mirroring, required three failure groups for mirroring the data in high redundancy.

Following are the useful command used in ASM:

Create the diskgroup of EXTERNAL Redundancy

CREATE DISKGROUP diskgroup1 EXTERNAL REDUNDANCY
FAILGROUP failuregroup1 DISK
'/raw/devices/diska1' NAME diska1,
'/raw/devices/diska2' NAME diska2

Create the diskgroup of Normal Redundancy:

CREATE DISKGROUP diskgroup2 NORMAL REDUNDANCY
FAILGROUP failuregroup1 DISK
'/raw/devices/diska1' NAME diska1,
'/raw/devices/diska2' NAME diska2
FAILGROUP failuregroup2 DISK
'/raw/devices/diskb1' NAME diskb1,
'/raw/devices/diskb2' NAME diskb2;

Create the diskgroup of High Redundancy

CREATE DISKGROUP diskgroup3 high REDUNDANCY
FAILGROUP failuregroup1 DISK
'/raw/devices/diska1' NAME diska1,
'/raw/devices/diska2' NAME diska2
FAILGROUP failuregroup2 DISK
'/raw/devices/diskb1' NAME diskb1,
'/raw/devices/diskb2' NAME diskb2;
FAILGROUP failuregroup3 DISK
'/raw/devices/diskc1' NAME diskc1,
'/raw/devices/diskc2' NAME diskc2;

Check the diskgroup present

SELECT name, failgroup FROM v$asm_disk;
NAME FAILGROUP
-------------- ---------------
diska1 failuregroup1
diska2 failuregroup1

Mount or dismount the diskgroup with sqlplus

ALTER DISKGROUP diskgroup1 DISMOUNT;
ALTER DISKGROUP diskgroup1 MOUNT;
ALTER DISKGROUP diskgroup1 MOUNT FORCE;
-- For all disk mounted
ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;

Drop the disk group

DROP DISKGROUP diskgroup1 INCLUDING CONTENTS;

Add or Remove the datafile from diskgroup of ASM
Note: During adding or removing please note all disk size should be equal and rebalance operation is performed may caused performance issue at that time.

-- Add disks in diskgroup
ALTER DISKGROUP diskgroup2 ADD DISK
'/raw/devices/diska3',
'/raw/devices/diska4';

— Drop a disk from diskgroup
ALTER DISKGROUP diskgroup2 DROP DISK diska1;

Manually rebalance operation after add or remove disk
Note: rebalance operation required PGA memory for extent relocation

-- added the disk in diskgroup with power 5 but its not return back until rebalance operation completed.
ALTER DISKGROUP data1 ADD DISK '/devices/diskd10' REBALANCE POWER 5 WAIT;

— Rebalance with power 10 but it return and worked in backgroup for check query v$ASM_OPERATION view.
ALTER DISKGROUP diskgroup2 REBALANCE MODIFY POWER 10;
Note: By default, the ALTER DISKGROUP statement does not wait until the operation is complete before returning. Query the V$ASM_OPERATION view to monitor the status of this operation.

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

GROUP_NUMBER PASS STAT
———— ——— —-
2 RESYNC WAIT
2 REBALANCE WAIT
2 COMPACT WAIT

 

Estimate the amount of work for a rebalance operation before drop or create disk
Note: Est_work column provide the information of allocation unit that is moved during rebalance operations.

-- Explain clause help for estimate
EXPLAIN WORK FOR ALTER DISKGROUP diskgroup2 DROP DISK diska1;
-- check the view for estimate:
SELECT est_work FROM V$ASM_ESTIMATE;

— check online
EXPLAIN WORK SET STATEMENT_ID=’online’ FOR ALTER DISKGROUP diskgroup2
ONLINE disk diska1;

SELECT est_work FROM V$ASM_ESTIMATE WHERE STATEMENT_ID=’online’;

Resize the disk or diskgroup all disk

-- Resize a specific disk of a diskgroup
ALTER DISKGROUP diskgroup1 RESIZE DISK diska1 SIZE 10G;

— Resize all disks in a failure group.
ALTER DISKGROUP diskgroup1 RESIZE DISKS IN FAILGROUP failuregroup1 SIZE 10G;

— Resize all disks in a disk group may it present more than one failure group it will do for all.
ALTER DISKGROUP diskgroup1 RESIZE ALL SIZE 10G;

Check the consistency or integrity of the Diskgroup

ALTER DISKGROUP data1 CHECK ALL;

ASM Disk Information or view ASM_DISK
FORMER Disk: disk assigned to diskgroup then dropped from diskgroup is former disk
CANDIDATE Disk: discovered disk but not assigned to diskgroup is candidate disk
MEMBER Disk: Disk that belong to a diskgroup is member disk

SQL> SELECT name, header_status, path FROM V$ASM_DISK
WHERE path LIKE '/devices/disk0%';

NAME      HEADER_STATUS PATH
--------- ------------- ---------------------
          FORMER        /raw/devices/disk01
          FORMER        /raw/devices/disk02
          CANDIDATE     /raw/devices/disk03
DISK04    MEMBER        /raw/devices/disk04
DISK05    MEMBER        /raw/devices/disk05
DISK06    MEMBER        /raw/devices/disk06

 

Check the size of diskgroup

SELECT name, type, total_mb, free_mb, required_mirror_free_mb,
usable_file_mb FROM V$ASM_DISKGROUP;

Note: Column descriptions
TOTAL_MB column is the total usable capacity of a disk group in megabytes.
FREE_MB column is the unused capacity of the disk group in megabytes
REQUIRED_MIRROR_FREE_MB column is the amount of space that need to be available in a diskgroup to restore full redundancy after the failure tolerated without adding additional storage.
USABLE_FILE_MB column is the amount of free space used for adjusted mirroring for new files to restore redundancy after a disk failure.

Application Containers in Oracle 12c Database

Leave a reply

Application Containers in Oracle Database 12c Release 2 (12.2)

An Application Containers is used for simplify the maintenance of the different application version. It easy to handle the different version of application changes in SQL with application container at database level.

Suppose one PDB1 database is working on Application version 10 and other PDB2 is want to work with latest version 12 of application. You can easily managed with application container feature of Oracle to sync the PDB2 with latest version 12 and donot sync the PDB1 database so it will work on old version of application.

An Application Container is composed by One Application Root having application metadata, link with zero or more Application Pluggable Databases,
zero or one Application Seed and zero or more Applications.

An Application Root is a Pluggable Database where the “Applications” are installed and maintained the application master data which is minimum required to run the application particular version.
On new version released of application has new master data which is maintained in new version. Later they can sync the PDB as customer wants to move to new version of application.
There may be only one Application Root per Application Container.
 
Creating an Application Root Container

1. For create an Application Root you have to be connected with sysdba privileges user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
-- Create the application root container
SQL> create pluggable database ROOTAPP as application container admin user sysappadmin identified by sysappadmin123;
Pluggable database created.
-- Open the application root container
SQL> alter pluggable database ROOTAPP open;
Pluggable database altered.
-- Check the application root container is created
SQL> select con_id, name , open_mode, application_root from v$pdbs where application_root='YES';
CON_ID NAME OPEN_MODE APP
------- -------- ---------- ---
5 ROOTAPP READ WRITE YES

Creating an Application Pluggable Database
Application pluggable database is created when you are connected with application root container. then created PDB in this is called application pluggable database.

SQL> alter session set container=ROOTAPP;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ROOTAPP
-- Create a pluggable database
SQL> create pluggable database PDB1APP admin user pdb1appadmin identified by pdb1appadmin123;
Pluggable database created.
--Opening the Application PDB
SQL> alter pluggable database PDB1APP open;
Pluggable database altered.
--Check the Application PDBs were created
SQL> select con_id, name , open_mode, application_root, application_pdb from v$pdbs;
CON_ID NAME OPEN_MODE APPLICATION_ROOT APPLICATION_PDB
-------- -------- ---------- ---------------- ---------------
5 ROOTAPP READ WRITE YES NO
6 PDB1APP READ WRITE NO YES

Create an application in Root Container

-- Check the current container
SQL> show con_name
CON_NAME
---------------
APPROOT

--Create a sales application on app container
alter pluggable database application SalesApp begin install '1.0';
Pluggable database altered.

-- Create a separate tablespace for application sales
CREATE TABLESPACE sales_app_ts DATAFILE 'location' SIZE 100M AUTOEXTEND ON NEXT 10M;
Tablespace created.

-- create user for sales application
create user sales identified by password1 default tablespace sales_app_ts;

-- Grant permission
GRANT CREATE SESSION, CREATE TABLE TO sales;
-- Create table for version 1.0
create table sales.tran SHARING=DATA (
id NUMBER,
name varchar2(100),
description VARCHAR2(100),
CONSTRAINT id_pk PRIMARY KEY (id)
);

-- insert data into the tran table
Insert into sales.tran values (1, 'shoes', ' Black color');

-- End the salesapp process
alter pluggable database application SalesApp end install;

-- Check the application is created
COLUMN app_name FORMAT A10
COLUMN app_version FORMAT A10
SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'SALESAPP';
APP_NAME APP_VERSIO APP_STATUS
---------- ---------- ------------
SALESAPP 1.0 NORMAL

SYNC the PDB database with application created

-- Switch to the application pdb database
ALTER SESSION SET container = PDB1APP;
SHOW CON_NAME
CON_NAME
----------------
PDB1APP

-- try to check the salesapp object
SQL> select * from sales.tran;
select * from sales.tran
*
ERROR at line 1:
ORA-00942: table or view does not exist

-- Sync the pdb with application root container
SQL> alter pluggable database application SALESAPP sync;
Pluggable database altered.

-- Again select the table
SQL> select name from sales.tran;
NAME
-------
SHOES

-- you can check the status from DBA_APP_PDB_STATUS view
ALTER SESSION SET container = PDB1APP;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10
SELECT con_uid,
app_name,
app_version,
app_status
FROM dba_app_pdb_status WHERE app_name = 'SALESAPP';

CON_UID    APP_NAME    APP_VERSIO APP_STATUS
---------- ----------- ---------- ------------
4291055883 SALESAPP    1.0        NORMAL

Upgrade the application present in application root container

-- connect with root container for modify the application
ALTER SESSION SET container = APPROOT;
-- Fire the command for upgrade the application
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UPGRADE '1.0' TO '2.0';
-- Suppose create one more table
create table sales.region ( name varchar(100));
-- insert data into region table
insert into sales.region ('NORTH');
-- end the upgrade process
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UPGRADE;

For use the upgraded application in Pluggable database need to sync the PDB database again

-- Connect with pdb of app container
alter session set container=PDB1APP;

-- sync with upgraded version
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP SYNC;

Uninstall the application from root application container

-- first start the uninstall command
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UNINSTALL;
-- Drop the created user
drop user sales cascade;
-- Drop the tablespace assoicated with user
Drop tablespace INCLUDING CONTENTS AND DATAFILES;
-- End the uninstall command:
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UNINSTALL;

Note: for removed from application pdb you need to again sync the pdb database

How to Create, Alter AND Drop a table

Leave a reply

How to Create, Alter AND Drop a table

CREATE TABLE: A table is basic unit of storage. It is composed of rows and columns. To create a table we will name the table and the columns of the table. We follow the rules to name tables and columns:
• It must begin with a letter and can be up to 30 characters long.
• It must not be duplicate and not any reserved word.

SYNTAX to create a table is:

CREATE TABLE tablename (column_name1 datatype (size), column_name2 datatype (size) …);

Example:
CREATE TABLE studen(rollno number (4), name varchar2 (15));

ALTER TABLE: After creating a table one may have need to change the table either by add new columns or by modify existing columns. One can do so by using alter table command.

SYNTAX to add a column is

ALTER TABLE tablename ADD(col1 varchar(10),col2 number(10));

SYNTAX to modify a column is:

ALTER TABLE tablename MODIFY(col1 datatype,col2 datatype);

DROP TABLE: To remove the definition of oracle table, the drop table statement is used.
Drop command is used to remove the structure of table so no rollback possible in this case.

SYNTAX to drop table is

DROP TABLE tablename

How to create Service Request in Oracle Support

Leave a reply

Create a Service Request in Oracle Support

In many cases we need to open service request with oracle support. We need to download oracle patches and bug fixes then we need to create Oracle support ticket. Oracle support team provide solution to us regarding problems like ORA-7445 error in alert log, any patch download etc.

Following are the steps involved in creating service request in Oracle:

1. Open the Internet explorer and go to the following url:

support.oracle.com

2. It will open the welcome page of the Site and click on sign button at right side.

Signin.JPG

3. It will redirect to new page for Username and Password.
Please enter your username and password.

Username&password.JPG

4. Home page for Oracle Support Site is open having different tabs on it. Select the Service request tab on it.

Homepage.JPG

5. Service Request page will show you the list of all open SR.
For create new service request, click on Create SR button.

CreateSR

6. Then new page will open as below:
Problem Summary: Write the problem Summary
Problem Description: Write about the detail of problem or error you faced
In Sofware Tab:
Product: Select the database enterprise or standard.
Product version: Oracle database version for which you faced problem 11.2.0.4
Database Platform: choose operating system version.
Problem Type: Choose the problem type like patching, ORA error

WriteProblem.JPG

7. After filling click next, go to Solution tab. Select option if you got any document otherwise press continue and next is enabled at right top and click next.

Solution.JPG

8. Go to More Detail tab, In this if you have log files and error snapshot. Please attached it and go to next button.

Moredetails.JPG

9. Choose the severity of ticket and give your contact details.

Contact.JPG

10. Press Next to complete the process and it will show your ticket in Service Request Tab:

FinalListSR.JPG