Oracle HowTo's - Some answers to my newbie questions on how to do stuff

mail

How to change parameters ?

Parameters are stored in views :

V$SYSTEM_PARAMETER
instance-wide values
V$PARAMETER
  • parameters that are currently in effect for the session
  • V$PARAMETER inherits values from V$SYSTEM_PARAMETER

To alter parameters, you'll have to use...

Difference between ALTER SESSION and ALTER SYSTEM :

ALTER SESSION ALTER SYSTEM
affects parameters related to the current connection to the DB any instance parameter
changes are visible until you disconnect from the database as long as the database is mounted
major options SCOPE=value, with value :
  • MEMORY : the change
    • is made in memory
    • takes effect immediately
    • persists until the database is shut down
  • SPFILE : the change
    • is made in the spfile
    • will take effect after the database is shut down and started up again
  • BOTH : the change
    • is made both in memory + spfile
    • takes effect immediately
    • persists after the database is shut down + started up again
default value :

Alternate solution

To change parameters, you may also (source) :
  1. generate an up-to-date pfile from the spfile
  2. edit the pfile
  3. generate a new spfile and restart the instance
mail

How to list Oracle database instances ?

Since a database instance is merely a group of processes, we'll use ps to list instances :
mail

How to list / kill active sessions ?

List sessions as oracle (sources : 1, 2):

export ORACLE_SID=ORACLE_SID; sqlplus -s '/ as sysdba' << EOSQL
SET LINESIZE 200
COLUMN username FORMAT A10
COLUMN IP_ADDRESS FORMAT A15
SELECT
	username,
	utl_inaddr.get_host_address(terminal) IP_ADDRESS,
	sid,
	serial#,
	program
FROM
	v\$session
WHERE
	username is not null
ORDER BY
	username;
EOSQL
USERNAME	IP_ADDRESS	SID	SERIAL#		PROGRAM
--------------- --------------- ------- --------------- --------------------------------------------
SYS		10.203.2.28	708	27		sqlplus@hostname (TNS V1-V3)

This query also returns the SQL*Plus session in which it's being executed.

In this context, sid stands for session identifier, and must not be mistaken with the system identifier (aka ORACLE_SID).

Kill sessions (source):

export ORACLE_SID=ORACLE_SID; sqlplus -s '/ as sysdba' << EOSQL
alter system kill session 'sid, serial#';
EOSQL
mail

How to delete everything from an Oracle table ?

truncate

truncate table tableName

  • Deletes all existing records of the table tableName
  • Can't be used with where clause
  • Drops the entire table contents when executed but not the table. (DROP deletes the entire contents along with the table)
  • Is faster than delete
  • Releases the storage space, and doesn't create a log file (rollback tablespace) so that no rollback is possible
  • Doesn't return the number of rows deleted when queried
  • doesn't fire triggers

delete

delete from tableName where [SQL condition]

  • Delete records of table tableName matching a condition : can be used with where clause
  • Can be rolled back
  • Deletes table contents row by row when executed
  • Is slow
  • Doesn't frees-up the storage space
  • Creates a log file (rollback tablespace)
  • Returns the number of rows deleted when queried
  • Fires triggers
  • Date gets copied into the rollback tablespace after delete is executed
mail

How to list schemas ? 

SET pagesize 50
select distinct owner from dba_objects order by owner;
mail

How to make sure I have the right SYS password ?

Situation

I have the SYS password and just want to make sure this is the right one. However, when sitting on the Oracle server, connecting as SYS with SQL*Plus is a passwordless action : 
export ORACLE_SID=oracleSid; sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 16 11:21:37 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL>

Details

Oracle does not check the password when logging as SYS locally.
Password check is bypassed for a local login as SYS because this account is the one used to start the database instance, before login routines are available. This password is found in /home/oracle/admin/oracleSid/dbs/orapworacleSid.

Solution

To test the SYS password, you have to login through the network layer of Oracle authentication : 
sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 16 11:03:33 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect sys@tnsName
Enter password: wrong password 
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect sys@tnsName
Enter password: right password 
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> connect sys@tnsName as SYSDBA
Enter password: wrong password 
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect sys@tnsName as SYSDBA
Enter password: right password 
Connected. 
SQL>

As a one-liner :

This command line assumes there is a /home/oracle/network/admin/tnsnames.ora entry matching the ORACLE_SID.

dbName='MYDB'; sysPassword='password'; echo -n "'sys' connection on '$dbName' : "; echo 'select * from dual;' | sqlplus sys@$dbName/$sysPassword as sysdba &>/dev/null && echo 'ok' || echo 'ko'

mail

How to grant / list / revoke user privileges ?

grant privileges :

todo

list privileges :

looks like the ...where owner = 'bob', ...where grantee = 'bob' clauses are case-sensitive.

List user privileges :

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS WHERE GRANTEE='RECAPE_fct';
SELECT TABLE_NAME, GRANTOR, PRIVILEGE FROM USER_TAB_PRIVS WHERE GRANTEE='RECAPE_fct';
SELECT * FROM USER_ROLE_PRIVS;


http://stackoverflow.com/questions/15066408/how-to-find-the-privileges-and-roles-granted-to-a-user-in-oracle
select * from USER_ROLE_PRIVS where USERNAME='RECAPE_fct';
select * from USER_TAB_PRIVS where Grantee = 'RECAPE_fct';
select * from USER_SYS_PRIVS where USERNAME = 'RECAPE_fct';


describe DBA_SYS_PRIVS
select * from DBA_SYS_PRIVS where GRANTEE = 'RECAPE_fct';

column GRANTEE format a30
column PRIVILEGE format a40
select * from DBA_SYS_PRIVS where GRANTEE = 'RECAPE_FCT';




set pagesize 50;
column OBJECT_TYPE format a30
column TABLE_NAME format a30
select DISTINCT
	DBA_OBJECTS.OBJECT_TYPE,
	auc.TABLE_NAME,
	auc.UPDATABLE,
	auc.INSERTABLE,
	auc.DELETABLE
from
	all_updatable_columns auc join DBA_OBJECTS on auc.TABLE_NAME=DBA_OBJECTS.OBJECT_NAME
where
	auc.OWNER='RECAPE';



select distinct grantee, table_name from TABLE_PRIVILEGES where grantee='RECAPE';
select distinct grantee, table_name from TABLE_PRIVILEGES where grantee='RECAPE_fct';

	no rows selected

	:-(((


col "GRANTEE" format a10
col "OWNER" format a10
col "TABLE_NAME" format a30
col "GRANTOR" format a10
SELECT * FROM table_privileges
WHERE grantee = 'RECAPE_FCT';

revoke privileges :

http://www.oracle-dba-online.com/sql/grant_and_revoke_privileges.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9020.htm


echo "revoke select, insert, update, delete on RECAPE from RECAPE_fct" | sqlplus / as sysdba
mail

How to get the status of an Oracle instance ?

select status from v$instance;
STATUS
------------------------------------
OPEN
export ORACLE_SID=ORACLE_SID; echo 'select status from v$instance;' | sqlplus -s / as sysdba
STATUS
------------------------------------
OPEN
mail

How to register the database in /etc/oratab ?

This allows to start the DB at boot time, provided it's registered with a Y :

echo "${ORACLE_SID}:${ORACLE_HOME}:Y" >> /etc/oratab

mail

How to create a database ?

All the commands described below must be run as oracle.

Step 1 : create the pfile (aka init.ora) :

This file is $ORACLE_BASE/admin/SID/pfile/initSID.ora, SID being the instance name.

  1. Check environment variables are correctly set : echo $ORACLE_{BASE,HOME}
    /u01/app/oracle /u01/app/oracle/product/12.1.0/dbhome_1/
  2. Create directories : sid="MYDB"; mkdir -p $ORACLE_BASE/{admin/$sid/pfile,oradata/$sid}
  3. touch $ORACLE_BASE/admin/$sid/pfile/init${sid}.ora
  4. Then append to it :
    db_name = MYDB
db_block_size = 8192
control_files = /u01/app/oracle/oradata/MYDB/control01.ctl
sga_target = 320M
pga_aggregate_target = 80M
  5. On startup, Oracle searches the pfile into $ORACLE_HOME/dbs. Since it's not there, let's symlink it :
    ln -s $ORACLE_BASE/admin/$sid/pfile/init${sid}.ora $ORACLE_HOME/dbs/init${sid}.ora

Step 2 : create the password file :

  1. Let's create the password file allowing sys to connect in sysdba mode :
    cd $ORACLE_HOME/dbs; orapwd file=orapw$sid password=p@ssw0rd
  2. Check it : cat $ORACLE_HOME/dbs/orapw$sid 
    ]\[ZORACLE Remote Password fileYS99A5C02080C9A1EB8▒▒se▒hE▒▒I@▒y{B ▒̑▒sC>▒(dt▒[▒▒▒?▒˨▒▒'z▒4Hg▒sK▒BƬ▒'▒▒1▒Bp|&ܟ▒@▒▒1▒Hw㑳^B5▒N`▒▒5▒u\( )m▒▒▒▒

Step 3 : create the instance :

  1. Create /home/oracle/createMYDB.sql :
    create database MYDB
character set AL32UTF8
national character set AL16UTF16
logfile
group 1 '/u01/app/oracle/oradata/MYDB/redo01a.log' size 50M,
group 2 '/u01/app/oracle/oradata/MYDB/redo02a.log' size 50M
datafile '/u01/app/oracle/oradata/MYDB/system01.dbf' size 500M extent management local
sysaux datafile '/u01/app/oracle/oradata/MYDB/sysaux01.dbf' size 500M
undo tablespace UNDO_TBS datafile '/u01/app/oracle/oradata/MYDB/undo_tbs01.dbf' size 100M
default temporary tablespace TEMP tempfile '/u01/app/oracle/oradata/MYDB/temp01.dbf' size 50M;
  2. export ORACLE_SID=MYDB; sqlplus /nolog
  3. connect / as sysdba 
    Connected to an idle instance.
  4. startup nomount; 
    ORACLE instance started.

Total System Global Area	335544320 bytes
Fixed Size			2924160 bytes
Variable Size			117440896 bytes
Database Buffers		209715200 bytes
Redo Buffers			5464064 bytes
  5. create spfile from pfile; 
    File created.
  6. shutdown abort; 
    ORACLE instance shut down.
  7. startup nomount; 
    ORACLE instance started.

Total System Global Area	335544320 bytes
Fixed Size			2924160 bytes
Variable Size			117440896 bytes
Database Buffers		209715200 bytes
Redo Buffers			5464064 bytes
    The Oracle instance starts and reads the spfile, ignoring the pfile.
  8. start $HOME/createMYDB
    This takes a while, but finally : 
    Database created.

Step 4 : create the dictionary + stored procedures tables :

  1. start $ORACLE_HOME/rdbms/admin/catalog.sql
    ~15-20minutes on my VM...
  2. start $ORACLE_HOME/rdbms/admin/catproc.sql
    HOURS on my VM!!!
Both scripts are long and verbose. Don't pay attention to error messages : the scripts are trying to delete content before re-creating it, but in our case (fresh install, new database), there is no old content to update.

Step 5 : assign passwords to sys and system :

  1. alter user sys identified by "password_sys"; 
    User altered.
  2. alter user system identified by "password_system"; 
    User altered.

Step 6 : the Product User Profile :

  1. Leave SQL*Plus : exit
  2. Log in as system : sqlplus system/password_system
  3. start $ORACLE_HOME/sqlplus/admin/pupbld.sql;
mail

How to generate the tnsnames.ora ?

With the netca assistant :

  1. If working via SSH, setup everything regarding X Server configuration (see this and this)
  2. As oracle, run netca
  3. Within this assistant, select :
    1. Local Net Service Name configuration
    2. Add
    3. Service name : MYDB
    4. Select tcp protocol
    5. Host name : oracle1
    6. Use standard port 1521
    7. ... (?)
    8. Then :

Manually :

In $ORACLE_HOME/network/admin/tnsnames.ora : 
MYDB =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))
		)
		(CONNECT_DATA =
			(SERVICE_NAME = MYDB)
		)
	)

MYDB is a free text label referring to a DB (?), even though there is no such DB yet.

Test it :

tnsping MYDB 
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-JUN-2015 17:05:28

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MYDB)))
OK (50 msec)
mail

How to configure a listener ?

With the netca assistant :

  1. If working via SSH, setup everything regarding X Server configuration (see this and this)
  2. As oracle, run netca
  3. Within this assistant, select :
    1. Listener configuration
    2. Add
    3. Listener name : LISTENER_ORACLESID
    4. Select tcp protocol
    5. Use standard port 1521
    6. Configure another listener ? No, thanks !
    7. Then :
  4. The console should look like :
    Oracle Net Services Configuration:
Configuring Listener:LISTENER_ORACLESID
Listener configuration complete.
Oracle Net Listener Startup:
	Running Listener Control:
		/home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start LISTENER_ORACLESID
	Listener Control complete.
	Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0
  5. Make sure the listener is active
  6. Have a look at the generated configuration : cat $ORACLE_HOME/network/admin/listener.ora 
    # listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER_ORACLESID =
	(DESCRIPTION_LIST =
		(DESCRIPTION =
			(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
			(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
		)
	)
    If the database does not use any external procedure, Oracle recommends deleting the (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) line to improve security.

Manually :

Creating $ORACLE_HOME/network/admin/listener.ora with :
LISTENER_ORACLESID =
	(DESCRIPTION_LIST =
		(DESCRIPTION
			(ADDRESS=(PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
		)
	)
should be enough.

Static registration of the database into the listener (source) :

  1. Simply append to $ORACLE_HOME/network/admin/listener.ora : 
    SID_LIST_LISTENER_ORACLESID =
	(SID_LIST =
		(SID_DESC =
			(GLOBAL_DBNAME = ORACLESID)
			(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1/)
			(SID_NAME = ORACLESID)
		)
	)
  2. Reload the listener configuration : lsnrctl reload
mail

How to install Oracle 12c on Debian Jessie ?

Requirements (sources : 1, 2) :

Item Minimum Recommended My choice
CPU Pentium 500MHz or faster any modern CPU will do the trick
RAM 1GB 4GB 1GB
HDD 500MB 10GB 15GB
SWAP See calculation rules 2GB

Create a new VM :

Just follow this procedure.

Install Oracle 12c (source) :

  1. Download Oracle binaries (.zip files) from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
  2. Send them to the virtual machine
  3. Install mandatory packages :
    apt install binutils autoconf automake bzip2 gcc less libc6-dev make libstdc++5 unzip zlibc build-essential libaio1
  4. Some libs are expected in other directories, we need to change that :
    ln -s /usr/lib/x86_64-linux-gnu/ /usr/lib64
  5. Create user and groups :
    groupadd oinstall; groupadd dba; useradd oracle -m -d /home/oracle -g oinstall -G dba -s /bin/bash; passwd oracle
  6. Some kernel parameters need to be edited to successfully install the database. Here is a script to identify them : 
    echo "sem: "; echo "min 250 32000 100 128"; echo "is " $(cat /proc/sys/kernel/sem); echo; \
echo "shmall: "; echo "min 2097152"; echo "is " $(cat /proc/sys/kernel/shmall); echo; \
echo "shmmax - should be able to hold the SGA - max 4G-1byte: "; echo "is " $(cat /proc/sys/kernel/shmmax); echo; \
echo "shmmni: "; echo "min 4096"; echo "is " $(cat /proc/sys/kernel/shmmni); echo; \
echo "file-max: "; echo "min 6815744"; echo "is " $(cat /proc/sys/fs/file-max); echo; \
echo "ip_local_port_range: "; echo "min/max 9000 65500"; echo "is " $(cat /proc/sys/net/ipv4/ip_local_port_range); echo; \
echo "rmem_default: "; echo "min 262144"; echo "is " $(cat /proc/sys/net/core/rmem_default); echo; \
echo "rmem_max: "; echo "min 4194304"; echo "is " $(cat /proc/sys/net/core/rmem_max); echo; \
echo "wmem_default: "; echo "min 262144"; echo "is " $(cat /proc/sys/net/core/wmem_default); echo; \
echo "wmem_max: "; echo "min 1048576"; echo "is " $(cat /proc/sys/net/core/wmem_max); echo; \
echo "aio-max-nr: "; echo "max 1048576"; echo "is " $(cat /proc/sys/fs/aio-max-nr); echo
  7. Change the parameters that don't fit, but feel free to set them all to the minimum with this script (Make a backup or Git the changed file(s) first) : 
    echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf

echo "kernel.shmall = 2097152" >> /etc/sysctl.conf
(2097152 means 2GB) Needs to fit your SGA (=main memory for database, be sure to have enough ram for PGA etc.)!!!!

echo "kernel.shmmax = 1200000000" >> /etc/sysctl.conf
# echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
# echo "net.ipv4.ip_local_port_range = 1024 65000" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048576" >> /etc/sysctl.conf
echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
  8. Edit system limits (Make a backup or Git the changed file(s) first) : 
    echo "oracle soft nproc 2047" >> /etc/security/limits.conf
echo "oracle hard nproc 16384" >> /etc/security/limits.conf
echo "oracle soft nofile 1024" >> /etc/security/limits.conf
echo "oracle hard nofile 65536" >> /etc/security/limits.conf
  9. Create directories to separate software, database files and recovery files :
    mkdir -p /opt/oracle/app/oracle /opt/oracle/oradata /opt/oracle/flash_recovery_area; chown -R oracle:oinstall /opt/oracle; chmod -R 775 /opt/oracle
  10. Prevent errors when the installer links some libraries :
    ln -s /usr/bin/awk /bin/awk; ln -s /usr/bin/basename /bin/basename
  11. And...
    reboot
  12. Unzip the 2 Oracle .zip files.
    Once unzipped, Oracle files use 2.8GB. Hope you're not too short on HDD space...
    Delete the .zip files to save space
  13. after unzipping the installation files add some tweaks : Some makefiles need manual tweaking because they can't find required libs because of some environment issues
    sed -i 's/\$(PLSHPROF_LINKLINE)/\$(PLSHPROF_LINKLINE) -lons/g' /opt/oracle/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
    sed -i 's/\$(RMAN_LINKLINE)/\$(RMAN_LINKLINE) -lons/g' /opt/oracle/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
    sed -i 's/\$(TG4PWD_LINKLINE)/\$(TG4PWD_LINKLINE) -lnnz12/g' /opt/oracle/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ins_rdbms.mk
    those files don't exist on my side (???)
  14. If installing remotely through SSH (on a VM running inside a remote SSH host ), do whatever necessary so that X11 apps can start :
    1. On the local workstation : install an X server (on Windows, Xming is fine). Start it and make it listening on apps coming via PuTTY
    2. On the oracle host : apt install x11-apps
    3. On the oracle host again, make sure /etc/ssh/sshd_config has the line : 
      X11Forwarding yes
    4. On your local workstation, start a new PuTTY session to the remote host (i.e. the host running the virtual machine), in which you'll check the Connection | SSH | X11 | Enable X11 forwarding option. Then, within this PuTTY session, run xeyes. If you can see 2 big eyes, it's going fine
    5. Then, on the remote host, connect to the virtual machine : ssh -X bob@virtualMachine
    6. Try again xeyes.
    This whole step is made necessary by the graphical installer requiring an X server. To workaround this, it is possible to run a silent install.
  15. Now install : the installer should guide you through each step. When the requirements are checked you can safely bypass the warnings, because not everything can be checked on an unsupported environment (i.e. not Red Hat).
    1. su oracle
    2. directory/where/you/unzipped/database/runInstaller -ignoreSysPrereqs
      -ignoreSysPrereqs is required to install Oracle on "uncertified" platforms (Debian, Ubuntu, ...)
  16. Possible errors during install :
    • Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2015-06-24_08-13-46PM/jdk/jre/lib/amd64/xawt/libmawt.so: libXtst.so.6: cannot open shared object file: No such file or directory
      apt install libxtst6 (source)
    • X11 connection rejected because of wrong authentication.
      1. As root : echo $DISPLAY
        localhost:10.0
        10 is what you'll get if you run : grep X11DisplayOffset /etc/ssh/sshd_config (details)
      2. As root : xauth list
        oracle1/unix:11	MIT-MAGIC-COOKIE-1	3ed6a232f4c564301b53b25e58faa6d9
oracle1/unix:10	MIT-MAGIC-COOKIE-1	e2413d3e20f44284ae33e60f5415fea0
      3. As oracle :
        touch /home/oracle/.Xauthority
xauth add oracle1/unix:10 MIT-MAGIC-COOKIE-1 e2413d3e20f44284ae33e60f5415fea0
      (source and details)
    • PRVF-0002 : could not retrieve local node name
      echo -e "127.0.0.1\t$(hostname)" >> /etc/hosts (source)
  17. When all errors have been fixed, proceed to the install by following the on-screen instructions.
    The db.rsp response file is generated during the install to allow replaying it in silent mode. A sample response file is provided as directory/where/you/unzipped/database/response/db_install.rsp.
  18. When asked to do so, run scripts as root :

    Don't forget this step when making a silent install.

    /home/oracle/app/oraInventory/orainstRoot.sh
    Changing permissions of /home/oracle/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /home/oracle/app/oraInventory to oinstall.
The execution of the script is complete.
    /home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh
    Performing root user operation.

The following environment variables are set as:
	ORACLE_OWNER=	oracle
	ORACLE_HOME=	/home/oracle/app/oracle/product/12.1.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]: /usr/bin
	Copying dbhome to /usr/bin ...
	Copying oraenv to /usr/bin ...
	Copying coraenv to /usr/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
  19. Now edit /home/oracle/.bash_profile : 
    export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1/
export ORACLE_OWNER=oracle
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$PATH:$ORACLE_HOME/bin
    And reload changes (as oracle) : source ~/.bash_profile
  20. Try logging in, as oracle : sqlplus /nolog 
    SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 25 12:16:25 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL>
  21. And voilà ! Enjoy !!!
mail

How to list tables and views of a schema ?

tables only (source) :

column OWNER format a15;
column OBJECT_NAME format a25;
SELECT DISTINCT
	OWNER,
	OBJECT_NAME
FROM
	DBA_OBJECTS
WHERE
	OBJECT_TYPE = 'TABLE'
	AND OWNER = 'mySchema';
Or equivalent : 
set pagesize 50;
column OWNER format a15;
column TABLE_NAME format a25;
SELECT DISTINCT
	OWNER,
	TABLE_NAME
FROM
	DBA_TABLES
WHERE
	OWNER = 'mySchema'
ORDER
	BY TABLE_NAME;

tables + views :

column OBJECT_TYPE format a25;
column OBJECT_NAME format a25;
column OWNER format a15;
SELECT DISTINCT
	OBJECT_TYPE,
	OBJECT_NAME,
	OWNER
FROM
	DBA_OBJECTS
WHERE
	(OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW')
	AND OWNER = 'mySchema';
mail

How to start / stop Oracle processes ?

If the listener is started before the database, the database will register automatically onto the listener.

Start / stop database instances :

All actions below are run as oracle.

Action DB type Step-by-step All-in-one
start primary
  1. export ORACLE_SID=ORACLE_SID
  2. Then start the instance with SQL*Plus :
    1. sqlplus /nolog
    2. connect sys/password as sysdba
    3. startup (this is equivalent to startup open)
  3. ORACLE instance started.

Total System Global Area	335544320 bytes
Fixed Size			2924160 bytes
Variable Size			222298496 bytes
Database Buffers		104857600 bytes
Redo Buffers			5464064 bytes
Database mounted.
Database opened.
 export ORACLE_SID=ORACLE_SID; echo startup | sqlplus -s / as sysdba
start standby If both the primary and standby databases are offline, then always (if possible) start the standby database before starting the primary database.
  1. Start the database :
    startup nomount;
  2. Mount the standby database :
    alter database mount standby database;
    Once it is mounted, the database can receive archived redo data from the primary database.
  3. Start the managed recovery operation :
    alter database recover managed standby database disconnect from session;
    Once the database is performing managed recovery, log apply services apply the archived redo logs to the standby database.
(source) 		export ORACLE_SID=ORACLE_SID; echo -e "startup nomount;\nalter database mount standby database;\nalter database recover managed standby database disconnect from session;" | sqlplus -s / as sysdba
stop primary + standby
  1. export ORACLE_SID=ORACLE_SID
  2. Then stop the instance with SQL*Plus :
    1. sqlplus /nolog
    2. connect sys/password as sysdba
    3. shutdown immediate
  3. Database closed.
Database dismounted.
ORACLE instance shut down.
 export ORACLE_SID=ORACLE_SID; echo 'shutdown immediate' | sqlplus -s / as sysdba 
Database closed.
Database dismounted.
ORACLE instance shut down.

Start / stop order :

Start :

  1. oracle standby
  2. listener standby
  3. oracle primary
  4. listener primary
  5. enable FSFO

Stop :

  1. disable FSFO
  2. listener standby
  3. oracle standby thus standby stopped in a consistent state. New data written on the primary will be stored in archlogs, ready to be sent to standby when back online
  4. listener primary
  5. oracle primary

Startup modes for standalone or primary databases (source) :

startup nomount
  1. Read the spfile
  2. Start the instance in memory
startup nomount pfile="init.ora"
  • Start the instance based on the parameters found in the text file init.ora
  • This is used to create a database, or to start with values different from those of the spfile
  • To figure out whether the DB was started using a pfile or a spfile : select decode(count(*), 1, 'spfile', 'pfile') from v$spparameter where rownum=1 and isspecified='TRUE';
startup mount
  • Read the controlfile. If not found, the DB stays nomount mode
  • This allows changing the DB behavior in special occasions, to enable archivelog or restore an RMAN backup, ...
startup open
  • Open datafiles
  • If a datafile is missing, the DB stays in mount mode and requires restoration.
alter database allows switching from nomount to mount, then to open in this specific order only :
  1. startup nomount
  2. alter database mount
  3. alter database open

Shutdown modes for both primary and standby databases (source)

normal
This mode is used when simply running shutdown. It is seldom used because it waits for all users to close their session manually before stopping the database.
immediate
Disconnect all sessions, cancel all ongoing transactions.
transitional
Wait for the ongoing transactions to end before disconnecting sessions. New connections requests are denied.
abort
Brutally stop the database, whatever is running (like a kill -9). A recover will be necessary at the next start. This should be used only if everything else failed.
mail

How to create / alter / drop / list users ?

Create (source) :

CREATE USER bob IDENTIFIED BY "thisIsAPoorP@ssw0rd123" DEFAULT TABLESPACE MY_TABLESPACE;

alter user :

change password

Delete (source) :

DROP USER bob;

List existing accounts :

SET linesize 300
COL "USERNAME" FORMAT A15
COL "ACCOUNT_STATUS" FORMAT A20
COL "CREATED" FORMAT A10
SELECT username, account_status, created, lock_date, expiry_date, profile
FROM dba_users;

List locked accounts (source) :

Same as above, with the extra where clause : 

WHERE account_status != 'OPEN';

If a user you've just manually unlocked is still LOCKED, this may be because of too many attempts with a wrong password (thank you, WebLogic!)

mail

How to delete an Oracle database ?

The clean method (sources : 1, 2) :

dbca -silent -deleteDatabase -sourceDB myDatabase

dbca is a GUI tool, the silent flag enables the CLI usage.

The kill kill KILL!!! method :

  1. Stop the Oracle instance
  2. Delete the data files :
    • /appli/oracle/$ORACLE_SID/oradata02/$ORACLE_SID_sysaux_01.dbf
    • /appli/oracle/$ORACLE_SID/oradata03/$ORACLE_SID_sysaux_02.dbf
    • /appli/oracle/$ORACLE_SID/oradata03/$ORACLE_SID_users_01.dbf
    • ...
  3. Delete the temp files :
  4. Delete the redologs :
    • /appli/oracle/$ORACLE_SID/oraredolog01/
    • /appli/oracle/$ORACLE_SID/oraredolog02/
    • ...
  5. (other files in /appli/oracle/$ORACLE_SID/... ???)
  6. Delete the Oracle password file : $ORACLE_HOME/dbs/orapw$ORACLE_SID
  7. Delete the Oracle lock file : $ORACLE_HOME/dbs/lk${ORACLE_SID}_

In the example paths above, we use ORACLE_DBNAME = ORACLE_SID

	control files : /appli/oracle/$ORACLE_SID/oractl01/$ORACLE_SID_ctrl01.ctl
	log archive : /appli/oracle/$ORACLE_SID/oraarch
	initParamFileName : /home/oracle/admin/$ORACLE_SID/dbs/init$ORACLE_SID.ora
	spfile : /home/oracle/admin/$ORACLE_SID/dbs/spfile$ORACLE_SID.ora

The ugly "other" method :

Use this method when the instance you're trying to remove is FUBAR (probably already broken via massive and irresponsible use of rm by a n00b who shouldn't have been given access to this server...).

  1. Stop the oracle processes related to the specific instance you want to delete
  2. Delete/empty the dedicated filesystems (/appli/oracle/$ORACLE_SID/, ...)
  3. Remove the corresponding entry from /etc/oratab
mail

How to start / stop listeners ?

Is the listener active ?

  • ps -ef | grep [t]nslsnr 
    UID	PID	PPID	C	STIME	TTY	TIME		CMD
oracle	25835	1	0	Oct22	?	00:00:04	/logiciels/oracle/oradb/11.2.0/ora/bin/tnslsnr listenerName -inherit
  • netstat -lpte | grep 1521 
    Proto	Recv-Q	Send-Q	Local Address	Foreign Address	State	User	Inode	PID/Program name
tcp6	0	0	[::]:1521	[::]:*		LISTEN	oracle	14991	1749/tnslsnr
  • lsnrctl status 
    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-JUN-2015 15:18:11

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias			LISTENER
Version			TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date		25-JUN-2015 15:00:55
Uptime			0 days 0 hr. 17 min. 18 sec
Trace Level		off
Security		ON: Local OS Authentication
SNMP			OFF
Listener Parameter File	/home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File	/home/oracle/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))
	(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

To stop a listener :

$ORACLE_HOME/bin/lsnrctl stop listenerName

Be careful with $ORACLE_HOME when switching Oracle versions.

To start a listener :

$ORACLE_HOME/bin/lsnrctl start listenerName 
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 12-NOV-2014 16:33:55

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /logiciels/oracle/oradb/11.2.0.a/ora/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /home/oracle/network/admin/listener.ora
Log messages written to /logiciels/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.example.com)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias			listenerName
Version			TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date		12-NOV-2014 16:33:55
Uptime			0 days 0 hr. 0 min. 0 sec
Trace Level		 off
Security		ON: Local OS Authentication
SNMP			ON
Listener Parameter File	/home/oracle/network/admin/listener.ora
Listener Log File	/logiciels/oracle/diag/tnslsnr/hostname/listener/alert/log.xml
Listening Endpoints Summary...
	(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.example.com)(PORT=1521)))
The listener supports no services
The command completed successfully
mail

How to get an exact match to show parameter ?

Situation

show parameter log_archive_dest_state_2 returns all parameters matching log_archive_dest_state_2, including log_archive_dest_state_20, log_archive_dest_state_21, log_archive_dest_state_22, ..., whereas I only want to see log_archive_dest_state_2.

Solution

Will output :
NAME				TYPE	VALUE
------------------------------- ------- -------
log_archive_dest_state_2	2	defer