Oracle/Database/Oracle Management

Oracle Documentation
Oracle Database Online Documentation 11g Release 2 (11.2) - http://www.oracle.com/pls/db112/homepage
 * Oracle® Database Administrator's Guide - 11g Release 1 - http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/toc.htm

Oracle Database Online Documentation 11g Release 1 (11.1) - http://www.oracle.com/pls/db111/homepage
 * Oracle® Database Administrator's Guide - 11g Release 1 - http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/toc.htm

TNS Listener Configuration
NOTE: Server configuration file.

Config File: $ORACLE_HOME/network/admin/listener.ora


 * 1) listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome\network\admin\listener.ora
 * 2) Generated by Oracle configuration tools.

SID_LIST_LISTENER = (SID_LIST =   (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome\bin\oraclr11.dll") ) )

LISTENER = (DESCRIPTION_LIST =   (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521)) ) )

ADR_BASE_LISTENER = C:\app\Administrator

TNS Client Configuration
NOTE: May need to be manually created on clients (server should already have)

Config File: $ORACLE_HOME/network/admin/tnsnames.ora

Server:
 * 1) tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome\network\admin\tnsnames.ora
 * 2) Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA = (DESCRIPTION =   (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) )   (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )

ORCL = (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

Client: ken = (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = oracledb)(PORT = 1521))    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

sqlplus sysman/test12@ken

Listener Status
As root or oracle user: $ORACLE_HOME/bin/lsnrctl status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER

Alias                    LISTENER Version                  TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ ction Start Date               05-APR-2011 15:16:39 Uptime                   0 days 0 hr. 15 min. 32 sec Trace Level              off Security                 ON: Local OS Authentication SNMP                     OFF Listener Parameter File  C:\app\Administrator\product\11.2.0\dbhome\network\adm in\listener.ora Listener Log File        c:\app\administrator\diag\tnslsnr\oracledb\listener\al ert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracledb)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully

Listener Services
As root or oracle user: $ORACLE_HOME/bin/lsnrctl services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:65 refused:0 state:ready LOCAL SERVER Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER  (ADDRESS=(PROTOCOL=tcp)(HOST=oracledb)(PORT=49191)) The command completed successfully

Start and Stop Listener
Start Listener: $ORACLE_HOME/bin/lsnrctl start

Stop Listener: $ORACLE_HOME/bin/lsnrctl stop

Reload Listener: $ORACLE_HOME/bin/lsnrctl reload

SQL*Plus
For sqlplus see sqlplus

Database Management
Connect using "/ as sysdba"

Trying to use sysman will result in: SQL> shutdown immediate; ORA-01031: insufficient privileges

Shutdown database service: SHUTDOWN IMMEDIATE;

Startup database service: STARTUP;

Create Database Instance
Methods:
 * Use the Database Configuration Assistant (DBCA). ** PREFERRED **
 * DBCA can be launched by the Oracle Universal Installer, depending upon the type of install that you select, and provides a graphical user interface (GUI) that guides you through the creation of a database. You can also launch DBCA as a standalone tool at any time after Oracle Database installation to create or make a copy (clone) of a database. Refer to Oracle Database 2 Day DBA for detailed information on creating a database using DBCA.
 * Use the CREATE DATABASE statement.
 * You can use the CREATE DATABASE SQL statement to create a database. If you do so, you must complete additional actions before you have an operational database. These actions include creating users and temporary tablespaces, building views of the data dictionary tables, and installing Oracle built-in packages. These actions can be performed by executing prepared scripts, many of which are supplied for you.
 * If you have existing scripts for creating your database, consider editing those scripts to take advantage of new Oracle Database features. Oracle provides a sample database creation script and a sample initialization parameter file with the Oracle Database software files. Both the script and the file can be edited to suit your needs. See "Manually Creating an Oracle Database".

References:
 * Creating a Database with the CREATE DATABASE Statement - http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/create003.htm
 * Creating an Oracle Database - http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#ADMIN002
 * Creating an Oracle 10g database from the command line only - http://www.adp-gmbh.ch/ora/admin/creatingdbmanually.html

Database Backup and Recovery
Oracle Database Backup and Recovery Basics - http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm

System Administration Accounts

 * SYS
 * SYSTEM
 * DBSNMP
 * SYSMAN

Enterprise Manager
iSQL*Plus URL: http://[HOSTNAME]:5560/isqlplus

iSQL*Plus DBA URL: http://[HOSTNAME]:5560/isqlplus/dba

Enterprise Manager 10g Database Control URL: https://[HOSTNAME]:1158/em https://localhost:1158/em

Service Status: (OracleDBConsoleorcl) export ORACLE_UNQNAME=orcl emctl status agent emctl stop dbconsole emctl start dbconsole

Other: emca -repos create emca -config dbcontrol db emca -config dbcontrol db -repos recreate

In windows this is controlled through the service: OracleDBConsoleorcl

ORACLE_UNQNAME not defined
Error: Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

Solution: export ORACLE_UNQNAME=orcl