Oracle DBA Script

 Login

1. Login as "oracle"
2. export TERM=vt100
3. export TMOUT=0
4. ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
5. export ORACLE_HOME
6. PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
7. export PATH
8. ORACLE_SID=MYDB1
9. export ORACLE_SID

Display User List

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDB SYSOP SYSAS

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

SYS                            TRUE  TRUE  FALSE

mndba                          TRUE  FALSE FALSE

Show current user name

show user;

Add User

create user nondba identifed by nondba;
grant sysdba to nondba
grant create session to nondba;
connect nondba/nondba

Check/enable/disable current archivelog mode (Reference: Oracle Distilled)

SELECT LOG_MODE FROM SYS.V$DATABASE;

or

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Current log sequence           15

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 2G

(Just in case you want to change the file destination)

SQL> alter system set log_archive_dest_1='LOCATION=C:\temp' scope = both;
 
System altered.
 
(show current archive log mode)

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     25
Current log sequence           27
SQL> 

(Switch the database to archivelog mode)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             285213588 bytes
Database Buffers          243269632 bytes
Redo Buffers                5844992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15
SQL> alter system switch logfile;

System altered.

SQL> host
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

(Double check if archivelog is written)

C:\Documents and Settings\Administrator>dir C:\app\Administrator\flash_recovery_
area\ORCL\ARCHIVELOG\2013_03_17\

 Volume in drive C has no label.
 Volume Serial Number is 800F-F6B1

Directory of C:\app\Administrator\flash_recovery_area\ORCL\ARCHIVELOG\2013_03_1
7

03/17/2013  12:10 PM            .
03/17/2013  12:10 PM           ..
03/17/2013  12:10 PM        47,624,192 O1_MF_1_15_8NBJLCBQ_.ARC

               1 File(s)     47,624,192 bytes
               2 Dir(s)  60,949,037,056 bytes free

C:\Documents and Settings\Administrator>exit

SQL>

Query.sql

[oracle@db01 ]$ cat queue.sql
set pages 2000
set lines 1000
set head on
col sid format 9999
col event format a25
col p2text format a12
col P2RAW format a30
col P1RAW format a30
col p1text format a12
col P1RAW format a30

select sid, event, p2text, p2, P2RAW, seconds_in_wait
from v$session_wait
where event not like '%message%'
/

Dump sqlplus output to formatted HTML file

spool asm.html
set markup html on
set echo on
set pagesize 200
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select 'this asm report was generated at: ==> ' , sysdate " " from dual;

Session_Detail.sql

set serveroutput on size 1000000

set echo off feed off veri off

set lines 200

DECLARE

  v_psid number;

  v_sid number;

  s v$session%ROWTYPE;

  p v$process%ROWTYPE;

BEGIN

  begin

  for c in (

    select sid

    from   v$process p, v$session s

    where  p.addr     = s.paddr

      --and  s.status = 'ACTIVE'

      --and  s.sid in (463,484)

      and s.sid in ( &1)

      --and module  like '%CINVTVIQ%'

      order by s.last_call_et desc) loop

          select * into s from v$session where sid  = c.sid;

          select * into p from v$process where addr = s.paddr;
   dbms_output.put_line('====================================================================================================');

          dbms_output.put_line('SID/Serial  : '|| s.sid||','||s.serial#);

          dbms_output.put_line('Foreground  : '|| 'PID: '||s.process||' - '||s.program);

          dbms_output.put_line('Shadow      : '|| 'PID: '||p.spid||' - '||p.program);

          dbms_output.put_line('Terminal    : '|| s.terminal || '/ ' || p.terminal);

          dbms_output.put_line('OS User     : '|| s.osuser||' on '||s.machine);

          dbms_output.put_line('Ora User    : '|| s.username);

          dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);

          dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));

          dbms_output.put_line('Login Time  : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));

          dbms_output.put_line('Last Call   : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99,990.0') || ' min');

          dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));

          dbms_output.put_line('Latch Spin  : '|| nvl(p.latchspin, 'NONE'));

          dbms_output.put_line('Current SQL statement:');

          for c1 in ( select * from v$sqltext

                                  where HASH_VALUE = s.sql_hash_value order by piece) loop

                dbms_output.put_line(chr(9)||c1.sql_text);

          end loop;

          dbms_output.put_line('Previous SQL statement:');

          for c1 in ( select * from v$sqltext

                                  where HASH_VALUE = s.prev_hash_value order by piece) loop

                dbms_output.put_line(chr(9)||c1.sql_text);

          end loop;

          dbms_output.put_line('Session Waits:');

            dbms_output.put_line(chr(9)||lpad('EVENT',30,' ')||'   '||lpad('P2TEXT',12,' ')||'   '||lpad('P2',12,' ')||'   '||lpad('seconds_in_wait',12,' ')) ;

            dbms_output.put_line(chr(9)||lpad('-----------------------------',30,' ')||'   '||lpad('------------',12,' ')||'   '||lpad('------------',12,' ')||'   '||lpad('------------',12,' ')) ;

          for c1 in ( select event, p2text, p2, seconds_in_wait from v$session_wait where sid = s.sid) loop

                dbms_output.put_line(chr(9)||lpad(c1.event,30,' ')||'   '||lpad(c1.p2text,12,' ')||'   '||lpad(c1.p2,12,' ')||'   '||lpad(c1.seconds_in_wait,12,' ')) ;

          end loop;

dbms_output.put_line('====================================================================================================');

  end loop;

  exception

    when no_data_found then

      dbms_output.put_line('NO ACTIVE SESSION!!!');

      return;

    when others then

      dbms_output.put_line(sqlerrm);

      return;

  end;

END;

/

Shutdown without wait

alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate

List of all files to be backuped up

select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Find Unique Name ORACLE_UNQNAME (reference here)

SELECT name, db_unique_name FROM v$database;

Reset password

alter user user_name identified by new_password;

Configure 11g Database Control (dbconsole) for 11gR2 Grid Infrastructure RAC Cluster (Original Article)

[oracle@x86node1 ~]$ emca -config dbcontrol db -silent -cluster -ASM_USER_ROLE SYSDBA -ASM_USER_NAME ASMSNMP -CLUSTER_NAME x86cluster -LOG_FILE/home/oracle/emConfig_dec19_2011.log -DBSNMP_PWD DBSNMP -SYS_PWD oracle -ASM_USER_PWD oracle -SID ADMIN -ASM_SID +ASM1 -DB_UNIQUE_NAME ORCL -EM_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -SYSMAN_PWD SYSMAN -SERVICE_NAME ADMIN -ASM_PORT 1521 -PORT 1521 -LISTENER_OH /u01/app/11.2.0/grid -LISTENER LISTENER -ORACLE_HOME /u01/app/oracle/product/11.2.0/dbhome_1 -HOST x86node1 -ASM_OH /u01/app/11.2.0/grid

Backup (Connect to RMAN)

1. run RMAN client: RMAN
2. connect to the database: 
1. CONNECT TARGET user@mydb
2. or connect using OS authen:
   CONNECT TARGET /
3. show default configuration:
   SHOW ALL;

Backup NOARCHIVELOG MODE - database is not open while backup

1. To make a consistence database backup, restart the database as below:
1. RMAN> CONNECT TARGET /
2. RMAN> SHUTDOWN IMMEDIATE;
3. RMAN> STARTUP FORCE DBA;
4. RMAN> SHUTDOWN IMMEDIATE;
5. RMAN> STARTUP MOUNT;
2. Start backup: BACKUP DATABASE;
   RMAN> backup database;
   
   Starting backup at 16-MAR-13 allocated channel:
   ORA_DISK_1 channel ORA_DISK_1: SID=154 device type=DISK channel
   ORA_DISK_1: starting full datafile backup set channel
   ORA_DISK_1: specifying datafile(s) in backup set
   input datafile file number=00001 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
   input datafile file number=00002 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
   input datafile file number=00005 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF
   input datafile file number=00003 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
   input datafile file number=00004 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
   channel ORA_DISK_1: starting piece 1 at 16-MAR-13
   channel ORA_DISK_1: finished piece 1 at 16-MAR-13
   piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2013_03_16\ O1_MF_NNNDF_TAG20130316T174230_8N8HNGN0_.BKP tag=TAG20130316T174230 comment=NONE
   channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
   channel ORA_DISK_1: starting full datafile backup set
   channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set
   channel ORA_DISK_1: starting piece 1 at 16-MAR-13 channel ORA_DISK_1: finished piece 1 at 16-MAR-13 piece handle=C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2013_03_16\ O1_MF_NCSNF_TAG20130316T174230_8N8HPNSD_.BKP tag=TAG20130316T174230 comment=NONE
   channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 16-MAR-13
3. Open the database:
   ALTER DATABASE OPEN;

Backup (Archivelog mode)

1. RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Backup (Incremental Backup)

1. Create a base of the Incremental Backup: BACKUP INCREMENTAL LEVEL 0 DATABASE;
2. creates a level 1 cumulative incremental backup:BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
3. creates a level 1 differential incremental backup:
   BACKUP INCREMENTAL LEVEL 1 DATABASE;

Backup (Incrementally updated Backup)

RECOVER COPY OF DATABASE 
WITH TAG 'incr_update';
BACKUP 
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE

NFS mount for RMAN backup

if you encounter the below error:


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d1 channel at 04/12/2013 21:41:13
ORA-19504: failed to create file "/mnt/Exadata_NFS/mmdprd1_db_14o6u2no"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

use the below mount option:
mount -t nfs -o hard,rw,noac,rsize=32768,wsize=32768 :

or use the below /etc/fstab option:

:/        /mnt/Exadata_NFS         nfs hard,rw,noac,rsize=32768,wsize=32768

It is tested okay on my Oracle Exadata.

Check user privillege

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = <theUser>
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY 1;

Exadata Healthcheck commands

http://gstk.net/index.php/Exadata

(Source here)

Check Free Tablespace size

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

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 ;

Check Tablespace filename and other parameters

select file_name,tablespace_name,round(bytes/1024/1024,2) as MB,AUTOEXTENSIBLE,round(maxbytes/1024/1024,2) as MAX_MB,status from dba_data_files where tablespace_name='USERS';

Change Tablespace to autoextend to unlimited

ALTER DATABASE DATAFILE '+DATA_EUX/xxxx/datafile/users.496.787767695' AUTOEXTEND ON MAXSIZE UNLIMITED;

Show asm diskgorup

select name from v$asm_diskgroup;


Add File to Table Space

SQL> ALTER TABLESPACE USERS ADD DATAFILE  '+DATA_EUX/cctxp/datafile/users.500.dbf' size 20G  AUTOEXTEND ON MAXSIZE UNLIMITED

Manually generate statistics for tables

EXEC DBMS_STATS.GATHER_TABLE_STATS('"SIEBEL"', '"S_EVT_ACT"', estimate_percent => 30, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254', degree => DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE);

Standard information for reporting to Oracle Support

Please upload following information.

(*) Environment Information
-- Exadata type(V1,V2,X2-2,X2-8) :
-- Rack type(Quarter, Half, Full) :
-- imageinfo output (Exadata Version info)
-- Opatch lsinventory output (Grid/RDBMS patches info)

(*) OSWatcher logs from DB nodes and Cells
OSwatcher logs for the 2 hours before the event
# find /opt/oracle.oswatcher/osw/archive/. -name "*14.01.04.0[0-2]00*" -exec zip /tmp/osw_`hostname -a`_14.01.04.zip {} \;
| | | |___ Hour when you are executing the test
| | |__Date
| |_Month
|_Year

(*) Please verify if ASLR is being used as follows:

# /sbin/sysctl -a | grep randomize

(*) Check if everything is running fine as of now :
$$GRID_HOME/bin/crsctl status res -t

Create tablespace

CREATE TABLESPACE REPORTING

DATAFILE

'+DATA_DBX' SIZE 144M AUTOEXTEND ON next 144M,

'+DATA_DBX' SIZE 144M AUTOEXTEND ON next 144M,

'+DATA_DBX' SIZE 144M AUTOEXTEND ON next 144M,

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;

144M = 12disks x 3 cell server x 4MB per unit

Move Tables from USERS to MYPROG:

set pages 30000

set lines 130

select 'alter table '||owner||'.'||TABLE_NAME||

   ' move tablespace MYPROG parallel 16;'

   from dba_tables

   where PARTITIONED = 'NO'

      and owner in ('MYPROG')

      and tablespace_name <> 'MYPROG';

Check if anything inside USERS:

select OWNER, segment_name, segment_type

  from dba_segments

  where TABLESPACE_NAME = 'USERS';

Check if any statistics profile created:

select name, SQL_TEXT, LAST_MODIFIED, DESCRIPTION, STATUS from dba_sql_profiles order by 1;