Day to Day Commands in DBA Life

Database and Instance Information:

set lines 300
set pages 300
col HOST_NAME for a35
col STARTUP_TIME for a20
col STATUS for a6
select name,instance_name,status,open_mode,database_role,log_mode,to_char(STARTUP_TIME,'MM-DD-YYYY HH24:MI:SS') "STARTUP_TIME",host_name,status from v$instance,v$database;

Session Information:

set lines 300
set pages 300
col USERNAME for a12
col OSUSER for a12
col STATUS for a10
col logon_time for a20
col MACHINE for a20
col TERMINAL for a15
col PROGRAM for a15
col MODULE for a15
col ACTION for a10

select sid,serial#,username,osuser,process,status,sql_id,to_char(logon_time,'MM-DD-YYYY HH24:MI:SS') "LOGON_TIME" from v$session WHERE USERNAME='&USERNAME';

select sid,MACHINE,TERMINAL,PROGRAM,MODULE,action,TYPE from v$session;

select sid,sql_id,SQL_ADDRESS,SQL_HASH_VALUE,SQL_EXEC_START,PREV_HASH_VALUE,PREV_EXEC_START,substr(CLIENT_INFO,20,20),SQL_TRACE from v$session;

select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session;

select sid,to_char(logon_time,'MM-DD-YYYY HH24:MI:SS') "LOGON_TIME",LAST_CALL_ET,EVENT,p1,p2,p3,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session;

alter session set current_schema = schema name;

select * from v$resource_limit where resource_name in ('processes','sessions');

select reason from dba_outstanding_alerts;

User Information:

set lines 300

col USERNAME for a10

col ACCOUNT_STATUS for a18

col DEFAULT_TABLESPACE for a15

col TEMPORARY_TABLESPACE for a15

col PROFILE for a10

select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,to_char(CREATED,'MM-DD-YYYY HH24:MI:SS') "CREATED",PROFILE from dba_users where username='&USERNAME';

set linesize 125

col USERNAME for a10

col ACCOUNT_STATUS for a15

col DEFAULT_TABLESPACE for a15

col TEMPORARY_TABLESPACE for a15

col PROFILE for a10

select USERNAME,ACCOUNT_STATUS,to_char(LOCK_DATE,'YYYY-MM-DD HH24:MI:SS') "LOCK_DATE",to_char(EXPIRY_DATE,'YYYY-MM-DD HH24:MI:SS') "EXPIRY_DATE",to_char(CREATED,'YYYY-MM-DD HH24:MI:SS') "CREATION_DATE",profile,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where USERNAME like upper('&USERNAME%') order by 1;

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c from dba_users d, sys.user$ u

where d.username = upper('&&username')

and u.user# = d.user_id;

   

Enter value for username: scott

old   3: where d.username = upper('&&username')

new   3: where d.username = upper('scott')

C

--------------------------------------------------------------------------------

alter user "SCOTT" identified by values 'F894844C34402B67';

SQL> alter user "SCOTT" identified by values 'F894844C34402B67';

System,Role and Table Privileges Information:

select  privilege||' '||table_name  "Privileges" from  dba_tab_privs where  grantee='&data' 

union

select  privilege from  dba_sys_privs where grantee='&data'

union

select granted_role from dba_role_privs where  grantee='&data';

Object Information:

set lines 300
set pages 300

set owner for a12

col OBJECT_NAME for a35

col OBJECT_TYPE for a18

select owner,object_name,object_type,status,to_char(created,'MM-DD-YYYY HH24:MI:SS') "CREATED",to_char(LAST_DDL_TIME,'MM-DD-YYYY HH24:MI:SS') "LAST_DDL_TIME" from dba_objects where OWNER='&OWNER' and OBJECT_NAME='&OBJECT_NAME" order by created;  (created/last_ddl_time/owner/object_type/status)

Index Information:

col OWNER for a12

col INDEX_NAME for a15

col INDEX_TYPE for a10

col TABLE_OWNER for a12

col TABLE_NAME for a12

select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,STATUS,LAST_ANALYZED,PARTITIONED from dba_indexes where OWNER='&OWNER' and INDEX_NAME='&INDEX_NAME';

Indexes on the table:

col OWNER for a12

col INDEX_NAME for a15

col INDEX_TYPE for a10

col TABLE_OWNER for a12

col TABLE_NAME for a12

select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,STATUS,LAST_ANALYZED,PARTITIONED from dba_indexes where TABLE_OWNER='&TABLE_OWNER' and TABLE_NAME='&TABLE_NAME';

Rebuilding indexes online:

alter index owner.index_name rebuild online;

Sync Checking:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Primary Side:

select thread#,max(sequence#) from v$archived_log group by thread#;

Standby Side:

select thread#,max(sequence#) from v$archived_log group by thread#;

select thread#,max(sequence#) from v$archived_log where APPLIED='YES' group by thread#;

select sequence#,status from v$managed_standby where sequence# > 98258 order by 1;

 SEQUENCE# STATUS
---------- ------------
     98259 RECEIVING
     98259 WAIT_FOR_LOG

Deferring Archive Destination:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string ENABLE

alter system set log_archive_dest_state_2=defer scope=both;

System altered.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string DEFER

Checking MRP Process:

select process,status from v$managed_standby;

status will be APPLYING_LOG while applying log files and WAITING_FOR_LOGS after applying all logfiles.

Starting MRP Process:

shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

Enabling MRP by current logfile:

alter database recover managed standby database using current logfile disconnect from session;

Cancelling the MRP Process:

alter database recover managed standby database cancel;

Enabling Recovery by current logfile:

alter database recover managed standby database using current logfile disconnect from session;

Archivelog deletion commands:

delete noprompt archivelog until time 'sysdate-3' device type disk;

delete noprompt archivelog until time 'sysdate-(4/24)' device type disk;   (If all archivelogs except the last 4 hours need to be deleted.)

Archivelog creation and completion time:

select RECID,STAMP,substr(NAME,36,30),THREAD#,SEQUENCE#,to_char(FIRST_TIME,'MM-DD-YYYY HH24:MI') "FIRST_TIME",to_char(COMPLETION_TIME,'MM-DD-YYYY HH24:MI') "COMPLETION_TIME" from v$archived_log where SEQUENCE# between 60605 and 60640 order by SEQUENCE#;

How to check standby database name:

sho parameter log_archive_dest_2, We get list of services from where we found standby database name

Registering archive redo log files:

alter database resgister logfile '<Archivelog file NAME>';

SCN Based backup using RMAN:

backup incremental from scn <SCN NO> database format '/tmp/Backupfilename_U%' tag 'Backup';

Current SCN:

select current_scn from v$database;

select to_char(CURRENT_SCN) from v$database;.

Connecting target database with no catalog:

rman no catalog target / or rman target / or rman, connect target /

Cataloging the backup:

catalog start with '<Path of the backups location>';

catalog backuppiece '<BackupPiece Name>';

Recovering database without REDO:

recover database NOREDO; Here redo data will not be applied until recovery completes

Deleting backup with tag name:

delete backup tag 'Backup';

Finding database role and it's current protection mode:

select name,open_mode,log_mode,database_role,protection_mode from v$database;

Creating standby control file from production:

rman target /
backup current controlfile for standby format '<path>';
transfer the control file backup
restore standby controlfile from '<location>';


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

The above statement may succeed without errors but the MRP process will still not start.
The reason is that since the controlfile has been restored from the primary, it is looking
for datafiles at the same location as are in primary instead of standby. For example, if the
primary datafiles are located at '+DATA/proddb_1/DATAFILE' and standby datafiles are at '+DATA/proddb_2/DATAFILE',
the new controlfile will show the datafiles location as '+DATA/proddb_1/DATAFILE'. This can be verified from the query "select name from v$datafile" on the standby instance. We need to rename all the datafiles to reflect the correct location.

Renaming of datafile can be done in two ways

1) alter database datafile '<file_name>/<file_id>' to '<new filename>';

2) RMAN target /

   catalog start with '+diskgroup/<dbname>/datafile/';

   switch database to copy;

Changing Archive log (log_archive_dest_1location:

alter system set log_archive_dest_1= 'LOCATION=<PATH>' scope=both;

datapump Queries:

Data pump jobs running, completed and stopped:

select * from dba_datapump_jobs;

Currently running Data pump sessions:

select * from v$DATAPUMP_SESSION;

Data pump sessions:

select * from DBA_DATAPUMP_SESSIONS;

Checking for the directories in the database for e.g. datapump directory

select * from dba_directories;

Finding dictionary information like pump

select table_name from dict where table_name like '%PUMP%';

substr function testing:

select RECID,STAMP,NAME from v$archived_log where SEQUENCE#=60605;

     RECID  STAMP   NAME                                                          
------------- ------------ ------------------------------------------------------------
         1       906048518 /archivelogs$ORACLE_SID/oradata/arch_$ORACLE_SID/log$ORACLE_SID_1_60605_821017614.arc

I want to impose substr function on the name column of the above query

select RECID,STAMP,substr(NAME,36,30) from v$archived_log where SEQUENCE#=60605;

     RECID      STAMP SUBSTR(NAME,36,30)
---------- ---------- ------------------------------
         1  906048518 log$ORACLE_SID_1_60605_821017614.arc

Here substr(NAME,36,30), The second argument (36) implies the sub string starts from character 36 and third argument (30) implies sub string continues forward to next 30 characters starting from 36th character of the total string "/archivelogs$ORACLE_SID/oradata/arch_$ORACLE_SID/log$ORACLE_SID_1_60605_821017614.arc"

Currently Running Jobs:

For the running jobs, if We use the scheduler

select owner , job_name , running_instance, session_id from all_scheduler_running_jobs
order by owner , job_name
/

If we use dbms_job

select job, instance, sid from dba_jobs_running
order by instance, job
/

Blocking sessions Information:

select l1.sid,' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2

           where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

select l1.inst_id, l1.sid, ' IS BLOCKING ',l2.sid, l2.inst_id from gv$lock l1,gv$lock l2
           where l1.block =1 and l2.request > 0 and l1.id1=l2.id1;

set pages 300
set pages 300
col OSUSER for a12
col USERNAME for a12
select sid,serial#,username,osuser,status from v$session where SID IN ('123','456');

select sid,username,MACHINE,TERMINAL,PROGRAM,MODULE,ACTION,to_char(LOGON_TIME,'MM-DD-YYYY HH24:MI:SS') "LOGON_TIME",LAST_CALL_ET from v$session where SID IN ('123','456');

select sid,P1,P2,WAIT_TIME,SECONDS_IN_WAIT,STATE from v$session_wait where SID IN ('123','456');

col EVENT for 25

select * from v$session_event where SID IN ('123','456');

SELECT a.sql_text

FROM   v$sqltext a,
       v$session b
WHERE  a.address = b.sql_address
AND    a.hash_value = b.sql_hash_value
AND    b.sid = &sid
ORDER BY a.piece;

Find command to delete files older than 90 days:

find /u01/app/oracle/product/11.2.0.3.5/rdbms/audit/* -name "*.aud" -mtime +90 -exec ls -l {} \;

OS Command to remove junk like backspace:

SQL> !stty erase ^?

If you want to erase backspace then !stty erase <backspace key>