ORA-00028: your session has been killed
ORA-00028: your session has been killed
Cause: A privileged user has killed your session and you are no longer logged on to the database.
Action: Login again if you wish to continue working. This simply means that your oracle session has been killed. So if you need you can login back to the database provided it is still available. You will also get this when your database goes down while you have a SQL*Plus session to the database.
How to create or view Oracle AWR reports
Oracle provides to script that can be used to create AWR reports.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The first script is used to generate AWR report for a standalone database. The second script is used to
genrate AWR report for different instances in a RAC database cluster. While running the second script it will ask
for the instance ID for which you want to generate the AWR report.
Here I am showing how to run this script to generate these reports. Once they are generated in text or html
format they can be tranfered to your local machine to do further analysis.
Click the link to view Creating awr report for a single instance Oracle database
Click the link to view Creating awr report for a multiple instance (RAC) Oracle database
Tnsnames.ora oracle file explained
Client Server Connection
Oracle client connects to Oracle database server using Oracle Net services. In modern distributed systems, 99% of the time the users (clients) connecting to Oracle database server will connect from a different machine. Oracle Net service facilitates this connection through a number of text based configuration files. Tnsnames.ora is such a configuration file which helps in establishing this connection. This file is located in
Linux: ORACLE_HOME/network/admin
Windows: ORACLE_HOME\network\admin
SQL*Plus command
Consider the command:
sqlplus system/oracle@MYORCL
where
sqlplus – is the binary executable tool you can use to connect to Oracle
system/oracle – is the username/password
@ – sign indicates that you want to connect to a remote database
MYORCL – called an alias. This is a name specified in tnsnames.ora file that will point to the database server to connect.
The main point discussion here is to understand what is “MYORCL” and how my Oracle client will know what MYORCL means. The process of “identifying the destination to connect from the alias” is called name resolution. Continue reading
srvctl modify instance command syntax and example
The srvctl modify instance command is used to modify an instance configuration in the OCR (Oracle cluster registry).
Used to modify the configuration for a database instance from its current node to another node or
changes the dependency between and ASM instance and a database instance.
Syntax:
srvctl modify instance -d db_unique_name -i inst_name {-n node_name | -s asm_instance_name | -r}
-d database name (unique name)
-i database instance name.
-n Node name.
-s asm_instance_name: name of the ASM instance (dependency to database instance).
-r : Remove ASM instance dependency from database instance.
Examples
An example of this command to relocate a database instance is:
srvctl modify instance -d crm -i crm1 -n my_new_node
The following example of this command establishes a dependency between an ASM instance and a database instance:
srvctl modify instance -d crm -i crm1 -s asm1
Oracle database startup stages and commands
Simply starting an Oracle database with startup command is not too complex. Actually it is just running a few commands and your database will come up happily and be ready for normal operation.
For example to start your Oracle database you can just login and execute startup command as follows.
$sqlplus / as sysdba
SQL>startup
This command will start your database.
But in the background there are a few stages that are hidden when you use the above command. Understanding these stages will help you to get a better insight view of Oracle startup process.
Oracle startup process consists of three stages
Stage 1: NOMOUNT
Stage 2: MOUNT
Stage 3: OPEN
Stage 1: NOMOUNT
This is the first stage in the startup process. You can start Oracle database in nomount mode using the command
SQL>startup nomount
When you execute the above command, an Oracle instance is started. When instance starts it will read the initialisation file (commonly known as parameter file) called init.ora file. From this parameter file the instance will know about the size of SGA, PGA, database buffer size and other configurable parameters. The instance will also start the Oracle background process such as (PMON, SMON, LGWR etc). This stage also opens the alert log and the trace files.
Stage 2: MOUNT
The next stage after NOMOUNT is called MOUNT. You can manually start an Oracle database in MOUNT stage using the command
SQL>startup mount
Or when database is already in nomount stage then you can change the stage by running the command
SQL>alter database mount;
When database goes into mount stage, it will read the control files specified in the parameter file. Remember the parameter file was read in the first stage (nomount). The control files contain the information about the physical structure of the database. So the control file will have the names and locations of all the datafiles and online redo log files. At this stage these datafiles and log files are not opened.
Some database administration operations can only be performed when the Oracle database is MOUNT stage. For example Oracle full database recovery can be done only when the database is in mount stage. If you want to rename a datafile you may need to take the database to mount stage unless the tablespace of the datafile is already offline.
Stage 3: OPEN
The final stage in the Oracle startup process. When the database is open then only normal database operations can takes place. Which means users and applications can login and start reading/writing data.
Running the command below will start the Oracle database and put into OPEN stage.
SQL>startup
And if the database is already in MOUNT stage then you can open the database using the command
SQL> alter database open;
When database is open it will open the datafiles and redo log files. If any of these files are missing or corrupted then Oracle will not open successfully and will return error.
Recent Comments