Oracle DBA Related Important Technique

         Important DBA Script:

========================

====Extended Data Types in Oracle Database 12c Release 1 (12.1) ======

https://oracle-base.com/articles/12c/extended-data-types-12cR1

Login as "sys" user as sysdba role
conn / as sysdba
show parameter max_string_size
MAX_STRING_SIZE----> STANDARD
shutdown immediate;
startup upgrade;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql
shutdown immediate;
startup;
showparameter MAX_STRING_SIZE
MAX_STRING_SIZE----> EXTENDED

======Change Oracle character set WE8MSWIN1252 to AL32UTF8 in 11g R2 and 12c ===

Dont forget to take full database backup before following below steps.

Login as: "sys" user

Check current character set
select * from nls_database_parameters ORDER BY PARAMETER;
NLS_CHARACTERSET----->WE8MSWIN1252
Target NLS_CHARACTERSET--->AL32UTF8

Stop the Database and startup in 'restrict' mode.
shutdown immediate;
startup restrict;

Now change to the desired character set.
alter database character set internal_use AL32UTF8;
alter database character set AL32UTF8;

Now stop the database and open as normal
shutdown immediate;
startup;
select * from nls_database_parameters ORDER BY PARAMETER;
NLS_CHARACTERSET----->  AL32UTF8

========= Kill datapump Export/Import ========

Identifying datapump jobs
Do a select from dba_datapump_jobs in sqlplus to get the job name:
> expdp system full=y

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…
expdp system full=y JOB_NAME=EXP_FULL

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       EXECUTING

Killing or stopping a running datapump job
The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!

When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…

Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes
Resuming a stopped job
Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)

SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE  
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING
Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

> expdp system ATTACH=EXP_FULL

Job: EXP_FULL
 Owner: SYSTEM
 Operation: EXPORT
 Creator Privs: TRUE
 GUID: A5441357B472DFEEE040007F0100692A
 Start Time: Thursday, 08 June, 2011 20:23:39
 Mode: FULL
 Instance: db1
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name      Parameter Value:
 CLIENT_COMMAND        system/******** full=y JOB_NAME=EXP_FULL
 State: IDLING
 Bytes Processed: 0
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp
 bytes written: 520,192

Worker 1 Status:
 Process Name: DW00
 State: UNDEFINED
(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE

Oracle: How to kill data pump jobs (Below example is for import (impdp) the same is applicable for export (expdp) )

When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too

Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs;

If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don’t press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import>kill_job

Are you sure you wish to stop this job (y/n): y

If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job   (Get the name_of_the_job using the above query)
3) Once you are attached to job, Type Kill_Job
ex: Import>kill_job

Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs

======== import key word for oracle by using imp command ======

imp system/sys123 file=HEMIS_2017_06_10.dmp log=imphemis.log fromuser=HEMIS
imp system/sys123 file=spagobi_foodmart_2017_06_10.dmp log=impspagobi.log fromuser=spagobi,foodmart
imp system/sys123 file=Faisal_12112014.dmp log=faisal.log fromuser=Faisal_hrm

======== Check tablespace in oracle =======

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by df.tablespace_name;

======== Check data file name wise size =======

set lines 100
col file_name format a70
select file_name,ceil(bytes / 1024 / 1024) "size MB",AUTOEXTENSIBLE
from dba_data_files;

========= Resize data file size =======

alter database datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' resize 5g;

====== Table wise row count ========

select table_name,
   to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) count
from user_tables
order by table_name;

=== Remote machine DB connection string from sqlplus command prompt ======

sqlplus user/pwd@//IP:1521/orcl

== Displays CPU usage for each User. Useful to understand database load by user ====

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

======= Drop Tablespace with datafile ===============

DROP TABLESPACE <TablespaceName> INCLUDING CONTENTS AND DATAFILES;

======== Export all tables without specific tables =========

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/';

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

expdp scott/tiger@dell schemas=SCOTT EXCLUDE=TABLE:\"IN\(\'BONUS\',\'SALGRADE\'\)\" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

============

GRANT READ, WRITE ON DIRECTORY test_dir TO system;
impdp system/sys123@dell directory=TEST_DIR remap_schema=scott:imam dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

====== only table import and export ==========

expdp scott/tiger@dell directory=TEST_DIR dumpfile=multi_table.dmp tables=scott.BONUS,scott.SALGRADE

impdp system/sys123@dell directory=TEST_DIR remap_schema=scott:imam dumpfile=multi_table.dmp logfile=impdpMULT.log

=======================================================

Oracle – Find consuming sql from process id

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Oracle – Disable dbms gather_stats_job or optimizer stats

To disable gather_stats_job in 10g:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

To disable gather_stats_job (also known as optimizer stats) in 11g:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Oracle – Find View Source Code

If you wish to find the source code for a view, you can look in the user_views table:

SQL> SELECT view_name, text FROM user_views;

You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).

SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;

Further Reading

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1109.htm

Categories:DBA Scripts, Oracle, SQLTags:find view definition, find view source code, oracle view, user_views, v$fixed_view_definition

Oracle – Retrieve instance name

To retrieve the instance name of the database instance you are currently connected to, you can perform a number of queries:

Non-public single-node query

SQL> SELECT name FROM v$instance;
INSTANCE_NAME
-----------------------
DEV

Non-public RAC node query

SQL> SELECT instance_name FROM gv$instance;
INSTANCE_NAME
-----------------------
DEV

Public query

SQL> SELECT sys_context('USERENV','DB_NAME') AS instance_name FROM dual;
INSTANCE_NAME
-----------------------
DEV

Oracle – MMON

MMON (Memory Monitor) is an Oracle background process that gathers memory statistics (snapshots) and stores this information in the AWR.

MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

MMON is available from Oracle 10g.

The default for recording an AWR snapshot is hourly.

Oracle – SMON

SMON (system monitor) is the Oracle background process which performs instance recovery, cleans up after unclean shutdowns and coalesces adjacent free extents into larger free extents.

SMON wakes up every 5 minutes to perform housekeeping duties, and the database instance will terminate if SMON is not running.

You can check if SMON is running from the command line in Linux/Unix:

$ ps -ef | grep pmon

Oracle – PMON

PMON is an Oracle background process created when you start the database instance. It is responsible for freeing up resources if a user process fails.

PMON normally wakes up every 3 seconds to perform housekeeping.

If PMON is not running then the database instance will terminate.

To speed up house-keeping you can wake-up PMON manually:

SQL> oradebug setmypid
SQL> oradebug wakeup 2

Oracle – Restricted Database Session

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered

Find and disconnect users connected during restricted session

Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
USERNAME LOGON_TIM PROCESS
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS      17-NOV-10 1310796
8 rows selected.

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode

If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;
LOGINS
----------
RESTRICTED

Oracle – Opatch

Opatch is an Oracle utility for the application and rolling back of interim patches to the Oracle RDBMS.  In order to use opatch it is recommended to set its location in $PATH.

Location

OPatch can be found under $ORACLE_HOME/OPatch

Current Patch History

To find the current patch history run the following:

$ opatch lsinventory [ -all ] [ -i[nvPtrLoc] <Path to oraInst.loc> ][ -oh <ORACLE_HOME>]
-all

– list all patches currently applied.

OPatch Version

$ sh opatch version
Invoking OPatch 10.2.0.4.2
OPatch Version: 10.2.0.4.2
OPatch succeeded.

OPatch Apply

$ opatch apply [-f[orce]] [-i[nvPtrLoc]<path to oraInst.log] [-m[inimize_downtime]] [-n[o_inventory]] [-oh<ORACLE_HOME>] [<patch_location>]
-force

– used to remove any conflicting patches and force the patch to be applied

-invPtrLoc

– used to locate the oraInst.loc file

-minimize_downtime

– used only in RAC instances, allowing for the remaining instances to remain up during patching. It is the responsibility of the user to shutdown each instance before applying the patches, after the last instance is patched then all instances can be brought back up.

-no_inventory

– This only works if the inventory is unavailable and allows for bypassing the inventory for reading and updates.

-oh

– Directory to be used instead of the default $ORACLE_HOME

patch_location

– Where to install the patch from

OPatch Rollback

Sometimes it is necessary to rollback a patch once it has been applied.

$ opatch rollback -id patch_id [-i[nvPtrLoc]<Path to oraInst.loc>] [-oh <ORACLE_HOME>] -pj patch_dir
-id

– The id of the patch that requires rollback

-ph

– The directory that is a valid patch area.

Categories:DBA Scripts, OPatch, Oracle, PatchingTags:OPatch, Patching Oracle RDBMS

Oracle – Check tablespace sizes

October 19, 2010Kerri RobbertsLeave a comment

One of the tasks a DBA undertakes is to monitor the amount of space consumed by the database. If a tablespace becomes spacebound than the database will freeze up as data cannot be written down to disk.

Check Tablespace Free Space

The below script will identify the available free space for each tablespace.

SELECT a.tablespace_name
, a.fileCount
, a.MaxTSBytes
, b.TSDataBytes
, a.MaxTSBytes - b.TSDataBytes FreeSpace
, ROUND((a.TotalFilesizeMB/a.MaxTSBytes)*100,2) DFAlloc
, ROUND((b.TSDataBytes/a.MaxTSBytes)*100,2) AS DATAAlloc
FROM
(SELECT tablespace_name
, COUNT(*) filecount
, SUM(CASE WHEN maxbytes > bytes THEN maxbytes/1048576 ELSE bytes/1048576 end) AS MaxTSBytes
, SUM(bytes)/1048576 TotalFilesizeMB
FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name
, SUM(bytes)/(1048576) AS TSDataBytes
FROM dba_extents GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 1
/
TABLESPACE_NAME FILECOUNT MAXTSBYTES TSDATABYTES FREESPACE  DFALLOC DATAALLOC
--------------- --------- ---------- ----------- ---------  ------- ---------
INTERFACE       13        22250      21906.4375  343.5625   100      98.46
LOAD            6         8550       7629.125    920.875    100      89.23
MLOG            1         1250       617.5625    632.4375   100      49.41
NOTES           3         4200       3728.6875   471.3125   100      88.78
SYSTEM          1         500        406.625     93.375     100      81.33
UNDO            3         4500       323.085938  4176.91406 100       7.18
XDB             1         100        44.9375     55.0625    100      44.94
6 rows selected.

Query Datafile Sizes

The below script queries how much free space is available per datafile. In this example only the system tablespace has been queried:

SELECT a.file_id,
SUBSTR(a.tablespace_name,1,10) tablespace,
a.autoextensible,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) MaxFileSize,
a.bytes/1048576 filesize,
(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 ELSE bytes/1048576 END) - nvl(b.usedbytes,0) free_Mb,
round(100*(1-(nvl(b.Usedbytes,0))/(CASE WHEN a.maxbytes > a.bytes THEN a.maxbytes/1048576 else bytes/1048576 END)),2) "%_FREE",
a.file_name
FROM  dba_data_files a,
(SELECT file_id
, sum(bytes)/1048576 Usedbytes
FROM dba_extents GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND a.tablespace_name = '&tablespace_name'
ORDER BY
a.tablespace_name,
a.file_id
/
FILE_ID TABLESPACE AUT MAXFILESIZE FILESIZE FREE_MB %_FREE FILE_NAME
------- ---------- --- ----------- -------- ------- ------ ---------
1       SYSTEM     NO  500         500      93.375  18.68 /u05/oracle/oradata/SYSTM_01.dbf

Once you have found the file_id for the datafile in the tablespace that needs expanding then you can increase the size of the tablespace.