Basic

startup nomount description

===========================
when you give startup nomount command parameter file is read from $ORACLE_HOME/dbs location.
and then your oracle instance started.
oracle instance is nothing but sga (system global area) and background processes.
If  it is unable to find prameter file it will through below error.


ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/11.1.0/dbs/initebsofdp.ora'


example:
============
SQL> startup nomount
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size                  2145984 bytes
Variable Size            2030043456 bytes
Database Buffers           50331648 bytes
Redo Buffers                5259264 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now sga allocated and background processes started.

[oracle@ebsofdp ~]$ ps -ef|grep -i ora_
oracle    4732     1  0 17:07 ?        00:00:00 ora_pmon_ebsofdp
oracle    4734     1  0 17:07 ?        00:00:00 ora_vktm_ebsofdp
oracle    4738     1  0 17:07 ?        00:00:00 ora_diag_ebsofdp
oracle    4740     1  0 17:07 ?        00:00:00 ora_dbrm_ebsofdp
oracle    4742     1  0 17:07 ?        00:00:00 ora_psp0_ebsofdp
oracle    4746     1  0 17:07 ?        00:00:00 ora_dia0_ebsofdp
oracle    4748     1  0 17:07 ?        00:00:00 ora_mman_ebsofdp
oracle    4750     1  0 17:07 ?        00:00:00 ora_dbw0_ebsofdp
oracle    4752     1  0 17:07 ?        00:00:00 ora_lgwr_ebsofdp
oracle    4754     1  0 17:07 ?        00:00:00 ora_ckpt_ebsofdp
oracle    4756     1  0 17:07 ?        00:00:00 ora_smon_ebsofdp
oracle    4758     1  0 17:07 ?        00:00:00 ora_reco_ebsofdp
oracle    4760     1  0 17:07 ?        00:00:00 ora_mmon_ebsofdp
oracle    4762     1  0 17:07 ?        00:00:00 ora_mmnl_ebsofdp
oracle    4764     1  0 17:07 ?        00:00:00 ora_d000_ebsofdp
oracle    4766     1  0 17:07 ?        00:00:00 ora_s000_ebsofdp
oracle    4786  4692  0 17:07 pts/1    00:00:00 grep -i ora_
[oracle@ebsofdp ~]$




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

startup mount

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

when you give startup mount command it will read the control file .Inside parameter file(init<sid>.ora) you will find control_files which will have controlfile path and numbe of controlfile.
control_files is a static parameter.
select name from v$controlfile;

SQL> alter database mount;

Database altered.

Now controlfile red.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/ebsofdp/control01.ctl_12jan2013
/u01/oracle/oradata/ebsofdp/control02.ctl
/u01/oracle/oradata/ebsofdp/control03.ctl

What happens when you missed controlfile?and what is the error you will get.?

if the backup of the controlfile is available just restore it.if it is not available its no posssible to open the database.
Error:
ORA-00205: error in identifying control file, check alert log for more info.




Things you can do in mount stage.
================================
1)enable/dis abled archive log
2) datafiles/redo log files can renamed at the mount stage.
3)changing dbid/dbname

After mount your database will open.

====================================
Datafiles and online redo log files are check for consistency while opening the database.
Datafiles and redo logfiles are made available to users at the OPEN stage.

What happens when you delete alert log file?

[oracle@devuser bdump]$ ls -ltrh alert*.log
-rw-r-----  1 oracle dba 45K Feb 22 15:21 alert_PROD.log

Now i will remove alert logfile.

[oracle@devuser bdump]$ rm alert_PROD.log

let's do something so that it will write to alert log file.

[oracle@devuser bdump]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 15:23:53 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Now check alert logfile it will get created.

[oracle@devuser bdump]$ ls -ltrh *.log
-rw-r--r--  1 oracle dba 4.5K Feb 22 15:24 alert_PROD.log
[oracle@devuser bdump]$

Which process will write to alert logfile?

Server process.

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

How to put the database in archivelog mode ?

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


[oracle@devuser bdump]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 15:26:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Current log sequence           1

To put the database in archive log mode use below command.

SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1

Also you can check with below commands.

SQL> select archiver from v$instance;

ARCHIVE
-------
STARTED

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> alter database open;

Database altered.

SQL>

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

How to check whether your database startup with the pfile or spfile in 10g ?

SQL> startup
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/home/oracle/product/10.2.
                                                 0/db_1/dbs/spfilePROD.ora

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/home/oracle/product/10.2.
                                                 0/db_1/dbs/spfilePROD.ora
SQL>

You can also check by usin below command

SQL> select name,value from v$parameter2 where name like '%spfile%';

NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------
spfile                                                                           /u01/home/oracle/product/10.2.0/db_1/dbs/spfilePRO
                                                                                 D.ora

==========


SQL> startup nomount pfile=initPROD.ora;
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes

SQL> alter database open;

Database altered.

No path in value column and thats mean your database startup with the pfile.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>

===============
How to find database is 32bit or 64bit ?

Go to $ORACLE_HOME/bin location under you will find oracle executable .

[oracle@devuser bin]$ file /u01/home/oracle/product/10.2.0/db_1/bin/oracle

/u01/home/oracle/product/10.2.0/db_1/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped.



=========

How to change archive destinaton and when to change archive destination ?

if archive destination getting fill you will change archive destination lets se how to change it.
First make a directory where you have free space availble in the mount point .As of now i am creaing
directoy in u02 mount with the name archive.

To check archive destination use below command and maximun you can specify 10 archive destinations.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10


SQL> !ls -ltrh /u01/home/oracle/product/10.2.0/db_1/dbs/arch/*.dbf |wc -l
9

Changing archive destination with below command.

SQL> alter system set log_archive_dest='/u02/archive';

System altered.

To see archive destination path use below command.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10

lets switch logfile to generate archive in u02 mount point and also it will change
sequence number and now switching for 4 times and current sequence number is 10.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
/


System altered.

SQL>
System altered.

SQL> SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/archive
Oldest online log sequence     12
Next log sequence to archive   14
Current log sequence           14
SQL>

lets see whether  archive got generated in /u02 mount point.
SQL> !ls -ltrh /u02/archive/*.dbf |wc -l
4

=======

How to change the dbid ?

1)shutdown immediate
2)startup mount
3) os level nid target=/ (/ means operating system authentication or sysdba privileges)

Current dbid is 219858109.

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
EBSOFDP    219858109

SQL>

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL>

nid is utility to change the dbid/dname .for help use nid help=y.
TO change dbid/dbname you need to have sysdba privileges.

[oracle@devuser PROD]$ nid help=y

DBNEWID: Release 10.2.0.1.0 - Production on Fri Feb 22 18:00:31 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

[oracle@devuser PROD]$ nid target=/

DBNEWID: Release 10.2.0.1.0 - Production on Fri Feb 22 18:00:41 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to database EBSOFDP (DBID=219858109)

Connected to server version 10.2.0

Control Files in database:
    /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control01.ctl
    /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control02.ctl
    /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control03.ctl

Change database ID of database EBSOFDP? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 219858109 to 631773769
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control01.ctl - modified
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control02.ctl - modified
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control03.ctl - modified
    Datafile /u01/home/oracle/product/10.2.0/db_1/PROD/system01.dbf - dbid changed
    Datafile /u01/home/oracle/product/10.2.0/db_1/PROD/undotbs01.dbf - dbid changed
    Datafile /u01/home/oracle/product/10.2.0/db_1/PROD/sysaux01.dbf - dbid changed
    Datafile /u01/home/oracle/product/10.2.0/db_1/PROD/users01.dbf - dbid changed
    Datafile /u01/home/oracle/product/10.2.0/db_1/PROD/temp01.dbf - dbid changed
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control01.ctl - dbid changed
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control02.ctl - dbid changed
    Control File /u01/home/oracle/product/10.2.0/db_1/ebsofdp/control03.ctl - dbid changed
    Instance shut down

Database ID for database EBSOFDP changed to 631773769.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.


above steps are very important which are highlighted in yellow color and in pink more than that.

[oracle@devuser PROD]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 22 18:01:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              62916756 bytes
Database Buffers          117440512 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

Now dbid changed successfully.

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
EBSOFDP    631773769

SQL>

How to take backup of controlfile?

SQL> alter database backup controlfile to trace as '/u02/oracle/control.sql';

Database altered.

SQL>

How to make binaries tar?

nohup tar -cvf - 11.2.0 | gzip -c > /backup/share/1120.tar.gz &
nohup tar -cvf - appl | gzip -c > /backup/share/appl.tar.gz &
nohup tar -cvf - 10.1.2 | gzip -c > /backup/share/1012.tar.gz &
nohup tar -cvf - 10.1.3 | gzip -c > /backup/share/1013.tar.gz &
nohup tar -cvf - comn | gzip -c > /backup/share/comn.tar.gz &

How to do untar?

gunzip -c  /backup/share/1120.tar.gz  | tar -xvf - &

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

How to rename a datafile?

1)shutdown immediate
2)startup mount  (bring the database in mount stage)
2)mv move the file you want to rename to desired name at os level.
3)alter database rename file '/u01/home/oracle/product/10.2.0/db_1/PROD/users01.dbf' to '/u01/home/oracle/product/10.2.0/db_1/PROD/customer01.dbf';
4)aler database open;

Number of tablespace
====================

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

Location of the datafiles.
=============================
SQL> select file_name from dba_data_files ;
FILE_NAME
--------------------------------------------------------------------------------
/u01/home/oracle/product/10.2.0/db_1/PROD/users01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/sysaux01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/undotbs01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/system01.dbf



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL>
Go to the location of datafile i.e, .dbf files.
/u01/home/oracle/product/10.2.0/db_1/PROD
[oracle@devuser PROD]$ ls -tlrh
total 912M
-rw-r-----  1 oracle dba  21M Feb 22 01:56 temp01.dbf
-rw-r-----  1 oracle dba  51M Feb 25 19:27 redo02.log
-rw-r-----  1 oracle dba  51M Feb 25 19:27 redo01.log
-rw-r-----  1 oracle dba 5.1M Feb 25 19:31 users01.dbf
-rw-r-----  1 oracle dba  26M Feb 25 19:31 undotbs01.dbf
-rw-r-----  1 oracle dba 481M Feb 25 19:31 system01.dbf
-rw-r-----  1 oracle dba 231M Feb 25 19:31 sysaux01.dbf
-rw-r-----  1 oracle dba  51M Feb 25 19:31 redo03.log
-rw-r-----  1 oracle dba 6.8M Feb 25 19:34 control03.ctl
-rw-r-----  1 oracle dba 6.8M Feb 25 19:34 control02.ctl
-rw-r-----  1 oracle dba 6.8M Feb 25 19:34 control01.ctl
[oracle@devuser PROD]$ mv users01.dbf customer01.dbf

[oracle@devuser PROD]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 25 19:36:55 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> alter database rename file '/u01/home/oracle/product/10.2.0/db_1/PROD/users01.dbf' to '/u01/home/oracle/product/10.2.0/db_1/PROD/customer01.dbf';
Database altered.

SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL>


SQL> select file_name from dba_data_files ;

FILE_NAME
--------------------------------------------------------------------------------
/u01/home/oracle/product/10.2.0/db_1/PROD/customer01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/sysaux01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/undotbs01.dbf
/u01/home/oracle/product/10.2.0/db_1/PROD/system01.dbf

SQL>

Rename datafile is highlighted in red colour.

Note::it is good practice that name of the tablespace and dbf should be same.

Scenario: tablespace name   users
               file name               users01.dbf

ENJOY AND FEEL THE SUBJECT!!!


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




To move or rename a redo logfile do the following.

1)Shutdown the database.
2)Start the database in mount mode.
3)Rename the physical file on the OS.
4)Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
5)Open the database.

Member of redo log and there location
=====================
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/home/oracle/product/10.2.0/db_1/PROD/redo03.log
/u01/home/oracle/product/10.2.0/db_1/PROD/redo02.log
/u01/home/oracle/product/10.2.0/db_1/PROD/redo01.log

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  188743680 bytes
Fixed Size                  1218412 bytes
Variable Size              67111060 bytes
Database Buffers          113246208 bytes
Redo Buffers                7168000 bytes
Database mounted.

SQL> host mv /u01/home/oracle/product/10.2.0/db_1/PROD/redo01.log /u01/home/oracle/product/10.2.0/db_1/PROD/redofile01.log

SQL> alter database rename file '/u01/home/oracle/product/10.2.0/db_1/PROD/redo01.log' to '/u01/home/oracle/product/10.2.0/db_1/PROD/redofile01.log';

Database altered.

SQL> alter database open;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/home/oracle/product/10.2.0/db_1/PROD/redo03.log
/u01/home/oracle/product/10.2.0/db_1/PROD/redo02.log
/u01/home/oracle/product/10.2.0/db_1/PROD/redofile01.log

SQL>