Difference between revisions of "Oracle DBA quick reference notes"

From Urandom
Jump to: navigation, search
Line 72: Line 72:
 
*        sessions=x*1.1+5
 
*        sessions=x*1.1+5
 
*        transactions=sessions*1.1
 
*        transactions=sessions*1.1
 +
 +
<pre>
 +
alter system set sessions=800 scope=spfile;
 +
alter system set processes=900 scope=spfile;
 +
alter system set transactions=1000 scope=spfile;
 +
 +
 +
</pre>

Revision as of 17:59, 5 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


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;