Login to
different database on the same host.
[oracle@localhost
~]$ echo $ORACLE_SID------->Display the name of the current database.
orcl
[oracle@localhost ~]$ ORACLE_SID=trndb------->Name of the database in which we want to login.
orcl
[oracle@localhost ~]$ ORACLE_SID=trndb------->Name of the database in which we want to login.
[oracle@localhost
~]$ echo $ORACLE_SID -------> To confirm name of the DB
Trndb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 21:33:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select instance_name from V$instance;
select instance_name from V$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available---------->problem because trndb is down.
solution:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup open;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
trndb
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
trndb OPEN
Again connecting to orcl database
[oracle@localhost ~]$ ORACLE_SID=orcl
[oracle@localhost ~]$ echo $ORACLE_SID
orcl-----------------------------------.Database name
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
Trndb
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 21:33:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select instance_name from V$instance;
select instance_name from V$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available---------->problem because trndb is down.
solution:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup open;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
trndb
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
trndb OPEN
Again connecting to orcl database
[oracle@localhost ~]$ ORACLE_SID=orcl
[oracle@localhost ~]$ echo $ORACLE_SID
orcl-----------------------------------.Database name
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
orcl OPEN
Note:SQL>
starup open;
SP2-0734: unknown command beginning "starup ope..." - rest of line ignored.
SQL> startup open;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Solution:SQL> startup open;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 1219640 bytes
Variable Size 1157628872 bytes
Database Buffers 402653184 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
Note: the value of ORACLE_SID should be change before
1)starting(opening) database i.e sql>startup open;
2)starting enterprise manager service i.e $emctl start dbconsole
But listner and isqlplus service can be started irrespective of the value of ORACLE_SID
eg.
For trndb
i)$ORACLE_SID=trndb
$sqlplus / as sysdba
SQL>startup open;
SQL>quit
SQL>emctl start dbconsole
For orcl
$ORACLE_SID=orcl
SP2-0734: unknown command beginning "starup ope..." - rest of line ignored.
SQL> startup open;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Solution:SQL> startup open;
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 1219640 bytes
Variable Size 1157628872 bytes
Database Buffers 402653184 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl
Note: the value of ORACLE_SID should be change before
1)starting(opening) database i.e sql>startup open;
2)starting enterprise manager service i.e $emctl start dbconsole
But listner and isqlplus service can be started irrespective of the value of ORACLE_SID
eg.
For trndb
i)$ORACLE_SID=trndb
$sqlplus / as sysdba
SQL>startup open;
SQL>quit
SQL>emctl start dbconsole
For orcl
$ORACLE_SID=orcl
$sqlplus / as
sysdba
SQL>startup open;
SQL>quit
SQL>emctl start dbconsole
Note: No need to start separately
$lsnrctl start
$isqlplus start
[oracle@localhost ~]$ emctl start dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
- An instance of Oracle Enterprise Manager 10g Database Control is already running.
* Connecting using sqlplus
[oracle@localhost ~]$ sqlplus system/oracle@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 21:58:36 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ sqlplus system/trndb@trndb
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 22:02:20 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> quit
Note: Port number for isqlplus is same (ie 5560) for all database
port number for enterprise manager is different for different database.
[oracle@localhost db_1]$ cd /u01/app/oracle/product/10.2.0/db_1/install
[oracle@localhost install]$ ls
createseed1.sh jlib portlist.ini rootlocaladd unix
createseed.sh make.log readme.txt seed.log utl
envVars.properties oratab rootdeletenode.sh templocal
[oracle@localhost install]$ cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158------>for orcl
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (trndb) = 5500------>for trndb
Enterprise Manager Agent Port (trndb) = 1830
* Starting isqlplus from web
http://localhost:5560/isqlplus
eg.
username system system
passwd oracle trndb
connect Identifier orcl trndb
where, orcl and trndb is the database name.
fig1 and fig 2
* Connecting using EM
http;//localhhost:1158/em------------->for orcl
fig1 and fig 2
http://localhost:5500/em----------->for trndb
To confirm EM
[oracle@localhost ~]$ emctl status dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.localdomain_orcl/sysman/log
Note:
emctl stop dbconsole
[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.
emctl status dbconsole
[oracle@localhost ~]$ emctl status dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
emctl start dbconsole
[oracle@localhost ~]$ emctl start dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .........................
....... started.
Database Startup Modes
1)NOMOUNT----An instance is typically started only in NOMOUNT mode during database creation,
during re-creation of control files, or during certain backup and recovery scenarios.
2)MOUNT---- Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and statuses of the data files and online
redo log files. However, no checks are performed to verify the existence of the data
files and online redo log files at this time.
To perform specific maintenance operations, start an instance and mount a database, but
do not open the database.
3)OPEN
A normal database operation means that an instance is started and the database is mounted
and opened. With a normal database operation, any valid user can connect to the database
and perform typical data access operations.
Opening the database includes the following tasks:
• Opening the online data files
• Opening the online redo log files
If any of the data files or online redo log files are not present when you attempt to open the
database, then the Oracle server returns an error.
During this final stage, the Oracle server verifies that all the data files and online redo log
files can be opened and checks the consistency of the database. If necessary, the System
Monitor (SMON) background process initiates instance recovery.
4)RESTRICT
5)FORCE
1)NOMOUNT
2)MOUNT
:Used in maintenance mode
:only DBA can log in
3)OPEN
Generally working mode
shutdown
A I T N
Shutdown Mode
Allows new connections No No No No
Waits until current sessions end No No No Yes
Waits until current transactions end No No Yes Yes
Forces a checkpoint and closes files No Yes Yes Yes
Shutdown mode:
• A = ABORT
• I = IMMEDIATE
• T = TRANSACTIONAL
• N = NORMAL
Starting and shutting down database using Enterprise Manager-----7-feb-2013
first check the port number
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/install
[oracle@localhost install]$ ls
createseed1.sh jlib portlist.ini rootlocaladd unix
createseed.sh make.log readme.txt seed.log utl
envVars.properties oratab rootdeletenode.sh templocal
[oracle@localhost install]$ vi portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (trndb) = 5500
Enterprise Manager Agent Port (trndb) = 1830
In browser;
http://localhost:1158/em
username:sys
password:oracle
connect As :SYSDBA
EM---Home---shutdown
Host credentails-----------both given by OS
username:oracle
password:oracle
Database credentials
username:sys
password:oracle
Database:orcl
Connect As:SYSDBA
click ok
Advance options
(.)immediate
click ok
click yes
SQL command
SHUTDOWN immediate
problem:can't start the database or can't login into EM
.
solution: wait for a while then try again.
click on start up
Host Credentials
username:oracle
password:oralce
database credentials
username:sys
password:oracle
Connect As: SYSDBA
Advanced Options
(.) open the database
click ok
click yes
fig starting2
SQL COMMAND
STARTUP
login to database
username:sys
password:oracle
Connect As :SYSDBA
Initialization Parameter.
location of the initialization parameter
[oracle@localhost db_1]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
hc_orcl.dat initdw.ora lkORCL orapwtrndb
hc_trndb.dat init.ora lkTRNDB spfileorcl.ora
hc_trndp.dat initorcl.ora orapworcl spfiletrndb.ora
spfileorcl.ora----------->server parameter file (binary file)
Also called SPFILE
where,orcl -------------->database name
used by default to open the database.
initorcl.ora------------->Parameter file (text file)
Also called PFILE
where, orcl-------------->DB name
this file is used automatically to open the database,if server paramere(spfile) is missing.
Types of Initialization Parameter
on the basis of their Nature of change
A.Dynamic Parameter-these parameter adopts changes of their value immediately
B.Non-Dynamic (static) Parameter-these paramete adopts changes after database restart.
Creating PFILE from SPFILE
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> create pfile from spfile;
File created.
SQL>quit
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat initdw.ora lkORCL orapwtrndb
hc_trndb.dat init.ora lkTRNDB spfileorcl.ora
hc_trndp.dat initorcl.ora orapworcl spfiletrndb.ora
init.ora---------->pfile
[oracle@localhost dbs]$ cat init.ora
if already exists, then remove it.
[oracle@localhost dbs]$ rm init.ora
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
*Creating SPFILE
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>quit
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
[oracle@localhost dbs]$ rm spfileorcl.ora
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb
hc_trndb.dat initdw.ora lkORCL orapworcl spfiletrndb.ora
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL> startup open;----->spfile is search, if spfile is not found,then initorcl.ora (pfile) file is search.
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 1219640 bytes
Variable Size 1157628872 bytes
Database Buffers 402653184 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
To confirm
[oracle@localhost dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
*By passing SPFILE
oracle@localhost dbs]$ sqlplus / as sysdba
SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora open;-->to open the database using the given PFILE, even if the SPFILE exists.
ORA-01081: cannot start already-running ORACLE - shut it down first
solution:
Viewing Inialization Parameter
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> show parameter <parameter_name>;----->to view the value of the given parameter.
eg.
SQL> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
java_pool_size big integer 0
large_pool_size big integer 0
shared_pool_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 0
SQL>show parameter <pattern>;------->Display values of all the parameters that matches the given pattern.
SQL> show parameter;---------------->Display all parameters.
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
undo_retention integer 900
undo_tablespace string UNDOTBS1
use_indirect_data_buffers boolean FALSE
user_dump_dest string /u01/app/oracle/admin/orcl/udu
*Altering the value of Initialization parameter.
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
SQL> alter system set sga_max_size=1400 scope=both;
alter system set sga_max_size=1400 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
solution:because of non-dynamic
SQL> alter system set sga_max_set=1400M scope=spfile;
alter system set sga_max_set=1400M scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
solution:
SQL> alter system set sga_max_size=1500M scope=spfile;
System altered.
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
SQL> shutdown immediate;
SQL> startup open;
SQL> show parameter sga_max_size;
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
note........need to find out the problem, why the value didn't change.
Viewing and Altering Initiazation parameter using EM.
http://localhost:1158/em
username:sys
password:oracle
connect as :sysdba
EM--administratoion-----under Database Configuration--click all initialization parameters
*Dynamic performance view
Name begin with v$
Dynamic performance views contains real-time data of the database and is useful for the tuning database performance.
http://localhost:5560/isqlplus
username:system
password:oracle
Connect Identifier as orcl
select * from v$sga;
output:
NAME VALUE
Fixed Size 1219184
Variable Size 838862224
Database Buffers 402653184
Redo Buffers 15556608
select * from v$table
problem:select * from v$table
*
ERROR at line 1:
ORA-00942: table or view does not exist
solution-----------need to be find out
select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
shared pool 167772160 167772160 0 0 0 STATIC 16777216
large pool 16777216 16777216 0 0 0 STATIC 16777216
java pool 16777216 16777216 0 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
DEFAULT buffer cache 402653184 402653184 0 0 0 INITIALIZING 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
SQL>startup open;
SQL>quit
SQL>emctl start dbconsole
Note: No need to start separately
$lsnrctl start
$isqlplus start
[oracle@localhost ~]$ emctl start dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
- An instance of Oracle Enterprise Manager 10g Database Control is already running.
* Connecting using sqlplus
[oracle@localhost ~]$ sqlplus system/oracle@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 21:58:36 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ sqlplus system/trndb@trndb
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 7 22:02:20 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> quit
Note: Port number for isqlplus is same (ie 5560) for all database
port number for enterprise manager is different for different database.
[oracle@localhost db_1]$ cd /u01/app/oracle/product/10.2.0/db_1/install
[oracle@localhost install]$ ls
createseed1.sh jlib portlist.ini rootlocaladd unix
createseed.sh make.log readme.txt seed.log utl
envVars.properties oratab rootdeletenode.sh templocal
[oracle@localhost install]$ cat portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158------>for orcl
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (trndb) = 5500------>for trndb
Enterprise Manager Agent Port (trndb) = 1830
* Starting isqlplus from web
http://localhost:5560/isqlplus
eg.
username system system
passwd oracle trndb
connect Identifier orcl trndb
where, orcl and trndb is the database name.
fig1 and fig 2
* Connecting using EM
http;//localhhost:1158/em------------->for orcl
fig1 and fig 2
http://localhost:5500/em----------->for trndb
To confirm EM
[oracle@localhost ~]$ emctl status dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/localhost.localdomain_orcl/sysman/log
Note:
emctl stop dbconsole
[oracle@localhost ~]$ emctl stop dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.
emctl status dbconsole
[oracle@localhost ~]$ emctl status dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
emctl start dbconsole
[oracle@localhost ~]$ emctl start dbconsole
TZ set to Asia/Chungking
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://localhost.localdomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .........................
....... started.
Database Startup Modes
1)NOMOUNT----An instance is typically started only in NOMOUNT mode during database creation,
during re-creation of control files, or during certain backup and recovery scenarios.
2)MOUNT---- Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and statuses of the data files and online
redo log files. However, no checks are performed to verify the existence of the data
files and online redo log files at this time.
To perform specific maintenance operations, start an instance and mount a database, but
do not open the database.
3)OPEN
A normal database operation means that an instance is started and the database is mounted
and opened. With a normal database operation, any valid user can connect to the database
and perform typical data access operations.
Opening the database includes the following tasks:
• Opening the online data files
• Opening the online redo log files
If any of the data files or online redo log files are not present when you attempt to open the
database, then the Oracle server returns an error.
During this final stage, the Oracle server verifies that all the data files and online redo log
files can be opened and checks the consistency of the database. If necessary, the System
Monitor (SMON) background process initiates instance recovery.
4)RESTRICT
5)FORCE
1)NOMOUNT
2)MOUNT
:Used in maintenance mode
:only DBA can log in
3)OPEN
Generally working mode
shutdown
A I T N
Shutdown Mode
Allows new connections No No No No
Waits until current sessions end No No No Yes
Waits until current transactions end No No Yes Yes
Forces a checkpoint and closes files No Yes Yes Yes
Shutdown mode:
• A = ABORT
• I = IMMEDIATE
• T = TRANSACTIONAL
• N = NORMAL
Starting and shutting down database using Enterprise Manager-----7-feb-2013
first check the port number
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/install
[oracle@localhost install]$ ls
createseed1.sh jlib portlist.ini rootlocaladd unix
createseed.sh make.log readme.txt seed.log utl
envVars.properties oratab rootdeletenode.sh templocal
[oracle@localhost install]$ vi portlist.ini
iSQL*Plus HTTP port number =5560
Enterprise Manager Console HTTP Port (orcl) = 1158
Enterprise Manager Agent Port (orcl) = 3938
Enterprise Manager Console HTTP Port (trndb) = 5500
Enterprise Manager Agent Port (trndb) = 1830
In browser;
http://localhost:1158/em
username:sys
password:oracle
connect As :SYSDBA
EM---Home---shutdown
Host credentails-----------both given by OS
username:oracle
password:oracle
Database credentials
username:sys
password:oracle
Database:orcl
Connect As:SYSDBA
click ok
Advance options
(.)immediate
click ok
click yes
SQL command
SHUTDOWN immediate
problem:can't start the database or can't login into EM
.
solution: wait for a while then try again.
click on start up
Host Credentials
username:oracle
password:oralce
database credentials
username:sys
password:oracle
Connect As: SYSDBA
Advanced Options
(.) open the database
click ok
click yes
fig starting2
SQL COMMAND
STARTUP
login to database
username:sys
password:oracle
Connect As :SYSDBA
Initialization Parameter.
location of the initialization parameter
[oracle@localhost db_1]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
hc_orcl.dat initdw.ora lkORCL orapwtrndb
hc_trndb.dat init.ora lkTRNDB spfileorcl.ora
hc_trndp.dat initorcl.ora orapworcl spfiletrndb.ora
spfileorcl.ora----------->server parameter file (binary file)
Also called SPFILE
where,orcl -------------->database name
used by default to open the database.
initorcl.ora------------->Parameter file (text file)
Also called PFILE
where, orcl-------------->DB name
this file is used automatically to open the database,if server paramere(spfile) is missing.
Types of Initialization Parameter
on the basis of their Nature of change
A.Dynamic Parameter-these parameter adopts changes of their value immediately
B.Non-Dynamic (static) Parameter-these paramete adopts changes after database restart.
Creating PFILE from SPFILE
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> create pfile from spfile;
File created.
SQL>quit
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat initdw.ora lkORCL orapwtrndb
hc_trndb.dat init.ora lkTRNDB spfileorcl.ora
hc_trndp.dat initorcl.ora orapworcl spfiletrndb.ora
init.ora---------->pfile
[oracle@localhost dbs]$ cat init.ora
if already exists, then remove it.
[oracle@localhost dbs]$ rm init.ora
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
*Creating SPFILE
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>quit
[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
[oracle@localhost dbs]$ rm spfileorcl.ora
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb
hc_trndb.dat initdw.ora lkORCL orapworcl spfiletrndb.ora
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL> startup open;----->spfile is search, if spfile is not found,then initorcl.ora (pfile) file is search.
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 1219640 bytes
Variable Size 1157628872 bytes
Database Buffers 402653184 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;
File created.
To confirm
[oracle@localhost dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs/
[oracle@localhost dbs]$ ls
hc_orcl.dat hc_trndp.dat initorcl.ora lkTRNDB orapwtrndb spfiletrndb.ora
hc_trndb.dat initdw.ora lkORCL orapworcl spfileorcl.ora
*By passing SPFILE
oracle@localhost dbs]$ sqlplus / as sysdba
SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora open;-->to open the database using the given PFILE, even if the SPFILE exists.
ORA-01081: cannot start already-running ORACLE - shut it down first
solution:
Viewing Inialization Parameter
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> show parameter <parameter_name>;----->to view the value of the given parameter.
eg.
SQL> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
java_pool_size big integer 0
large_pool_size big integer 0
shared_pool_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 0
SQL>show parameter <pattern>;------->Display values of all the parameters that matches the given pattern.
SQL> show parameter;---------------->Display all parameters.
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
undo_retention integer 900
undo_tablespace string UNDOTBS1
use_indirect_data_buffers boolean FALSE
user_dump_dest string /u01/app/oracle/admin/orcl/udu
*Altering the value of Initialization parameter.
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
SQL> alter system set sga_max_size=1400 scope=both;
alter system set sga_max_size=1400 scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
solution:because of non-dynamic
SQL> alter system set sga_max_set=1400M scope=spfile;
alter system set sga_max_set=1400M scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
solution:
SQL> alter system set sga_max_size=1500M scope=spfile;
System altered.
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
SQL> shutdown immediate;
SQL> startup open;
SQL> show parameter sga_max_size;
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1504M
note........need to find out the problem, why the value didn't change.
Viewing and Altering Initiazation parameter using EM.
http://localhost:1158/em
username:sys
password:oracle
connect as :sysdba
EM--administratoion-----under Database Configuration--click all initialization parameters
*Dynamic performance view
Name begin with v$
Dynamic performance views contains real-time data of the database and is useful for the tuning database performance.
http://localhost:5560/isqlplus
username:system
password:oracle
Connect Identifier as orcl
select * from v$sga;
output:
NAME VALUE
Fixed Size 1219184
Variable Size 838862224
Database Buffers 402653184
Redo Buffers 15556608
select * from v$table
problem:select * from v$table
*
ERROR at line 1:
ORA-00942: table or view does not exist
solution-----------need to be find out
select * from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
shared pool 167772160 167772160 0 0 0 STATIC 16777216
large pool 16777216 16777216 0 0 0 STATIC 16777216
java pool 16777216 16777216 0 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
DEFAULT buffer cache 402653184 402653184 0 0 0 INITIALIZING 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
