Managing Resources

Create Resource Management

Usefull tables:
1. v$session column "resource_consumer_group"
2. dba_rsrc_consumer_groups
3. dba_rsrc_consumer_group_privs
4. dba_rsrc_group_mappings
5. dba_rsrc_plan_directives
6. dba_rsrc_plans

In oracle database,there exists package dbms_resource_manager which helps for managing resources as the name says itself.
For the first time you should create pending area,in which resource management objects should be defined and validated before they are activated.

--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--create resource consumer groups
*Note: There exist 3 parameters that can be defined during group creation:
1.CONSUMER_GROUP->name of the group
2.COMMENT
3.CPU_MTH->ROUND_ROBIN(default,fair distribution),RUN_TO_COMPLETION(most active sessions are ahead)
SQL>exec dbms_resource_manager.create_consumer_group(consumer_group => 'session_limit',comment => 'limit group');

--map user in that group
SQL>exec dbms_resource_manager.set_consumer_group_mapping(attribute => 'CLIENT_OS_USER',value=> 'TEST_USER', consumer_group => 'session_limit');
*Note:There exists other options,not just for users,but also for : CLIENT_PROGRAM, CLIENT_MACHINE,ORACLE_USER...

--create plan
SQL>exec dbms_resource_manager.create_plan('session_limit_plan',comment => 'limit plan');

--create plan directive for special consumer group
SQL>exec dbms_resource_manager.create_plan_directive(plan => 'session_limit_plan', group_or_subplan => 'session_limit',comment => 'Limit Session time',switch_group => 'kill_session',switch_time => 60 );
*Note:In this case session assigned to that group will have execution time limit 60sec and after exceeding this number session will be killed(You can assign following values also:CANCEL_SQL,another group name)

It is essential to create plan directive for OTHER_GROUPS(group which is assigned to the object when there exists no active group for that object)

--create plan directive for other_groups
SQL>exec dbms_resource_manager.create_plan_directive(plan=> 'session_limit_plan',group_or_subplan => 'OTHER_GROUPS',comment=> 'no comment');

--validate pending area(submit also validates but for debugging it is better to validate before submitting )
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area(validates,submits,clears pending area)
SQL>exec dbms_resource_manager.submit_pending_area;

--enable resource manager
SQL>alter system set resource_manager_plan = 'session_limit_plan' scope = both;
*Note: if you see v$session view like that:
SQL>select s.resource_consumer_group
from v$session s
where s.username='TEST_USER'
You will see that the value is OTHER_GROUPS, to switch to our desired group,follow these steps:
-----------------------------------Start of Variant 1------------------------------------------
--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--grant switching
SQL>exec
dbms_resource_manager_privs.grant_switch_consumer_group('test_user','session_limit',false);

--setting initial group
SQL>exec dbms_resource_manager.set_initial_consumer_group('test_user','session_limit');

--validate pending area
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area
SQL>exec dbms_resource_manager.submit_pending_area;
-----------------------------------End of Variant 1------------------------------------------

-----------------------------------Start of Variant 2------------------------------------------
--create pending area
SQL> exec dbms_resource_manager.create_pending_area();

--switch between consumer
SQL>exec dbms_resource_manager.switch_consumer_group_for_sess (session_id => '134', session_serial=> '4699', consumer_group => 'session_limit');

--validate pending area
SQL>exec dbms_resource_manager.validate_pending_area;

--submit pending area
SQL>exec dbms_resource_manager.submit_pending_area;
-----------------------------------End of Variant 2------------------------------------------

Delete Resource Management

--deactivate resource manager
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='' scope = both;

--create pending area
SQL>exec dbms_resource_manager.create_pending_area();

--delete plan
SQL> exec dbms_resource_manager.DELETE_PLAN ('SESSION_LIMIT_PLAN');

--delete consumer group
SQL> exec dbms_resource_manager.delete_consumer_group(consumer_group=>'SESSION_LIMIT');

--submit pending area
SQL> exec dbms_resource_manager.submit_pending_area;

Hints

Real World Scenario:

1.  Direct select from database tables performed in 8sec,while linked select(I mean select from linked database) needed much more time to be performed...The problem was that first select was using nested loop and another one hash join (execution plan can be seen by pressing F5).For solving this problem there exists hint for select,in this case the hint is to use not hash join but nested loop,syntax is the following: /*use_nl(table1,table2,table3...)*/

select /*+use_nl(table1,table2,table3...)*/ .........
from table1,table2,table3........



2. Hints to use "indx_name" index. on "table1 " table.
select /*+index(table1 indx_name) ...*/ .........
from table1,table2,table3........


3. The ordered hint requests that the tables should be joined in the order that they are specified in the from clause
select /*+ ordered */ .........
from table1,table2,table3........


4.The rule cancels any other hints specified for this statement.
select /*+ rule */ .........
from table1,table2,table3........


5.The full(table) tells the optimizer to do a full scan of the specified table.
select /*+ full(table1)*/ .........
from table1,table2,table3........


6.cache specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
select /*+ cache */ .........

from table1,table2,table3........

7.no_cache specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

select /*+ no_cache*/ .........

from table1,table2,table3........

8.first_rows use the cost based approach for best response time.

select /*+ first_rows */ .........

from table1,table2,table3........

9.all_rows use the cost based approach for best throughput.

select /*+ all_rows */ .........

from table1,table2,table3........


10.append , noappend Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.

select /*+ append*/ .........

from table1,table2,table3.......

Log Miner

This is the utility for reading archive log files.As you know archive redo log files saves all the data needed for database recovery and record all changes (INSERT,DELETE,UPDATE) made to the database.
*It gives undo and redo sqls.

1.You should create dbms_logmnr package,the script for creating this is saved in %ORACLE_HOME%\RDBMS\ADMIN\dbmslmd.sql.
To run this:
SQL>@%ORACLE_HOME%\RDBMS\ADMIN\dbmslmd.sql

2.Define exists or not and where exists archive log files,for future registering:
SQL>select name from v$archived_log;
and copy full path of archive log files....

3.Build log miner:
SQL>execute dbms_logmnr_d.build('dictionary.ora','c:\oracle\product\10.2.0\database');
*dictionary.ora file is for defining sql statements (not to be written they in hexadecimal).

4.Register these archive log files with log miner.
SQL>execute dbms_logmnr.add_logfile('c:\oracle\product\flash_recovery_area\mfd\archivelog\2010_08_18\o1_mf_1_1613_66pw6rmd_.arc',dbms_logmnr.addfile);
SQL>execute dbms_logmnr.add_logfile('c:\oracle\product\flash_recovery_area\mfd\archivelog\2010_08_18\o1_mf_1_1614_66pw6zxm_.arc',dbms_logmnr.addfile);
SQL>execute dbms_logmnr.add_logfile('c:\oracle\product\flash_recovery_area\mfd\archivelog\2010_08_18\o1_mf_1_1612_66pw5k2f_.arc',dbms_logmnr.addfile);

5.Make sure that you have registered correctly logs for mining.
select log_id, filename from v$logmnr_logs;

6.Start log miner:
SQL>execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

7.As v$logmnr_contents is a temporary view, once you disconnect your session , you won't be able to see the content.So let's create the table for saving it permanently:
SQL>create table logmnr_table as select * from v$logmnr_contents;

8.Stop logminer:
SQL>execute dbms_logmnr.end_logmnr();

Tracing

It is difficult to trace individual process when will it be finished.For this I suggest you some useful tricks:
In v$session there is the column called "action" which shows,as you guess,the action of the certain session.
Method DBMS_APPLICATION_INFO.SET_ACTION() will set desired action to this session which will be shown in v$session "action" column.

I used this in inserting multiple rows in a table.
begin
for i in 1..100000000
loop
insert into test_tb_big(name,surname)
values('mari','kupatadze');
DBMS_APPLICATION_INFO.SET_ACTION(i);
end loop;
end;
And at this time i was able to see i's value in v$session "action" column.Useful for understanding when this insert is supposed to be finished.

*There is also DBMS_APPLICATION_INFO.SET_MODULE() function.

Initialization Parameters

BACKGROUND_DUMP_DEST->describes the location where background process trace files are.

USER_DUMP_DEST->location of user session trace files are/(will be) located

MAX_DUMP_FILE_SIZE->maximum size for trace file.The default value for this parameter is UNLIMITED which means no restrictions on trace file size.This parameter applies for all types of trace files.If a value is specified for this parameter, it must be a numeric value, optionally suffixed with the letter K (kilobytes) or the letter M ( megabytes). If no suffix is provided means number of blocks.

*NOTE
Some parameters are not modifiable by alter session set command.To determine this you should query the view $parameter and see the column named "ISSYS_MODIFIABLE" appropriate values are false or true.If the value is false you should open database:
SQL>startup open;
SQL>create pfile from spfile;
edit data in newly created pfile save it and shutdown database:
SQL>shutdown immediate;
start database by non default parameter file:
SQL>startup pfile=(path of newly created pfile) ;
SQL>create spfile from pfile;
SQL>shutdown immediate;
startup normally:
SQL>startup;

Or  Simply,if it works:

SQL>alter system set parameter_name=value/'value' scope=spfile;

SQL>shutdown immediate;

SQL>startup;

Resize logfile

There doesn't exist direct way for resizing logfile you must:
Logfile,to be dropped,should not be CURRENT(written by LGWR process) or ACTIVE(needed for instance recovery,means that it is now being archived).It must be in INACTIVE state.If logfile is in CURRENT state run this command to make it ACTIVE:
SQL>alter system switch logfile;

see the status:
select * from v$log

At this moment it is ACTIVE if we wait for a while(or manually make it inactive by runing ALTER SYSTEM CHECKPOINT; command which will force logfile to be archived ),ARCn will archive this logfile and its current status will be INACTIVE,at this moment this logfile can be dropped.
*NOTE i have 3 group and one member in each group.
SQL> alter database drop logfile group 1;

Add new one,with different size:
SQL> alter database add logfile group 1 'C:\oracle\product\oradata\MFD\REDO01.LOG' size 500m reuse;

*NOTE that real file is not deleted and that is why i used the command reuse.

See the size:
select t.bytes/1024/1024 from v$log t

To see percentage of used space in current logfile:

SELECT le.leseq "Current log sequence No",

100*cp.cpodr_bno/le.lesiz "Percent Full",

cp.cpodr_bno "Current Block No",

le.lesiz "Size of Log in Blocks"

FROM x$kcccp cp, x$kccle le

WHERE le.leseq =CP.cpodr_seq

AND bitand(le.leflg,24) = 8

Error Handling

ORA-12518: TNS:listener could not hand off client connection tips
Start->Run->services.msc->OracleServiceMFD(instance name) manually start it
-------------------------------------------------------------------------------------------------
ORA-01034: ORACLE not available
The database and the instance are not started.
SQL>startup;
-------------------------------------------------------------------------------------------------
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
SQL>alter system disable restricted session;
-------------------------------------------------------------------------------------------------
ORA-01033: ORACLE initialization or shutdown in progress
database in mount or nomount mode
if it is in nomount:
   SQL>alter database mount;
   SQL>alter database open;
if it is in mount:
   SQL>alter database open;
-------------------------------------------------------------------------------------------------

Log file switch (checkpoint incomplete) * increase the size of the redo logs * add more redo log groups

See free and allocated space in datafiles

Useful data dictionary tables:
1.dba_free_space
2.dba_data_files

SELECT a.tablespace_name,a.file_name,a.bytes/1024/1024 allocated_mb,b.free_mb
FROM dba_data_files a,
(SELECT file_id, SUM(bytes/1024/1024) free_mb
FROM dba_free_space
GROUP BY file_id) b
WHERE a.file_id=b.file_id

Usefull Tables

DBA_OBJECTS
OBJECT_NAME
OBJECT_TYPE ->table,index,sequence,table partition,procedure,pakage,view,function,synonym,job...
CREATED
LAST_DDL_TIME
STATUS ->valid...
...and some other columns
DBA_SEGMENTS
SEGMENT_NAME ->table name or other
SEGMENT_TYPE ->nested table,index,table,cluster
TABLESPACE_NAME
BYTES ->size of that segment(for exmaple size of the table)
...and some other columns
DBA_TABLESPACES
TABLESPACE_NAME
BLOCK_SIZE
STATUS ->online,offline...
CONTENTS ->undo,permanent
LOGGING ->logging,nologging
RETENTION ->not apply,noguarantee
BIGFILE->no,yes
...
v$log

GROUP# ->group number
SEQUENCE# ->sequence number
BYTES ->size
MEMBERS ->amount of members
ARCHIVED ->archived or not
STATUS ->active,current,inactive
.....
v$logfile
GROUP# ->group number
STATUS ->
TYPE->ONLINE,OFFLINE
MEMBERS ->destination of the log member

v$sql
SQL_TEXT
SQL_FULLTEXT
SQL_ID
FIRST_LOAD_TIME
....
v$session
SADDR
SID
USER#
USERNAME
STATUS->active,inactive
SERVER->shared,dedicated
SCHEMA#
SCHEMANAME
OSUSER->operating system user
MACHINE->name of machine
TYPE->user,..
SQl_ADDRESS
SQL_ID->id of executed/executing sql value you can find in v$sql data dictionary view
PREV_SQL_ADDR
PREV_SQL_ID
LOGON_TIME

DBA_TABLES
OWNER
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
STATUS->valid,invalid..
PCT_FREE
PCT_USED
LOGGING
BACKED_UP->N/Y
NUM_ROWS
LAST_ANALYZED-> date
PARTITIONED->NO/YES
TEMPORARY
NESTED
ROW_MOVEMENT
SKIP_CORRUPT->enabled,disabled(if there exists corrupt block this option allows to skip it during query,if there exist and the value of this column is disabled than there will be ORA-01578: ORACLE data block corrupted )
DROPPED

Uninstall Oracle

1.Uninstall all Oracle components using the Oracle Universal Installer (OUI).
2.Run regedit.exe and delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE. This contains registry entires for all Oracle products.
Delete any references to Oracle services left behind in the following part of the registry:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*
It should be obvious which ones relate to Oracle.
3.Reboot your machine.
4.Delete the "C:\Oracle" directory, or whatever directory is your ORACLE_BASE.
5.Delete the "C:\Program Files\Oracle" directory.
6.Empty the contents of your "c:\temp" directory.
7.Empty your recycle bin.

Manually Deleting Database
C:\>sqlplus sys/password as sysdba
SQL>startup mount;
SQL>drop database;

Install Oracle10g on Windows XP and Create the Database&Install patch on it

Go to the link: http://www.oracle.com/technetwork/database/10201winsoft-095341.html
and choose the product.

Run Oracle Universal Installer.
Steps:
1. Advanced Installation->Next
2. Enterprise Edition(631MB)->Next
3. Name : Ora10g
Path :C:\oracle\product\10.2.0
->Next
*At this moment there might happen errors.If the error message is the following :'The install has detected that primary IP address of the system is DHCP-assigned....' This means that you have obtained an IP address automatically..you should change it manually.After solving this problem you should press retry button and continue the installation.
4.Install database Software only->Next->Install->Exit->Yes.
Oracle is installed.

Creating database...

Steps:
1.Start->All Programs->Oracle - Ora10g->Configuration and Migration Tools->Database Configuration Assistant.->Next
2.Create Database->Next
3.General Purpose->Next
4.Global Database Name:MFDB
SID :MFDB
->Next
5.->Next(Let's leave default settings)
6.Password :******
Confirm Password:******
->Next
7.File System->Next
8.Use Database File Locations from Template->Next
9.->Next
10.If you want HR,SCOTT schemas to be installed you should check "Sample Schemas"->Next
11.->Next
12.->Next
13.->Finish->OK
*At this moment there may happen an error,if the error message is the following:"..credential retrieval failed".In this case you should change sqlnet.ora file content from SQLNET.AUTHENTICATION_SERVICES= (NTS) to SQLNET.AUTHENTICATION_SERVICES= (NONE).
*NTS means that OS accounts can connect to oracle as sysdba without specifying password.
*NONE means that no one can connect to oracle without specifying password.

Because of tnsnames.ora and listener.ora files are not configured during this installation.They should be configured manually,more correctly,with the help of Net Configuration Assistant.
Steps:
1.Start->All Programs->Oracle - Ora10g->Configuration and Migration Tools->Net Configuration Assistant.
2.Listener configuration->Next
3.Add->Next
4.Listener name:LISTENER
->Next
5.->Next
6.->Next
7.->Next
8.->Finish

Now create tnsnames.ora file:
1.Start->All Programs->Oracle - Ora10g->Configuration and Migration Tools->Net Configuration Assistant.
2.Local NetService Name configuration->Next
3.Add->Next
4.Service Name:MFD
->Next
5.TCP->Next
6.Host name:your host name(computer name)->Next
7.->Next
8.Net Service Name:MFD
->Next
9.->Next->Next->Finish

Oracle is ready to be accessed. If you find it difficult to connect to oracle with system/pass as sysdba you should connect to oracle by sys/pass as sysdba and type the command: grant sysdba to system;

Upgrade Oracle from 10.2.0.1 To 10.2.0.4

I had my post about this,but found another more helpful and easy understandable one from Babu B 's blog .Here it is:


1. Patch Set Overview

Patch set release 10.2.0.4. Before installing this patch set you must be need 10.2.0.1 version.

2. Requirements

Oracle DB : Oracle 10.2.0.1 (later)
Operating System: Windows


3. Pre - Installation Tasks

1. Identify prior installation

Before installing this patch you must install oracle 10.2.0.1 (or later version)

2. Download Patch set

Download 6810189 patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.

3. Shutdown oracle database.

C:\>set oracle_sid= GOLDLINK

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys@GOLDLINK as sysdba
Enter password:
Connected.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Stop all services

C:\>set oracle_sid=GoldLink

C:\>net stop OracleDBConsoleGOLDLINK
The OracleDBConsoleGOLDLINK service is stopping................
The OracleDBConsoleGOLDLINK service was stopped successfully.


C:\>net stop OracleServiceGOLDLINK
The OracleServiceGOLDLINK service is stopping.
The OracleServiceGOLDLINK service was stopped successfully.


C:\>net stop OracleOraDB10g_Home1iSQL*Plus..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

C:\>net stop OracleOraDB10g_Home1TNSListener
The OracleOraDb10g_home1TNSListener service is stopping.
The OracleOraDb10g_home1TNSListener service was stopped successfully.

Note: while patch installation time If you face Distributed Transaction Coordinator still running. You should be stop this service

C:\>net stop msdtc
The Distributed Transaction Coordinator service is stopping.
The Distributed Transaction Coordinator service was stopped successfully.

4. Backup your database.

Oracle recommends that you create a backup of the Oracle 10g installation before you install the patch set.

5. Check Tablespace Sizes and Set Parameter Values

Review the following sections before upgrading a database.

8. Upgrade the Database

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

1. Start all services

2. Connect sys user

C:\> sqlplus /NOLOG

SQL> CONNECT SYS/SYS_password AS SYSDBA

3. Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql

SQL> SPOOL OFF

Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.

This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems.

4. Restart the database:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

5. Compile Invalid Objects

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> select * from v$version;

create ADDM(Automatic Database Diagnostic Monitor) report manually

ADDM is an adviser,which detects problems and gives the recommendations. It uses statistical data from AWR,which is stored in sysaux tablespace.These statistical data is stored in the disk by snapshots in every hour(but you can change this parameter which will be shown later) and the retention period by default is 7 days.Be careful because the parameter statistics_level should not have the value "basic"(default is "typical").AWR report which we have discussed earlier gives more detailed information than ADDM report.

To create snapshot manually:

SQL>exec dbms_workload_repository.create_snapshot;

To change retention period(default 7 days) to 3 days and interval(default 1 hr) to 20 minutes:

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>4320,interval=>20);

To create report:

C:/> cd %ORACLE_HOME%
C:/> cd rdbms\admin

SQL>@addmrpt.sql
.
.
Enter value for begin_snap:1
.
.
Enter value for end_snap:7
.
.
Enter value for report_name: --default is addmrpt_1_1_7.txt

create AWR report manually

*Create snapshot manually

exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();

*Create AWR report

C:\>cd %ORACLE_HOME%
C:\>cd RDBMS
C:\>cd ADMIN
C:\>sqlplus / as sysdba
SQL>@awrrpt.sql
.
.
Enter value for report_type: html --or text
.
.
Enter value for num_days: --without specifying the number lists all completed snapshots.
.
.
Enter value for begin_snap:1
.
.
Enter value for end_snap:4
.
.
Enter value for report_name:awrrpt_1_4.html

Use expdp(data pump export ) & impdp utilities

These utilities are useful for moving data from one database to another database..Here are the steps how to do this:


Exporting:
1. SQL> create directory expdp_dir as 'C:\dump_exports';
2. SQL> grant read,write on directory expdp_dir to hr,user1,user2;
3.C:\>expdp hr/hr tables=table1,table2,table3 directory=expdp_dir dumpfile=tables_exp.dmp logfile=tables_exp.log

*directory->location where log and dump files are located

Importing:
1. C:\>impdp hr/hr tables=table1,table2,table3 directory=expdp_dir dumpfile=tables_exp.dmp logfile=tables_imp.log

remap_tablespace option is useful if we don't have the tablespace which was used during the export,in this case you can use remap_tablespace=EXAMPLE:USERS and the contents will be imported from example tablespace into users tablespace

Note that: this utilities are similar to exp&imp but:

• expdp&impdp are server-based when exp&imp are client-based.
• dump files generated by exp can not be imported by impdp and vice-versa.