Tag Archives: delete

Managed Resource Manager in Oracle

Managed Resource Manager in Oracle

Resource Manager is used for resource allocation among the many concurrent database sessions as needed. Different job need different resource allocation like ETL job, OLTP server etc
You can set at session level when particular job begins.

Terms used in Resource Manager Configuration
RESOURCE CONSUMER GROUP: Collection of user sessions that are grouped together based on resource requirements. It allocate resources on Resources consumer group not individual session. Default group present in database : SYS_GROUP (session started by SYS or SYSTEM user), DEFAULT_CONSUMER_GROUP (sessions started by user accounts), OTHER_GROUPS (sessions that belong to a consumer group that is not part of active plan)
RESOURCE PLAN: Resource Manage allocate resources to resource plan. One resource plan active at a time. child resource plan directives controls resource allocation for a different consumer group. You can create your own resource plan.
RESOURCE PLAN DIRECTIVE: Resource Manager allocates resources to consumer groups according to the set of resource plan directives.A directive can limit resource allocation to consumer group like CPU, session etc. Each directive references one consumer group.

Example of Resource plan as SALES_PLAN
One active plan SALES_PLAN, its directive having 75% CPU for OLTP Consumer group, 20% for reporting consumer group and 5% CPU for others.

Resouceplanexplain

Handle Privileges for Resource Manager

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(GRANTEE_NAME => 'SCOTT', PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER', ADMIN_OPTION => FALSE);

Creating a Resource plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',
CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 75,
CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 25);
END;
/

It will create the following resource plan:

Consumer Group	Level 1	Level 2	Level 3
--------------  ------- ------- -------
SYS_GROUP	    100%	-	    -
MYGROUP1	    -	    75%	    -
MYGROUP2	    -	    25%	    -
OTHER_GROUPS	-	    -	    100%

Check the current resource plan

SQL> show parameter resource

                                                                                                                       
NAME                                 TYPE        VALUE                                                                      
------------------------------------ ----------- ------------------------------                                             
resource_limit                       boolean     TRUE                                                                       
resource_manager_cpu_allocation      integer     6                                                                          
resource_manager_plan                string      SCHEDULER[0x4446]:DEFAULT_MAINTENANCE_PLAN

Force fully use your Resource plan until you manually change it not effected by scheduler

--Disabling Plan Switches by Oracle Scheduler Windows use force keyword it will not effected by scheduler until you manually change it.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';

Check list and meaning of jobs

SELECT plan,plan_id,status,comments from DBA_RSRC_PLANS;

View displays the currently active resource plan and its subplans.

SELECT name, is_top_plan FROM v$rsrc_plan;

NAME                             IS_TO 
-------------------------------- ----- 
DEFAULT_MAINTENANCE_PLAN         TRUE  

Check timing from the history of Resource plan

SELECT sequence# seq, name plan_name,
to_char(start_time, 'DD-MON-YY HH24:MM') start_time,
to_char(end_time, 'DD-MON-YY HH24:MM') end_time, window_name
FROM v$rsrc_plan_history;

SEQ PLAN_NAME                START_TIME      END_TIME        WINDOW_NAME        
--- ------------------------ --------------- --------------- -------------------
  1 DEFAULT_PLAN             02-AUG-18 02:08 02-AUG-18 22:08                                                                                                                            
  2 DEFAULT_MAINTENANCE_PLAN 02-AUG-18 22:08                 THURSDAY_WINDOW

Check the running status with SID and resource consumer group

SELECT se.sid sess_id, co.name consumer_group,
se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
FROM v$rsrc_session_info se, v$rsrc_consumer_group co
WHERE se.current_consumer_group_id = co.id;

Check which directives are defined for plans in your database

select plan, group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3
from DBA_RSRC_PLAN_DIRECTIVES
where plan like ('DEFAULT_%')
order by plan, mgmt_p1 desc, mgmt_p2 desc, mgmt_p3 desc;

PLAN                     GROUP_OR_SUBPLAN      MGMT_P1 MGMT_P2 MGMT_P3
------------------------ --------------------- ------- ------- -------
DEFAULT_MAINTENANCE_PLAN SYS_GROUP             75      0       0
DEFAULT_MAINTENANCE_PLAN OTHER_GROUPS          0       50      0
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK_SUB_PLAN 0       25      0
DEFAULT_MAINTENANCE_PLAN ORA$DIAGNOSTICS       0       25      0
DEFAULT_PLAN             SYS_GROUP             75      0       0
DEFAULT_PLAN             OTHER_GROUPS          0       90      0
DEFAULT_PLAN             ORA$AUTOTASK_SUB_PLAN 0       5       0
DEFAULT_PLAN             ORA$DIAGNOSTICS       0       5       0


Note: DEFAULT_PLAN they are allowed 90% and 5% respectively & DEFAULT_MAINTENANCE_PLAN allowed 75% and 25%

Check in Resource plan Scheduler

select window_name,RESOURCE_PLAN from DBA_SCHEDULER_WINDOWS;

Update a Resource Manager PLAN

Exec DBMS_RESOURCE_MANAGER.UPDATE_PLAN(PLAN => 'DAYTIME',NEW_COMMENT => '50% more CPU resources');

Delete a Resource manager plan

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'OLTP_PLAN')

Disable the resource manager

1. Set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both

2. Change the active windows to use the null resource manager plan

execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

Advertisements

Implementing Parallel DML Operations in Database

Implementing Parallel DML Operations in Database

Parallel DML Operation is disable by default. For enable fire the following command:

ALTER SESSION ENABLE PARALLEL DML;

to force parallel behaviour, regardless of the parallel degree you have placed on an object:

ALTER SESSION FORCE PARALLEL DML;

For session to run parallel DML operations:
You can run parallel session by specifying in hints or table have degree value more than 1. You can use parallel operation on all DML Statements INSERT, UPDATE & DELETE

1. Enable the session for Parallel DML operations:

ALTER SESSION ENABLE PARALLEL DML;

2. Run the parallel operation by specifying HInts in the statements:

INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;

UPDATE /*+ PARALLEL(EMP,4) */ EMP SET SAL = SAL*1.01 WHERE DEPTNO=10;

DELETE /*+ PARALLEL(EMP,4) */ FROM EMP WHERE DEPTNO=10;

If you want to delete or update large amount of data in bigger table in Enterprise edition then you can speed up with Parallel hints for the operations.

Note: Insert parallel is not work on single insert statement
Table having the trigger is not worked with parallel hints

Select, Update, Insert AND Delete Operations on a Table

SELECT STATEMENT:
SELECTING ALL COLUMNS OF THE TABLE:
A ‘SELECT’ statement is used as a DATA RETRIEVAL statement i.e. It retrieves information from the database.

SYNTAX:
SQL> SELECT * FROM TABLE NAME;
• SELECT identifies WHAT COLUMNS.
• FROM identifies WHICH TABLE.
Simply, SELECT clause specify which column is to be displayed & FROM clause specify the table containing the columns listed in the SELECT clause.
Here, ‘*’ is used to select all columns.

SELECTING SPECIFIC COLUMNS OF THE TABLE:
SYNTAX:
SQL> SELECT ENAME,JOB FROM EMP
 
SELECTING DISTINCT ELEMENTS FROM THE TABLE:
SYNTAX:
SQL> SELECT DISTINCT ENAME,JOB FROM EMP
The SELECT DISTINCT statement is used to return only distinct (different) values.It is used to remove duplicate values.

SQL> select * from empxyz;

NAME                   AGE
----------------------- ------
anju                    23
jkg                     34
anju                    23

SQL> select distinct name,age from empxyz;

NAME                    AGE
----------------------- -------
anju                    23
jkg                     34


INSERT STATEMENT:
SYNTAX:
SQL> INSERT into CSE(student,rollno) VALUES ('MONIKA',651);
INSERT statement is used to ADD NEW ROW TO A TABLE.
Using INSERT We can only insert on row at a time. As shown in above example,
In above example CSE is the name of the TABLE & STUDENT, ROLLNO are its two ATTRIBUTES.
Enclose CHARACTER & DATE values within a SINGLE QUOTATION MARKS.
 
DELETE STATEMENT:
SYNTAX:
SQL> DELETE from CSE where rollno BETWEEN 605 AND 630;
i.e. DELETE FROM table [WHERE condition];
If we OMIT WHERE CLAUSE then ALL ROWS OF THE COLUMN ARE DELETED.

UPDATE STATEMENT:
SYNTAX:
SQL> UPDATE cse SET rollno=21 WHERE student='ITIKA';
Here, If we do not use WHERE clause then ALL ROWS OF THE TABLE ARE UPDATED.
SPCIFIED ROW or ROWS are modified if we specify the WHERE clause

Managed Stored Scripts in RMAN

Managed Stored Scripts in RMAN

We can create Scripts for RMAN backup and restore operation for better and easy way to use. Scripts can be local or global.
Local Script is linked with target database and global script is registered in recovery catalog so that every database can use it from catalog.
 
Create Stored RMAN Scripts
Create Script command is used to create the stored script and stored in target database. If mention Global, then it stored in recovery catalog.

1. Connect RMAN to target database and recovery catalog

-- create a full backup script used default setting of show all command.
Create script full_backup
(
crosscheck archivelog all;
Backup database plus archivelog;
)--you can create global script with following syntax:
create global script full_backup_global
(
Backup database plus archivelog;
)

-- Create script from file
Create script full_backup from file 'C:\script\rmanbackup.txt';

Replace Stored Scripts
Replace script command is used to modified the existing script present in target/catalog database.

-- Only database backup
Replace script full_backup
(
Backup database;
)

Execute Stored RMAN Script
Execute script is command to run the script on RMAN prompt.

Run
{
EXECUTE SCRIPT full_backup;
}
--For global script:
RUN
{
EXECUTE GLOBAL SCRIPT full_backup_global;
}

List Stored RMAN Script
List script command to display the name fo script present in database.
-- List all the script from connected target database
LIST SCRIPT NAMES;
-- List global script
LIST GLOBAL SCRIPT NAMES;

--LIST ALL SCRIPT
LIST ALL SCRIPT NAMES;

Print Stored RMAN Scripts
PRINT SCRIPT Command is used for display the content of script.

-- Print the local script
PRINT SCRIPT full_backup;
-- Pring the global script
PRINT GLOBAL SCRIPT full_backup_global;

-- Pring the script to text file
PRINT SCRIPT full_back TO FILE 'C:\scripts\fullbackup.txt';

Delete RMAN Stored script
Delete command is used to delete the stored script in catalog/target database

DELETE SCRIPT 'full_backup';
DELETE GLOBAL SCRIPT 'full_backup_global';

Execute script direct from OS command prompt

Rman target / catalog rman@catdb script 'C:\script\fullbackup.txt';

 

Secure password in Scripts using Wallet feature of Oracle

Secure password in Scripts using Wallet feature of Oracle

1. Configure the Wallet and set the password for wallet:

Mkstore –wrl "wallet location" -create

Example:

Mkstore  -wrl E:\oracle\wallet –create

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -create
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:

 2. Secure the password of user which you want to use in script in wallet.

mkstore –wrl "wallet location" -createCredential

db_connection_string is an entry on your tnsnames.ora or any service name to connect to the desired database.
 
Example:

mkstore –wrl     E:\oracle\wallet  -createCredential  prod   username   password

Note:prod is an entry on your tnsnames.ora file

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -createCredential prod sys
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
Create credential oracle.security.client.connect_string1

 3. Adding the following lines in sqlnet.ora for mentioned the location of wallet.

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
(SOURCE=
(METHOD= FILE)
(METHOD_DATA = (DIRECTORY=E:\oracle\wallet)
))

 4. You can list the credential you set in wallet.

Mkstore –wrl E:\oracle\wallet -listcredential

Example:

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:   l
List credential (index: connect_string username)
1: prod sys

5. Delete the added credential from the wallet

Mkstore –wrl "wallet location" –deleteCredential

Example:

mkstore -wrl E:\oracle\wallet -deleteCredential prod

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -deleteCredential prod
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
Delete credential
Delete 1

 6. Delete the wallet created for use

mkstore -wrl “wallet location”  -delete

Example

mkstore -wrl E:\oracle\wallet –delete

C:\Windows\System32>mkstore -wrl E:\oracle\wallet -delete
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:

 7. Check wallet is deleted or not

mkstore -wrl E:\oracle\wallet –list
Use the wallet user in scripts as follows
Sqlplus /@prod

DBMS SERVICE package for managed service in single instance oracle

DBMS_SERVICE package for managed service in oracle

In Oracle, you can also create service on database through package.

For creation of Service:

exec dbms_service.create_service ( service_name => 'SERVICE1' , network_name => 'SERVICE1');

Check the service status:

select  service_id , name, network_name,creation_Date from dba_services;

Start the service:

exec dbms_service.start_Service('SERVICE1');

Stop the service:

exec dbms_service.stop_service('SERVICE1');

Delete the service:
Service must be stopped before deletion command.

exec dbms_service.delete_service('SERVICE1');

Disconnect the session from service:

exec dbms_service.disconnect_session('SERVICE1');