Oracle DBA quick reference notes

From Urandom
Jump to: navigation, search

Contents

GUI tools

* dbca - create/modify/delete databases with a GUI
* dbum - database upgrade manager GUI

Pfiles

These are the main tweaking files for the databases.

* location of pfiles $ORACLE_HOME/dbs/init{ORACLE_SID}.ora
* the encoded file is spfile{ORACLE_SID}.ora (has to be (re)moved to use text only)
* SQL command to create pfile "create pfile from spfile;"


tablespaces

* CREATE TABLESPACE "SW_DATA" DATAFILE '/usr/local/oradata/TEST/sw_data01.dbf' SIZE 8G;
* alter tablespace temp add tempfile ’….’ size 1500M;

Users

create user sw dfault tablespace sw_data identified by sw;
grant dba to sw;
create role sw_user;


Start/Stop

su oracle
. oraenv
lsnrctl start #start the listener
rlwrap sqlplus / AS SYSDBA
startup
startup mount
quit
emctl start dbconsole #database console start
#from herje is stoping
emctl stop dbconsole #database console stop
rlwrap sqlplus / AS SYSDBA
shutdown normal #will wait for all clients to disconnect
shutdown immediate #will disconnect client
shutdown abort #force stop
quit
lsnrctl stop #stop the listener

For grid controlled ASM

. oraenv 
+ASM
crsctl start ora.asm #for starting
crsctl stop ora.asm #for stopping
crsctl stat res -t #for status

ASM

http://oracle-base.com/articles/10g/automatic-storage-management-10g.php

Export / IMport

impdp sw full=y dumpfile=database.dmp


Rman and recovery area

Check rman destinations and usage

SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

show parameter db_recovery
alter system set db_recovery_file_dest_size=20G scope=both;


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH

Show existing backups and delete some

LIST BACKUP SUMMARY;
LIST BACKUP;
DELETE BACKUP TAG 'TAG20140502T122048';

Number of sessions, processes and transactions

show parameter sessions
show parameter processes
show parameter transactions

General logic is:

  • processes=x
  • sessions=x*1.1+5
  • transactions=sessions*1.1
alter system set sessions=800 scope=spfile;
alter system set processes=900 scope=spfile;
alter system set transactions=1000 scope=spfile;


User management

Add user with login permissions

CREATE USER USERNAME
    IDENTIFIED BY PASSWORD;
GRANT create session TO USERNAME;

Show users system privileges

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;

Tablespace available sizes

set pagesize 100

column file_name format a32
column tablespace_name format a15
column status format a3 trunc
column t format 999,999.000 heading "Total MB"
column a format a4 heading "Aext"
column p format 990.00 heading "% Free"

SELECT df.file_name,
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
(fs.s/df.bytes*100) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_id;

column file_name clear
column tablespace_name clear
column status clear
column t clear
column a clear
column p clear
ttitle off

or a smaller output

select sum(bytes)/1024 kbytes_alloc, tablespace_name
from sys.dba_temp_files
group by tablespace_name

the new query will be

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

Locked accounts

Login attempts

select profile , resource_name, limit
from dba_profiles
where resource_name ='FAILED_LOGIN_ATTEMPTS'; 

Unlock user

alter user <username> account unlock;

Search for DB object

SET LINESIZE 800;
select * from dba_objects where object_name='SEARCHABLE';

Show information about current connected user sessions

SET LINESIZE 800;
select username, LAST_CALL_ET, STATUS,   osuser,   terminal from   v$session where username  is not null order by   username,  osuser;
Personal tools