Please enable JavaScript.

Coggle requires JavaScript to display documents.

Chapter 5. MANAGING THE ORACLE INSTANCE (5.01 Describe the Stages of…

- - - - emctl start dbconsole
      - emctl stop dbconsole
      - emctl status dbconsole
  - - - lsnrctl start
      - lsnrctl status
  - - - connect user/pass[connect_alias]
      - connect user/pass[connect_alias] as sysdba
      - connect user/pass[connect_alias] as sysoper
      - connect / as sysdba
      - connect / as sysoper
    - - SYSOPER
        
        STARTUP
        
        SHUTDOWN
        
        ALTER DATABASE [MOUNT | OPEN | CLOSE | DISMOUNT]
        
        ALTER [DATABASE | TABLESPACE] [BEGIN | END] BACKUP
        
        RECOVER
      - SYSDBA
        
        All of SYSOPER's privilege
        
        Create a database
        
        Do an incomplete recovery
        
        Create other SYSOPER and SYSDBA
    - - SHUTDOWN
        
        All files are closed
        
        The instance does not exist
      - NOMOUNT
        
        The instance has been built in memory
        
        No connection has been made to a database
        
        Try to locate one of 3 default parameter files
        
        $ORACLE_HOME/dbs/spfileSID.ora
        
        $ORACLE_HOME/dbs/spfile.ora
        
        $ORACLE_HOME/dbs/initSID.ora
        
        Alert log location: BACKGROUND_DUMP_DEST
      - MOUNT
        
        The instance locates and reads the database control file
      - OPEN
        
        All database files are located and opened
        
        The database is made available for use by end users.
    - - NORMAL (Clean)
        
        No new user connection will be permitted
        
        All current connection are allowed to continue
        
        Only once all users have logged off, will the database shut down.
      - TRANSACTIONAL (Orderly)
        
        No new user connection are permitted.
        
        Existing sessions that are not in a transaction will be terminated
        
        Session currently in a transaction are allowed to complete the transaction -> terminated
        
        Once all sessions are terminated, the database will shut down.
      - IMMEDIATE (Consistent)
        
        No new sessions are permitted, and all currently connected sessions are terminated.
        
        Any active transactions are rolled back.
        
        The database will shut down.
      - ABORT (Disorderly)
        
        Equivalent of a power cut
- - - - SELECT name, value FROM v$parameter ORDER BY name;
      - SELECT name, value FROM v$spparameter ORDER BY name;
      - v$parameter shows the parameter values currently in effect in the running instance
      - v$spparameter shows the values in spfile on disk
    - - CREATE spfile [='spfilename'] FROM pfile [='pfilename']
      - CREATE pfile [='pfilename'] FROM spfile [='spfilename']
  - - - The static parameters cannot by changed other than with an ALTER SYSTEM command with a SCOPE=SPFILE clause.
        
        ALTER SYSTEM SET log_buffer=6m SCOPE=SPFILE;
      - Some parameters can applied to the whole instance but can be adjusted for individual sessions
        
        ALTER SYSTEM SET optimizer_mode=all_rows;
        
        ALTER SESSION SET optimizer_mode=first_rows;
      - A few parameters that can only be modified at the session level
        
        ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yy hh24:mi:ss';
    - - SELECT name, value from v$parameter WHERE isbasic='TRUE' ORDER BY name;
      - SELECT s.name, s.value
        FROM v$spparameter s JOIN v$parameter p ON s.name=p.name
        WHERE p.isbasic='TRUE' ORDER BY name;