Search this blog for other interesting articles

Thursday 28 July 2011

Activating emergency user in java only system

This article answers the following queries:


  • How to unlock, if administrator user id got locked in a standalone java  alone system?
  • How to activate emergency user in java only system?
  • How to handle, if administrator user id got locked in dual stack system?


 What are the scenarios in which emergency user SAP*  to be activated?
In some cases, you might have configured user management incorrectly and can no longer logon to any application or all administrator users got locked. In these cases, you can activate emergency user SAP* which enables you to  logon to application.

Process to be followed to activate emergency user SAP*

Start the config tool
Set the following User Management Engine(UME)  properties


Property
Value
Remarks
ume.superadmin.activated
True
This activates SAP* user
ume.superadmin.password
<password>
Enter the new password for SAP* user. This option is provided by SAP to ensure more security so that system is not accessible with default password



Restart the AS Java
The SAP* user will now get activated and all others user will get deactivated. You can log on with the SAP* user and fix your configuration as per the requirement in cases of  incorrect user management configuration.
After that, go to  identity management to unlock all the locked users.
After fixing the configuration issue, SAP* user should be deactivated again as mentioned below:


  • Start the config tool
  • Set the property ume.superadmin.activated to false
  • Restart the AS Java.

 Please note all the above process to activate emergency user is for standalone java system.

How to handle,   if administrator user id got locked in dual stack system?
 In case of dual stack installation i.e. ABAP + JAVA stack it is very easy. Please proceed as mentioned below to resolve the issue:
i)   Logon to ABAP system with any administrator user or the SAP* user
ii) Either create a new administrator user for java stack or unlock the locked administrator user using SUo1







How to recover an SAP instance when a control file got corrupted?


This article answers the following queries:
·         What are the scenarios in which contol file should be backed up in oracle?
·         How to backup control file in oracle?
·         How to recover an instance when one of the  control files got corrupted ?
·         How multiple control files are managed in Oracle ?
---------------------------------------------------------------------------------------
Scenarios in which control file to be backed up  in Oracle
It is very important to backup control file whenever there is a change in the physical structure of the database. Some of the examples of structural changes are
  • Adding or dropping or renaming of datafiles
  • Adding or dropping a tablespace
  • Altering the read/write state of the tablespace
  • Adding or dropping redolog files or groups

Control file backup procedure  in Oracle
ALTER DATABASE BACKUP CONTROLFILE statement is used to backup control files in Oracle.
There are 2 options to backup control files.
  1. Backup the control file to a binary file (duplicate of existing control file) using the below statement:
ALTER DATABASE BACKPUP CONTROLFILE  TO  ‘/oracle/backup/control.bkp’;

  1. Produce SQL statements that can later be used to re-create control file.

ALTER DATABASE BACKUP CONTROLFILE  TO TRACE;
Above command writes  a SQL script to the database trace file where it can be captured and edited to regenerate the control file.

Recovering an instance when one of the  control files got corrupted
Oracle recommends to have atleast 2 control files each stored on a different disk.
(Please note, for SAP applications , 3 control files are generally maintained in an oracle database at different locations)
If a control file got corrupted due to a disk failure the respective instance should be shut down. Once the disk drive is repaired, the corrupted control file can be restored using the copy of control file from other disk and the instance can be brought up. In these cases, media recovery is not required.

How multiple control files are managed in Oracle?

  • All the control files that are listed in the CONTROL_FILES parameter are updated by the database.
  • The database reads only the first file listed in the CONTROL_FILES parameter during database operation
  • If any of the control files got corrupted or become unavailable during database operation, the instance won’t function and it should be shutdown and recovered as mentioned earlier








Enter your email address:


Delivered by FeedBurner



Steps to be followed to rename or move control files location

This article answers the following queries :
·         How to find out the current location of control files?
·         Can we rename or move control file location? What is the parameter for that ?
·         What is the process to be followed to rename or move control file location?
-----------------------------------------------------------------------------------------
Query to find current location of control files :

SQL > select name from v$controlfile;

After executing the above query, System outputs  the location of all control files in the system
Usually there will be 3 control files in oracle database( for SAP applications).

Can we rename or move control files location? If so, what is that parameter?

Yes. If required, control files location can changed with the control_files instance parameter.
Please execute the below query to display the current value of control_files parameter
SQL > show parameter control_files
The above command outputs the current value. This value can be changed with the ALTER SYSTEM command. In other words the current location of the control files can be changed  using ALTER SYSTEM command.

Steps to be followed to rename or move control files location

1)      Alter the control_files parameter using the ALTER SYSTEM command
Syntax:
SQL> ALTER SYSTEM SET control_files=’<New controlfile1 path or newcontrol file name>’, <New controlfile2 path or newcontrol file name>’, <New controlfile13 path or newcontrol name>’ SCOPE=SPFILE;
 Eg: In the below command set the paths or names  for various control files  as per your requirement
SQL> ALTER SYSTEM SET control_files=’D:\ORACLE\..\..\..\Newname1.CTL’, 
’D:\ORACLE\..\..\..\Newname2.CTL’,  ’D:\ORACLE\..\..\..\Newname3.CTL’
SCOPE=SPFILE;

2)     Shutdown the database
SQL > shutdown immediate;

3)     Rename the physical file in the OS
SQL >  HOST  MOVE <old controlfile with path> <new controlfile with path >
 Eg:
SQL > HOST  MOVE  D:\ORACLE\ORADATA\CONTROL01.CTL  D:\ORACLE\ORADATA\NEW_CONTROL01.CTL
4)     Start the database
SQL > startup



Enter your email address:


Delivered by FeedBurner

Control file and its functions in Oracle


This article answers the following queries :
·         What is the control file in oracle?
·         What are the functions of control file in oracle ?
·         What are the  control file contents ?
·         Can database function properly without control file?
·         Can we edit control file ?
·         Who can modify control file ?
·         At what time control file will be created?


What is control file in Oracle and what are its functions ?
Control file is a small binary file which is necessary for the database to start and operate successfully.

What are the functions of control file ?
Each control file is associated with only one database.  Oracle continuosly updates control file during database use so this file must be available for writing whenever the database is in the open state.  Control file will be created at the same time of database creation.
Whenever a datafile or a redolog file is added to , dropped or renamed in the database, the control file is updated to reflect the physical structural change.
These changes are recorded in control file which facilitates :

  • Oracle to identify the files that are required or available in case database recovery is needed
  • Oracle to identify the datafiles and redolog files to open during database startup.

Can database function properly without control file?
No. Database cannot function properly if  the control file is not available.

Can we edit control file ? Who can modify control file ?
Please note control file cannot be edited by database administrator or by any user. Control file information can only be modified by Oracle.

Contents of control files :

  • Control file contains information such as
  • Database name
  • Timestamp of database creation
  • Tablespace information
  • The log history
  • Archive log information
  • Current log sequence number
  • Checkpoint information
  • Names and locations of associated datafiles and redolog files
  • Datafile copy information
  • Datafile offline ranges
  • Backup set and backup related information
  • Backup datafile and redolog information






Enter your email address:


Delivered by FeedBurner

Enabling or Disabling archivelog mode in Oracle


This article answers the following queries :
·         How to findout  whether current archive mode  in Oracle ?
·         How to connect to SQL in oracle?
·         How to findout whether archive mode is on or off in oracle ?
·         How to enable archive log mode in Oracle ?
·         How to disable archive log mode in Oracle ?
·         What are the steps to be followed in Oracle to change archive log  mode?
·         How to shut down the oracle database ?
·         How to startup the oracle database in mount mode?


How to findout current archive mode in Oracle ?

Goto SQL prompt of oracle and provide the following command to find out the archive mode  in Oracle.

SQL > select  log_mode from v$database;

It ouptus similar to as shown below if the database is in no archive mode.
LOG_MODE
------------------
NO ARCHIVE LOG

Steps to be followed in Oracle to Change the archive log mode

1)      Connect to SQL plus
How to connect to SQL ?
First login to the database server using orasid user
             Then connect to sqlplus as follows
sqlplus /nolog
conn /as sysdba

Then you will be connected to database and SQL prompt appears as below:
SQL >

2)     Find out the current archive log mode by providing below command as explained earlier:
SQL > select  log_mode from v$database;

3)     If current archive log is NO ARCHIVE LOG mode and if you would like to set to ARCHIVE LOG mode, first of all shut down the oracle database.
How to shutdown oracle database?
Proceed as follows to shutdown the database
           SQL > shutdown immediate;
            Please wait for a while and system brings down  the oracle database and SQL prompt   
            appears.

4)     Please note database should be in mount mode while changing the archive log mode in Oracle.
How to start the oracle database in Mount mode?
Start the database in mount mode as shown below :
SQL> startup mount
An output similar to below will appear:
ORACLE Instance started
Total System Area  xxxxxxxx  bytes
Fixed size                     xxxxxx bytes
Variable size              xxxxxxx bytes
Database buffers            xxxxx bytes
Redo buffers                     xxxx bytes
Database mounted

5)     Now since database is mounted provide the following command at SQL      
      prompt

SQL > alter database archivelog;
Once this command is given System prompts you with a message “Database altered”

DISABLING ARCHIVE LOG MODE
Please note : For example, if you would like to set the database in NO ARCHIVE LOG mode ( or disable archive log mode ) then command should be
SQL > alter database  noarchivelog;

6)     Now open the database using the following command
SQL > alter database open ;
After this system prompts you with a message “Database altered” confirming that the data base opened

7)     Now cross check the current archive mode using the command below
SQL > select  log_mode from v$database;
If you have enabled archive log mode then system prompts with a message like
LOG_MODE
----------------
ARCHIVE LOG

Please note changing archive log mode can also be done through brtools utility also. Will explain that process for the same in another article







Enter your email address:


Delivered by FeedBurner



Monday 25 July 2011

Difference between PFILE and SPFILE in Oracle


This article answers the following queries :

  • What is PFILE in Oracle?

  • What is SPFILE  in Oracle?

  • What is the use of PFILE or SPFILE in Oracle ?

  • What is the difference between PFILE and SPFILE in Oracle ?

  • What are the advantages of SPFILE compared to PFILE in Oracle ?

  • How to check whether database is  running with PFILE or SPFILE ?



Use of PFILE or SPFILE

When an Oracle instance is started, its characteristics are set by reading some initialization parameters. These parameters are specified either through PFILE or SPFILE.  (0r)

When an oracle instance is being started, it reads either PFILE or SPFILE to set its characteristics.


What is PFILE ?

A PFILE is a static, client side text file. This file normally resides on the server. However in case you would like to start oracle from a remote machine, you will need a local copy of the PFILE in that machine.

This file can be edited with a standard text editor like notepad or vi editor. This file will be commonly referred as INIT.ORA file.

What is SPFILE ?

SPFILE (Server parameter file) is a persistent server side binary file. SPFILE should only be modified through “ALTER SYSTEM SET” command . Editing an SPFILE directly will corrupt the file and the start up of the database or instance will not be possible.

As SPFILe is a server side binary file,  local copy of the PFILE is not required to start oracle from a remote machine.


Advantages of SPFILE compared to PFILE

  • A SPFILE doesnot need a local copy of  the pfile to start  oracle from a remote machine.  Thus eliminates configuration problems.

  • SPFILE  is a binary file and modications to that can only be done through ALTER SYSTEM SET command.

  • As SPFILE is maintained by the server, human errors can be eliminated as the parameters are checked before modification in SPFILE

  • It is easy  to locate SPFILE as it is stored in a central location

  • Changes to the parameters in SPFILE will take immediate effect without restart of the instance i.e Dynamic change of parameters is possible

  • SPFILE can be backed up by RMAN


Difference between PFILE and SPFILE in Oracle :


SNO
PFILE
SPFILE
1
Static, client side text file
Persistent server side binary file
2
Local copy of pfile required to start database from a remote machine
local copy is not required
3
Can be edited through notepad or vi editor
Editing directly will corrupt the file. It should be modified only through ALTER SYSTEM SET command
4
Is available in earlier versions of Oracle 9i
Available from Oracle 9i and above
5
Prone to human errors while modification
Eliminates human errors as parameters are checked before modification
6
Cannot be backed up by RMAN
Can be backed up by RMAN
7
Parameters in pfile cannot be changed dynamically and system needs to be bounced for the new changes to take effect
Dynamic change of some parameters is possible. Need not restart server for the changes to take effect



Query to findout, if database is running with PFILE or SPFILE

Goto SQL prompt and execute the following command to figure out if database is started with a PFILE or SPFILE.


SQL > SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File” FROM sys.v_$parameter WHERE name = ‘spfile’;





Enter your email address:


Delivered by FeedBurner



Search this blog for other interesting articles

Please subscribe & activate link received to your email id to receive latest articles

Enter your email address:

Delivered by FeedBurner