- 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