Archive for the ‘Uncategorized’ Category

Setting up Instance Caging

Posted: July 3, 2015 in Uncategorized

A great way to limit CPU on a shared database server is by using instance caging, below are the steps to implement instance caging.

1. Check to see if instance caging is enabled

SQL> select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed='ON';
no rows selected

Its not enabled

2. Set the CPU count

Set the cpu_count to what figure your require to limit the CPU in the instance to

SQL> alter system set cpu_count = 4;
System altered.

3. Enable instance caging

SQL> alter system set resource_manager_plan = 'default_plan';
System altered.

4. Check to see if instance caging is enabled

SQL> select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed='ON';
NAME
--------------------------------
DEFAULT_PLAN

You have now enabled instance caging limiting your database to a maximum of 4 cpu’s to use

Changing Default profile limits

Posted: October 23, 2013 in Uncategorized

How to change the default profile limits useful for setting the password life time to stop the password timing out and asking the user to change the password especially when applications are connecting to the DB.

SYSTEM@WEBDB1> @qprofile
Enter value for 1: DEFAULT
old   1: select * from dba_profiles where profile =upper(‘&1’)
new   1: select * from dba_profiles where profile =upper(‘DEFAULT’)

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7

16 rows selected.

SYSTEM@WEBDB1> alter profile default limit password_life_time unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit failed_login_attempts unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit password_lock_time unlimited;

Profile altered.

SYSTEM@WEBDB1> alter profile default limit password_grace_time unlimited;

Profile altered.

SYSTEM@WEBDB1> @qprofile
Enter value for 1: DEFAULT
old   1: select * from dba_profiles where profile =upper(‘&1’)
new   1: select * from dba_profiles where profile =upper(‘DEFAULT’)

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
—————————— ——————————– ——– —————————————-
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

SYSTEM@WEBDB1>

Welcome to my blog, this is going to be a site dedicated to tools and tips for Oracle DBA’s! it will be mainly a place for me to store all my sql scripts I have created over the years but i’m sure they will be helpful to many a DBA.