Difference between revisions of "Oracle DBA quick reference notes"

From Urandom
Jump to: navigation, search
Line 90: Line 90:
 
     IDENTIFIED BY PASSWORD;
 
     IDENTIFIED BY PASSWORD;
 
GRANT create session TO USERNAME;
 
GRANT create session TO USERNAME;
 +
</pre>
 +
 +
== Tablespace available sizes ==
 +
<pre>
 +
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
 +
 
</pre>
 
</pre>

Revision as of 17:23, 28 May 2014

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

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

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

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