BuddhiBase

Oracle DBA in 5 minutes, with the below commands guide 

Disclaimer 

• Thumb rule - Do it* at your own risk. 
  
• *it = Any Changes/Steps/Activities/Commands/DIY/Tutorials/Hacks that are mentioned in this blog or blog entries.

• These steps/changes (it*) may not be supported by the respective products and/or companies, unless explicitly specified somewhere. Please check for yourself.
• All these are for personal use, (tinkering / hacks). I don't claim any of these failproof.
  
• Do take, backup of your systems i.e application, database, etc., before venturing into any changes.
  • Stop systems before any changes
  • Start systems after any changes

How to find the location of the Oracle DB

• cat /etc/oratab
• Find out the location and SID
  
• Find the file owner of that location by "ls"
• All the below operations has to be performed by that owner only.
  

How do you bring up (start) your listener and db

• Login to the machine (using ssh or putty), sudo as necessary (sudo su - tobeUsername)
  
• cd to oracle/db_home (as per the earlier find)
• . oraenv
  
• enter oracle SID to set your environment variables
  
• cd bin
• ./lsnrctl start
• ./sqlplus "/ as sysdba"
• SQL>startup;
• exit;

How do you search if a table/constraint/view/procedure exists in oracle DB

•  select * from all_objects where lower(object_name) like '%%'
• Other tables to query (do a desc tableName)
  
  • all_tables
  • all_tab_columns
    
  • all_constraints
  • all_cons_columns 

How to check the contents of a view ?

• select text from all_views where view_name like '%%';

How to find DDL (Create table SQL) for a particular object

• select dbms_metadata.get_ddl(object_type, object_name, owner)  from all_objects where lower(object_name) like '%%'
  

How to shutdown a database

Safe way

• cd oracle_home/bin
• . oraenv
• Enter oracle SID to set your enviornment variables
  
• ./sqlplus "/ as sysdba"
  
• shut immediate;

Not Safe - for abnormal termination!

• To find the process - ps -aef | grep pmon
• Issue a kill command - kill -9 processId_pid 
• To kill all DB Process -
  
  • ps aux | grep -ie pmon | awk '{print $2}' | xargs kill -9 
    

List all users of a database

• select username from dba_users
  

Current database properties and files associated

• select * from v$database;
• select name, value from v$parameter where name like '%%'
  • select name, value from v$parameter where lower(name) in ('session_cached_cursors','log_archive_dest_1','pga_aggregate_target','remote_login_passwordfile', 'aq_tm_processes', 'processes','log_buffer','session_cached_cursors','job_queue_processes','open_cursors','shared_pool_size','sga_target', 'db_securefile') order by name ;
    
• show parameter control_files;
• select * from dba_directories;
  
• select * from v$tablespace;
• select * from v$instance;
  
• select * from dba_tablespaces;
  
• select * from v$database; - current schema/db information
• select distinct SEGMENT_TYPE from dba_segments;
• select distinct CONTENTS from dba_tablespaces;
• select * from dba_data_files;
• select * from dba_temp_files;
  
• select * from v$log;
• select * from v$logfile;
• select * from v$log_history;
• select * from v$tempfile;
• select file_name,bytes,autoextensible from dba_temp_files;
• select decode(value, null, 'pfile', 'spfile') "init file type" from sys.v_$parameter where name = 'spfile';

P.S Table names prefix v_$ = v$

DB Software version and components information

• select banner from sys.v_$version;
• select * from sys.v_$version;
• select * from v$option;
  
• select comp_name, status from dba_registry;

How to check, if DB is spatial enabled

• select * from user_sdo_themes

How to find out  privileges for my object ?

• select * from table_privileges (for table)
• select * from all_objects where lower(object_name) like '%_privileges'  (to find out all privileges tables/views)
  

How to find constraints and synonyms of my objects ?

• select * from all_constraints or all_cons_columns
• select * from dba_synonyms where TABLE_OWNER not in ('MDSYS','SYSTEM','ORDSYS','DBSNMP','XDB','APPQOSSYS','SYS','WMSYS')  and OWNER=SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
  

How to find the history of SQL statements executed on a DB

• select * from v$sqlstats
  

How to find out all DB Connected Sessions ...

• select sid,serial#,username,osuser,TO_CHAR(logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,program,machine,status from v$session;

How to kill all sessions

alter system enable restricted session;
begin    
    for x in ( 
            select Sid, Serial#, machine, program from v$session   where upper(machine) <> 'hostName'
        ) loop 
        execute immediate 'Alter System Kill Session '|| x.Sid  || ',' || x.Serial# || ''' IMMEDIATE'; 
    end loop; 
end;
alter system disable restricted session;

How to find out all db schema's

• select distinct owner from  dba_segments where owner not in ('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP');

What is DBSNMP and why its used ?

DBSNMP is a default DB User/Schema. The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Password is created at installation or database creation time. For more info check http://www.orafaq.com/wiki/List_of_default_database_users

How to monitor a DB from OEM Oracle Enterprises Manager ?

Go to DB and perform the below before installing or configuring the DB plugin. The below will create DBSNMP user/schema for oem purpose.

    SQL> shutdown;
    SQL> startup;
    SQL> conn / as sysdba;
    SQL> alter user sysman identified by new_password;
    SQL> alter user dbsnmp identified by new_password;
    SQL> alter user sysman account unlock;
    SQL> alter user dbsnmp account unlock;
    SQL> shutdown;
    SQL> startup;
    To Deletes the SNMPAgent role and DBSNMP user
        SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
    Recreation Script
        SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql
    SQL> shutdown;
    SQL> startup;

What are pfile and spfile 

These are Initialization Parameter files. A PFILE is a static, client-side text file editable by a text editor. An SPFILE (Server Parameter File) is a binary file (which cannot be edited), for SPFile change you need use "ALTER SYSTEM SET".

How to create pfile and spfile from SQL prompt ?

• create pfile from spfile;
• create spfile from pfile;
  

How to find the pfile/spfile file locations ?

• cat /etc/oratab
• find dbhome directory
• cd dbhome/dbs
• backup and edit files as necessary.
  

How to find what parameter file server if using ?

•  select decode(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

How to find out DB Statistics and health 

• select * from all_ind_statistics
  • select * from all_tab_statistics where owner='?' and stale_stats ='yes' order by last_analyzed desc;
    
• select * from all_tab_statistics
  

Find all db links

• select * from all_db_links
  

How to find oraInventory location

• cat /etc/oraInst.loc | grep inventory_loc

How to change oraInventory (do it at your own risk)

• Find oraInventory location
• Edit oraInventory/ContentsXML/inventory.xml
  

What is archive mode  (ARCHIVELOG) ? why its used.

With out bringing down the database, you can backup a database using this option. 

Also, If you have enabled this on your Database, it will help you backup/recover database at any given instance of time. Development database tends to change over time, and if there is any failure we can recover it - provided the database is in ARCHIVELOG. Archive log will generate many auto backup files and it will be stored @ the below locations

select name, value from v$parameter where lower(name) in ('log_archive_dest_1','log_archive_dest_2', 'log_archive_dest_3') order by name ;

 How do you disable archivelog (make it NOARCHIVELOG)

• Shutdown database
• edit pfile parameter and set log_archive_start = false
• startup mount;
• alter database noarchivelog;
• alter database open;
• shutdown immediate;
• startup;

How to backup database (export) ?

You need to create a data dump dir before you export/import using the below SQL. 

• create directory dump_dir as '/data/dumps';
• expdb system/sysman00 full=Y dumpfile=expdp_${ORACLE_SID}_NAME_`date +%Y%m%d`.dmp logfile=expdp_${ORACLE_SID}_OEMDEVDB_`date +%Y%m%d`.log directory=dump_dir
  

How to restore a backup (import) ?

• impdp user/password@SID schemas=SchemaName DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_***.dmp LOGFILE=expdp_***.log

P.S impdb and expdb are available at $ORACLE_HOME/bin

How to find dbf/control files or oradata folder ?

• Run SQL - show parameter control_files ; OR
• Find for file (under product directory) - control*.ctl

You can take the backup of oradata folder and restore DB when required ! , you need spfile/pfile too

How to find, what locale, language, Unicode (character set) support is enabled in a database ?

• select * from v$nls_parameters;

If you are looking for a handy guide on DB administration - this can help you.

Tweet