[grid@rhel11grac1 oraagent_oracle]$ crsctl stat res ora.sgculdb.db -p
NAME=ora.sgculdb.db
TYPE=ora.database.type
ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=
ACTIVE_PLACEMENT=1
AGENT_FILENAME=%CRS_HOME%/bin/oraagent%CRS_EXE_SUFFIX%
AUTO_START=restore
CARDINALITY=2
CHECK_INTERVAL=1
CHECK_TIMEOUT=30
CLUSTER_DATABASE=true
DATABASE_TYPE=RAC
DB_UNIQUE_NAME=sgculdb
DEFAULT_TEMPLATE=PROPERTY(RESOURCE_CLASS=database) PROPERTY(DB_UNIQUE_NAME= CONCAT(PARSE(%NAME%, ., 2), %USR_ORA_DOMAIN%, .)) ELEMENT(INSTANCE_NAME= %GEN_USR_ORA_INST_NAME%) ELEMENT(DATABASE_TYPE= %DATABASE_TYPE%)
DEGREE=1
DESCRIPTION=Oracle Database resource
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=60
FAILURE_THRESHOLD=1
GEN_AUDIT_FILE_DEST=/u01/app/oracle/admin/sgculdb/adump
GEN_START_OPTIONS=
GEN_START_OPTIONS@SERVERNAME(rhel11grac1)=open
GEN_START_OPTIONS@SERVERNAME(rhel11grac2)=open
GEN_USR_ORA_INST_NAME=
GEN_USR_ORA_INST_NAME@SERVERNAME(rhel11grac1)=sgculdb1
GEN_USR_ORA_INST_NAME@SERVERNAME(rhel11grac2)=sgculdb2
HOSTING_MEMBERS=
INSTANCE_FAILOVER=0
LOAD=1
LOGGING_LEVEL=1
MANAGEMENT_POLICY=AUTOMATIC
NLS_LANG=
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
ORACLE_HOME_OLD=
PLACEMENT=restricted
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
ROLE=PRIMARY
SCRIPT_TIMEOUT=60
SERVER_POOLS=ora.sgculdb
SPFILE=+DATA_DG/sgculdb/spfilesgculdb.ora
START_DEPENDENCIES=weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns) hard(ora.DATA_DG.dg,ora.FRA.dg) pullup(ora.DATA_DG.dg,ora.FRA.dg)
START_TIMEOUT=600
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA_DG.dg,shutdown:ora.FRA.dg)
STOP_TIMEOUT=600
TYPE_VERSION=3.2
UPTIME_THRESHOLD=1h
USR_ORA_DB_NAME=
USR_ORA_DOMAIN=
USR_ORA_ENV=
USR_ORA_FLAGS=
USR_ORA_INST_NAME=
USR_ORA_INST_NAME@SERVERNAME(rhel11grac1)=sgculdb1
USR_ORA_INST_NAME@SERVERNAME(rhel11grac2)=sgculdb2
USR_ORA_OPEN_MODE=open
USR_ORA_OPI=false
USR_ORA_STOP_MODE=immediate
VERSION=11.2.0.3.0
Resource Dependencies (Start dependencies):-
Each resources will be having a dependencies. For the above database from the properties we could see that below
dependenices has been defined.
START_DEPENDENCIES=weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns) hard(ora.DATA_DG.dg,ora.FRA.dg) pullup(ora.DATA_DG.dg,ora.FRA.dg)
Pullup --> This defines that whenever we are starting an resource it automatically starts another resource. This will effect whenever the resource which has to
started automatically is not running.
Eg:-
For the database sgculdb we could see that the pullup dependencies is defined as pullup(ora.DATA_DG.dg,ora.FRA.dg) , which means whenever the database sgculdb
is started it will start Data_dg and Fra diskgrop automatically if these diskgroups are not running.
Lets test this.
[oracle@rhel11grac1 dbs]$ srvctl stop database -d sgculdb
[oracle@rhel11grac1 dbs]$ srvctl status database -d sgculdb
Instance sgculdb1 is not running on node rhel11grac1
Instance sgculdb2 is not running on node rhel11grac2
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g data_dg
Disk Group data_dg is running on rhel11grac2,rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g fra
Disk Group fra is running on rhel11grac2,rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl stop diskgroup -g data_dg
[grid@rhel11grac1 oraagent_oracle]$ srvctl stop diskgroup -g fra
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g data_dg
Disk Group data_dg is not running
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g fra
Disk Group fra is not running
-- We have stoped the database and the diskgroups.
Lets start the database without starting the diskgroups. Since the database has pullup dependency on data_dg and fra diskgroup
it should automatically start when the db is started.
[oracle@rhel11grac1 dbs]$ srvctl start database -d sgculdb
[oracle@rhel11grac1 dbs]$ srvctl status database -d sgculdb
Instance sgculdb1 is running on node rhel11grac1
Instance sgculdb2 is running on node rhel11grac2
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g data_dg
Disk Group data_dg is running on rhel11grac2,rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl status diskgroup -g fra
Disk Group fra is running on rhel11grac2,rhel11grac1
-- Diskgroups are started.
Hard Dependency:- This dependency defines that the resource which specified should be up and running before the start
of the resource. Here for sgculdb the hard dependency is defined as data_dg and fra, which means
these diskgroups should be up and running before the start of the database.
hard(ora.DATA_DG.dg,ora.FRA.dg)
Weak Dependency :- if resource A has a weak start dependency on another resource B then when we attempt to start the resource A will also
attempt to start the resource B if resource B is not running. The result of the attempt to start the resource
B is however of no consequence to the result of starting resource A.
weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,global:ora.gns)
Eg :- The start of resource sgculdb has a weak start dependencies on ora.listener, ora.scan_listener, ora.ons and ora.gns.
When we attempt to start sgculdb it will try to start the ora.listener, ora.scan_listener, ora.ons if it is not running,
but if these resources didn't comeup due to any issues it will not affect the start of the database since it is weak dependency.
[oracle@rhel11grac1 dbs]$ srvctl stop instance -d sgculdb -i sgculdb1
[grid@rhel11grac1 oraagent_oracle]$ srvctl status listener -l listener -n rhel11grac1
Listener LISTENER is enabled on node(s): rhel11grac1
Listener LISTENER is running on node(s): rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl stop listener -l listener -n rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl status listener -l listener -n rhel11grac1
Listener LISTENER is enabled on node(s): rhel11grac1
Listener LISTENER is not running on node(s): rhel11grac1
-- Lets start the instance, it has to bring listener as well since it has weak dependency.
[oracle@rhel11grac1 dbs]$ srvctl start instance -d sgculdb -i sgculdb1
[oracle@rhel11grac1 dbs]$ srvctl status instance -d sgculdb -i sgculdb1
Instance sgculdb1 is running on node rhel11grac1
[grid@rhel11grac1 oraagent_oracle]$ srvctl status listener -l listener -n rhel11grac1
Listener LISTENER is enabled on node(s): rhel11grac1
Listener LISTENER is running on node(s): rhel11grac1
-- Listener has been bought up automatically.
Attraction Dependency :- If a resource A has attraction dependency on resource b then crs prefers to place the resource A on the server
in which resource B runs.
Stop dependency.
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA_DG.dg,shutdown:ora.FRA.dg)
If a resource A has a hard stop dependency on resource B then resource A must be stopped when B stops running.
Eg. Here the database sgculdb has hard stop dependency on resource DATA_DG and FRA diskgroup, hence the resource sgculdb should be stoped
running before stoping resource DATA_DG and FRA diskgroup.
If we try to attemp to stop this diskgroup resource then it will trhrow eror if sgculdb is up and running.
[grid@rhel11grac1 oraagent_oracle]$ srvctl stop diskgroup -g fra
PRCR-1065 : Failed to stop resource ora.FRA.dg
CRS-5017: The resource action "ora.FRA.dg stop" encountered the following error:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "FRA" precludes its dismount
. For details refer to "(:CLSN00108:)" in "/u01/grid/product/11.2.0.4/log/rhel11grac2/agent/crsd/oraagent_grid//oraagent_grid.log".
CRS-2675: Stop of 'ora.FRA.dg' on 'rhel11grac2' failed
CRS-5017: The resource action "ora.FRA.dg stop" encountered the following error:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "FRA" precludes its dismount
. For details refer to "(:CLSN00108:)" in "/u01/grid/product/11.2.0.4/log/rhel11grac1/agent/crsd/oraagent_grid//oraagent_grid.log".
CRS-2675: Stop of 'ora.FRA.dg' on 'rhel11grac1' failed
Thursday, 17 March 2016
Wednesday, 28 October 2015
Full Transportable Export 12c
Full Transportable export in 12c
Full transportable export/import used in many
scenarios.
1)
Upgrading a database from 11g (11.2.0.3 & up) to
12c. Create a empty database in 12c and Export the 11g database and import to 12c.
2)
Transporting database to another server.
3)
Transporting non-cdb into non-cdb or cdb.
New parameter TRANSPORTABLE=ALWAYS , if exporting in
11g then need to use VERSION=12 while exporting.
Data movement methods.
1)
Meta data of objects residing in transportable
tablespace are unloaded into dumpfile.
2)
Acutal data (datafile) will be copied from source to
target system.
3)
Objects residing in non-transportable tablespace
(system & sysaux) will be unloaded to dumpfile set.
Advantages
of full transportable over transportable tablespace.
1. Transportable tablespaces
are usually the fastest fast way to move user and application data between databases, because tablespace data files are moved all
together from the source database to the target.
Moving an entire data file is generally much faster
than exporting and importing individual rows or even blocks of data. However, traditional
transportable tablespaces can require a fairly complicated set of steps to move user and application metadata needed
to effectively use these tablespace data files inthe destination database. A migration using
transportable tablespaces can therefore be characterized as being very
fast but more complex.
2. At the same
time, full transportable export/import uses the transportable tablespaces
mechanism to move user
and application data. This results in a migration that is very fast, even for
very large volumes of data.
Most important, full transportable export/import moves all of the system, user,
and
application
metadata needed for a database migration, without the complex set of steps
required for a traditional
transportable tablespaces operation.
Thus, full
transportable export/import combines the ease of use of Oracle Data Pump with
the performance
of transportable tablespaces, resulting in a feature that makes database
migration faster and easier.
Example.
Transporting full database using transportable
option in expdp.
DBname : source
Source :-
11.2.0.3 (RHEL x86 64 bit)
- Little Endian
Target :-
12.1.0.2 (Solaris 10 x86 64 bit)
- Little Endian
If it is different endian then you need to use dbms_file_transfer or rman
convert to convert the endian format.
a)
Create a Skeleton database in 12c (Make sure to
create with same character set as source db).
b)
Changed the default permanent tablespace name in the
skelton database, because we alreay have users tablespace in the source db.
SQL>
alter tablespace users rename to users1;
Tablespace
altered.
SQL>
alter tablespace users1 offline;
Tablespace
altered.
SQL> !
mv users01.dbf users1_01.dbf
SQL> !
ls -lrt
total
3411040
-rw-r----- 1 oracle
oinstall 52429312 Oct 29 08:46 redo02.log
-rw-r----- 1 oracle
oinstall 52429312 Oct 29 08:46 redo03.log
-rw-r----- 1 oracle
oinstall 62922752 Oct 29 08:47 temp01.dbf
-rw-r----- 1 oracle
oinstall 629153792 Oct 29 09:42 sysaux01.dbf
-rw-r----- 1 oracle
oinstall 62922752 Oct 29 09:42 undotbs01.dbf
-rw-r----- 1 oracle
oinstall 817897472 Oct 29 09:42 system01.dbf
-rw-r----- 1 oracle
oinstall 5251072 Oct 29 09:44 users1_01.dbf
-rw-r----- 1 oracle
oinstall 52429312 Oct 29 09:44 redo01.log
-rw-r----- 1 oracle
oinstall 10043392 Oct 29 09:44 control01.ctl
SQL>
alter database rename file '/opt/oracle/oradata/source/users01.dbf' to
'/opt/oracle/oradata/source/users1_01.dbf';
Database
altered.
SQL> alter
tablespace users1 online;
Tablespace
altered.
c)
Check the tablespace in the source db (11g)
SQL> select name from V$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
ACTIVE_DATA
IN_ACTIVE_DATA
User
tablespace are (USERS / ACTIVE_DATA / IN_ACTIVE_DATA)
SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/source/system01.dbf
/opt/oracle/oradata/source/sysaux01.dbf
/opt/oracle/oradata/source/undotbs01.dbf
/opt/oracle/oradata/source/users01.dbf
/opt/oracle/oradata/source/active_Data01.dbf
/opt/oracle/oradata/source/in_active_data01.dbf
6 rows selected.
Check whether the tablespace are self contained.
SQL> exec
dbms_tts.transport_set_check('USERS,IN_ACTIVE_DATA,ACTIVE_DATA',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
Put these tablespace in read only mode.
SQL> alter tablespace USERS read only;
Tablespace altered.
SQL> alter tablespace ACTIVE_DATA read only;
Tablespace altered.
SQL> alter tablespace IN_ACTIVE_DATA read only;
Tablespace altered.
d)
Export the database using transportable parameter.
SQL> create directory exp_full as
'/opt/oracle/oradata/source';
Directory created.
[oracle@rhel11gr2rac1 source]$ expdp
directory=exp_full dumpfile=full_transortable.dmp version=12 full=y
transportable=always logfile=full_trans.log
Export: Release 11.2.0.3.0 - Production on Thu Oct
29 09:00:33 2015
Copyright (c) 1982, 2011, Oracle and/or its
affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
Starting
"SYS"."SYS_EXPORT_FULL_01":
/******** AS SYSDBA directory=exp_full dumpfile=full_transortable.dmp
version=12 full=y transportable=always logfile=full_trans.log
Estimate in progress using BLOCKS method...
Processing object type
DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type
DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type
DATABASE_EXPORT/EARLY_OPTIONS/TABLE_DATA
Processing object type
DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type
DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type
DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 63.43 MB
Processing object type
DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type
DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported
"XDB"."XDB$H_INDEX" 1.089 MB 283 rows
...
...
...
. . exported
"SYSTEM"."REPCAT$_TEMPLATE_OBJECTS" 0 KB 0 rows
. . exported
"SYSTEM"."REPCAT$_TEMPLATE_PARMS" 0 KB 0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS" 0 KB 0 rows
. . exported
"SYSTEM"."REPCAT$_TEMPLATE_SITES" 0 KB 0 rows
. . exported
"SYSTEM"."REPCAT$_TEMPLATE_TARGETS" 0 KB 0 rows
. . exported
"SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows
. . exported
"SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows
. . exported
"SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
Master table
"SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
/opt/oracle/oradata/source/full_transortable.dmp
******************************************************************************
Datafiles required for transportable tablespace
ACTIVE_DATA:
/opt/oracle/oradata/source/active_Data01.dbf
Datafiles required for transportable tablespace
IN_ACTIVE_DATA:
/opt/oracle/oradata/source/in_active_data01.dbf
Datafiles required for transportable tablespace
USERS:
/opt/oracle/oradata/source/users01.dbf
Job "SYS"."SYS_EXPORT_FULL_01"
successfully completed at 09:06:14
e)
Transfer the dumpfiles and datafiles to the target
server.
[oracle@rhel11gr2rac1 source]$ scp / opt/oracle/oradata/source/full_transortable.dmp
oracle@192.168.0.51:/opt/oracle/oradata/source/
Password:
full_transortable.dmp
100% 98MB
7.0MB/s 00:14
[oracle@rhel11gr2rac1 source]$ scp
/opt/oracle/oradata/source/active_Data01.dbf
oracle@192.168.0.51:/opt/oracle/oradata/source/active_Data01.dbf 100% 100MB
6.3MB/s 00:16
[oracle@rhel11gr2rac1 source]$ scp /opt/oracle/oradata/source/in_active_data01.dbf
oracle@192.168.0.51:/opt/oracle/oradata/source/
Password:
in_active_data01.dbf
100% 100MB 6.3MB/s
00:16
[oracle@rhel11gr2rac1 source]$ scp
/opt/oracle/oradata/source/users01.dbf oracle@192.168.0.51:/opt/oracle/oradata/source/
Password:
users01.dbf
100% 5128KB 5.0MB/s 00:00
f)
Import on target side.
SQL> create directory imp_full as
'/opt/oracle/oradata/source';
Directory created.
-bash-3.2$ impdp directory=imp_full
dumpfile=full_transortable.dmp full=y
transport_datafiles='/opt/oracle/oradata/source/users01.dbf','/opt/oracle/oradata/source/active_Data01.dbf','/opt/oracle/oradata/source/in_active_data01.dbf'
logfile=imp.log
Import: Release 12.1.0.2.0 - Production on Thu Oct
29 09:57:24 2015
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and
Real Application Testing options
Master table
"SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=imp_full
dumpfile=full_transortable.dmp full=y
transport_datafiles=/opt/oracle/oradata/source/users01.dbf,/opt/oracle/oradata/source/active_Data01.dbf,/opt/oracle/oradata/source/in_active_data01.dbf
logfile=imp.log
Processing object type
DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type
DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type
DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type
TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP"
already exists
Processing object type
DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
----
----
....
Job "SYS"."SYS_IMPORT_FULL_01"
completed with 10 error(s) at Thu Oct 29 10:12:50 2015 elapsed 0 00:15:20.
Verify the tablespace in target .
SQL> select TABLESPACE_NAME, status, CONTENTS
from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
SYSAUX ONLINE PERMANENT
UNDOTBS1 ONLINE UNDO
TEMP ONLINE TEMPORARY
USERS1 ONLINE PERMANENT
ACTIVE_DATA ONLINE PERMANENT
IN_ACTIVE_DATA ONLINE PERMANENT
USERS ONLINE PERMANENT
NAME STATUS
------------------------------------------------------------
-------
/opt/oracle/oradata/source/system01.dbf SYSTEM
/opt/oracle/oradata/source/in_active_data01.dbf ONLINE
/opt/oracle/oradata/source/sysaux01.dbf ONLINE
/opt/oracle/oradata/source/undotbs01.dbf ONLINE
/opt/oracle/oradata/source/active_Data01.dbf ONLINE
/opt/oracle/oradata/source/users1_01.dbf ONLINE
/opt/oracle/oradata/source/users01.dbf ONLINE
Upgrade as well as migration is compelted.
Tuesday, 27 October 2015
Resource Manager in CDB & PDB
Resource Manager in CDB & PDB
In CDB RM can manage resource on two levels.
CDB Level :
Can manage the workloads for multiple PDB’s that are contending for
system and CDB resources.
PDB Level : Manage resource within the PDB.
PDB compete for resources: - CPU / Exadata I/O and
Parallel Servers.
This is done by granting different shares according
to the priority of the PDB’s. More shares can be
allocated for the important PDB’s.
No Consumer group nor shares can be defined for root
container.
Eg:-
CDB running with two PDB , each pdb is allocated
with 1 share.
PDB1 -> (Share = 1/2 ( 50% Guaranteed CPU), limited
to 100% CPU )
PDB2 -> (Share = 1/2 ( 50% Guaranteed CPU),
limited to 100% CPU )
Now another 2 PDBS are added then
PDB1 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
PDB2 -> (Share = 1/4 ( 25% Guaranteed CPU), limited
to 100% CPU )
PDB3 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
PDB4 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
Say PDB1 is very important and we have allocated 2
shares then total share = (2+1+1+1 =5)
PDB1 -> (Share = 2/5 ( 40% Guaranteed CPU),
limited to 100% CPU )
PDB2 -> (Share = 1/5 ( 20% Guaranteed CPU),
limited to 100% CPU )
PDB3 -> (Share = 1/5 ( 20% Guaranteed CPU), limited
to 100% CPU )
PDB4 -> (Share = 1/5 ( 20% Guaranteed CPU),
limited to 100% CPU )
The Maximum limits can be controlled using the below
parameters
Utilization_limit
( To control the maximum utilization of resource of a PDB)
Parallel_server_limit ( To override
utilization_limit only for parallel servers)
Eg.
Utilization_limit
= 30 ( Then the maximum system
resource used by this PDB will be 30%)
Parallel_server_limit = 50 ( If the parallel_server_target is set = 200
then this limit says
that 50% of parallel
servers to be used i.e. 100 )
These are the values corresponding to the default
directives that is automatically added to your plan.
It can be changed using dbms_resource_manager
package using update_cdb_default_directive procedure.
Eg.
PDB1 (2 Shares / utilization set to 50%, parallel_server_limit set to 50% (parallel
target set as 200) then
->
(Share = 2/5 (40% Guaranteed CPU), limited to 50% CPU / ExaI/O, parallel server
limited to 100)
PDB2 (1 Shares / utilization set to 30%, parallel_server_limit set to 30% (parallel
target set as 200) then
-> (Share = 1/5 (20%
Guaranteed CPU), limited to 30% CPU / ExaI/O, parallel server limited to 60)
PDB3 (1 Shares / utilization set to 20%, parallel_server_limit set to 20% (parallel
target set as 200) then
-> (Share = 1/5 (20%
Guaranteed CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)
PDB4 2 Shares / utilization set to 20%, parallel_server_limit set to 20% (parallel
target set as 200) then
-> (Share = 1/5 (20% Guaranteed
CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)
Autotask allocation default is (-1 share) which is
20% of system resource ,utilization limit is 90
And parallel limit is 100%. We can change it
accordingly.
Eg.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select name, open_mode from V$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_PURCHASE READ WRITE
PDB_HR READ WRITE
SQL> exec
dbms_resource_manager.CREATE_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.CREATE_CDB_PLAN
(plan => 'PLAN_FOR_PDBS', comment => 'RM PLANS for PDBS');
PL/SQL procedure successfully completed.
SQL> begin
2
dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE
3 (
4 PLAN => 'PLAN_FOR_PDBS',
5 PLUGGABLE_DATABASE =>
'PDB_PURCHASE',
6 SHARES => 4,
7 UTILIZATION_LIMIT => NULL,
8 PARALLEL_SERVER_LIMIT =>
NULL
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE
3 (
4 PLAN => 'PLAN_FOR_PDBS',
5 PLUGGABLE_DATABASE =>
'PDB_HR',
6 SHARES => 1,
7 UTILIZATION_LIMIT => 20,
8 PARALLEL_SERVER_LIMIT =>
20
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> exec
dbms_resource_manager.VALIDATE_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.SUBMIT_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> alter system set resource_manager_plan =
'PLAN_FOR_PDBS';
System altered.
SQL> select PLAN, PLUGGABLE_DATABASE, SHARES,
UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT from dba_cdb_rsrc_plan_directives;
PLAN
PLUGGABLE_DATABASE SHARES
UTILIZATION_LIMIT PARALLEL_SERVER_LIMIT
----------------------------------------
---------------------------------------- ---------- ----------------- ---------------------
DEFAULT_CDB_PLAN
ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_CDB_PLAN ORA$AUTOTASK
90 100
DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK 90 100
ORA$INTERNAL_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE
ORA$INTERNAL_CDB_PLAN ORA$AUTOTASK
ORA$QOS_CDB_PLAN
ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
ORA$QOS_CDB_PLAN ORA$AUTOTASK
90 100
PLAN_FOR_PDBS
ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
PLAN_FOR_PDBS ORA$AUTOTASK
90 100
PLAN_FOR_PDBS PDB_PURCHASE 4
PLAN_FOR_PDBS PDB_HR
1 20 20
12 rows selected.
Lets test it..
Vi insert_pdb_purchase.sh
export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s manzoor/ahamed@pdb_purchase <<EOF
> pdb_purchase_test.log
set timing on;
set time on;
begin
for i
in 1..100000 loop
insert into emp values (i, dbms_random.string('U',30), 10000+i);
end loop;
commit;
end;
/
exit
EOF
Save and close above script.
Vi insert_pdb_hr_test.sh
export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s manzoor/ahamed@pdb_purchase <<EOF
> pdb_purchase_test.log
set timing on;
set time on;
begin
for i
in 1..100000 loop
insert into emp values (i, dbms_random.string('U',30), 10000+i);
end
loop;
commit;
end;
/
exit
EOF
Save and close above script.
[oracle@rhel11gr2rac1 ~]$ cat insert_pdb_hr_test.sh
nohup /home/oracle/insert_pdb_purchase.sh &
nohup /home/oracle/insert_pdb_hr.sh &
nohup ./insert_pdb_hr_test.sh &
[oracle@rhel11gr2rac1 ~]$ cat pdb_purchase_test.log
PL/SQL procedure successfully completed.
Elapsed: 00:00:46.06
[oracle@rhel11gr2rac1 ~]$ cat pdb_hr_test.log
PL/SQL procedure successfully completed.
Elapsed: 00:02:04.27
We could see that script is completed in 46 sec in
pdb_purchase db, whereas in pdb_hr it took 2 mins and 4 secs
SQL> select con_id, sql_id, sql_text,
(cpu_time/1000000)/executions "Avg CPU Time",
(ELAPSED_TIME/1000000)/executions "Execution Time", executions from V$sql where
PARSING_SCHEMA_NAME = 'MANZOOR'
CON_ID
SQL_ID SQL_TEXT Avg
CPU Time Execution Time EXECUTIONS
---------- -------------
-------------------------------------------------- ------------ --------------
----------
3
049v557vkq39j begin for i in 1..100000
loop insert into emp v 25.007198 46.049448 1
alues (i,
dbms_random.string('U',30), 10000+i); e
nd loop; commit; end;
4 049v557vkq39j begin for i in 1..100000 loop insert into emp v 24.877219 124.257585 1
alues (i,
dbms_random.string('U',30), 10000+i); e
nd loop; commit; end;
As from the query you can see that the execution
time take much longer in container 4 which is pdb_hr.
CON_ID SID SERIAL# PROGRAM EVENT
---------- ---------- ----------
------------------------------------------------ -------------------------
4 59 2970 sqlplus@rhel11gr2rac1.manzoor.com
(TNS V1-V3) resmgr:cpu quantum
3 85 143 sqlplus@rhel11gr2rac1.manzoor.com
(TNS V1-V3) db file sequential read
SQL> select con_id, plan_name,
to_char(begin_time, 'HH:MI') time,
60 * (select value from v$osstat where stat_name =
'NUM_CPUS') total,
60 * (select value from v$parameter where name =
'cpu_count') db_total,
sum(cpu_consumed_time) / 1000 consumed,
sum(cpu_wait_time) / 1000 throttled
from gv$rsrcmgrmetric_history
group by con_id, plan_name, begin_time order by
begin_time;
CON_ID
PLAN_NAME TIME TOTAL
DB_TOTAL CONSUMED THROTTLED
---------- ------------------------------ -----
---------- ---------- ---------- ----------
1
PLAN_FOR_PDBS 09:10
60 60 .081 0
2
09:10 60 60
0 0
3
09:10 60 60
13.291 .987
4 09:10 60 60
11.069 48.875
1
PLAN_FOR_PDBS 09:11 60 60
1.322 0
2
09:11 60 60
0 0
3 09:11 60 60
0 0
4
09:11 60 60
10.159 38.797
1
PLAN_FOR_PDBS 09:12 60 60
.134 0
2
09:12 60 60
0 0
3
09:12 60 60
0 0
4
09:12 60
60 0 0
From the above metric we could see that container 4
is throttled and container 3 is not during the execution time.
Managing Resouce within PDB.
Its is same like managing resource in non-cdb except
below.
Non-CBD PDB
Multilevel resource plans Single level resource plan only
Upto 32 consumer groups upto 8 consumer groups
Subplans No
subplans
Subscribe to:
Posts (Atom)