Oracle/Database/Oracle Management

From Omnia
< Oracle‎ | Database
Revision as of 16:45, 15 April 2015 by Kenneth (talk | contribs) (Created page with "== Oracle Management == == Oracle Documentation == Oracle Database Online Documentation 11g Release 2 (11.2) - http://www.oracle.com/pls/db112/homepage * Oracle® Database A...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Oracle Management

Oracle Documentation

Oracle Database Online Documentation 11g Release 2 (11.2) - http://www.oracle.com/pls/db112/homepage

Oracle Database Online Documentation 11g Release 1 (11.1) - http://www.oracle.com/pls/db111/homepage

TNS Listener Configuration

NOTE: Server configuration file.

Config File: 

$ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome\network\admin\listener.ora
# 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: 

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome\network\admin\tnsnames.ora
# 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 <machine: ORACLEDB, pid: 2108>
         (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:

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

keywords

Retrieved from "https://aznot.com/index.php?title=Oracle/Database/Oracle_Management&oldid=1993"