• databases
• oracle

databases/oracle

About

• Oracle Database Product Documentation - 11g Release 2 (11.2) for IBM AIX on POWER Systems (64-Bit)

Installation

Two administrative user accounts are automatically created when Oracle Database is installed:

• SYS (default password: CHANGE_ON_INSTALL)

• SYSTEM (default password: MANAGER)

Filesystem layout

• Logs are stored in the directory diag

sql*plus

Connecting to the Database with SQL*Plus

• Oracle Database Administrator's Guide 11g, Release 2 (11.2) #Connecting to the Database with SQL*Plus

• Oracle Database Administrator's Guide 11g, Release 2 (11.2) SQL*Plus User's Guide and Reference

• Oracle Database Administrator's Guide 11g, Release 2 (11.2) - Database Net Services Administrator's Guide #Configuring Naming Methods

• Do not enter the password on the cli, use a prompt. (stored in history, visible in process table, …).
• Prepare environment

  • The PATH should include $ORACLE_HOME/bin.

  • On Linux you can store it in your .profile or .bashrc for a permanent configuration or specify it manually before you connect.

       1 export ORACLE_SID orcl
       2 export ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
       3 export LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib
    

  • On Windows the environment is stored in the registry below

    HKEY_LOCAL_MACHINE\SOFTWARE\oracle.

    • Can be overridden on the shell.
      • Windows CMD

           1 set ORACLE_SID="orcl"
           2 set ORACLE_HOME="C:\ORACLE\11.2.0\DATABASE"
           3 echo %ORACLE_SID%
           4 echo %ORACLE_HOME%
        

      • Windows PowerShell

           1 $env:ORACLE_SID="orcl"
           2 $env:ORACLE_HOME="C:\ORACLE\11.2.0\DATABASE"
           3 $env:ORACLE_SID
           4 $env:ORACLE_HOME
        

• /nolog prevents immediate connection to a database.

• Syntax

  • CONNECT statement

       1 CONN[ECT] [logon] [AS {SYSOPER | SYSDBA}]
    

  • where logon is

       1 {username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]
    

    • Single / means external authentication (e.g. OS auth).

    • username refers to a valid database user name.

    • connect_identifier may

      • be a "Oracle Net connect identifier" aka "net service name" for a remote connection.
        • If omitted, sqlplus tries a local connection.

        • Typically resolved via tnsnames.ora, but may also be other ways.

      • use easy connect syntax
        "host[:port][/service_name][:server][/instance_name]"

        • IPv6 addresses must be enclosed with square brackets.

        • Leads to far for now. Please see:
          Oracle Database Net Services Administrator's Guide

    • edition may be a specific edition_name or just the DATABASE_DEFAULT

      • The user must have the USE privilege on the edition.

Formatting output

Defaults

   1 SQL> show linesize;
   2 linesize 80
   3 SQL> show pagesize;
   4 pagesize 14
   5 SQL> show timing;
   6 timing OFF

   1 set linesize 120;
   2 set pagesize 25;
   3 set timing on;

List online redo logs

   1 SELECT * FROM V$LOG;
   2 SELECT * FROM V$LOGFILE;
   3 SELECT * FROM V$LOG_HISTORY;

Fast Recovery Area (FRA)

• aka recovery area

• Oracle Database Online Documentation 11g, Release 2 (11.2) - Configuring the RMAN Environment #Configuring the Fast Recovery Area

• Oracle Database Online Documentation 11g, Release 2 (11.2) - Recovery Manager Architecture #Fast Recovery Area

• Configured in database via

• DB_RECOVERY_FILE_DEST_SIZE

  • DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area.

• DB_RECOVERY_FILE_DEST

  • DB_RECOVERY_FILE_DEST specifies the default location for the fast recovery area.

  • The fast recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

Show current FRA configuration

Connect with sqlplus AS SYSDBA

   1 sqlplus sys/ AS SYSDBA
   2 
   3 SQL> show parameter db_recovery;
   4 NAME                                 TYPE        VALUE
   5 ------------------------------------ ----------- ------------------------------
   6 db_recovery_file_dest                string      D:\ORACLE\ADMIN\FLASH_RECOVERY
   7                                                  _AREA
   8 db_recovery_file_dest_size           big integer 50G

Show FRA usage

Connect with sqlplus AS SYSDBA

   1 set linesize 120;
   2 set pagesize 25;
   3 SELECT * FROM V$RECOVERY_AREA_USAGE;
   4 SELECT * FROM V$RECOVERY_FILE_DEST;

Check flashback state

• Flashback online redo logs are usually stored in FRA.

Use the view

   1 select FLASHBACK_ON FROM V$DATABASE;

Archive log mode and archive logs

Use the view

   1 SQL> select NAME, LOG_MODE from V$DATABASE;
   2 
   3 NAME      LOG_MODE
   4 --------- ------------
   5 dbname    ARCHIVELOG

Show archived redo log destination

   1 show parameter log_archive_dest;
   2 
   3 NAME                                 TYPE        VALUE
   4 ------------------------------------ ----------- ----------------------------------
   5 log_archive_dest                     string
   6 log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_DEST
   7 …

Manage a database and its instances

• Oracle Database Concepts 11g Release 2 (11.2) - 13 Oracle Database Instance

• Oracle Database Online Documentation 11g, Release 2 (11.2) / Database Administration - Database Administrator's Guide #3 Starting Up and Shutting Down

A database goes through the following phases when it proceeds from a shutdown state to an open database state:

• NOMOUNT

  • Instance started without mounting database.
  • The instance is started, but is not yet associated with a database.

• MOUNT

  • Database mounted
  • The instance is started and is associated with a database by reading its control file (see "Overview of Control Files").
  • The database is closed to users.

• OPEN

  • Database open
  • The instance is started and is associated with an open database.
  • The data contained in the data files is accessible to authorized users.

Connect via sqlplus as sysdba and select one of the following states

   1 STARTUP NOMOUNT
   2 STARTUP MOUNT
   3 STARTUP OPEN

A transition between the states can be achieved with

   1 ALTER DATABASE MOUNT;
   2 ALTER DATABASE OPEN;
   3 ALTER DATABASE OPEN READ ONLY;
   4 ALTER DATABASE OPEN READ WRITE;
   5 ALTER DATABASE CLOSE;
   6 ALTER DATABASE NOMOUNT;

In read only mode the data file and redo log files are not touched.

An instance cannot open a previously opened and closed database again. The instance must be shutdown and a new instance opened.

There are several possibilities to shutdown the instance

   1 SHUTDOWN NORMAL;
   2 SHUTDOWN TRANSACTIONAL;
   3 SHUTDOWN IMMEDIATE;
   4 SHUTDOWN ABORT;

The possible SHUTDOWN statements are: * SHUTDOWN ABORT

• This mode is intended for emergency situations, such as when no other form of shutdown is successful.
• This mode of shutdown is the fastest.
• However, a subsequent open of this database may take substantially longer because instance recovery must be performed to make the data files consistent.
• Note:

  Because SHUTDOWN ABORT does not checkpoint the open data files, instance recovery is necessary before the database can reopen. The other shutdown modes do not require instance recovery before the database can reopen.

* SHUTDOWN IMMEDIATE

• This mode is typically the fastest next to SHUTDOWN ABORT.
• Oracle Database terminates any executing SQL statements and disconnects users. Active transactions are terminated and uncommitted changes are rolled back.

* SHUTDOWN TRANSACTIONAL

• This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down.
• This mode can take a significant amount of time depending on the nature of the current transactions.

* SHUTDOWN NORMAL

• This is the default mode of shutdown.
• The database waits for all connected users to disconnect before shutting down.

Useful queries

Table analysis

   1 SELECT table_name, column_name
   2         FROM user_tab_columns
   3         WHERE column_name LIKE '%KEYWORD%' 
   4         ORDER BY table_name;

rman

Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files.

• Oracle Database Backup and Recovery User's Guide 11g Release 2 (11.2)

• rman supports no (dashed) cli options like -h

• Oracle Database Online Documentation 11g, Release 2 (11.2) / Database Administration - Database Backup and Recovery Reference

Connecting to a database with rman

• Connection is made similar to sqlplus.

  Please see
  #Connecting to the Database with SQL*Plus

  • Takes an additional keyword TARGET in front of the rman target database.

  • Requires SYSDBA privileges.

    The AS SYSDBA keywords are implied and cannot be explicitly specified.

Connect as user sys via cli

   1 rman target sys/

Connect as user sys via a CONNECT statement

   1 rman
   2 CONNECT TARGET SYS@dbname

Show the default RMAN configuration

Connect to the target

   1 SHOW ALL;

LIST BACKUP

Connect with sqlplus AS SYSDBA

Show a summary

   1 LIST BACKUP SUMMARY;

Show a detailed list

   1 LIST BACKUP;

Show a list of expired backups

   1 LIST EXPIRED BACKUP;

List archive logs

   1 LIST ARCHIVELOG ALL;

CROSSCHECK

Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository.

Connect with sqlplus AS SYSDBA

   1 CROSSCHECK BACKUP;
   2 LIST EXPIRED BACKUP;

   1 CROSSCHECK ARCHIVELOGS;
   2 LIST ARCHIVELOG ALL;

DELETE EXPIRED

After the update with #CROSSCHECK the expired backups may be deleted.

   1 DELETE EXPIRED BACKUP;