Admin Oracle

Data export / importexport-import is a generalistic tool, that allows :applicative table or schema savetransfer a base or a tablespace from a machine to anotherversion migration in case of server changeschema renameDB reorganisation4 levels of export/import :FULLSCHEMASTABLESTABLESPACES (since Oracle 9i)

Connecting through SQL*PlusConnecting with user/password@SID:sqlplus scott@orcl Connecting locally (with default SID, defined in ORACLE_SID env variable)sqlplus scott/tiger

Allows to get history for SQLPLUS

Variable substitution:Variables substitutions are available thanks to the & symbolVariable concatenation:&1. the "." indicates the end of variable.

Starting up a databasesqlplus sys/oracle as sysdba>startup

DBCA ToolDBCA 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

Oracle Universal InstallerCan create a new database engine or apply patchsets.

PSU : Patches Set Update5th level of versioning, eg: 11.2.0.4.1

CPU: Critical Patch Updateincluded in PSUs, contains patches for security issues

SQL DeveloperSQL 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

OEM DatabaseIt's a tool allowing to administer the Database.In order to activate OEM Database, it is necessary to open sql*plus or SQL developerselect 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 packBy 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

MMONTools to analyse the performances, which publishes data to AWR repository (stored in SYSAUX).

Parameter Files2 categories : pfile and spfilespfile is recorded under a binary formatpfile is plain text.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

When modifying parameter, you can modify it either in memory (no persistence), in the spfile (only active in next startup) or bothexample : alter system set sga_target = 400M scope=BOTH;

DB_NAME : database name (up to 8 chars). Often equals instance name ($ORACLE_SID)CONTROL_FILES : name of control file, oracle searches for this at instance startupUNDO_TABLESPACE : name of undo segments tablespacePROCESSES : parameter that limits processes number for the OSSGA_TARGET : SGA sizePGA_AGGREGATE_TARGET : max cumulated PGA sizeMEMORY_TARGET : max PGA + SGA sizeDB_BLOCK_SIZE : data bloc size (between 2 ko and 32 ko)Cannot be modified without recreating DB. 8ko and 16ko are common valuesCOMPATIBLE : compatibility parameter (see version compatibility, cannot go back to earlier versions)DB_RECOVERY_FILE_DEST (since 10g) : points to flash recovery area (archive log and RMAN save by default)DB_RECOVERY_FILE_DEST_SIZE : max size of files in flash recovery areaSTATISTICS_LEVEL : stats collection level. 3 possible levels : BASIC, TYPICAL, ALLBASIC unables automatic stats managementTYPICAL (default) enable automatic stats management AWRALL more stats but less performanceLOG_ARCHIVE_DEST_n : archivelog dest directoryNLS_LANGUAGE : default instance language, used for messages, date and time. Default value is replaced by NLS_LANG of each session.NLS_TERRITORY : default instance territory, used to know days and weeks, defualt date formatting, monetary symbols and num separatorsDIAGNOSTIC_DEST : diagnostic directory root

CompatibilityFeature compatibilityIt 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 compatibilityIt is also possible to use the optimization processes from a previous version.this is driven by the optimizer_features_enable parameter

For security purposes, it is possible to disable remote connection if the parameter REMOTE_LOGIN_PASSWORDFILE. Can be NONE or EXCLUSIVE

Thanks to a password file located in :$ORACLE_HOME/dbs/orapw<BDDNAME> To create the file :orapwd file=<path> password=<sys-password> [entries=<#entries>] 

OS Authenticationit is possible to connect using that authentication if the current user is member of the dba user group.on linux : useradd -g dba <user>

Operators

SYS usercan start/stop the DB.

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)

Example of quota allocation : ALTER USER rhuser1 QUOTA UNLIMITED on TSRHTAB; ALTER USER comuser1 QUOTA 0 on TSCOMTAB;

2 types of grants :Objects grants ; manipulation on specific objets, e.g. update/select/insert/delete on a table, or execute on a procedureSystem grants ; manipulation in a whole schemaList of object privilèges :AlterDeleteExecuteIndexInsertReferencesSelectUpdate

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

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)

VSS ServiceVSS Service allows to integrate the Oracle database and the Virtual Machine Snapshot

Starting the databaseStarting the listenerlsnrctl start Starting the databaseexport 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 OPENThe corresponding instance status is respectively STARTED, MOUNTED and OPENHere 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

The first state while starting the DB, where you can just create the DB, or re create the control fileThis is a state where you can start an instance even though the database doesn't physically exist yet

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

The third state of a DB start, where all files are open and users can create tablespaces, tables and indexes

to shut down database, you have to specify the following syntax :SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]NORMAL : wait for all transactions to endTRANSACTIONAL : same as above but you can't create new transactions in the meantimeIMMEDIATE : rollback existing transactions then shutdown immediately the DBABORT : shutdown immediately without even considering existing transactions (they will be rolled back at the next start)to switch from OPEN to MOUNT state of DB, you have to shutdown the instance and reboot in MOUNT state

Standard EditionEntreprise EditionPersonal Editionexpress Edition

Killing active sessionsSoft killfirst 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 killfind 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

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 automaticallyExample 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

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 stateIn case of control file based view, accessible only from the MOUNT stateAliases : x$, v_$ (use v_$ for grants)

Memory based viewlocal instance ; different than gv$session which represents all instances cumulated

Control file based view

Control file based view

Memory based view

show current parameter value

show parameter value in spfile

show parameter with multiple values

show default values of parameter

System viewsDBA_* : needs system accessALL_* : 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_* queryingexampleSELECT * FROM dba_tables WHERE owner NOT IN ('SYS','SYSTEM') ORDER BY owner; 

DBMS_METADATA to obtain ddlThanks 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"

RAC DatabaseSeveral database instances with automatic load balancing.High availability system, the database cluster can be spread across the company network.

Automatic Storage Management

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 madethe control file thus contains :DB nameOther datafiles and redo logs locationdate and time of DB creationSequence number of the current redo logSCNlast checkpoint infoRMAN save dataRedo log and archive log history usageYou can see the control file of a databse via OEM in the menu Storage > Control file

System Change Number

Optimal Flexible ArchitectureORACLE BASE = u01/app/oracle ORACE_HOME = u01/app/oracle/product/12.1/db_1

Oracle InventoryAllows to record all the oracle products

SPFILEContains the information for the database parameter.it is located under :/u01/app/oracle/product/12.2.0/dbhome_1/dbs

Schema = propertyUser = identityTo Oracle, Schema and User are quite the same notion.

Implements a backup database thanks to "redo" transactions which allows to use physical standby database in the case where the primary database fails

Relational DataBase Management systemallows better performance, better data integrity and multi usage (competitive access)

Automatic Diagnostic RepositoryEach 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;

Alert logs are logs tracing majors events, like :DB creationShutdown/StartupOperations on DB structureInfo on writing and archive of redo logsInternal errors (ORA-600), data corruption (ORA-1578), deadlocks (ORA-000060)name : alert_<SID>.log

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 criticalityhow 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 ;

Consists of formerly Online Redo Log that have already been filled with logs.

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 LOGhow to manually change current redo log :alter system switch logfile;

System Global Area(should be dimensioned for 5-10% of data volume in size)

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

Serves for java stored procedures

Serves for data streaming optimization (e.g. when using data pump)

Blocks of data read in the diskContains data filesWhen a request is made, a whole block is loaded even for the simplest request

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;

DatabaseWriter nExecutes 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 LogDatabaseWriter also executes Checkpoints when needed

Log WriterWrites the queued transactions in an Online Redo Log

Program Global Area / Private Global AreaMemory 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+

A commit can end the transaction, validating the modifications in the DBThe 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.

Independant of user connexionsLaunched at instance startupExample of background process :Database Writer (DBWn) : writing modified blocs on diskLog Writer (LGWR) : writing on disk of redo log buffer, validate transactionsProcess Monitor (PMON) : clean and rollback after abrupt end sessionSystem Monitor (SMON) : consistency of database after abrupt server closing (executes media recovery to recover archive log + redo logs)Archiver (ARCn) : archive redo logCheckpoint (CKPT) : trigger a checkpoint to synchronize redo log and data filesManageability Monitor (MMON)

1 per sessionthe server process transfers the datafiles modified to the data buffer cache, but does not execute the modificationsA PA is associated to each server process

Multi Thread ServerIn 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 insteadThe dispatcher puts the session in a queue and the first server process available will be assigned to the sessionIn Oracle 10g and later, you can switch dynamically to MTS with the following command :alter system set shared_servers = 3;

Database accessed by an instance is divided in tablespaces

TablespacesA 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 tempfileList of differents tablespaces :DATA : storage of applicatives tables and indexesSYSTEM : storage of tables and indexes belonging to data dictionary (not applicatives tables/indexes)SYSAUX : storage of performance indicators such as AWR (Automatic Workload Repository), logminers, and so on) only present on oracle 10g and afterUNDO : storage of rollback segments ; when updating a data in a table, the undo tablespace stores the old value for a set time. It can be accessed in the case, for example, of a select command executed BEFORE the updateUSERS : storage of usersTEMP : storage of sorting and grouping when PGA is overloadedDifferent 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

SegmentSpace occupied by an object in a tablespace.Different types of segments : Table segmentsIndex segmentsLOB segments (space occupied by Large OBjects)rollback/undo segmentstemporary segmentsThese types are more or less equivalent to their tablespace

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;

Oracle Managed FilesSimplified 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

Physical representation of data, tables, index storage

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

Transactions are :AtomicConsistentIsolatedDurableTransaction lead to a COMMIT or a ROLLBACKOption AUTOCOMMIT can be activated, after one or several transactions

Used for transaction rollback, DB consistency and flashback operations

Multi Version Concurrency ControlUsed to allow multiple reading of a table without placing locks

SELECT instruction doesn't place locklocking unit is data lineLocking is automatic on INSERT, UPDATE, DELETE (Exclusive type lock)Use view DBA_WAITERS in case of locks for more info

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.

Constraints are verified after each operationIf constraints are specified as DEFERRABLE, they are verified after each transaction instead

To connect to a distant DB, you have to know :DB namemachine nameTCP IP portvalid accountWhen creating a DB, you have access to :ORACLE_SIDDB_NAME / DB_UNIQUE_NAMEINSTANCE_NAMEglobal name = DB_NAME + DB_DOMAINservice

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.

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

This file contains sqlnet alisases.It contains 2 main things :An address list which contains addresses to server protocol + host + portThe SID OR the service name of the DB instance that you wish to accessExample :# 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

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.oralsnrctl stop Stop the listenerslsnrctl status Display listeners statuslsnrctl reload Reload configuration

Descriptive file of the listener2 parts to this file :Description of the listener itself, which protocol it uses, what host and port it listens toThe list of different SIDs which gives the different DBs info, in case you must reboot remotely on of the DB (contains DB name, oracle home and SID name)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

shell command : netmgrThis manager can help configure the different files, sqlnet.ora, tnsnames.ora and listener.ora

ORA-12154 TNS : could not resolve service name Local instance name not known in tnsnames.oraBeware if the automatic suffix is defined in sqlnet.ora, in that case tnsnames.ora must take it into accountORA-12203 TNS : unable to connect to destination Listener not started, or address error in tnsnames.oraORA-12500 TNS : listener failed to start a dedicated server process The listener is correct but DB not startedORA-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.oraOR the listener doest not listen DB connexionsOR DB is in dynamic declaration and is not startedORA-12533 TNS : illegal ADDRESS parameters Error in tnsnames.ora fileORA-12545 TNS : name lookup failure Error in tnsnames.ora fileORA-12560 TNS : protocol adapter error Windows error, can originate form service OracleService<instance_name> not startedOR ORACLE_SID missing or bad definition in local instanceOR ORACLE_HOME bad definitionORA-1034 Oracle not available DB not started

unidirectional link between 2 DBexample of DB link creation : CREATE PUBLIC DATABASE LINK rh_usa CONNECT TO scott IDENTIFIED BY tiger USING 'conn_link';