Friday, October 3, 2014

Oracle 11g R2 Admin Guide - Chapter 03 Starting Up and Shutting Down


- Database Startup Options
 
1. Start Oracle database from SQLPlus

$ sqlplus / as sysdba

SQL> startup

2. Start Oracle database from RMAN

$ rman target /

RMAN> startup

3. Start Oracle database from Enterprise Manager


- Startup Oracle database Using Oracle Restart (SRVCTL)
 
$ srvctl start db -d orcl

$ srvctl status db -d orcl
Database is running.

- To start up with SQL*Plus with a nondefault server parameter file:
 

- Without using Oracle Restart SRVCTL

SQL> startup pfile='?/dbs/initorcl.ora';

- Using Oracle Restart and ASM

$ srvctl modify database -d orcl -p '+DATA/ORCL/spfileorcl.ora'

$ srvctl start db -d orcl

- Using Oracle Restart and a copy of spfile (avoid using pfile with SRVCTL got error!)

SQL> create pfile from spfile;

SQL> create spfile from pfile;

$ srvctl stop db -d orcl 

$ srvctl modify database -d orcl -p '$ORACLE_HOME/dbs/spfileorcl.ora'

$ srvctl start db -d orcl 

$ srvctl status db -d orcl 
Database is running.

- Automatic Startup of Database Services - Oracle recommended using Oracle Restart (SRVCTL)
 

- Adding service "orclsrv" with manual startup option

$ srvctl add service -d orcl -s orclsrv -y manual

- Modifying service "orclsrv" to automatic startup option

$ srvctl modify service -d orcl -s orclsrv -y automatic

- Starting Up an Instance
  • NOMOUNT, Start the instance without mounting a database
  • MOUNT, Start the instance and mount the database, but leave it closed
  • OPEN, Start the instance, and mount and open the database to all users or in restricted mode
  • FORCE, Force the instance to start after a startup or- shutdown problem
  • OPEN RECOVER, complete media recovery begin immediately

- Starting an Instance, and Mounting and Opening a Database
 

$ sqlplus /nolog

SQL> startup;

SQL> startup normal;

$ srvctl start db -d orcl

- Starting an Instance Without Mounting a Database during database creation
 

SQL> startup nomount;

$ srvctl start db -d orcl -o nomount

- Starting an Instance and Mounting a Database to enable redo log archiving options, Performing full database recovery.
 

SQL> startup mount;

$ srvctl start db -d orcl -o mount


- Restricting Access to an Instance at Startup to users with "CREATE SESSION", "RESTRICTED SESSION" privileges for one of the following tasks:
  • Perform an export or import of data
  • Perform a data load (with SQL*Loader)
  • Temporarily prevent typical users from using data
  • Perform certain migration or upgrade operations

 

SQL> startup restrict;

$ srvctl start db -d orcl -o restrict

SQL> alter user hr identified by oracle account unlock;

SQL> conn hr
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> conn / as sysdba

SQL> grant restricted session to hr;

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee='HR'; 
GRANTEE                       |PRIVILEGE                               |ADM
------------------------------|----------------------------------------|---
HR                            |RESTRICTED SESSION                      |NO
HR                            |CREATE SESSION                          |NO

SQL> select grantee, privilege from dba_tab_privs where grantee='HR';
GRANTEE                       |PRIVILEGE
------------------------------|----------------------------------------
HR                            |EXECUTE

SQL> select * from dba_role_privs where grantee='HR';
GRANTEE                       |GRANTED_ROLE                  |ADM|DEF
------------------------------|------------------------------|---|---
HR                            |RESOURCE                      |NO |YES

SQL> conn hr
Enter password: 
Connected

- Change Database status from one to anther using "alter database" statement:
 

SQL> conn / as sysdba

SQL> shutdown immediate;

SQL> startup nomount restrict;

SQL> select instance_name, status, logins, host_name from v$instance;
INSTANCE_NAME   |STATUS      |LOGINS    |HOST_NAME
----------------|------------|----------|----------------------------------------------------------------
orcl            |STARTED     |RESTRICTED|demo1.localdomain

SQL> alter database mount;

SQL> select instance_name, status, logins, host_name from v$instance;
INSTANCE_NAME   |STATUS      |LOGINS    |HOST_NAME
----------------|------------|----------|----------------------------------------------------------------
orcl            |MOUNTED     |RESTRICTED|demo1.localdomain

SQL> alter database open;

SQL> select instance_name, status, logins, host_name from v$instance;
INSTANCE_NAME   |STATUS      |LOGINS    |HOST_NAME
----------------|------------|----------|----------------------------------------------------------------
orcl            |OPEN        |RESTRICTED|demo1.localdomain

SQL> alter system enable restricted session;

SQL> select instance_name, status, logins, host_name from v$instance;
INSTANCE_NAME   |STATUS      |LOGINS    |HOST_NAME
----------------|------------|----------|----------------------------------------------------------------
orcl            |OPEN        |RESTRICTED|demo1.localdomain

SQL> alter system disable restricted session;

SQL> select instance_name, status, logins, host_name from v$instance;
INSTANCE_NAME   |STATUS      |LOGINS    |HOST_NAME
----------------|------------|----------|----------------------------------------------------------------
orcl            |OPEN        |ALLOWED   |demo1.localdomain


- Forcing an Instance to Start (it like shutdown then startup)
 
SQL> startup force;

$ srvctl start db -d orcl -o force


- Automatic Database Startup at Operating System Start
https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

- Opening a Database in Read-Only Mode
 
SQL> startup mount;

SQL> alter database open read only;

SQL> alter database open read write;


- Shutting Down a Database
 
a. SQL Plus
SQL> shutdown normal;

SQL> shutdown immediate;

SQL> shutdown transactional;

SQL> shutdown abort;

b. Oracle Restart (SRVCTL)

$ srvctl stop db -d orcl -o normal

$ srvctl start db -d orcl -o immediate;

$ srvctl start db -d orcl -o transactional;

$ srvctl start db -d orcl -o abort;


- Placing a Database into a Quiesced State
 
SQL> alter system quiesce restricted;

SQL> select active_state from v$instance;
ACTIVE_ST
---------
QUIESCED

SQL> alter system unquiesce;

SQL> select active_state from v$instance; (database states: NORMAL, QUIESCING, QUIESCED)
ACTIVE_ST
---------
NORMAL

- determine the sessions that are blocking the quiesce operation (as sys, hr user has an opened session)
 
SQL> col user format a15
SQL> col osuser format a15
SQL>  select bl.sid, se.serial#, user, osuser, type, program 
      2 from v$blocking_quiesce bl, v$session se where bl.sid = se.sid;
       SID|   SERIAL#|USER           |OSUSER         |TYPE      |PROGRAM
----------|----------|---------------|---------------|----------|------------------------------------------------
       133|        27|HR             |oracle         |USER      |sqlplus@demo1.localdomain (TNS V1-V3)

SQL> alter system kill session '133,27' immediate;

- In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
 
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';


https://oracle-base.com/articles/misc/killing-oracle-sessions

- Suspending and Resuming a Database
 
SQL> alter system suspend;

System altered.

SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
SUSPENDED

SQL> alter system resume;

System altered.

SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
ACTIVE


No comments:

Post a Comment