by Thomas Broussard 4 years ago
437
More like this
To connect to a distant DB, you have to know :
When creating a DB, you have access to :
unidirectional link between 2 DB
example of DB link creation :
CREATE PUBLIC DATABASE LINK rh_usa CONNECT TO scott IDENTIFIED BY tiger USING 'conn_link';
ORA-12154 TNS : could not resolve service name
Local instance name not known in tnsnames.ora
Beware if the automatic suffix is defined in sqlnet.ora, in that case tnsnames.ora must take it into account
ORA-12203 TNS : unable to connect to destination
Listener not started, or address error in tnsnames.ora
ORA-12500 TNS : listener failed to start a dedicated server process
The listener is correct but DB not started
ORA-12505: TNS : Listener could not resolve SID given in connect descriptor ORA-12514: TNS : Listener could not resolve the service name given in connect descriptor
SID or service name error in tnsnames.ora
OR the listener doest not listen DB connexions
OR DB is in dynamic declaration and is not started
ORA-12533 TNS : illegal ADDRESS parameters
Error in tnsnames.ora file
ORA-12545 TNS : name lookup failure
Error in tnsnames.ora file
ORA-12560 TNS : protocol adapter error
Windows error, can originate form service OracleService<instance_name> not started
OR ORACLE_SID missing or bad definition in local instance
OR ORACLE_HOME bad definition
ORA-1034 Oracle not available
DB not started
shell command : netmgr
This manager can help configure the different files, sqlnet.ora, tnsnames.ora and listener.ora
To ensure that a client can reach the server, a listener must be set up initially to connect the client to the DB instance.
For this purpose, the file listener.ora describes the listener that is used.
The different files are located ar $ORACLE_HOME/network/admin
shell commands :
lsnrctl start
Starts the listeners reading the listener.ora
lsnrctl stop
Stop the listeners
lsnrctl status
Display listeners status
lsnrctl reload
Reload configuration
listener.ora
Descriptive file of the listener
2 parts to this file :
Example of file :
# listener.ora Network Configuration File: d:\oracle\ora112\NETWORK\ADMIN\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = DEMO) (SID_NAME = DEMO) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oralocal)(PORT = 1521)) ) ) ADR_BASE_LISTENER = d:\oracle
The client, to communicate with an Oracle server, must possess an Oracle client with TNS (Transaction Network Services) which connects to the server thanks to the listener (default port : 1521).
For this purpose, generally a file Tnsnames.ora is included in the Oracle Client (the goal is to shorten all the connexion parameters into an alias to simplify connexion)
Once the connexion is made between client and server, the listener is not used anymore.
This file contains sqlnet alisases.
It contains 2 main things :
Example :
# Generated by Oracle configuration tools. PRODRH = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PROD01)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) ) DEMO = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = TEST01)(PORT = 1521)) ) (CONNECT_DATA = (SID = ORATEST) ) )
The shell command tnsping <tns_name> allows to see if the tns is valid
Example of file for Windows :
# sqlnet.ora Network Configuration File: d:\oracle\ora112\NETWORK\ADMIN\sqlnet.ora SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = d:\oracle\ora112\log
Constraints are verified after each operation
If constraints are specified as DEFERRABLE, they are verified after each transaction instead
From the Oracle 10g version onwards, after an unwanted drop it is possible to restore the table for a period after the drop by using the flashback command.
Example of table restoration :
FLASHBACK TABLE nom_table TO BEFORE DROP;
After a drop / flashback, the constraints and indexes have their names modified, and foreign keys are not restored.
Transactions are :
Transaction lead to a COMMIT or a ROLLBACK
Option AUTOCOMMIT can be activated, after one or several transactions
SELECT instruction doesn't place lock
locking unit is data line
Locking is automatic on INSERT, UPDATE, DELETE (Exclusive type lock)
Use view DBA_WAITERS in case of locks for more info
Multi Version Concurrency Control
Used to allow multiple reading of a table without placing locks
Used for transaction rollback, DB consistency and flashback operations
Database accessed by an instance is divided in tablespaces
Files
Physical representation of data, tables, index storage
DataFile management
How to alter Datafile size :
ALTER DATABASE DATAFILE 'g:\oracle\oradata\oratest\data03.dbf' resize 50M;
How to create a Datafile :
ALTER TABLESPACE data ADD DATAFILE 'g:\oracle\oratest\ora_data04.dbf' size 100M;
How to move or rename a Datafile :
ALTER TABLESPACE data OFFLINE NORMAL; HOST move g:\oracle\oradata\oratest\data03.dbf f:\oracle\oradata\oratest\data03.dbf ALTER TABLESPACE data RENAME DATAFILE 'g:\oracle\oradata\oratest\data03.dbf' TO 'f:\oracle\oradata\oratest\data03.dbf'; ALTER TABLESPACE data ONLINE;
How to delete a Datafile :
shutdown immediate startup mount ALTER DATABASE DATAFILE 'g:\oracle\oradata\oratest\data04.dbf' OFFLINE DROP; alter database open
OS Block
Tablespaces
A tablespace is a logical structure based on one or several datafiles (the physical structure equivalent), except for the TEMP tablespace who is based on a tempfile
List of differents tablespaces :
Different states of a tablespace : OFFLINE, READ WRITE and READ ONLY.
--
BigFile tablespaces exists, where a datafile is assigned to a tablespace with a limit of 128To
Tablespace Management
Example of tablespace creation :
CREATE TABLESPACE tsrhbigtab DATAFILE 'd:\oracle\oradata\RH\tsrhbigtab01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL [UNIFORM SIZE 10M] SEGMENT SPACE MANAGEMENT AUTO;
The creation of the tablespace spurs the creation of the specified datafile.
The third line allows the file to autoextend if the 500M size is topped, up to the point of 2000M.
The fourth and fifth line are options for the storage management inside the file.
When the tablespace is created you can create a table associated with this tablespace :
CREATE TABLE emp ( empno NUMBER,name varchar(30),…) TABLESPACE tsrhbigtab PCTFREE 20 STORAGE (INITIAL 50M) ;
The PCTFREE command allows to reserve some space (in the exemple, 20%) inside a data block in case the table should be modified (e.g. adding columns)
How to put tablespace in READ ONLY :
ALTER TABLESPACE data READ ONLY;
How to delete a tablespace :
DROP TABLESPACE tsname [INCLUDING CONTENTS [AND DATAFILES] ] [CASCADE CONSTRAINTS]
Example :
DROP TABLESPACE tstab INCLUDING CONTENTS AND DATAFILES;
How to rename a tablespace :
alter tablespace tbscomtpa RENAME TO tbscompta;
OMF
Oracle Managed Files
Simplified creation of tablespaces ; when this is active, you can just create tablespaces with simple commands such as :
create tablespace tstab;
To use that mode, you must first specify the attribute DB_CREATE_FILE_DEST which equas the destination folder where the tablespaces will be created
syntax
Segment
Segment
Space occupied by an object in a tablespace.
Different types of segments :
These types are more or less equivalent to their tablespace
Extent
DB Block
1 per session
the server process transfers the datafiles modified to the data buffer cache, but does not execute the modifications
A PA is associated to each server process
MTS
Multi Thread Server
In MTS, each client process uses a shared server process already active and designated by a dispatcher.
In this mode, the User Global Area is located not in the PGA anymore, but in the SGA instead
The dispatcher puts the session in a queue and the first server process available will be assigned to the session
In Oracle 10g and later, you can switch dynamically to MTS with the following command :
alter system set shared_servers = 3;
Independant of user connexions
Launched at instance startup
Example of background process :
A commit can end the transaction, validating the modifications in the DB
The transaction is then validated by LGWR by writing in the redo log file. After that, the server process can release the locks set up because of the transaction.
Relational DataBase Management system
allows better performance, better data integrity and multi usage (competitive access)
PGA
Program Global Area / Private Global Area
Memory allocated at the beginning of each session.
Area where temporary variables are stocked (sorting options, session info, cursors state, stack space). Only contains information for Oracle processes.
The upper limit of space allocated can be defined with PGA_AGGREGATE_LIMIT (hard stop) for oracle 12c, or PGA_AGGREGATE_TARGET (cap that he tries not to top) for oracle 9+
SGA
System Global Area
(should be dimensioned for 5-10% of data volume in size)
Redo Log Buffer
LGWR
Log Writer
Writes the queued transactions in an Online Redo Log
DBWn
DatabaseWriter n
Executes transactions written in the online Redo Log.
An Online Redo Log will not be archived before the Database Writer have not executed all the transactions of the Redo Log
DatabaseWriter also executes Checkpoints when needed
Database Buffer Cache
Blocks of data read in the disk
Contains data files
When a request is made, a whole block is loaded even for the simplest request
Checkpoints
Regularly, checkpoints will be triggered.
It means that the Database Files will be synchronized with the Online Redo Log transactions written in there. (as opposed to the normal state where Database Files are not necessarily in phase with the Redo Logs, the latter having all the transaction written, contrary to the former)
how to force a checkpoint :
alter system checkpoint;
Streams Pool
Serves for data streaming optimization (e.g. when using data pump)
Fixed SGA
Java Pool
Serves for java stored procedures
Large Pool
Shared Pool
Data dictionary cache
Library cache
Cache where recent requests are stored (the requests are stored, not the answers to this requests) to help recast them faster if needed.
To help reduce the size of this cache, requests with bind variables count as only one request even if the bind variale changes each time
Logs
Redo Logs
When a transaction is made, several minutes can happen before physical datafiles are modified. The only file that is immediately modified is the Redo Log, which allows any user to know the new state.
The redo log is in that fact the sole witness of that transaction in that case, hence its criticality
how to add a redo log group :
ALTER DATABASE base ADD LOGFILE GROUP 4('pathfile/logfile.rdo', 'pathfile/logfile2.rdo') SIZE 20m;
how to add a redo log to a group :
ALTER DATABASE base ADD LOGFILE MEMBER 'pathfile/logfile.rdo’ TO GROUP 1 ;
Online Redo Log
The redo logs save any modification in the DB and are critical to execute restorations or to track activity in the DB.
Several (e.g. 3) groups of redo log are being filled, one at a time. When all are filled, at least one is archived before being emptied and it starts all over again. A group can contain 1, 2 or even 3 logs.
In case of a spike of activity, groups can be added in the rotation to shield from the spikes.
Each redo log change is called SWITCH LOG. The current redo log used is the CURRENT LOG.
It is strongly advised to adjust the size of Redo Logs to have leverage on the frequency of SWITCH LOG
how to manually change current redo log :
alter system switch logfile;
Archived Redo Log
Consists of formerly Online Redo Log that have already been filled with logs.
Alert logs
Alert logs are logs tracing majors events, like :
name : alert_<SID>.log
ADR
Automatic Diagnostic Repository
Each component is assigned a folder associated to their ADR_HOME ; they are all located in ADR_BASE.
(warning : neither ADR_HOME or ADR_BASE are environment variable or Oracle parameters)
In ADR_HOME you have different folders which aggregates alert logs, diverses traces and so on.
The infrastructure of ADR is made to manage the whole cycle of life of errors : detection, saving, analysis, communication with Oracle support, correction, closing.
In ADR, a problem is related to a code, for example ORA-00600, where an incident is an instance of a problem ; you can have 10 incidents related to the same problem.
The command line ADRCI allows you to navigate inside the ADR.
For more info about what ADR contains, the following command details each folder :
select * from v$diag_info;
Implements a backup database thanks to "redo" transactions which allows to use physical standby database in the case where the primary database fails
Schema = property
User = identity
To Oracle, Schema and User are quite the same notion.
SPFILE
Contains the information for the database parameter.
it is located under :
/u01/app/oracle/product/12.2.0/dbhome_1/dbs
Oracle Inventory
Allows to record all the oracle products
Optimal Flexible Architecture
ORACLE BASE = u01/app/oracle ORACE_HOME = u01/app/oracle/product/12.1/db_1
Describes database structure.
Necessary to start database instance (physical consistency)
At database startup, checks SCN (System Change Number) of each file ; if one file as a different number than the others (due to a restore for example), an alert is made
the control file thus contains :
You can see the control file of a databse via OEM in the menu Storage > Control file
System Change Number
File System storage
RAC Database
Several database instances with automatic load balancing.
High availability system, the database cluster can be spread across the company network.
ASM
Automatic Storage Management
DBMS_METADATA to obtain ddl
Thanks to this package, one can extract the ddl.
select dbms_metadata.getddl('TABLE', table_name, owner)
from dba_tables
where owner='SCOTT'
returns (extract)
CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
System views
DBA_* : needs system access
ALL_* : objects for one user and objects from other users on which the selected one has rights on. No need to specify access.
USER_*: objects for one user, no need to specify particular access
DBA_* querying
example
SELECT * FROM dba_tables
WHERE owner NOT IN ('SYS','SYSTEM')
ORDER BY owner;
Either memory based views or views based on the control file.
In case of memory based view, accessible even if the base is in NOMOUNT state
In case of control file based view, accessible only from the MOUNT state
Aliases : x$, v_$ (use v_$ for grants)
show default values of parameter
show parameter with multiple values
show parameter value in spfile
show current parameter value
Memory based view
Control file based view
Memory based view
local instance ; different than gv$session which represents all instances cumulated
UNIX :
in /etc/oratab file, for each instance put an entry of this type :
<ORACLE_SID>:<ORACLE_HOME>:{Y|N}
Example :
COURS:/u01/app/oracle/product/12.2.0:Y
the Y/N determines id the DB is booted automatically
Example of dbora script :
#!/bin/bash
# Les 2 commentaires "chkconfig" et "description" suivants sont OBLIGATOIRES
# On fournit liste_run_level, priorite_start, priorite_stop : 3,5 et 90, 10
# chkconfig: 35 90 10
# description: démarrage automatique d'Oracle
ORACLE_OWNER="oracle"
ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1"
case "$1" in
start)
echo -n "Lancement bases Oracle :"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
touch /var/lock/subsys/dbora
echo "OK"
;;
stop)
echo -n "Arret bases Oracle :"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
rm -f /var/lock/subsys/dbora
echo "OK"
;;
status)
ps -ef | grep pmon | grep -v grep
;;
*)
echo "Usage: $0 {start|stop|status}"
esac
Then here are the shell commands to execute :
chgrp oinstall dbora chmod 750 dbora chkconfig --add dbora
Killing active sessions
Soft kill
first list the active sessions :
SELECT s.sid, s.serial#, s.osuser,s.program FROM v$session s
then kill the sessions by providing their sid and serial# , so given their respective values '33' and '5337':
ALTER SYSTEM KILL SESSION '33,5337' IMMEDIATE;
Hard kill
find the spid of a session with the below query :
SELECT s.sid,p.spid,s.osuser,s.program FROM v$process p,v$session s WHERE p.addr = s.paddr and SID=33;
then use orakill utility (windows) or kill and providing the value '410' for the corresponding spid:
kill -9 410
Standard Edition
Entreprise Edition
Personal Edition
express Edition
to shut down database, you have to specify the following syntax :
SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]
to switch from OPEN to MOUNT state of DB, you have to shutdown the instance and reboot in MOUNT state
Starting the database
Starting the listener
lsnrctl start
Starting the database
export ORACLE_SID=COURS sqlplus / as sysdba
then in sql*plus
> startup
It is possible to precise the state while starting the database for instance :
startup NOMOUNT
When starting the database by the Startup command, you can specify the state in which you want to open it : NOMOUNT, MOUNT and OPEN
The corresponding instance status is respectively STARTED, MOUNTED and OPEN
Here is the code to switch states in a database :
alter database mount; alter database open;
note that you cannot alter DB from nomount to open directly, you have to switch to the intermediary state MOUNT between
OPEN
The third state of a DB start, where all files are open and users can create tablespaces, tables and indexes
MOUNT
The second state of the DB start.
While in this state, someone with sysdba privilege can administer and modify the DB structure, as well as restore DB or even check the control file, but anyone else can not connect to the DB
NOMOUNT
The first state while starting the DB, where you can just create the DB, or re create the control file
This is a state where you can start an instance even though the database doesn't physically exist yet
VSS Service
VSS Service allows to integrate the Oracle database and the Virtual Machine Snapshot
Profile
Users can be assigned profiles, which purpose is to set limits on different parameters (time per session, cpu consumption, or even password fails retries)
Each user is assigned a default profile when created, and it can be changed later (with OEM for example, or by command line)
Role
A role encompass several users to pool grants.
The classic usecase is that you create a role, give this role some privileges and then add users to this role
Grants
2 types of grants :
List of object privilèges :
Quota allocation
Example of quota allocation :
ALTER USER rhuser1 QUOTA UNLIMITED on TSRHTAB; ALTER USER comuser1 QUOTA 0 on TSCOMTAB;
User Creation/Deletion
Example of user creation :
CREATE USER rh IDENTIFIED BY passrh DEFAULT TABLESPACE tsrhtab TEMPORARY TABLESPACE temp;
Example of user deletion :
DROP USER rh CASCADE;
(CASCADE also the schema associated to the user)
SYS user
can start/stop the DB.
OS Authentication
it is possible to connect using that authentication if the current user is member of the dba user group.
on linux :
useradd -g dba <user>
Thanks to a password file located in :
$ORACLE_HOME/dbs/orapw<BDDNAME>
To create the file :
orapwd file=<path> password=<sys-password> [entries=<#entries>]
For security purposes, it is possible to disable remote connection if the parameter REMOTE_LOGIN_PASSWORDFILE.
Can be NONE or EXCLUSIVE
Parameter Files
2 categories : pfile and spfile
It can be interesting in case of errors to generate the pfile from spfile in order to diagnose the possible problems due to bad conf.
named pfile<SID>.ora, init<SID>.ora or spfile<SID>.ora (spfile<SID>.ora is read if it exists, otherwise it tries to read pfile<SID>.ora or init<SID>.ora)
you can create spfile from pfile and conversely :
create spfile from pfile create pfile from spfile
Sample content of a pfile (instance is named COURS):
COURS.__data_transfer_cache_size=0 COURS.__db_cache_size=587202560 COURS.__inmemory_ext_roarea=0 COURS.__inmemory_ext_rwarea=0 COURS.__java_pool_size=16777216 COURS.__large_pool_size=33554432 COURS.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment COURS.__pga_aggregate_target=637534208 COURS.__sga_target=956301312 COURS.__shared_io_pool_size=50331648 COURS.__shared_pool_size=251658240 COURS.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/COURS/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u01/app/oracle/oradata/COURS/controlfile/o1_mf_fw94rvoy_.ctl','/u01/app/oracle/fast_recovery_area/COURS/COURS/controlfile/o1_mf_fw94rvqf_.ctl' *.control_management_pack_access='none' *.db_block_size=8192 *.db_create_file_dest='/u01/app/oracle/oradata' *.db_domain='nat.fr' *.db_name='COURS' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/COURS' *.db_recovery_file_dest_size=8016m *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=COURSXDB)' *.memory_target=1515m *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
The parameter file only contains parameters that have been tailored to the instance (e.g. 30~ parameters out of a possible 500)
To see a parameter via SQL command :
SHOW PARAMETER param
Compatibility
Feature compatibility
It can be necessary to run the database under a particular version.
it is possible to do so by setting the compatible parameter value to a previous version.
Performance compatibility
It is also possible to use the optimization processes from a previous version.
this is driven by the optimizer_features_enable parameter
When modifying parameter, you can modify it either in memory (no persistence), in the spfile (only active in next startup) or both
example :
alter system set sga_target = 400M scope=BOTH;
MMON
Tools to analyse the performances, which publishes data to AWR repository (stored in SYSAUX).
OEM Database
It's a tool allowing to administer the Database.
In order to activate OEM Database, it is necessary to open sql*plus or SQL developer
select dbms_xdb_config.getHttpsPort from dual;
if the above command returns 0, then it is necessary to open an HTTPS port:
exec dbms_xdb_config.setHttpsPort(5500);
you can browse the dashboard at this location :
https://localhost:5500/em
Disabling the performance pack
By default, the performance analysis is activated, it is mandatory to deactivate it unless you pay for the performance extension pack.
To disable it, open OEM then in
"Configuration" > Initialization Parameters > (type "control" in the filter)
Then click on "control_management_pack_access" and click on define and select none
SQL Developer
SQL Developer is located under :
/u01/app/oracle/product/12.2.0/dbhome_1/sqldeveloper
DBA View allows to browse the detailed characteristics of the current database.
This view is reserved to system admins
Oracle Universal Installer
Can create a new database engine or apply patchsets.
CPU: Critical Patch Update
included in PSUs, contains patches for security issues
PSU : Patches Set Update
5th level of versioning, eg: 11.2.0.4.1
DBCA Tool
DBCA Tool allows to create new Databases.
It can use a template to derive from in order to fasten the database creation.
This tool is located in :
/u01/app/oracle/product/12.2.0/dbhome_1/bin
Starting up a database
sqlplus sys/oracle as sysdba
>startup
Variable substitution:
Variables substitutions are available thanks to the & symbol
Variable concatenation:
&1. the "." indicates the end of variable.
Allows to get history for SQLPLUS
Connecting through SQL*Plus
Connecting with user/password@SID:
sqlplus scott@orcl
Connecting locally (with default SID, defined in ORACLE_SID env variable)
sqlplus scott/tiger
Data export / import
export-import is a generalistic tool, that allows :
4 levels of export/import :
