Tuesday, July 30, 2013

Step By Step to Restore RMAN to New Host

Old Topics But Always Good To share , Find Below the Links for Steps to Restore RMAN to New Host

Step by Step Restore RMAN to New Host Here

Thank you
Osama Mustafa

Monday, July 22, 2013

WebLogic Error BEA-141281


This Solutions for this errors :

Change Directory to Domain directory

cd /u01/app/oracle/domains/IDMDomain/servers/AdminServer 
if you have lock you will find folder called  tmp
rm -rf AdminServer.lok

 Thank you
Osama mustafa

Wednesday, July 17, 2013

New Slideshare Document ( EnterPrise manager 12c )

Upload New Documents to my SlideShare Account :

  1. Add target manually em12c Here
  2. Configure email notification and incidents rule  Here
  3. Deploy Agent in Em12c Here
Thank you
Osama Mustafa

Saturday, July 13, 2013

Clone Pluggable Database

If you want to Clone One Of Pluggable database , How can you do that :

SQL> select name from v$pdbs ;

NAME
------------------------------
PDB$SEED
TEST_1
TEST_2
Let's Clone Test_1 , Create folder on os level to move test_1 data. 

[oracle@test12c u01]$ mkdir clone
[oracle@test12c u01]$ cd clone/
[oracle@test12c clone]$ pwd
/u01/clone
Open Test_1 Read Only : 
SQL> alter pluggable database test_1 close immediate;Pluggable database altered.
SQL> alter pluggable database test_1 open read only ;Pluggable database altered.
Set this parameter to our created directory above : 
SQL> show parameter db_create_file_dest ;

NAME     TYPE VALUE
------------------------------------ -----------
db_create_file_dest     string

SQL> alter system set db_create_file_dest='/u01/clone';             System altered.
SQL> show parameter db_create_file_dest ;

NAME     TYPE VALUE
------------------------------------ -----------
db_create_file_dest     string /u01/clone
SQL> create pluggable database clone from test_1 ;Pluggable database created.
SQL> alter pluggable database clone open ;Pluggable database altered.
Test Clone Pluggable database 

[oracle@test12c ~]$ sqlplus sys/sys@test12c:1521/clone as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 13 20:20:24 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
------------------------------
CLONE
SQL> select name from v$pdbs ;

NAME
------------------------------
PDB$SEED
TEST_1
TEST_2
CLONE
You can drop Clone Database using the below command

SQL > Alter Pluggable database Clone Including Datafiles ;
Thank you 
Osama mustafa

Point-In-Time Recovery for a Pluggable Database

Check Pluggable database that you have :
SQL> select name from v$pdbs ;

NAME
------------------------------
PDB$SEED
TEST_1
TEST_2
TEST_3
Shutdown database , to configure database archivelog
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
 ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size    2287864 bytes
Variable Size  838862600 bytes
Database Buffers  402653184 bytes
Redo Buffers    8859648 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.

SQL> alter database open 
Database altered.

SQL> alter pluggable database all open; 
Pluggable database altered.

SQL> alter system set db_recovery_file_dest_size = 2G scope=both; 
System altered.

SQL> alter pluggable database all open; 
Pluggable database altered.
Export ORACLE_SID for container database and enter rman to backup  like below

[oracle@test12c backup]$ export ORACLE_SID=db12c
[oracle@test12c backup]$ rman target '"/ as sysbackup"'

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jul 13 17:13:42 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1274669151)

Auto Backup controlfile :
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Backup Script that we need :
Run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u01/backup/db12c/%U';
backup database plus archivelog;
}
 Where 
Format '/u01..':  Location for backup


now move to our pluggable database
SQL>alter session set container=test_1 ;
SQL>create tablespace test_1  datafile '/u01/app/oracle/oradata/db12c/test_1/test_1.dbf' size 10m;
SQL>create user test identified by test temporary tablespace temp default tablespace test_1;
SQL>grant create session, create table, unlimited tablespace to test ;
SQL>create table data (id varchar2(100)) tablespace test_1;

Enter Data using The Below code in above table :
begin
 for i in 1.. 10000 loop
    insert into data values ('osama');
 end loop;
 commit;
end;
After you insert data close pluggable database to start restore.
alter pluggable database test_1 CLOSE ;
Restore script :
run {
set until SCN = 1832026 ;
restore pluggable database test_1;
recover pluggable database Test_1 auxiliary destination='/u01/backup/db12c';
alter pluggable database TEST_1 open resetlogs;
}

Note :  To get SCN for database before insert use the query below :
SQL > select timestamp_to_scn(sysdate) from v$database;

After Restore :
sqlplus sys/sys@test12c:1521/test_1 ;
SQL> select count(*) from data ;
select count(*) from data
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
Thank you
Osama Mustafa

Friday, July 12, 2013

Adopting NON-CDB to CBD

This article describe steps to Adopt non-container database 12c to container 12c 
[oracle@test12c Desktop]$ ps -ef | grep pmon
oracle    3230     1  0 04:04 ?        00:00:09 ora_pmon_db12c
oracle   12112     1  0 08:20 ?        00:00:07 ora_pmon_NonCDB
oracle   29621  3203  0 18:47 pts/1    00:00:00 grep pmon
Where :
db12c : Container database
NonCDB : Non Container Database

Let Start

[oracle@test12c Desktop]$ export ORACLE_SID=NonCDB

SQL> select instance_name from v$instance ;
INSTANCE_NAME
----------------
NonCDB

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

SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area  939495424 bytes
Fixed Size    2295080 bytes
Variable Size  348130008 bytes
Database Buffers  583008256 bytes
Redo Buffers    6062080 bytes
Database mounted.

SQL> alter database open read only;
Database altered.
Run the below procedure to generate the manifest file. 

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/u01/noncdb.xml');
PL/SQL procedure successfully completed.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
 I already working on the same host so no need to copy datafiles,if you are not working on same host copy datafiles to same location.

Let's Work On Container Database .

after copy datafiles to folder, On Os Level , Create directory to use file_name_convert.

[oracle@test12c oradata]$ mkdir newnoncdb
[oracle@test12c newnoncdb]$ pwd
/u01/app/oracle/oradata/newnoncdb
[oracle@test12c Desktop]$ export ORACLE_SID=db12c
[oracle@test12c Desktop]$ sqlplus / as sysdba
 
SQL> create pluggable database non_cdb as clone
  2  using '/u01/noncdb.xml'
  3  file_name_convert=('/u01/app/oracle/oradata/NonCDB','/u01/app/oracle/oradata/newnoncdb') copy;
Pluggable database created.

 Where

  1.  using '/u01/noncdb.xml' : file that should be generated using dbms_pdb.describe
  2. '/u01/app/oracle/oradata/NonCDB' : location for Copied Non_CDB datafile
  3. ,'/u01/app/oracle/oradata/newnoncdb' : location for created directory.

SQL> alter pluggable database non_cdb open ;        
Pluggable database altered.

SQL> alter pluggable database non_cdb close ;
Pluggable database altered.

SQL> alter pluggable database non_cdb open ;
Pluggable database altered.
NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
NEWDB_PLUG MOUNTED
NEW_PLUG_COPY MOUNTED
NEW_PLUG_MOVE MOUNTED
NON_CDB       READ WRITE
Now you plugged NON-CDB to Container Database successfully without any problems and it will works fine.

the below step is optional but recommanded for production to move all thing from non-cdb to cdb. ( specially for upgrade 12c later ) 

SQL> alter session set container=Non_cdb;
Session altered.
OR 
[oracle@test12c newnoncdb2]$ sqlplus sys/sys@test12c:1521/Non_cdb as sysdba  ;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql   

Thank you
Osama Mustafa

Thursday, July 11, 2013

Tuesday, July 9, 2013

Pluggable Database Part (3)

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

Friday, July 5, 2013

Pluggable Database Tutorial (12c) Part 2

In my Part 1 Tutorial for how to deal with Pluggable database 12c I mentioned how to use pluggable database , How to create pluggable database , how to create tablespace , and how to open/close pluggable database .

Please check the Part ( 1 ) before continue reading this article here.

Welcome to Pluggable database Part 2 


  • Rename Pluggable Database 
  • Manage Pluggable database
  • Drop Pluggable database
  • Security In Pluggable database


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

NAME     CON_ID
----------------------------- ----------
TEST  3

as you see in the above query, I already created pluggable database called test. and it's in Read write Mode.

Rename Pluggable database 

SQL> alter pluggable database TEST close immediate ;Pluggable database altered.
SQL> alter pluggable database TEST open restricted ;Pluggable database altered.
SQL> select name, open_mode from v$pdbs;

NAME                OPEN_MODE
------------------------------ ----------
TEST       READ WRITE
SQL> alter pluggable database TEST rename global_name to new ;Pluggable database altered.
SQL> select name, con_id from v$active_services order by 1;

NAME      CON_ID
--------------------------------------- ----------
new   3
if you are not connected to pluggable database or set session container then you will recicve error message  
ORA-65046: operation not allowed from outside a pluggable 
SQL> alter pluggable database new close immediate ;Pluggable database altered.

SQL> alter pluggable database new open ;Pluggable database altered.
Manage Pluggable Database

Back to root container using / as sysdba like below :
SQL> conn / as sysdba
Connected.
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
new           3

We Control which list for for tablespace & datafiles by using con_id.
  • List tablespace in root container 
SQL> select tablespace_name, con_id from cdb_tablespaces where con_id=1; 

TABLESPACE_NAME       CON_ID
------------------------------ ----------
SYSTEM 1
SYSAUX 1
UNDOTBS1 1
TEMP 1
USERS 1
CDATA 1

  • List Database In root Container 

 SQL> select file_name, con_id from cdb_data_files where con_id=1;
FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/users01.dbf      1
/u01/app/oracle/oradata/db12c/undotbs01.dbf      1
/u01/app/oracle/oradata/db12c/sysaux01.dbf      1
/u01/app/oracle/oradata/db12c/system01.dbf      1
/u01/app/oracle/oradata/db12c/gls/test.dbf      1
  • Temp Tablespace  in root container 
SQL> select file_name, con_id from cdb_temp_files where con_id=1; 

FILE_NAME CON_ID
----------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf      1
  • Create Tablespace  ( already mentioned in Part (1)) 
SQL> create tablespace test datafile '/u01/app/oracle/oradata/db12c/gls/test03.dbf' size 20M; Tablespace created.
SQL> select tablespace_name, con_id from cdb_tablespaces order by con_id;


TABLESPACE_NAME   CON_ID
------------------------------ ----------
SYSTEM 1
TEST 1
CDATA 1
SYSAUX 1
TEMP 1
UNDOTBS1 1
USERS 1
SYSAUX 2
TEMP 2
SYSTEM 2
TEMP 3
SYSAUX 3
PDB_TEST 3
SYSTEM 3
14 rows selected.

  •  Create temp tablespace 
SQL> create temporary tablespace temp_test tempfile '/u01/app/oracle/oradata/db12c/gls/temp_test.dbf' size 20M ;
Tablespace created.
SQL> select file_name, con_id from cdb_temp_files where con_id=1;

FILE_NAME CON_ID
--------------------------------------------------------------------------- ----------
/u01/app/oracle/oradata/db12c/temp01.dbf      1
/u01/app/oracle/oradata/db12c/gls/temp_test.dbf      1

The Same Steps in root container for create tablespace and temp tablespace for pluggable database.

Security In Pluggable Database

In This Section we will discuss how to manage Users, roles and privileges.

before Demonstration you need to know what is the difference between two users  type :
  • Common  : when you create this kind of users in root it's automatically replicated in all Pluggable database.
  • Local : this kind of users only created on pluggable database that you are connected to it now. and dose not effect on others pluggable database.
To Create Common Users you need to be connected to root container.

SQL> conn / as sysdba
Connected.
SQL> create user c##osama identified by osama ;User created.
SQL> select username, common, con_id from cdb_users where username like 'C##%';

USERNAME COM CON_ID
------------------------------------ --- ----------
C##TEST YES      1
C##OSAMA YES      1
C##TEST YES      3
C##OSAMA YES      3
SQL> grant create session to c##osama ;Grant succeeded.
SQL> conn c##osama/osama@test12c:1521/db12c ;
Connected.
Let's connect to pluggable database :

The user i will created it here will not appear in root container.
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.

SQL> create user test identified by test ;User created.

SQL> grant create session to test ;Grant succeeded.
 SQL> select username, common, con_id  from cdb_users where username ='TEST';

USERNAME      COM CON_ID
------------------ ----------
TEST  NO      3
 SQL> conn test/test@test12c:1521/new ;
Connected.

 Same rules and conditions applied on Roles if you created in Root Container it will be replicated to pluggable database, on other hand if you created in Pluggable database it will be local without effecting Container.


Let's connect to Root Container

SQL> conn / as sysdba
Connected.
SQL> create role c##root_role ;Role created.

SQL> select role, common, con_id from cdb_roles where role='C##ROOT_ROLE';

ROLE  COM CON_ID
--------------------- --- ----------
C##ROOT_ROLE  YES      1
C##ROOT_ROLE  YES      3
SQL> conn sys/sys@test12c:1521/new as sysdba
Connected.
SQL> create role test2;Role created.
 SQL> select role, common, con_id from cdb_roles where role='TEST2';

ROLE COM CON_ID
---------------- --- ----------
TEST2 NO      3
SQL> create role hr container=all ;
create role hr container=all
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
SQL> create user hr identified by hr container=all ;
create user hr identified by hr container=all
                             *
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
---> you cannot create a common role inside a PDB.

Check user privileges :

SQL> select grantee, privilege, common, con_id from cdb_sys_privs
where privilege='CREATE SESSION' and grantee='TEST'; 

GRANTEE PRIVILEGE  COM  CON_ID
-------------- ---------------------------------------
TEST CREATE SESSION  NO       3
Drop Pluggable Database

SQL > drop pluggable database new  including datafiles;
Pluugable database dropped.
This document also available on slidshare here


Thank you
Osama mustafa 

Thursday, July 4, 2013

Pluggable Database Tutorial Part 1 (12c)

[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

Tuesday, July 2, 2013

Upgrade 11.2.0.3 to 12c Part 1

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

Monday, July 1, 2013

TEMP UNDO TABLESPACE : Another 12C Feature

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