Basics

Oracle cheat-sheet (1-day DBA) source: http://ss64.com/ora/

Feel free to look up this PDF. It’s a very good book for beginner DBAs.


LISTENER

lsnrctl status
lsnrctl start
tnsping [instance_name]

STARTUP

Startup a database instance. This is a SQL*Plus command (not part of standard SQL) Syntax:

   STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT 

   STARTUP [FORCE] [RESTRICT] [PFILE=filename] MOUNT [dbname]

   STARTUP [FORCE] [RESTRICT] [PFILE=filename] OPEN [Open_options] [dbname] 

Open_options:
         READ {ONLY | WRITE [RECOVER]} | RECOVER

Key:
   FORCE
                 Shut down the current Oracle instance (if it is running) with
                 SHUTDOWN mode ABORT, before restarting it.
                 If the current instance is running and FORCE is not specified,
                 an error results. FORCE is useful while debugging and under
                 abnormal circumstances. It should not normally be used.

   RESTRICT
                 Only allow Oracle users with the RESTRICTED SESSION system privilege
                 privilege to connect to the database. Later, you can use the
                 ALTER SYSTEM command to disable the restricted session feature.

   PFILE=filename
                 The init.ora parameter file to be used while starting up the instance.
                 If PFILE is not specified, then the default STARTUP parameter file is
                 used. The default file used is platform specific.
                 For example, the default file is $ORACLE_HOME/dbs/init$ORACLE_SID.ora
                 on UNIX, and %ORACLE_HOME%\database\initORCL.ora on Windows.

   MOUNT dbname
                 Mount a database but do not open it.
                 dbname is the name of the database to mount or open.
                 If no database name is specified, the database name is taken
                 from the initialization parameter DB_NAME.

   OPEN
                 Mount and open the specified database.

   NOMOUNT
                 Don't mount the database upon instance startup.
                 Cannot be used with MOUNT, or OPEN.

   RECOVER
                 Specifies that media recovery should be performed, if necessary,
                 before starting the instance.
                 STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE
                 command and starting an instance.
                 Only 'complete recovery' is possible with the RECOVER option.

                 Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON,
                 regardless of whether or not AUTORECOVERY is enabled.

                 If a redo log file is not found in the expected location,
                 recovery will continue by prompting you with the suggested location
                 and name of the subsequent log files that need to be applied.

Startup NOMOUNT will run the background processes for the instance.
Startup MOUNT will open the Control Files.
Startup OPEN will open the Data Files.

On Windows, starting the Oracle services will also startup the instance.

Examples
Startup with a pfile or SPfile

When starting an Oracle instance with the simple command ‘startup’, Oracle will first look for a binary SPfile in the default location; if it can’t find one, it will look for a pfile(init.ora) in the default location.

If you start the oracle instance and specify pfile=xxx then Oracle will use the ‘old-fashioned’ pfile as you requested.

Startup pfile=”C:\oracle\admin\live\pfile\init.ora”

Strangely there is no option that will start an oracle instance with a binary spfile residing in a non-default location.
As a workaround for this, create a traditional init.ora containing one line:
SPfile=E:\pathtoyourSPfile\SPfileLive.ora
Then startup the instance calling the pfile which in turn will reference the SPfile that you want.
For this to work – make sure you don’t also have an SPfile in the default location.


ALTER DATABASE

Open an existing database, and /or modify associated files.

Syntax:

   ALTER DATABASE database_name options

Options:

open/mount options:
   MOUNT
   MOUNT STANDBY DATABASE
   MOUNT CLONE DATABASE
   MOUNT PARALLEL
   MOUNT STANDBY DATABASE
   CONVERT
   OPEN [READ ONLY]
   OPEN [READ WRITE] RESETLOGS|NORESETLOGS [MIGRATE]
   ACTIVATE STANDBY DATABASE
   [NATIONAL] CHARACTER SET char_set

archivelog options:
   ARCHIVELOG
   NOARCHIVELOG

backup and recovery options:
   BACKUP CONTROLFILE TO 'filename' [REUSE]
   BACKUP CONTROLFILE TO TRACE [RESETLOGS] [AS 'filename' [REUSE]]
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   RECOVER recover_clause
   RECOVER MANAGED STANDBY standby_recover_clause
   END BACKUP

Datafile options:
   CREATE DATAFILE 'filename' AS filespec
   DATAFILE 'filename' ONLINE
   DATAFILE 'filename' OFFLINE [DROP]
   DATAFILE 'filename' RESIZE int K | M
   DATAFILE 'filename' AUTOEXTEND OFF
   DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]
   DATAFILE 'filename' END BACKUP
   RENAME FILE 'data_file_name' TO 'data_file_name'

   TEMPFILE 'filename' ONLINE
   TEMPFILE 'filename' OFFLINE
   TEMPFILE 'filename' DROP [INCLUDING DATAFILES]
   TEMPFILE 'filename' RESIZE int K | M
   TEMPFILE 'filename' AUTOEXTEND OFF
   TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M | UNLIMITED]

redo log options:
   ADD LOGFILE [THREAD int] [GROUP int] filespec
   ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int
   ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'
   DROP LOGFILE GROUP int
   DROP LOGFILE ('filename')
   DROP LOGFILE MEMBER 'filename'
   RENAME FILE 'redolog_file_name' TO 'redolog_file_name'
   CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]
   CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]

Parallel server options:
   CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]
   SET DBLOW = 'text'
   SET DBHIGH = 'text'
   SET DBMAC = ON | OFF
   ENABLE [PUBLIC] THREAD int
   DISABLE THREAD int

Backwards compatibility options:
   RENAME GLOBAL_NAME TO database [domain]
   RESET COMPATIBILITY

Any option above that includes a ‘filename’ can be extended to cover multiple files using the syntax: (‘filename1′,’filename2’)

‘filename’ [offline] DROP will only work on a tablespace consisting of a single datafile, for a tablespace comprising multiple datafiles you can only drop a datafile by dropping the entire tablespace.

Some of the commands above can only be used when the database is in a particular state:

MOUNT, CONVERT – Require that the db is Not Mounted.
ARCHIVELOG, NOARCHIVLOG, RECOVER – Require that the db is Mounted but not open (must be mount exclusive – not mount parallel).
ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET – Require that the db is Open.

All other options will work with the db mounted, open or closed as long as none of the files involved are ‘in use’

database_name is defined when the database is created – it is normally set to the same as the database SID.

Examples

Resize datafile:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' RESIZE 500m;

Take datafile offline:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' OFFLINE;
Database altered.

Take datafile online, when doing this, file recovery is needed to update the timestamp in the offline datafile header.:

SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;
 ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE
 *
 ERROR at line 1:
 ORA-01113: file 16 needs media recovery
 ORA-01110: data file 16: 'F:\ORADATA\LIVE\Mydb02.ORA'
SQL> RECOVER DATAFILE 'F:\oradata\live\Mydb02.ora';
 Media recovery complete.
SQL> ALTER DATABASE DATAFILE 'F:\oradata\live\Mydb02.ora' ONLINE;
Database altered

Change the database character set to UTF8, this must be done with no users connected (restricted session)

SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
    <do a full backup>
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET UTF8;
SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
SQL> STARTUP;

SHUTDOWN

Shutdown a database instance. This is a SQL*Plus command (not part of standard SQL) Syntax:
   SHUTDOWN ABORT

   SHUTDOWN IMMEDIATE

   SHUTDOWN TRANSACTIONAL [LOCAL]

   SHUTDOWN NORMAL

key:
   ABORT
              The fastest possible shutdown of the database without waiting for
              calls to complete or users to disconnect.
              Uncommitted transactions are not rolled back.
              Client SQL statements currently being processed are terminated.
              All users currently connected to the database are implicitly disconnected
              and the next database startup will require instance recovery.

              You must use this option if a background process terminates abnormally.

   IMMEDIATE
              Does not wait for current calls to complete or users to disconnect from
              the database.
              Further connects are prohibited.
              The database is closed and dismounted.
              The instance is shutdown and no instance recovery is required on the next database startup.

   NORMAL
              NORMAL is the default option which waits for users to disconnect from
              the database.
              Further connects are prohibited.
              The database is closed and dismounted.
              The instance is shutdown and no instance recovery is required on the next database startup.

   TRANSACTIONAL [LOCAL]

              A planned shutdown of an instance, allowing active transactions to complete first.
              It prevents clients from losing work without requiring all users to log off.
              No client can start a new transaction on this instance.
              Attempting to start a new transaction results in disconnection.
              After completion of all transactions, any client still connected to
              the instance is disconnected.
              Now the instance shuts down (SHUTDOWN IMMEDIATE).
              The next startup of the database will not require any instance recovery procedures.

              The LOCAL mode specifies a transactional shutdown on the local instance only,
              so that it only waits on local transactions to complete, not all transactions.
              This is useful, for example, for scheduled outage maintenance.

This site uses Akismet to reduce spam. Learn how your comment data is processed.