[[http://tmade.de|Home tmade.de]] [[http://wiki.tmade.de|Home Wiki]] ===== Oracle ===== ==== Maintain ==== su - oracust1 #Authenticate as "oracust1" user expdp parfile=exp_customer.par #Starts dumping sqlplus /nolog @/scripts/drop_ts.sql CUSTOMER #Delete tablespace "CUSTOMER" and all related users sqlplus /nolog @create_ts_cust.sql CUSTOMER SID #Create tablespace, paramter 1(CUSTOMER)= Name of the tablespace, parameter 2 (SID) databaseinstance sqlplus /nolog @/scripts/create_ts_cust.sql CUSTOMER sid1 #Create new tablespace "CUSTOMER" on "sid1" sqlplus /nolog @/scripts/grant_user.sql USERNAME mypassword #Create user "USERNAME" with password "mypassword" ==== Select ==== As follows some selects: select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'CUSTOMER' and p.addr (+) = s.paddr; alter system kill session ','; Example: select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'CUSTOMER' and p.addr (+) = s.paddr; #Determine session numbers of all sessions of a specific user. alter system kill session '140,193'; #Kill a single session that was determined with the above statement. sqlplus / as sysdba #Login as "sysdba" select count(*) from customer.queryindexitem; #Select tablespace "customer" and field "queryindexitem" select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME='dba_objects'; select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'DBA_%' and OBJECT_TYPE='TABLE'; select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'V$%'; Result: 357874 TRUNCATE TABLE customer.queryindexitem; #Delete all entries from table "queryindexitem" in tablespace "customer" Table truncated. select count(*) from customer.queryindexitem; #Select number of entries from table "queryindexitem" in tablespace "customer" Result: 0 ==== Import ==== Requirement: * Tablespace has to exist! * Check export and import dir select * from dba_directories; #Check declared Oracle directories create or replace directory EXP_DIR as '/opt/oracle/ora_data/custx'; #Create or edit export directory create or replace directory DATA_PUMP_DIR as '/opt/oracle/ora_data/custx'; #Create or edit data pump directory Select username from dba_users; #To determine der databaseuser/database schema su - oraxxx impdp parfile=imp_customer.par ==== Start/ Stop Oracle DB ==== To start a ORACLE DB on linux: su – ora (cust1, cust2, restore, train) sqlplus / as sysdba startup; To speed up start for tests: startup nomount; To stop ORACLE DB: su – ora sqlplus / as sysdba shutdown immediate; ==== Listener ==== orarestore@hostname:~> lsnrctl start LIRESTORE orarestore@hostname:~> lsnrctl stop LIRESTORE orarestore@hostname:~> lsnrctl status LIRESTORE oracust2@roth:~> lsnrctl LSNRCTL> set current_listener LIRESTORE Current Listener is LIRESTORE LSNRCTL> services Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1524))) The listener supports no services The command completed successfully LSNRCTL> stop Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=1524))) The command completed successfully LSNRCTL> start Starting /opt/oracle/oracust2/bin/tnslsnr: please wait... ==== Check Connection ==== To check the connection to the database, login with the required user: su - oracle sqlplus /nolog connect user/password quit #disconnect user ==== Checks ==== select * from dba_directories; #Check declared Oracle directories create or replace directory EXP_DIR as '/opt/oracle/ora_data/custx'; #Create or edit export directory create or replace directory DATA_PUMP_DIR as '/opt/oracle/ora_data/custx'; #Create or edit data pump directory select username from dba_users; #To determine der databaseuser/database schema ==== Connecting SQL*PLUS with a shell script ==== #!/bin/bash read_sql_stmt() { typeset stmt=$1 typeset login=$2 echo " set feedback off verify off heading off pagesize 0 $stmt; exit " | sqlplus -s $login } read_sql_stmt "select username, user_id from dba_users" "system/system_pw" | while read u i do echo "user $u has userid $i" done ==== Setup ==== alter system set sga_max_size=10G scope = SPFILE; #Set RAM alter system set memory_max_target=10G scope = SPFILE; #Set RAM alter system set memory_target=10G scope = SPFILE; #Set RAM create spfile from pfile; #pfile=readable spfile=crypted and just readable from Oracle mount -o remount,size=20g /dev/shm #Remount /dev/shm with 20G memory reservation Check setup: show parameter memory; show parameter sga; Set Parameter to configfile: /opt/oracle/orarestore/dbs/initxxxxxx.ora #If changes were made on this file you have to execute "create spfile from pfile;" ==== Account ==== select t_login, t_password from schema.tablename; #Show all users within schema update schema.tablename set t_password='48A538310CCA5506BBFF12208F6B5F1B' where t_login='SuperUser'; #Update Password for user "SuperUser" commit; #Activate change ==== Useful Commands ==== select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME='dba_objects'; select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'DBA_%' and OBJECT_TYPE='TABLE'; select OBJECT_NAME, OBJECT_TYPE from dba_objects where OBJECT_NAME like 'V$%'; select FILE_NAME from DBA_DATA_FILES; #Check active tablespaces sql>set linesize 300 #Set linesize (lenght) to 300 (max is 32767) sql>spool /tmp/testfile #Output sql-statement into a file "/tmp/testfile " sql>spool off #Swith off output to file