One database with two instances of same name from same user

Hi guy’s, hope all of you doing well.Yesterday we face new issue, I would like to share same with you. In one of our development server scheduled cold backup use to happened on weekly basis.In this database while going with health check I found that two instances are running with same name & user of single database.hope you understood the issues.

bash-3.00$ ps -ef |grep pmon
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orad21  8486  8427   0 23:26:44 pts/2       0:00 grep pmon

Reason: In this server schedule cold backup use to happen on weekly backup. But while shutting down it was not clean shutdown so showing the two background process for each.

bash-3.00$ ps -ef |grep pmon
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orasam  8500  8427   0 23:29:08 pts/2       0:00 grep pmon

Now see the background process as well.You may find two different background processes are running with same name but with different date & time.

bash-3.00$ ps -ef |grep ora_
  orasam  1670     1   0   Apr 27 ?           0:03 ora_smon_sam
  orasam  1662     1   0   Apr 27 ?           0:07 ora_dbw2_sam
  orasam 29000     1   0   Feb 18 ?          21:31 ora_dbw0_sam
  orasam 29002     1   0   Feb 18 ?          19:49 ora_dbw1_sam
  orasam 29020     1   0   Feb 18 ?         143:50 ora_mmnl_sam
  orasam 29025     1   0   Feb 18 ?          11:35 ora_arc1_sam
  orasam 28994     1   0   Feb 18 ?         155:24 ora_pmon_sam
  orasam  1672     1   0   Apr 27 ?           0:01 ora_reco_sam
  orasam 29029     1   0   Feb 18 ?           0:54 ora_qmnc_sam
  orasam  1656     1   0   Apr 27 ?           0:06 ora_mman_sam
  orasam 29035     1   0   Feb 18 ?           0:01 ora_q000_sam
  orasam 29012     1   0   Feb 18 ?          36:35 ora_smon_sam
  orasam  1668     1   0   Apr 27 ?           0:08 ora_ckpt_sam
  orasam 29010     1   0   Feb 18 ?          79:27 ora_ckpt_sam
  orasam 29018     1   0   Feb 18 ?           7:22 ora_mmon_sam
  orasam 28998     1   0   Feb 18 ?           4:21 ora_mman_sam
  orasam  1658     1   0   Apr 27 ?           0:08 ora_dbw0_sam
  orasam 29016     1   0   Feb 18 ?         119:51 ora_cjq0_sam
  orasam 29023     1   0   Feb 18 ?          14:19 ora_arc0_sam
  orasam 29006     1   0   Feb 18 ?          20:45 ora_dbw3_sam
  orasam 28996     1   0   Feb 18 ?           3:52 ora_psp0_sam
  orasam  1654     1   0   Apr 27 ?           1:10 ora_pmon_sam
  orasam  1674     1   0   Apr 27 ?           0:05 ora_cjq0_sam
  orasam  1660     1   0   Apr 27 ?           0:07 ora_dbw1_sam
  orasam 29014     1   0   Feb 18 ?           0:04 ora_reco_sam
  orasam 29004     1   0   Feb 18 ?          20:12 ora_dbw2_sam
  orasam 29008     1   0   Feb 18 ?          63:42 ora_lgwr_sam
  orasam  1664     1   0   Apr 27 ?           0:07 ora_dbw3_sam
  orasam  8226     1   3 23:14:21 ?           2:51 ora_m000_sam
  orasam  8506  8427   0 23:29:29 pts/2       0:00 grep ora_
  orasam  6996     1   0   Apr 19 ?           0:04 ora_q002_sam
  orasam  1666     1   0   Apr 27 ?           0:06 ora_lgwr_sam
 
Action: Find out the current background pid of database, please find the query for same.
 
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type = ‘BACKGROUND’
                and p.spid= ‘1662’;
 

Excluding these pid kill rest all pid’s
Except these all background pid was killed.(It was for Apr 27).So you can killed
Please find the current status & difference as well.
 
bash-3.00$ ps -ef |grep ora_
  orasam 29000     1   0   Feb 18 ?          21:33 ora_dbw0_sam
  orasam 29002     1   0   Feb 18 ?          19:51 ora_dbw1_sam
  orasam 29020     1   0   Feb 18 ?         144:07 ora_mmnl_sam
  orasam 29025     1   0   Feb 18 ?          11:35 ora_arc1_sam
  orasam 28994     1   0   Feb 18 ?         155:43 ora_pmon_sam
  orasam 29029     1   0   Feb 18 ?           0:55 ora_qmnc_sam
  orasam 29035     1   0   Feb 18 ?           0:01 ora_q000_sam
  orasam 29012     1   0   Feb 18 ?          36:42 ora_smon_sam
  orasam  9030  8427   0 02:52:13 pts/2       0:00 grep ora_
  orasam 29010     1   0   Feb 18 ?          79:36 ora_ckpt_sam
  orasam 29018     1   0   Feb 18 ?           7:23 ora_mmon_sam
  orasam 28998     1   0   Feb 18 ?           4:21 ora_mman_sam
  orasam 29016     1   0   Feb 18 ?         120:06 ora_cjq0_sam
  orasam 29023     1   0   Feb 18 ?          14:20 ora_arc0_sam
  orasam 29006     1   0   Feb 18 ?          20:47 ora_dbw3_sam
  orasam 28996     1   0   Feb 18 ?           3:52 ora_psp0_sam
  orasam 29014     1   0   Feb 18 ?           0:04 ora_reco_sam
  orasam 29004     1   0   Feb 18 ?          20:14 ora_dbw2_sam
  orasam 29008     1   0   Feb 18 ?          63:46 ora_lgwr_sam
  orasam  8226     1   3 23:14:21 ?         205:37 ora_m000_sam
  orasam  6996     1   0   Apr 19 ?           0:04 ora_q002_sam
bash-3.00$

Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

SAP BRTOOLS for table reorganization

** Tested with UNIX / Solarise operating system.

Sorry guy’s for delay on new post.As we were on trip to GOA for one week.So now we will continue with BR Tool.

So here is another one tested example for table reorganization using the BRTOOL. Hope you guys enjoy with this one as well…..!!!!

bash-3.00$ /oracle/brspace -u / -f tbreorg -t BALDAT -degree 8
BR1001I BRSPACE 7.00 (51)
BR1002I Start of BRSPACE processing: sefphmbn.tbr 2011-04-06 07.40.07
BR0484I BRSPACE log file: /oracle/P2/sapreorg/sefphmbn.tbr
BR1301W Error message from likeywlib:
===…could not load SSF library /usr/sap/SAM/SYS/exe/run/libsapsecu.so .

BR1301W Error message from likeywlib: likey_init: Tried to load SAPSECU lib (“/usr/sap/SAM/SYS/exe/run/libsapsecu.so”), rc = 10.
BR1301W Error message from likeywlib:
===…could not load SSF library libsapsecu.so .

BR1301W Error message from likeywlib: likey_init: Couldn’t load SAPSECULIB (“libsapsecu.so”) using function SsfSupInit (), rc = 10.
BR1302W Initialization of license key library likeywlib failed, return code 1
BR1304W Checking SAP license failed at location BrLicCheck-108
BR0602W No valid SAP license found – please contact SAP

BR0280I BRSPACE time stamp: 2011-04-06 07.40.14
BR1009I Name of database instance: SAM

BR1010I BRSPACE action ID: sefphmbn
BR1011I BRSPACE function ID: tbr
BR1012I BRSPACE function: tbreorg

BR0280I BRSPACE time stamp: 2011-04-06 07.40.22
BR0657I Input menu 353 – please enter/check input values
——————————————————————————-
Options for reorganization of tables: SAPSR3.BALDAT (degree 1)

 1 ~ New destination tablespace (newts) …….. []
 2 ~ Separate index tablespace (indts) ……… []
 3 – Parallel threads (parallel) …………… [1]
 4 ~ Table/index parallel degree (degree) …… [8]
 5 – Create DDL statements (ddl) …………… [yes]
 6 ~ Category of initial extent size (initial) . []
 7 ~ Sort by fields of index (sortind) ……… []
 8 – Table reorganization mode (mode) ………. [online]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2011-04-06 07.40.39
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.40.39
BR1108I Checking tables for reorganization…

BR0280I BRSPACE time stamp: 2011-04-06 07.40.40
BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0280I BRSPACE time stamp: 2011-04-06 07.40.41
BR0370I Directory /oracle/SAM/sapreorg/sefphmbn created

BR0280I BRSPACE time stamp: 2011-04-06 07.40.41
BR1101I Starting online table reorganization…
BR0280I BRSPACE time stamp: 2011-04-06 07.40.42
BR1124I Starting online reorganization of table SAPSR3.BALDAT …
BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1105I Table SAPSR3.BALDAT reorganized successfully

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1141I 1 of 1 table processed – 14230500 of 14230500 rows done
BR0204I Percentage done: 100.00%, estimated end time: 7:54
BR0001I **************************************************

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2011-04-06 07.54.13
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRSPACE time stamp: 2011-04-06 07.57.27
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
y
BR0280I BRSPACE time stamp: 2011-04-06 07.57.36
BR0257I Your reply: ‘y’
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: sefphmbn.tbr 2011-04-06 07.57.36
BR0280I BRSPACE time stamp: 2011-04-06 07.57.37
BR1007I BRSPACE terminated with errors

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

SAP BRTOOLS for tablespace extend

** Tested with UNIX / Solarise operating system.

In our organisation SAP is front end application. So as DBA we do have exposure on BRTOOL,BRSPACE etc.So just sharing the BR tool handling issue from todays onwards.

So here is one tested example for adding datafile in tablespace using the BRTOOL. Hopw you guys enjoy with this one…..!!!!

 

bash-3.00$ brtools
BR0651I BRTOOLS 7.00 (11)

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.35
BR0656I Choice menu 1 – please make a selection
——————————————————————————-
BR*Tools main menu

 1 = Instance management
 2 – Space management
 3 – Segment management
 4 – Backup and database copy
 5 – Restore and recovery
 6 – Check and verification
 7 – Database statistics
 8 – Additional functions
 9 – Exit program

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2011-04-06 07.10.39
BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.39
BR0656I Choice menu 5 – please make a selection
——————————————————————————-
Database space management

 1 = Extend tablespace
 2 – Create tablespace
 3 – Drop tablespace
 4 – Alter tablespace
 5 – Alter data file
 6 – Move data file
 7 – Additional space functions
 8 – Reset program status

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
1
BR0280I BRTOOLS time stamp: 2011-04-06 07.10.57
BR0663I Your choice: ‘1’

BR0280I BRTOOLS time stamp: 2011-04-06 07.10.57
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

 1 – BRSPACE profile (profile) …… [initSAM.sap]
 2 – Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) … []
 4 – Confirmation mode (confirm) …. [yes]
 5 – Scrolling line count (scroll) .. [20]
 6 – Message language (language) …. [E]
 7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.11.09
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.11.09
BR0681I Enter string value for “tablespace” []:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.11.32
BR0683I New value for “tablespace”: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.11.32
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

 1 – BRSPACE profile (profile) …… [initSAM.sap]
 2 – Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) … [3]
 4 – Confirmation mode (confirm) …. [yes]
 5 – Scrolling line count (scroll) .. [20]
 6 – Message language (language) …. [E]
 7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend -t 3]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.12.55
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.12.55
BR0681I Enter string value for “tablespace” [3]:

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.17
BR0686I The value of “tablespace” was not changed

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.17
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

 1 – BRSPACE profile (profile) …… [initSAM.sap]
 2 – Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) … [3]
 4 – Confirmation mode (confirm) …. [yes]
 5 – Scrolling line count (scroll) .. [20]
 6 – Message language (language) …. [E]
 7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend -t 3]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-04-06 07.13.59
BR0663I Your choice: ‘3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.13.59
BR0681I Enter string value for “tablespace” [3]:
PSAPSR3
BR0280I BRTOOLS time stamp: 2011-04-06 07.14.51
BR0683I New value for “tablespace”: ‘PSAPSR3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.14.51
BR0657I Input menu 81 – please check/enter input values
——————————————————————————-
BRSPACE options for tablespace extension

 1 – BRSPACE profile (profile) …… [initSAM.sap]
 2 – Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) … [PSAPSR3]
 4 – Confirmation mode (confirm) …. [yes]
 5 – Scrolling line count (scroll) .. [20]
 6 – Message language (language) …. [E]
 7 – BRSPACE command line (command) . [-p initSAM.sap -s 20 -l E -f tsextend -t PSAPSR3]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2011-04-06 07.15.13
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initSAM.sap -s 20 -l E -f tsextend -t PSAPSR3’

BR0280I BRTOOLS time stamp: 2011-04-06 07.15.13
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRTOOLS time stamp: 2011-04-06 07.15.25
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.00 (11)
BR1002I Start of BRSPACE processing: sefphjwn.tse 2011-04-06 07.15.25

BR0280I BRSPACE time stamp: 2011-04-06 07.15.26
BR1009I Name of database instance: SAM
BR1010I BRSPACE action ID: sefphjwn
BR1011I BRSPACE function ID: tse
BR1012I BRSPACE function: tsextend

BR0280I BRSPACE time stamp: 2011-04-06 07.15.26
BR0657I Input menu 303 – please check/enter input values
——————————————————————————-
Options for extension of tablespace PSAPSR3 (1. file)

 1 * Last added file name (lastfile) ……. [/oracle/SAM/sapdata2/sr3_30/sr3.data30]
 2 * Last added file size in MB (lastsize) . [10240]
 3 – New file to be added (file) ……….. [/oracle/SAM/sapdata2/sr3_31/sr3.data31]
 4 ~ Raw disk / link target (rawlink) …… []
 5 – Size of the new file in MB (size) ….. [10240]
 6 – File autoextend mode (autoextend) ….. [no]
 7 # Maximum file size in MB (maxsize) ….. []
 8 # File increment size in MB (incrsize) .. []
 9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31’ size 10240M autoextend off]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
3
BR0280I BRSPACE time stamp: 2011-04-06 07.16.07
BR0663I Your choice: ‘3’

BR0280I BRSPACE time stamp: 2011-04-06 07.16.07
BR0681I Enter string value for “file” (<file>|<sapdata>|<N>) [/oracle/SAM/sapdata2/sr3_31/sr3.data31]:
/oracle/SAM/sapdata2/sr3_31/sr3.data31
BR0280I BRSPACE time stamp: 2011-04-06 07.17.24
BR0683I New value for “file”: ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31’

BR0280I BRSPACE time stamp: 2011-04-06 07.17.24
BR0657I Input menu 303 – please check/enter input values
——————————————————————————-
Options for extension of tablespace PSAPSR3 (1. file)

 1 * Last added file name (lastfile) ……. [/oracle/SAM/sapdata2/sr3_30/sr3.data30]
 2 * Last added file size in MB (lastsize) . [10240]
 3 – New file to be added (file) ……….. [/oracle/SAM/sapdata2/sr3_31/sr3.data31]
 4 ~ Raw disk / link target (rawlink) …… []
 5 – Size of the new file in MB (size) ….. [10240]
 6 – File autoextend mode (autoextend) ….. [no]
 7 # Maximum file size in MB (maxsize) ….. []
 8 # File increment size in MB (incrsize) .. []
 9 – SQL command (command) …………….. [alter tablespace PSAPSR3 add datafile ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31’ size 10240M autoextend off]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2011-04-06 07.17.49
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.17.49
BR1091I Next data file can be specified now
BR0675I Do you want to perform this action?
BR0676I Enter ‘y[es]’ to perform the action, ‘n[o]/c[ont]’ to skip it, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2011-04-06 07.18.17
BR0257I Your reply: ‘c’
BR0678I The action will be skipped…
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-04-06 07.18.17
BR0370I Directory /oracle/SAM/sapreorg/sefphjwn created

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR0319I Control file copy created: /oracle/SAM/sapreorg/sefphjwn/cntrlSAM.old 14237696

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR0370I Directory /oracle/SAM/sapdata2/sr3_31 created

BR0280I BRSPACE time stamp: 2011-04-06 07.18.18
BR1088I Extending tablespace PSAPSR3…

BR0280I BRSPACE time stamp: 2011-04-06 07.19.33
BR1016I SQL statement ‘alter tablespace PSAPSR3 add datafile ‘/oracle/SAM/sapdata2/sr3_31/sr3.data31′ size 10240M autoextend off’ executed successfully
BR1051I Tablespace PSAPSR3 extended successfully with file: /oracle/SAM/sapdata2/sr3_31/sr3.data31 10240M

BR0280I BRSPACE time stamp: 2011-04-06 07.19.33
BR0340I Switching to next online redo log file for database instance SAM…
BR0321I Switch to next online redo log file for database instance SAM successful

BR0280I BRSPACE time stamp: 2011-04-06 07.19.35
BR0319I Control file copy created: /oracle/SAM/sapreorg/sefphjwn/cntrlSAM.new 14237696

BR0280I BRSPACE time stamp: 2011-04-06 07.19.35
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRSPACE time stamp: 2011-04-06 07.21.00
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
yes
BR0280I BRSPACE time stamp: 2011-04-06 07.21.03
BR0257I Your reply: ‘yes’
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: sefphjwn.tse 2011-04-06 07.21.03
BR0280I BRSPACE time stamp: 2011-04-06 07.21.03
BR1007I BRSPACE terminated with errors

###############################################################################

BR0292I Execution of BRSPACE finished with return code 4

BR0668I Warnings or errors occurred – you can continue to ignore them or go back to repeat the last action
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.03
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
s
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.08
BR0257I Your reply: ‘s’
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
yes
BR0280I BRTOOLS time stamp: 2011-04-06 07.21.11
BR0257I Your reply: ‘yes’
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-04-06 07.21.11
BR0654I BRTOOLS terminated with errors
bash-3.00$

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Oracle Database startup shutdown process in oracle

STARTUP

Normal database operation means that an instance is started and the database is mounted and open. This mode allows any valid user to connect to the database and perform typical data access operations.

Start an instance, read the initialization parameters from the default server parameter file location, and then mount and open the database by using the STARTUP command by itself (you can, of course, optionally specify the PFILE clause):

STARTUP NOMOUNT

You can start an instance without mounting a database. Typically, you can do so only during database creation. Use the STARTUP command with the NOMOUNT clause:

STARTUP MOUNT

You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

• Enabling and disabling redo log-archiving options.
• Performing full database recovery.

Start an instance and mount the database, but leave it closed by using the STARTUP command with the MOUNT clause:

STARTUP FORCE

In unusual circumstances, you might experience problems when attempting to start a database instance. You should not force a database to start unless you are faced with the following:

• You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands.
• You experience problems when starting an instance.

If one of these situations arises, you can usually solve the problem by starting a new instance (and optionally mounting and opening the database) using the STARTUP command with the FORCE clause:

STARTUP RESTRICT

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

• Perform an export or import of database data
• Perform a data load (with SQL*Loader)
• Temporarily prevent typical users from using data
• During certain migration and upgrade operations

Typically, all users with the CREATE SESSION system privilege can connect to an open database. Opening a database in restricted mode allows database access only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege. Only database administrators should have the RESTRICTED SESSION system privilege. Further, when the instance is in restricted mode, a database administrator cannot access the instance remotely through an Oracle Net listener, but can only access the instance locally from the machine that the instance is running on.

Start an instance (and, optionally, mount and open the database) in restricted mode by using the STARTUP command with the RESTRICT clause:

Later, use the ALTER SYSTEM statement to disable the RESTRICTED SESSION feature:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

STARTUP OPEN RECOVER

If you know that media recovery is required, you can start an instance, mount a database to the instance, and have the recovery process automatically start by using the STARTUP command with the RECOVER clause.

=====================================================================

SHUTDOWN NORMAL

To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

Normal database shutdown proceeds with the following conditions:

• No new connections are allowed after the statement is issued.
• Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN IMMEDIATE

Use immediate database shutdown only in the following situations:

• To initiate an automated and unattended backup
• When a power shutdown is going to occur soon
• When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

To shut down a database immediately, use the SHUTDOWN command with the IMMEDIATE clause:

Immediate database shutdown proceeds with the following conditions:

• No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
• Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
• Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

The next startup of the database will not require any instance recovery procedures.

SHUTDOWN TRANSACTIONAL

When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

Transactional database shutdown proceeds with the following conditions:

• No new connections or new transactions allowed starting after the statement is issued.
• After all transactions have completed, any client still connected to the instance is disconnected.
• At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

The next startup of the database will not require any instance recovery procedures.

A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

SHUTDOWN ABORT

You can shut down a database instantaneously by aborting the database instance. If possible, perform this type of shutdown only in the following situations:

The database or one of its applications is functioning irregularly and none of the other types of shutdown works.

• You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).
• You experience problems when starting a database instance.

When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:

An aborted database shutdown proceeds with the following conditions:

• After issuing the statement, no new connection or transaction are allowed.
• Current client SQL statements being processed by Oracle Database are immediately terminated.
• Uncommitted transactions are not roll back.
• Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

The next startup of the database will require instance recovery procedures.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »