Oracle 12c Cheat Sheet: Difference between revisions

From Objectif Client Inc
Jump to navigation Jump to search
(Created page with "==Create Dabase== Create Database dbca : in /u01/app/oracle/product/12.1.0/dbhome_1/bin/")
 
No edit summary
 
(26 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==Quick Check==
Listner status <pre>lsnrctl status</pre>
It must display services (XE or E1Local in this case) :
<pre>
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
</pre>
If the listner is not running you get:
<pre>
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
</pre>
To start the listner:
<pre> lsnrctl start</pre>
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
<pre>
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> </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
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>
</pre>
==Access to Oracle Manager==
<pre> http://localhost:5500/em</pre>
==Shutdown Database==
* Connect as sysdba
* shutdown or shutdown transactional or shutdow immediate  or shutdow abort (will require instance recovery procedures)
<pre>
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></pre>
<pre>SQL>connect sys/password as sysdba
Connected.
SQL></pre>
<pre>SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
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>
<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 Dabase==
Create Database dbca : in /u01/app/oracle/product/12.1.0/dbhome_1/bin/
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>

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
Retrieved from "http://wiki.objclt.ca/wiki/index.php?title=Oracle_12c_Cheat_Sheet&oldid=757"