Oracle 12c Cheat Sheet: Difference between revisions

Latest revision as of 06:09, 29 May 2017

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>

Create TableSpace

CREATE TABLESPACE "JDELOCAL_DV920" DATAFILE 'C:\E1LOCAL\DATA\JDELOCAL_DV920.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "JDELOCAL_DV920" IDENTIFIED BY VALUES password DEFAULT TABLESPACE "JDELOCAL_DV920" TEMPORARY TABLESPACE "TEMP";

alter user JDELOCAL_DV920  quota unlimited on JDELOCAL_DV920;

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

@@ Line 48: / Line 48: @@
 ==Startup Database==
-* Connect as sysdba
-* startup (or startup mount or startup nomount)
+* start a sql session
-* if startup with nomount: database open
-* if startup with nomount: alter database mount;  alter database open
 <pre>
 sqlplus /nolog
@@ Line 59: / Line 57: @@
 SQL> </pre>
+* Connect as sysdba
 <pre>
 SQL> connect sys/password as sysdba
 Connected to an idle instance.
 SQL> </pre>
+* Inquiry the current state (if applicable)
+<syntaxhighlight lang="sql">
+select open_mode from v$database;
+</syntaxhighlight>
+* startup (or startup mount or startup nomount)
+* if startup with nomount: alter database mount;  alter database open
+* if startup with mount: alter database open
 <pre>
 SQL> startup
@@ Line 77: / Line 84: @@
 SQL>
 </pre>
+==Access to Oracle Manager==
+<pre> http://localhost:5500/em</pre>
 ==Shutdown Database==
@@ Line 100: / Line 110: @@
 SQL></pre>
+==Create TableSpace==
+<syntaxhighlight lang="sql">
+CREATE TABLESPACE "JDELOCAL_DV920" DATAFILE 'C:\E1LOCAL\DATA\JDELOCAL_DV920.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192
+  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
+</syntaxhighlight>
+<syntaxhighlight lang="sql">
+CREATE USER "JDELOCAL_DV920" IDENTIFIED BY VALUES password DEFAULT TABLESPACE "JDELOCAL_DV920" TEMPORARY TABLESPACE "TEMP";
+</syntaxhighlight>
-==List Users==
+<syntaxhighlight lang="sql">
+alter user JDELOCAL_DV920  quota unlimited on JDELOCAL_DV920;
+</syntaxhighlight>
+==Users Management==
+===Create a User===
+<syntaxhighlight lang="oracle11">CREATE USER user IDENTIFIED BY password;</syntaxhighlight>
+===Grant Access===
+<syntaxhighlight lang="oracle11">GRANT role TO user;</syntaxhighlight>
+=== List Users ===
+<syntaxhighlight lang="oracle11">SELECT * FROM ALL_USERS;</syntaxhighlight>
+===Change Password===
+<syntaxhighlight lang="oracle11">ALTER USER user IDENTIFIED BY password;</syntaxhighlight>
 ==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):
+<pre>SQLNET.AUTHENTICATION_SERVICES=(NTS)</pre>
+Restart the Oracle Services (Start – Run – services.msc).
+go to Start – Run – CMD and type in
+<pre>SQLPLUS / NoLog</pre>
+In there,  type
+<pre>CONNECT / AS SYSDBA</pre>
+This will get connected with DBA privileges
+Now, you can change the password of SYS to set a  password with the following SQL
+<pre>ALTER USER sys IDENTIFIED BY oracle;</pre>
+==Audit Failed Logon==
+* Activate Audit Trail
+<pre> SQL>  audit create session whenever not successful; </pre>
+* List access
+<pre> SELECT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') AS logtime FROM dba_audit_trail ORDER BY logtime DESC; </pre>
+<pre> 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; </pre>
+==Recovery==
+===No log===
+ORA-00742: Log read detects lost write in thread 1 sequence 20 block 248885
+<syntaxhighlight lang="sql">
+recover database until cancel;
+</syntaxhighlight>
+<syntaxhighlight lang="sql">
+Specify log: filename | Auto | Cancel
+AUTO
+</syntaxhighlight>

Oracle 12c Cheat Sheet: Difference between revisions

Latest revision as of 06:09, 29 May 2017

Contents

Quick Check

Startup Database

Access to Oracle Manager

Shutdown Database

Create TableSpace

Users Management

Create a User

Grant Access

List Users

Change Password

Create Dabase

Security

Audit Failed Logon

Recovery

No log

Navigation menu

Oracle 12c Cheat Sheet: Difference between revisions

Latest revision as of 06:09, 29 May 2017

Quick Check

Startup Database

Access to Oracle Manager

Shutdown Database

Create TableSpace

Users Management

Create a User

Grant Access

List Users

Change Password

Create Dabase

Security

Audit Failed Logon

Recovery

No log

Navigation menu

Search