Osama mustafa – Page 16 – Technology Geek

Since Pluggable Database is New Topic to post about , I Cannot post Everything in One Post so i decide to do it as tutorial and this one part (3) , every Topics uploaded Here on my blog or on my Account on Shareslide here.

In This topic :

How to unplug database.
Plugging Database to another Container Database.

Let’s Start :

SQL> select name, con_id from v$active_services ;

NAME CON_ID
——————————— ———-
new2 4
new 3
db12cXDB 1
db12c 1
SYS$BACKGROUND 1
SYS$USERS 1

as you see i already create two pluggable database new,new2 and now :

SQL> alter pluggable database new close immediate ;

Pluggable database altered.

SQL> alter pluggable database new2 close immediate ;

Pluggable database altered.

Unplug database included with xml file :

SQL> alter pluggable database new unplug into ‘/u01/app/oracle/oradata/new.xml’;Pluggable database altered.

SQL> alter pluggable database new2 unplug into ‘/u01/app/oracle/oradata/new_2.xml’;Pluggable database altered.

Drop Database :

SQL> drop pluggable database new keep datafiles ;Pluggable database dropped.

SQL> drop pluggable database new2 keep datafiles ;Pluggable database dropped.

make sure you drop database :

SQL> select pdb_name, status from cdb_pdbs ;

PDB_NAME STATUS
———– ————-
PDB$SEED NORMAL

Before Plug Database to any Container you need to make sure from compatibility , there’s PL/SQL code written by Oracle to check compatibility.

SQL > DECLARE
compatible BOOLEAN := FALSE;
BEGIN
compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => ‘/u01/app/oracle/oradata/new.xml’);
if compatible then
DBMS_OUTPUT.PUT_LINE(‘Is pluggable PDB1 compatible? YES’);
else DBMS_OUTPUT.PUT_LINE(‘Is pluggable PDB1 compatible? NO’);
end if;
END;
/
Is pluggable PDB1 compatible? YES
PL/SQL procedure successfully completed.

The Same will be for new2.xml

Now Let’s Create Database Using With Two Way :

SQL> create pluggable database newdb_plug using ‘/u01/app/oracle/oradata/new.xml’ nocopy tempfile reuse ;

Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME STATUS
—————— ————-
NEWDB_PLUG NEW
PDB$SEED NORMAL

Another method to plug database :

SQL> create pluggable database new_plug_copy using ‘/u01/app/oracle/oradata/new_2.xml’
2 copy
3 FILE_NAME_CONVERT=(‘/u01/app/oracle/oradata/New2′,’/u01/app/oracle/oradata/new_plug_copy’);

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
NEWDB_PLUG MOUNTED
NEW_PLUG_COPY MOUNTED

Therefore

Copy Clause :

if you want the files listed in the XML file to be copied to the new location and used for the new PDB.

Nocopy Clause

if you want the files for the PDB to remain in their current locations.

But what if i want to move all datafiles and create new pluggable database,oracle 12c provide you with new clause to do this which is “move” check below :

SQL> create pluggable database new_plug_move using ‘/u01/app/oracle/oradata/new_2.xml’
2 move
3 FILE_NAME_CONVERT=(‘/u01/app/oracle/oradata/New2′,’/u01/app/oracle/oradata/move’);
create pluggable database new_plug_move using ‘/u01/app/oracle/oradata/new_2.xml’
*
ERROR at line 1:
ORA-65122: Pluggable database GUID conflicts with the GUID of an existing container.

The above error is normal because i already create pluggable database using new_2.xml to solve it

SQL> create pluggable database new_plug_move as clone using ‘/u01/app/oracle/oradata/new_2.xml’
2 move
3 FILE_NAME_CONVERT=(‘/u01/app/oracle/oradata/New2′,’/u01/app/oracle/oradata/move’);

Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME STATUS

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

NEWDB_PLUG NEW

PDB$SEED NORMAL

NEW_PLUG_COPY NEW

NEW_PLUG_MOVE NEW

You can open any database now and work on them.
This article uploaded to Slide Share here.

Reference
1-Oracle Documentation here

Thank you
Osama Mustafa

Pluggable Database Tutorial Part 1 (12c)

Posted on July 4, 2013December 23, 2018 by Osama Mustafa in Database

[oracle@test12c Desktop]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 3 19:05:10 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> show con_name

CON_NAME
——————-
CDB$ROOT

Where
CON_NAME : Displays the name of the Container to which you are connected when connected to a Consolidated Database. For non-consolidated database, it will return “Non Consolidated”.

SQL> show con_id

CON_ID
————
1

Where
CON_ID : Displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0.

Now I want to check how Name for my my pluggable database , while installation i chosen five container with prefix db_

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
db_1 3
db_2 4
db_3 5
db_4 6
db_5 7
9 rows selected.

Listener will look like the below :

Example how to connect Container , In my case i didn’t create service in tnsnames.ora i am using easy connect :

SQL> conn sys/sys@localhost:1521/db_1 as sysdba
Connected.

SQL> show con_name

CON_NAME
—————-
DB_1

SQL> show con_id

CON_ID
————
3

Work On Pluggable Database :

After create Container and enable pluggable database , we need to add new one since container empty.

Under /u01/app/oracle/oradata ,create new folder

[oracle@test12c db12c]$ mkdir test
[oracle@test12c db12c]$ chmod -R 775 test

Sqlplus / as sysdba

SQL > create pluggable database TEST admin user admin identified by admin
file_name_convert= (‘/u01/app/oracle/oradata/db12c/pdbseed/’,’/u01/app/oracle/oradata/db12c/test/’);

Pluggable database created.

SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME Status
———————– ————
PDB$SEED NORMAL
TEST NEW

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID
—————————————————————- ———-
SYS$BACKGROUND 1
SYS$USERS 1
db12c 1
db12cXDB 1
TEST 3

Now Con_id=3 , Most of Oracle Data Dictionary contains new_column called con_id , to check datafile related to new pluaggable database :

SQL> select name from v$datafile where con_id=3 ;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf

Manage Oracle Container and Pluggable Database :

If you need to shutdown container, it will not be different as before :

SQL> show con_name
CON_NAME
——————————
CDB$ROOT

SQL> shutdown ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup ;
ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.

Check status for PDB :

SQL>select name, open_mode from v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

If you the above Status for Test Database you will see it as mounted state which mean we cannot create anything yet on database, Let’s Open it

SQL> alter pluggable database TEST open ;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST READ WRITE

The Same for close Option

SQL> alter pluggable database TEST close immediate ;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs ;NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TEST MOUNTED

Now you can open/close all pluggable database :

SQL > Alter pluggable database all Open;
SQL > Alter pluggable database all close ;

SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=3 ;

TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3

to get data file

SQL> select file_name, con_id from cdb_data_files where con_id=3 ;NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/gls/system01.dbf
/u01/app/oracle/oradata/db12c/gls/sysaux01.dbf

SQL> select file_name, con_id from cdb_temp_files where con_id=3;

FILE_NAME CON_ID
——————————————
/u01/app/oracle/oradata/db12c/gls/pdbseed_temp01.dbf 3

If you do the below query to create tablespace, it will not be created under TEST database, therefore it will be created on root :

SQL > create tablespace cdata datafile ‘/u01/app/oracle/oradata/db12c/gls/test.dbf’ SIZE 30M;

SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSTEM 2
TEMP 2
SYSAUX 2
SYSTEM 3
SYSAUX 3
TEMP 3
12 rows selected.

Same for temp tablespace :

SQL> create temporary tablespace root_temp tempfile ‘/u01/app/oracle/oradata/db12c/temp_01.dbf’ SIZE 30M;

If you need to create Tablespace in pluggable database follow the below , you have to options

connect to pluggable database in our case test using tnsnames.ora or easy connect

connect sys/sys@localhost:1521/test

alter session command line

alter session set container=

SQL> alter session set container=TEST;Session altered.

SQL> create tablespace pdb_test datafile ‘/u01/app/oracle/oradata/db12c/TEST/test_pdb.dbf’ SIZE 20M;
Tablespace created.

SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;TABLESPACE_NAME CON_ID
—————————— ———-
SYSTEM 3
SYSAUX 3
TEMP 3
PDB_TEST 3

Same for temporary tablespace, next post i will provide another manage for pluggable database.

Also I Upload this tutorial On SlideShare Here

Thank you

Osama mustafa

Reference :
1- oracle Documentation Here
2- Oracle Documentation Here

Upgrade 11.2.0.3 to 12c Part 1

Posted on July 2, 2013December 23, 2018 by Osama Mustafa in Database

In this article i am describing Step by step how to upgrade database using dbua , in part 2 i will use the manual upgrade process.

The Link For Document : Here

Thank you
Osama mustafa

TEMP UNDO TABLESPACE : Another 12C Feature

Posted on July 1, 2013December 23, 2018 by Osama Mustafa in Database

Testing is not finished yet , Everyday working on database 12C , Lot Of New Features , TEMP_UNDO_TABLESPACE one of these new features, In older Oracle Versions Temp Tables are stored in Undo Tablespace, But now with #DB12c you can enable Parameter TEMP_UNDO_TABLESPACE by Default this parameter set to FALSE But in Dataguard 12c this parameter is set to TRUE by default.

The Benefit of Using Temp Undo Tablespace :

reduce the amount of using undo tablespace
in this case performance improved because less data will be written on redolog.
Temp Undo Tablespace support DML

SQL> show parameter TEMP_UNDO_ENABLED;

NAME TYPE VALUE
———————————— ———– ——-
temp_undo_enabled boolean FALSE

You Don’t have to reset Database to Enable this Parameter because it’s working on Session level

SQL> alter session set temp_undo_enabled=TRUE ;
Session altered.

also you set this parameter on database level :

SQL> alter system set temp_undo_enabled=true;
System altered.

SQL> show parameter temp_undo_enabled ;

NAME TYPE VALUE
———————————— ———– ——-
temp_undo_enabled boolean TRUE

View Related to temp undo tablespace :

SQL> desc V$TEMPUNDOSTAT;
Name Null? Type
—————————————– ——– —————————-
BEGIN_TIME DATE
END_TIME DATE
UNDOTSN NUMBER
TXNCOUNT NUMBER
MAXCONCURRENCY NUMBER
MAXQUERYLEN NUMBER
MAXQUERYID VARCHAR2(13)
UNDOBLKCNT NUMBER
EXTCNT NUMBER
USCOUNT NUMBER
SSOLDERRCNT NUMBER
NOSPACEERRCNT NUMBER
CON_ID NUMBER

tiny features : Truncate table in 12c

Posted on June 27, 2013December 23, 2018 by Osama Mustafa in Database

New Features with Database 12c , CASCADE Usually used with Drop or update command (10g,11g) But now with Database 12c , you can use this features with Truncate command

SQL> select * from test ;
no rows selected

SQL> desc test ;

Name Null? Type
—————————————– ——– —————————-
TEST_ID NOT NULL NUMBER
TEST_NAME VARCHAR2(20)

SQL> truncate table test cascade ;
Table truncated.

More Features coming up 🙂

Thank you

Osama mustafa

Another New Features 12c : generated as identity / Sequence Replacement

Posted on June 27, 2013December 23, 2018 by Osama Mustafa in Database

in old version of oracle database if you want to create automatic generated number you have to create sequence and use attribute nextval.

But with oracle database 12c this concept is changed new features add when you create table called generated as identity.

Check the Below Demonstration which explain this new features :

SQL> create table test (test_id number generated as identity , test_name varchar2(20));

SQL> desc test ;
Name Null? Type
—————————————– ——– ————–
TEST_ID NOT NULL NUMBER
TEST_NAME VARCHAR2(20)

SQL> insert into test values (1,’osama’);
insert into test values (1,’osama’)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

The TEST_ID Column will be inserting automatically no need to use in insert command.

SQL> insert into test (TEST_NAME) values ( ‘Jennifer’ );
1 row created.

SQL> select * from test ;
TEST_ID TEST_NAME
———- ——————–
1 Jennifer

Check another example :

SQL> create table test2 (TEST_ID NUMBER generated as identity (start with 1 increment by 1 cache 30 order), TEST_NAME varchar2(20));
Table created.

SQL> insert into test2 (test_name) values (‘JENNIFER’);
SQL> insert into test2 (test_name) values (‘STEVE’);
SQL> insert into test2 (test_name) values (‘USER’);

SQL> select * from test2 ;

TEST_ID TEST_NAME
———- ——————–
1 JENNIFER
2 STEVE
3 USER

Thank you

Osama mustafa

Move Datafiles Online 12c

Posted on June 26, 2013December 23, 2018 by Osama Mustafa in Database

As you know Oracle database 12c released Yesterday and we all still test it and learn what are the new features !!

For the first time in Oracle Database you can move objects online without offline or shutdown database which is very useful and helpful for any DBA’s

Check the example below :

SQL> select file_name from dba_data_files ;

FILE_NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/system01.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf

Let’s move system01.dbf

SQL> alter database move datafile ‘/u01/app/oracle/oradata/db12c/system01.dbf’ to ‘/u01/system.dbf’;

SQL> select File_name from dba_data_files ;
FILE_NAME
——————————————————————————–
/u01/system.dbf
/u01/app/oracle/oradata/db12c/sysaux01.dbf
/u01/app/oracle/oradata/db12c/users01.dbf
/u01/app/oracle/oradata/db12c/undotbs01.dbf

Thank you
Osama mustafa

Oracle Enterprise Manager unable to Start

Posted on June 26, 2013December 23, 2018 by Osama Mustafa in Application

When I checked the log Located $ORACLE_HOME/SID_HOSTNAME/sysman/log

I found the below errors :

app.ContextInitializer contextInitialized.420 – Integration Class not found

and

ERROR main: nmectla_agentctl: Error connecting to

First you need to make sure that the link in emd.properties and ports and configured right and hostname is correct, restrat dbconsole if this not works then check the below

On Host :

oracle@TEST:/u01/app/$ echo $TZ
localtime

oracle@TEST:/u01/app/$ export TZ=Etc/GMT+2

emctl config agent updateTZ

emctl resetTZ agent

Restart Dbconsole

Emctl start dbconsole

Thank you
Osama mustafa