Oracle 12c Cheat Sheet

Quick Check

Listner status

lsnrctl status

It must display services (XE or E1Local in this case) :

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-OCT-2014 20:46:10

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                06-OCT-2014 17:19:19
Uptime                    0 days 3 hr. 26 min. 50 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/vmsvr075/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmsvr075)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=vmsvr075)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/E1Local/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "E1Local" has 1 instance(s).
  Instance "E1Local", status READY, has 1 handler(s) for this service...
Service "E1LocalXDB" has 1 instance(s).
  Instance "E1Local", status READY, has 1 handler(s) for this service...
The command completed successfully

If the listner is not running you get:

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 06-OCT-2014 20:48:54

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

To start the listner:

 lsnrctl start

Wait 1 / 2 minutes to rerun lsnrctl status This waiting time might be necessary for the listner to "discover" the database.

Startup Database

• start a sql session

sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 6 21:13:01 2014

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

SQL> 

• Connect as sysdba

SQL> connect sys/password as sysdba
Connected to an idle instance.
SQL> 

• Inquiry the current state (if applicable)

select open_mode from v$database;

• startup (or startup mount or startup nomount)
• if startup with nomount: alter database mount; alter database open
• if startup with mount: alter database open

SQL> startup
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size		    2927528 bytes
Variable Size		  281019480 bytes
Database Buffers	  339738624 bytes
Redo Buffers		    5459968 bytes
Database mounted.
Database opened.
SQL> 

Access to Oracle Manager

 http://localhost:5500/em

Shutdown Database

• Connect as sysdba
• shutdown or shutdown transactional or shutdow immediate or shutdow abort (will require instance recovery procedures)

sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 6 21:08:00 2014

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

SQL>

SQL>connect sys/password as sysdba
Connected.
SQL>

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Users Management

Create a User

CREATE USER user IDENTIFIED BY password;

Grant Access

GRANT role TO user;

List Users

SELECT * FROM ALL_USERS;

Change Password

ALTER USER user IDENTIFIED BY password;

Create Dabase

Create Database dbca : in /u01/app/oracle/product/12.1.0/dbhome_1/bin/

Security

Setup password the first time Replace the first line in sqlnet.ora (C:\Oracle\E1Local\NETWORK\ADMIN):

SQLNET.AUTHENTICATION_SERVICES=(NTS)

Restart the Oracle Services (Start – Run – services.msc). go to Start – Run – CMD and type in

SQLPLUS / NoLog

In there, type

CONNECT / AS SYSDBA

This will get connected with DBA privileges Now, you can change the password of SYS to set a password with the following SQL

ALTER USER sys IDENTIFIED BY oracle;

Audit Failed Logon

• Activate Audit Trail

 SQL>  audit create session whenever not successful; 

• List access

 SELECT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') AS logtime FROM dba_audit_trail ORDER BY logtime DESC; 

 SELECT * FROM (SELECT DISTINCT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as logtime FROM dba_audit_trail ORDER BY logtime ASC)WHERE ROWNUM <= 10; 

Recovery

No log

ORA-00742: Log read detects lost write in thread 1 sequence 20 block 248885

recover database until cancel;

Specify log: filename | Auto | Cancel
AUTO