Some Good Links as well for Working on 12C
http://osamamustafa.blogspot.in/2013/07/pluggable-database-tutorial-part-1-12c.html
http://osamamustafa.blogspot.in/2013/07/pluggable-database-tutorial-12c-part-2.html
http://osamamustafa.blogspot.in/2013/07/pluggable-database-part-3.html
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2289016 bytes
Variable Size 989856392 bytes
Database Buffers 654311424 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.
But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB2 MOUNTED
PDB3 MOUNTED
From CDB$ROOT we can manage any PDB.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Startup of PDBs can be automated using a startup trigger.
SQL> create or replace trigger Sys.After_Startup
after startup on database
begin
execute immediate 'alter pluggable database all open';
end;
/
Trigger created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1636814848 bytes
Fixed Size 2288968 bytes
Variable Size 973079224 bytes
Database Buffers 654311424 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
PDB3 READ WRITE
http://osamamustafa.blogspot.in/2013/07/pluggable-database-tutorial-part-1-12c.html
http://osamamustafa.blogspot.in/2013/07/pluggable-database-tutorial-12c-part-2.html
http://osamamustafa.blogspot.in/2013/07/pluggable-database-part-3.html
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2289016 bytes
Variable Size 989856392 bytes
Database Buffers 654311424 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.
But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB2 MOUNTED
PDB3 MOUNTED
From CDB$ROOT we can manage any PDB.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
To open one PDB :-
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
To open ALL PDBS :-
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
PDB3 READ WRITE
Or we can move down to a PDB container to stop and start them individually.
SQL> alter session set container=PDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB1
SQL> shutdown;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
Startup of PDBs can be automated using a startup trigger.
SQL> create or replace trigger Sys.After_Startup
after startup on database
begin
execute immediate 'alter pluggable database all open';
end;
/
Trigger created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1636814848 bytes
Fixed Size 2288968 bytes
Variable Size 973079224 bytes
Database Buffers 654311424 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE
PDB3 READ WRITE
---------------------------------------------------------
There is one library cache, but it, along with much
of the SGA has been redesigned to function in a multi-tenant environment, which means, it processes library cache a bit
differently than it did before. One of the additions to the v$librarycache view, as with other v$views is the CON_ID column-
From the container database you can see the con_id, designating the instance container identifier and the gets:
SQL> select con_id, gets from v$librarycache;
of the SGA has been redesigned to function in a multi-tenant environment, which means, it processes library cache a bit
differently than it did before. One of the additions to the v$librarycache view, as with other v$views is the CON_ID column-
From the container database you can see the con_id, designating the instance container identifier and the gets:
SQL> select con_id, gets from v$librarycache;
CON_ID GETS
---------- ----------
0 11288
0 16644
0 406
0 46
0 162
0 1473
0 2
0 1
0 2
0 59
0 2
---------- ----------
0 11288
0 16644
0 406
0 46
0 162
0 1473
0 2
0 1
0 2
0 59
0 2
CON_ID GETS
---------- ----------
0 211
0 6143
0 1
0 2280
0 3314
0 10
0 10
---------- ----------
0 211
0 6143
0 1
0 2280
0 3314
0 10
0 10
18 rows selected.
Once switching to a pluggable database:
SQL> alter session set container=CLTST12C2;
Session altered.
I receive the same information and not change in con_id values for any of the rows, but I wasn't processing anything that might be using
the library cache, either-
the library cache, either-
SQL> select con_id, gets from v$librarycache;
CON_ID GETS
---------- ----------
0 11622
0 16902
0 412
0 46
0 162
0 1504
0 2
0 1
0 2
0 63
0 2
---------- ----------
0 11622
0 16902
0 412
0 46
0 162
0 1504
0 2
0 1
0 2
0 63
0 2
CON_ID GETS
---------- ----------
0 211
0 6198
0 1
0 2322
0 3384
0 11
0 2
0 10
---------- ----------
0 211
0 6198
0 1
0 2322
0 3384
0 11
0 2
0 10
19 rows selected.
If we do a quick look at what views now are available for library information:
SQL> select view_name from dba_views
2 where view_name like '%LIBRARY%';
2 where view_name like '%LIBRARY%';
VIEW_NAME
--------------------------------------------------------------------------------
V_$MAP_LIBRARY
V_$LIBRARYCACHE
V_$LIBRARY_CACHE_MEMORY
V_$JAVA_LIBRARY_CACHE_MEMORY
GV_$MAP_LIBRARY
GV_$LIBRARYCACHE
GV_$LIBRARY_CACHE_MEMORY
GV_$JAVA_LIBRARY_CACHE_MEMORY
DBA_HIST_LIBRARYCACHE
KU$_LIBRARY_VIEW
CDB_HIST_LIBRARYCACHE <--Well, that's new! :)
--------------------------------------------------------------------------------
V_$MAP_LIBRARY
V_$LIBRARYCACHE
V_$LIBRARY_CACHE_MEMORY
V_$JAVA_LIBRARY_CACHE_MEMORY
GV_$MAP_LIBRARY
GV_$LIBRARYCACHE
GV_$LIBRARY_CACHE_MEMORY
GV_$JAVA_LIBRARY_CACHE_MEMORY
DBA_HIST_LIBRARYCACHE
KU$_LIBRARY_VIEW
CDB_HIST_LIBRARYCACHE <--Well, that's new! :)
11 rows selected.
This is a duplicate of the DBA_HIST_LIBRARYCACHE, but is specifically designed to give us detail at the tenant level vs. the global level, which would assist you during
testing:
testing:
SQL> desc cdb_hist_librarycache;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAMESPACE NOT NULL VARCHAR2(15)
GETS NUMBER
GETHITS NUMBER
PINS NUMBER
PINHITS NUMBER
RELOADS NUMBER
INVALIDATIONS NUMBER
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER
CON_DBID NUMBER
CON_ID NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
NAMESPACE NOT NULL VARCHAR2(15)
GETS NUMBER
GETHITS NUMBER
PINS NUMBER
PINHITS NUMBER
RELOADS NUMBER
INVALIDATIONS NUMBER
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER
CON_DBID NUMBER
CON_ID NUMBER
------------------------------------------------------------
There are a TON of newer roles involving a number of new features for Goldengate, etc., but the Pluggable Database ones are pretty much self explantatory:
CDB_DBA
PDB_DBA
CAPTURE_ADMIN
CDB_DBA
PDB_DBA
CAPTURE_ADMIN
------------------------------------------------------------
There isn't a cdb_database view, so they've incorporated a number of new columns:
SQL> desc v$database;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(20)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(39)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(22)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
CONTROLFILE_CONVERTED VARCHAR2(3)
PRIMARY_DB_UNIQUE_NAME VARCHAR2(30)
SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3)
MIN_REQUIRED_CAPTURE_CHANGE# NUMBER
CDB VARCHAR2(3)
CON_ID NUMBER
PENDING_ROLE_CHANGE_TASKS VARCHAR2(512)
CON_DBID NUMBER
There isn't a cdb_database view, so they've incorporated a number of new columns:
SQL> desc v$database;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(20)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(39)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(22)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
CONTROLFILE_CONVERTED VARCHAR2(3)
PRIMARY_DB_UNIQUE_NAME VARCHAR2(30)
SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3)
MIN_REQUIRED_CAPTURE_CHANGE# NUMBER
CDB VARCHAR2(3)
CON_ID NUMBER
PENDING_ROLE_CHANGE_TASKS VARCHAR2(512)
CON_DBID NUMBER
Those last four columns are the ones I believe you are concerned with and again, pretty self-explanatory and why they were added.
-----------------------------------------------------------
Located in the $ORACLE_HOME/rdbms/admin directory, there is a script: noncdb_to_pdb.sql
This is the script that will convert a non-PDB database that has been upgraded and plugged into a 12c container into a PDB and remove the duplicate
metadata.
Located in the $ORACLE_HOME/rdbms/admin directory, there is a script: noncdb_to_pdb.sql
This is the script that will convert a non-PDB database that has been upgraded and plugged into a 12c container into a PDB and remove the duplicate
metadata.
----------------------------------------------------------
All default connections for clients would be done by Oracle service names. They would simply connect through this and it's much simpler than it sounds.
All default connections for clients would be done by Oracle service names. They would simply connect through this and it's much simpler than it sounds.
----------------------------------------------------------
The query you show here, "select count(*) from CDB.SCHEMA.TABLE;" is a format very similar to semi-tenant database design we see in MSSQL and this would violate the
distinct containment of the PDB. Now, with that said, you could set up a dblink and select as you would to any other dblink configuration.
The query you show here, "select count(*) from CDB.SCHEMA.TABLE;" is a format very similar to semi-tenant database design we see in MSSQL and this would violate the
distinct containment of the PDB. Now, with that said, you could set up a dblink and select as you would to any other dblink configuration.
----------------------------------------------------------
Users are contained at both the CDB and then isolated to PDB access, as well:
Roles and access can be limited and a user in on PDB does not have access automatically to another PDB unless granted roles and priveleges.
Users are contained at both the CDB and then isolated to PDB access, as well:
Roles and access can be limited and a user in on PDB does not have access automatically to another PDB unless granted roles and priveleges.
SQL> desc dba_sa_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME NOT NULL VARCHAR2(1024)
POLICY_NAME NOT NULL VARCHAR2(30)
USER_PRIVILEGES VARCHAR2(4000)
USER_LABELS VARCHAR2(4000)
MAX_READ_LABEL VARCHAR2(4000)
MAX_WRITE_LABEL VARCHAR2(4000)
MIN_WRITE_LABEL VARCHAR2(4000)
DEFAULT_READ_LABEL VARCHAR2(4000)
DEFAULT_WRITE_LABEL VARCHAR2(4000)
DEFAULT_ROW_LABEL VARCHAR2(4000)
Name Null? Type
----------------------------------------- -------- ----------------------------
USER_NAME NOT NULL VARCHAR2(1024)
POLICY_NAME NOT NULL VARCHAR2(30)
USER_PRIVILEGES VARCHAR2(4000)
USER_LABELS VARCHAR2(4000)
MAX_READ_LABEL VARCHAR2(4000)
MAX_WRITE_LABEL VARCHAR2(4000)
MIN_WRITE_LABEL VARCHAR2(4000)
DEFAULT_READ_LABEL VARCHAR2(4000)
DEFAULT_WRITE_LABEL VARCHAR2(4000)
DEFAULT_ROW_LABEL VARCHAR2(4000)
SQL> desc cdb_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)
CON_ID NUMBER
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)
CON_ID NUMBER
No comments:
Post a Comment