11/06/2015 DBA : Oracle 12c exploring ­ Part 1

0 mais Próximo blog»

DBA
Home Scripts Backup and Recovery Oracle Database

Friday, May 9, 2014 Search This Blog

Oracle 12c exploring ­ Part 1

Container – either a PDB or the root container. Root contains the schema, schema objects and non­schema objects to which all
PDB’s belong. About Me
Every CDB has following.
San deep
ONE Root container ­ It is the core of the CDB, contains all oracle supplied metadata like packages, core dictionary objects and
common users. It is denoted by CDB$ROOT. Follow 3
ONE seed PDB ­ It is a template distributed for other PDB’s creation. It is named as PDB$SEED. We cant modify or add objects in
I am working as Sr. DBA an
PDB$SEED. started my career in early
USER CREATED PDB – It is the pluggable database created by users for their application purpose. It contains user data started with advanced repli
of oracle db and now focusing on various
1. To check whether the connected database is CONTAINER database (CDB) or PLUGGABLE database tasks. Exploring daily and learning various
like Ms Sql Sever, Mysql. I have decided t
a blog bit late but still I feel it would be wo
share the knowledge I learnt that might he
SQL> select name,cdb,con_id,con_dbid from v$database;
people.
NAME CDB CON_ID CON_DBID
­­­­­­­­­ ­­­ ­­­­­­­­­­ ­­­­­­­­­­ Please do feel free to comment and corre
ORCL YES 0 1368449947 if I am wrong. I am glad to accept it.

SQL> show parameter enable;

View my complete profile
NAME TYPE VALUE
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­ ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
enable_ddl_logging boolean FALSE Blog Archive
enable_pluggable_database boolean TRUE
▼ 2014 (46)
► December (1)
SQL> col pdb_name format a30
SQL> select pdb_id,pdb_name,status,con_id,dbid,con_uid from cdb_pdbs; ► September (3)
► August (1)
PDB_ID PDB_NAME STATUS CON_ID DBID CON_UID
­­­­­­­­­­ ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ ­­­­­­­­­­­­­ ­­­­­­­­­­ ­­­­­­­­­­ ­­­­­­­­­­ ► July (4)
3 PDBORCL NORMAL 1 2287824469 2287824469 ► June (14)
2 PDB$SEED NORMAL 1 4083296649 4083296649
▼ May (23)
PDB$ORCL is user created PDB. Shared Pool Internals
2. Checking how the database file structure are available in container database
Oracle database Architecture ­ Part
2
SQL> select name from v$datafile;
Oracle database Architecture ­ Part
NAME 1
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ Index on Foreign Keys to avoid
/data/orcl/system01.dbf locks
/data/orcl/sysaux01.dbf
Connecting to MYSQL databases
/data/orcl/undotbs01.dbf
from Oracle database...
/data/orcl/pdbseed/system01.dbf
/data/orcl/users01.dbf Step by Step procedure for
/data/orcl/pdbseed/sysaux01.dbf applying patch set from...
/data/orcl/pdborcl/system01.dbf Block Recovery using RMAN with
/data/orcl/pdborcl/sysaux01.dbf Valid backup when ...
/data/orcl/pdborcl/pdborcl_users01.dbf (for PDB)
Migrating from 11.2.0.3 (NON­
CDB) to 12c (PDB) u...
SQL> select name from v$controlfile; Steps for sharing the NFS drive to
other linux sys...
NAME Oracle 12c exploring ­ Part 2
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
/data/orcl/control01.ctl Datafiles contains uncommitted
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl data
Redo and Undo in Oracle database
Block Recovery using RMAN with
SQL> col member format a45
No Valid backup dur...
SQL> select member from v$logfile;
RMAN_CAT.DBMS_RCVCAT
MEMBER version 11.02.00.01 while re...
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ Recovery of the database when we
/data/orcl/redo03.log lose redo logs wh...
/data/orcl/redo02.log
/data/orcl/redo01.log Recovery after loss of INACTIVE
redo log files
3. Checking for database files in OS level Extending the filesystem using

http://sandeepnandhadba.blogspot.com.br/2014/05/oracle­12c­exploring­part­1.html 1/4
11/06/2015 DBA : Oracle 12c exploring ­ Part 1
Logical Volume and ...
[oracle@oeltest orcl]$ ls ­ltrh
Flushing single statement from
total 2.0G
Library cache
drwxr­x­­­. 2 oracle oinstall 4.0K Feb 27 23:17 pdbseed
drwxr­x­­­. 2 oracle oinstall 4.0K Feb 28 00:36 pdborcl Oracle 12c exploring ­ Part 1
­rw­r­­­­­. 1 oracle oinstall 51M Feb 28 09:53 redo01.log Edition Based Redefinition – Locks
­rw­r­­­­­. 1 oracle oinstall 51M Feb 28 09:53 redo03.log and No Data Fou...
­rw­r­­­­­. 1 oracle oinstall 5.1M Feb 28 09:53 users01.dbf
­rw­r­­­­­. 1 oracle oinstall 89M Feb 28 10:04 temp01.dbf Edition Based Redefiniton ­ 11gR2
­rw­r­­­­­. 1 oracle oinstall 216M Feb 28 10:10 undotbs01.dbf Certification
­rw­r­­­­­. 1 oracle oinstall 761M Feb 28 10:10 sysaux01.dbf
­rw­r­­­­­. 1 oracle oinstall 781M Feb 28 10:10 system01.dbf Steps to apply patch set from
11.2.0.3.0 to 11.2.0...
­rw­r­­­­­. 1 oracle oinstall 18M Feb 28 10:10 control01.ctl
­rw­r­­­­­. 1 oracle oinstall 51M Feb 28 10:10 redo02.log

4. Each PDB will have its own datafiles.

Pages
[oracle@oeltest orcl]$ cd pdborcl/
[oracle@oeltest pdborcl]$ ls ­ltrh Home
total 936M
Scripts
­rw­r­­­­­. 1 oracle oinstall 21M Feb 28 00:35 temp01.dbf
­rw­r­­­­­. 1 oracle oinstall 5.1M Feb 28 00:42 pdborcl_users01.dbf Oracle Database
­rw­r­­­­­. 1 oracle oinstall 261M Feb 28 00:42 system01.dbf
­rw­r­­­­­. 1 oracle oinstall 671M Feb 28 00:42 sysaux01.dbf Backup and
[oracle@oeltest pdborcl]$ cd ../pdbseed/ Recovery
[oracle@oeltest pdbseed]$ ls ­ltrh
total 1006M
­rw­r­­­­­. 1 oracle oinstall 88M Feb 28 00:27 pdbseed_temp01.dbf
­rw­r­­­­­. 1 oracle oinstall 671M Feb 28 00:28 sysaux01.dbf
­rw­r­­­­­. 1 oracle oinstall 261M Feb 28 00:28 system01.dbf
[oracle@oeltest pdbseed]$

5. Listener will have separate service for “user created pdbs”

[oracle@oeltest admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 ­ Production on 28­FEB­2014 10:25:29

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
­­­­­­­­­­­­­­­­­­­­­­­­
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 ­ Production
Start Date 28­FEB­2014 10:24:47
Uptime 0 days 0 hr. 0 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oeltest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeltest.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oeltest.localdomain)(PORT=5500))(Security=
(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl.localdomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.localdomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.localdomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

6. Checking how the TNS entries are place in tnsnames.ora file.

[oracle@oeltest admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oeltest.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain)
)
)

To connect to pluggable database, directly, let us add tns entry in tnsnames.ora file.

PDBORCL =
(DESCRIPTION =

http://sandeepnandhadba.blogspot.com.br/2014/05/oracle­12c­exploring­part­1.html 2/4
11/06/2015 DBA : Oracle 12c exploring ­ Part 1
(ADDRESS = (PROTOCOL = TCP)(HOST = oeltest.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl.localdomain)
)
)

[oracle@oeltest admin]$ tnsping pdborcl

TNS Ping Utility for Linux: Version 12.1.0.1.0 ­ Production on 28­FEB­2014 10:31:29

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oeltest.localdomain)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl.localdomain)))
OK (80 msec)

7. Various methods to connect to PDB databases.

a. We can using separate tns string.
[oracle@oeltest admin]$ sqlplus sys@pdborcl as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 10:28:31 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

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

SYS_CONTEXT('USERENV','CON_NAME')
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
PDBORCL

b. Secondly, we can connect using alter session between CDB to PDB or from any PDB.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV
­­­­­­­­­­­­­­­­­­­­
PDB$SEED

SQL> alter session set container=PDBORCL;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV
­­­­­­­­­­­­­­­­­­­­
PDBORCL

c. Switching to Root container again.

SQL> alter session set container=ORCL;
ERROR:
ORA­65011: Pluggable database ORCL does not exist.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

SYS_CONTEXT('USERENV
­­­­­­­­­­­­­­­­­­­­
CDB$ROOT

Note: we need to give as CDB$ROOT and not the CDB’s name.

8. After logging to PDBORCL, let us see how the datafile structures are.
SQL> select name from v$datafile;

http://sandeepnandhadba.blogspot.com.br/2014/05/oracle­12c­exploring­part­1.html 3/4
11/06/2015 DBA : Oracle 12c exploring ­ Part 1
NAME
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
/data/orcl/undotbs01.dbf
/data/orcl/pdborcl/system01.dbf
/data/orcl/pdborcl/sysaux01.dbf
/data/orcl/pdborcl/pdborcl_users01.dbf

SQL> select member from v$logfile;

MEMBER
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
/data/orcl/redo03.log
/data/orcl/redo02.log
/data/orcl/redo01.log

SQL> select name from v$controlfile;

NAME
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
/data/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Posted by San deep at 1:14 AM Recommend this on Google

No comments:

Post a Comment

Enter your comment...

Comment as: Google Account

Publish Preview

Newer Post Home Older Post

Subscribe to: Post Comments (Atom)

Awesome Inc. template. Powered by Blogger.

http://sandeepnandhadba.blogspot.com.br/2014/05/oracle­12c­exploring­part­1.html 4/4