An Oracle RAC database has the same processes and memory structures as a single-instance Oracle database as well as additional process and memory structures that are specific to Oracle RAC.
The list of Oracle RAC processes are:
1)LMS—Global Cache Service Process
2)LMD—Global Enqueue Service Daemon
3)LMON—Global Enqueue Service Monitor
4)LCK0—Instance Enqueue Process
After one instance caches data, any other instance within the same cluster database can acquire a block image from another instance in the same database faster than by reading the block from disk. This is called cache fusion, any instance need data that is cached by other instance, is passed to it by using private interconnect.
The Global Cache Service(GCS) and Global Enqueue Service(GES) maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). Oracle RAC processes and the GRD collaborate to enable Cache Fusion.
Sunday, August 31, 2008
Oracle Clusterware Processes on UNIX-Based Systems
To support clusterware on unix system several processes must run on each node in the cluster.
The Oracle Clusterware processes on UNIX-based systems are:
1)crsd: This is Cluster Ready Services Daemon. This process runs as root user. It performs high availability recovery and management operations such as maintaining the OCR and managing application resources. This process restarts automatically upon failure.
2)evmd: This is EVent Manager Daemon. This process runs as root user. It starts the racgevt process to manage FAN server callouts.
3)ocssd: This is Oracle Cluster Synchronization Services Daemon. This process runs as oracle user. It manages cluster node membership and failure of this process results in cluster restart.
4)oprocd: This is Oracle PROcess monitor Daemon. This process runs as root user. Note that this process only appears on platforms that do not use vendor clusterware with Oracle Clusterware.
Related Documents
http://arjudba.blogspot.com/2010/03/cluvfy-fails-with-prvf-5436-prvf-9652.html
http://arjudba.blogspot.com/2010/03/in-11gr2-grid-rootsh-fails-with-crs.html
http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/oracle-rac-software-components.html
http://arjudba.blogspot.com/2008/08/oracle-clusterware-processes-on-unix.html
http://arjudba.blogspot.com/2008/08/configure-raw-devices-for-asm-in-rac.html
http://arjudba.blogspot.com/2008/08/crs-stack-fails-to-start-after-reboot.html
http://arjudba.blogspot.com/2008/08/configure-network-for-oracle-rac.html
http://arjudba.blogspot.com/2008/08/pre-installation-rac-environement-setup.html
http://arjudba.blogspot.com/2008/08/configure-server-to-install-oracle-rac.html
The Oracle Clusterware processes on UNIX-based systems are:
1)crsd: This is Cluster Ready Services Daemon. This process runs as root user. It performs high availability recovery and management operations such as maintaining the OCR and managing application resources. This process restarts automatically upon failure.
2)evmd: This is EVent Manager Daemon. This process runs as root user. It starts the racgevt process to manage FAN server callouts.
3)ocssd: This is Oracle Cluster Synchronization Services Daemon. This process runs as oracle user. It manages cluster node membership and failure of this process results in cluster restart.
4)oprocd: This is Oracle PROcess monitor Daemon. This process runs as root user. Note that this process only appears on platforms that do not use vendor clusterware with Oracle Clusterware.
Related Documents
http://arjudba.blogspot.com/2010/03/cluvfy-fails-with-prvf-5436-prvf-9652.html
http://arjudba.blogspot.com/2010/03/in-11gr2-grid-rootsh-fails-with-crs.html
http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/oracle-rac-software-components.html
http://arjudba.blogspot.com/2008/08/oracle-clusterware-processes-on-unix.html
http://arjudba.blogspot.com/2008/08/configure-raw-devices-for-asm-in-rac.html
http://arjudba.blogspot.com/2008/08/crs-stack-fails-to-start-after-reboot.html
http://arjudba.blogspot.com/2008/08/configure-network-for-oracle-rac.html
http://arjudba.blogspot.com/2008/08/pre-installation-rac-environement-setup.html
http://arjudba.blogspot.com/2008/08/configure-server-to-install-oracle-rac.html
Recover database after missing online redo logs and all controlfiles.
This example is based on,
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.
1.
Let's start by deleting online redo log files and controlfile of my running database.
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
SQL> !rm /oradata2/arjudba/arjudba/redo0*
3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.
6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel
9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means
1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.
This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.
Related Documents
Recover database after only lose of all controlfiles
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.
1.
Let's start by deleting online redo log files and controlfile of my running database.
SQL> select MEMBER from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
SQL> !rm /oradata2/arjudba/arjudba/redo0*
3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.
6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel
9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means
1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.
This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.
Related Documents
Recover database after only lose of all controlfiles
Recover database after only lose of all controlfiles
This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.
In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.
Let's start by deleting controlfile of my running database.
1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/control03.ctl
2.Delete all copies of controlfile. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/arjudba/arjudba/control0*
3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info
As it can't read controlfile so ORA-00205 arises.
4.From this state is your situation what you need to do. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below. Note that you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first
Control file created.
6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.
7.Open the database after recovery completes.
SQL> alter database open;
Database altered.
8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode
Thursday, August 28, 2008
Startup fails with ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
Problem Description
While I start my database with my initialization parameter it fails with oracle error ORA-01261 and ORA-01262 as below:
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:04:27
RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory
Cause of The Problem
The linux error No such file or directory indicates all what you need to do. It may be the case that,
-You might not start your database with proper or recently updated pfile/spfile. May be you changed your location of your db_recovery_file_dest and that initialization parameter that you used to startup the database has not reflected yet.
-The directory location specified by the db_recovery_file_dest parameter is not on your system.
Solution of The Problem
If you start your database with pfile edit it and change the location of db_recovery_file_dest parameter to a valid one. Be sure oracle has proper permission on the directory from OS.
However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and change the location of db_recovery_file_dest parameter.
-Create spfile from pfile='1.ora';
-Start your database.
While I start my database with my initialization parameter it fails with oracle error ORA-01261 and ORA-01262 as below:
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:04:27
RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory
Cause of The Problem
The linux error No such file or directory indicates all what you need to do. It may be the case that,
-You might not start your database with proper or recently updated pfile/spfile. May be you changed your location of your db_recovery_file_dest and that initialization parameter that you used to startup the database has not reflected yet.
-The directory location specified by the db_recovery_file_dest parameter is not on your system.
Solution of The Problem
If you start your database with pfile edit it and change the location of db_recovery_file_dest parameter to a valid one. Be sure oracle has proper permission on the directory from OS.
However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and change the location of db_recovery_file_dest parameter.
-Create spfile from pfile='1.ora';
-Start your database.
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value
Problem Description
While performing disaster recovery to a new host, you need to start the database in nomount sate(in fact to restore control file) but database could not start in nomount state because it fails with RMAN-04014, ORA-07446.
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:03:44
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value '/oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump' for parameter user_dump_dest.
Cause of The Problem
The path /oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump as shown in output does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initialize parameter.
Solution of The Problem
If you use pfile to start your database then edit the pfile with any editor (for example vi on unix) and either change the location of user_dump_dest or remove the parameter user_dump_dest from pfile. And then perform startup.
If you like to use spfile then first create pfile from spfile, then edit the pfile and then create spfile from the pfile, like.
1.Create pfile from spfile.
SQL> create pfile='1.pfile' from spfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilearju.ora';
2.Edit the pfile.
SQL>!vi 1.pfile
Remove the entry of user_dump_dest or add valid entry.
3.Create spfile from pfile.
SQL>create spfile from pfile='1.pfile';
4.start the database.
SQL>STARTUP NOMOUNT
While performing disaster recovery to a new host, you need to start the database in nomount sate(in fact to restore control file) but database could not start in nomount state because it fails with RMAN-04014, ORA-07446.
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:03:44
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value '/oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump' for parameter user_dump_dest.
Cause of The Problem
The path /oracle/app/oracle/product/10.2.0/db_1/admin/arjudba/udump as shown in output does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initialize parameter.
Solution of The Problem
If you use pfile to start your database then edit the pfile with any editor (for example vi on unix) and either change the location of user_dump_dest or remove the parameter user_dump_dest from pfile. And then perform startup.
If you like to use spfile then first create pfile from spfile, then edit the pfile and then create spfile from the pfile, like.
1.Create pfile from spfile.
SQL> create pfile='1.pfile' from spfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilearju.ora';
2.Edit the pfile.
SQL>!vi 1.pfile
Remove the entry of user_dump_dest or add valid entry.
3.Create spfile from pfile.
SQL>create spfile from pfile='1.pfile';
4.start the database.
SQL>STARTUP NOMOUNT
ORA-09925: Unable to create audit trail file
Problem Description
Whenever you try to startup the database with pfile then it fails with error RA-09925: Unable to create audit trail file along with Linux Error: 2: No such file or directory.
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:02:29
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
Cause of the Problem
The error ORA-09925 indicates that oracle was unable to write the audit information into the audit directory. The audit directory is specified by audit_trail parameter in the initialization parameter. Just after the ORA-09925 if there is some OS error then that is notified. The linux error clearly identified that which is No such file or directory.
Solution of the Problem
There may be several solutions to this problem.
1.Create the approprite directory/location specified by audit_trail initialization parameter from operating system and grant appropriate permission to that directory.
2.Change the location of audit_trail initialization parameter to a valid location in the OS.
If you start your database with pfile then you can easily do this by editing your pfile with an editor and modify the audit_trail parameter.
However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and the location of audit_trail parameter.
-Create spfile from pfile='1.ora'
-Start your database with the spfile.
Whenever you try to startup the database with pfile then it fails with error RA-09925: Unable to create audit trail file along with Linux Error: 2: No such file or directory.
RMAN> startup force pfile='/oradata2/arjudbapfile.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:02:29
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925
Cause of the Problem
The error ORA-09925 indicates that oracle was unable to write the audit information into the audit directory. The audit directory is specified by audit_trail parameter in the initialization parameter. Just after the ORA-09925 if there is some OS error then that is notified. The linux error clearly identified that which is No such file or directory.
Solution of the Problem
There may be several solutions to this problem.
1.Create the approprite directory/location specified by audit_trail initialization parameter from operating system and grant appropriate permission to that directory.
2.Change the location of audit_trail initialization parameter to a valid location in the OS.
If you start your database with pfile then you can easily do this by editing your pfile with an editor and modify the audit_trail parameter.
However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and the location of audit_trail parameter.
-Create spfile from pfile='1.ora'
-Start your database with the spfile.
Message file RMAN.msb not found
Error Description
I give the full path of rman executable file location and I am getting error RMAN<>.msb not found as below.
-bash-3.1$ /oradata2/bin/rman target /
Message file RMAN<>.msb not found
Verify that ORACLE_HOME is set properly
Solution of The problem
Believe me, as I still got you have not set ORACLE_HOME properly. So set it.
To know your current settings of ORACLE_HOME, issue,
-bash-3.1$ echo $ORACLE_HOME
/oradata2/bin/
Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/
On unix set the value for the current session by,
-bash-3.1$ export ORACLE_HOME=/oradata2/
On Windows environment you have to set by
set ORACLE_HOME=C:\oracle or like that.
In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME.
After setting correct ORACLE_HOME now try to connect to rman.
-bash-3.1$ /oradata2/bin/rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 28 14:37:53 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
I give the full path of rman executable file location and I am getting error RMAN<
-bash-3.1$ /oradata2/bin/rman target /
Message file RMAN<
Verify that ORACLE_HOME is set properly
Solution of The problem
Believe me, as I still got you have not set ORACLE_HOME properly. So set it.
To know your current settings of ORACLE_HOME, issue,
-bash-3.1$ echo $ORACLE_HOME
/oradata2/bin/
Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/
On unix set the value for the current session by,
-bash-3.1$ export ORACLE_HOME=/oradata2/
On Windows environment you have to set by
set ORACLE_HOME=C:\oracle or like that.
In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME.
After setting correct ORACLE_HOME now try to connect to rman.
-bash-3.1$ /oradata2/bin/rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 28 14:37:53 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
Wednesday, August 27, 2008
How to restore an Spfile from autobackup older than 7 days
Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.
However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.
For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;
In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.
Tuesday, August 26, 2008
RMAN-06172: no autobackup found
Error Description:
While performing disaster recovery I get the error RMAN-06172 as below.
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initARJU.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00';
Starting restore at 27-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Cause of The Error
To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you will not be able to restore spfile or controlfile.
Solution of The Problem
1)To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file. So find autobackup of the controlfile and use it for restore operation.
Also ensure that you have autobackup intact and your autobackup is not corrupted. If you have corrupted backup then you will get above error.
My autobackup piece is /export/home/oracle/8rji9vrq_1_1 and so I used,
RMAN> restore spfile from '/export/home/oracle/8rji9vrq_1_1';
2)In situation you have autobackup of controlfile and it is not corrupted then still you can get the error. It is because of platform difference in endian format. It was because of BIG endian or LITTLE endian format. If you take backup in BIG endian platform and want to restore it to LITTLE endian format then above error will appear. You can check the endian format by below query.
We see Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) has big endian format but Solaris Operating System (x86) has little endian format. So we can't perform any restore operation in Microsoft Windows 64-bit for AMD or Solaris Operating System (x86) or Microsoft Windows IA (32-bit) (All these has Little endian format) from backup that was taken from Big endian format like Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) or IBM Power Based Linux or IBM zSeries Based Linux or HP-UX (64-bit) or AIX-Based Systems (64-bit).
If you try to restore in Little edian format, from backup taken in Big endian format platform RMAN can't read the backuppiece and will return,
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
The solution is to use same endian format.
Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
ORA-19573: cannot obtain exclusive enqueue for datafile 1
While performing disaster recovery I get the error RMAN-06172 as below.
RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initARJU.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00';
Starting restore at 27-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
Cause of The Error
To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you will not be able to restore spfile or controlfile.
Solution of The Problem
1)To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file. So find autobackup of the controlfile and use it for restore operation.
Also ensure that you have autobackup intact and your autobackup is not corrupted. If you have corrupted backup then you will get above error.
My autobackup piece is /export/home/oracle/8rji9vrq_1_1 and so I used,
RMAN> restore spfile from '/export/home/oracle/8rji9vrq_1_1';
2)In situation you have autobackup of controlfile and it is not corrupted then still you can get the error. It is because of platform difference in endian format. It was because of BIG endian or LITTLE endian format. If you take backup in BIG endian platform and want to restore it to LITTLE endian format then above error will appear. You can check the endian format by below query.
SQL> set linesize 100
SQL> col PLATFORM_NAME format a80
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux 64-bit for AMD Little
Apple Mac OS Big
Microsoft Windows 64-bit for AMD Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
17 rows selected.
We see Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) has big endian format but Solaris Operating System (x86) has little endian format. So we can't perform any restore operation in Microsoft Windows 64-bit for AMD or Solaris Operating System (x86) or Microsoft Windows IA (32-bit) (All these has Little endian format) from backup that was taken from Big endian format like Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) or IBM Power Based Linux or IBM zSeries Based Linux or HP-UX (64-bit) or AIX-Based Systems (64-bit).
If you try to restore in Little edian format, from backup taken in Big endian format platform RMAN can't read the backuppiece and will return,
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
The solution is to use same endian format.
Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Restore fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067
Problem Symptoms
From the backuppiece restore spfile commnad fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067
RMAN> restore spfile from '/backup1/snap/june/8rji9vrq_1_1';
Starting restore at 27-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /backup1/snap/june/8rji9vrq_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/27/2008 01:05:07
ORA-19870: error reading backup piece /backup1/snap/june/8rji9vrq_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Cause of the Problem
RMAN could not read the backuppiece. There may be several reasons behind it. Either it is not the file what is expected in the RMAN command. Suppose my command was restore spfile and this backuppiece may be backups of archived logs. Another reason may be the file in OS level is made read only status. As RMAN opens the backuppiece in read write mode so error may come.
Solution of the Problem
1)Be sure the command you are using is valid one in terms of backuppiece. Suppose here I used restore spfile then this piece must be backup of controlfile.
2)Make sure that backuppiece is read write mode on OS. You can check it on unix by,
$ls -l backupiece_name
In order to make read write mode.
$chmod 666 backupiece_name
Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
How to skip a tablespace for restore operation
From the backuppiece restore spfile commnad fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067
RMAN> restore spfile from '/backup1/snap/june/8rji9vrq_1_1';
Starting restore at 27-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /backup1/snap/june/8rji9vrq_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/27/2008 01:05:07
ORA-19870: error reading backup piece /backup1/snap/june/8rji9vrq_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Cause of the Problem
RMAN could not read the backuppiece. There may be several reasons behind it. Either it is not the file what is expected in the RMAN command. Suppose my command was restore spfile and this backuppiece may be backups of archived logs. Another reason may be the file in OS level is made read only status. As RMAN opens the backuppiece in read write mode so error may come.
Solution of the Problem
1)Be sure the command you are using is valid one in terms of backuppiece. Suppose here I used restore spfile then this piece must be backup of controlfile.
2)Make sure that backuppiece is read write mode on OS. You can check it on unix by,
$ls -l backupiece_name
In order to make read write mode.
$chmod 666 backupiece_name
Related Documents
RMAN-06026, RMAN-06023 During Restore Using RMAN
How to skip a tablespace for restore operation
Sunday, August 24, 2008
ORA-31605 returned from LpxXSLResetAllVars in routine kuxslResetParams
Problem Description
SQL> select dbms_metadata.get_ddl('TABLE','A') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
Solution of The Problem
Solution is the same as in http://arjudba.blogspot.com/2008/08/ora-39212-installation-error-xsl.html.
After executing dbms_metadata_util.load_stylesheets reconnect to your session.
SQL> select dbms_metadata.get_ddl('TABLE','A') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
Solution of The Problem
Solution is the same as in http://arjudba.blogspot.com/2008/08/ora-39212-installation-error-xsl.html.
After executing dbms_metadata_util.load_stylesheets reconnect to your session.
ORA-39212: installation error: XSL stylesheets not loaded correctly
Problem Description
SQL> select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7398
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7447
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
Cause of The Problem
The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet."
It may be the cause that the stylesheets were not loaded at all in the database, or they were not converted to the database character set.
Suppose if you convert your database character set using ALTER DATABASE statement then likely you will face this problem.
Solution of The Problem
Note that never use ALTER DATABASE statement to migrate your characterset.
1)Connect as sysdba.
SQL> conn / as sysdba
Connected.
2)Execute dbms_metadata_util.load_stylesheets to reload the stylesheets.
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
3)Exit and run the above query,
SQL>exit
$>conn ARJU/a
SQL>select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
SQL> select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7398
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7447
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
Cause of The Problem
The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet."
It may be the cause that the stylesheets were not loaded at all in the database, or they were not converted to the database character set.
Suppose if you convert your database character set using ALTER DATABASE statement then likely you will face this problem.
Solution of The Problem
Note that never use ALTER DATABASE statement to migrate your characterset.
1)Connect as sysdba.
SQL> conn / as sysdba
Connected.
2)Execute dbms_metadata_util.load_stylesheets to reload the stylesheets.
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
3)Exit and run the above query,
SQL>exit
$>conn ARJU/a
SQL>select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
Steps and examples to use logminer for Remote mining
Many of us are curious to know about remote mining. Source database mining in production database you might avoid. So remote mining comes. Remote mining means the database that analyze of redo log files or archived redo log files is not same as of source database- that generate redo/archived log files.
The system of using logminer in remote database is transfer the analysis redo or archived redo log files as well as tranfer the mining dictionary to the remote database.
In the following section I demonstrate an step by step idea to do the task.
Scenario: Online redo log file redo01 need to be analyzed in remote machine jupiter.
Step1 -Extract logminer dictionary: (On Source Database)
In source that is in production server build the dictionary by,
SQL>EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
After executing this procedure in archive log mode database adds the dictionary information to the online redo logs, which, in turn, go into the archived logs.
This redo log file must have been created before the redo log file that you want to analyze, but should be as recent as possible.
You can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, by,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc
Step 2- Transfer the logminer dictionary and log for analysis into the mining database.
SQL> !scp /oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc oracle@jupiter:
Password:
o1_mf_1_1284_4c23hcd 100% |*************************************************| 9465 KB 00:01
Then also transfer the redo log. Based on your requirement you can transfer archived log or online redo log. To see a defined time archived log query by select NAME from v$archived_log where completion_time >SYSDATE-1;
In this example I will analysis online redo log file.
SQL> !scp /oradata1/arju/ARJU/redo01.log oracle@jupiter:
Password:
redo03.log 100% |*************************************************| 51200 KB 00:08
Step 3- Specify the files for analysis.(In mining/remote database)
Here specify the logfile that need to mine plus specify the dictionary files.
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_1284_4c23hcd6_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
Step 4- Start the logminer session(In mining database)
Don't specify here DICTFILENAME option. Execute the following,
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
Step 5- Query V$LOGMNR_CONTENTS:
You can follow for query as in How to use Oracle Logminer to analysis Logfile
Step 6- End the LogMiner Session:
EXECUTE DBMS_LOGMNR.END_LOGMNR;
Related Documents
How to use Oracle Logminer to analysis Logfile
The system of using logminer in remote database is transfer the analysis redo or archived redo log files as well as tranfer the mining dictionary to the remote database.
In the following section I demonstrate an step by step idea to do the task.
Scenario: Online redo log file redo01 need to be analyzed in remote machine jupiter.
Step1 -Extract logminer dictionary: (On Source Database)
In source that is in production server build the dictionary by,
SQL>EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.
After executing this procedure in archive log mode database adds the dictionary information to the online redo logs, which, in turn, go into the archived logs.
This redo log file must have been created before the redo log file that you want to analyze, but should be as recent as possible.
You can find out which redo log files contain the start and end of an extracted dictionary. To do so, query the V$ARCHIVED_LOG view, by,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
NAME
--------------------------------------------------------------------------------
/oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc
Step 2- Transfer the logminer dictionary and log for analysis into the mining database.
SQL> !scp /oradata1/ARJU/archivelog/2008_08_24/o1_mf_1_1284_4c23hcd6_.arc oracle@jupiter:
Password:
o1_mf_1_1284_4c23hcd 100% |*************************************************| 9465 KB 00:01
Then also transfer the redo log. Based on your requirement you can transfer archived log or online redo log. To see a defined time archived log query by select NAME from v$archived_log where completion_time >SYSDATE-1;
In this example I will analysis online redo log file.
SQL> !scp /oradata1/arju/ARJU/redo01.log oracle@jupiter:
Password:
redo03.log 100% |*************************************************| 51200 KB 00:08
Step 3- Specify the files for analysis.(In mining/remote database)
Here specify the logfile that need to mine plus specify the dictionary files.
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/redo01.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_1284_4c23hcd6_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
Step 4- Start the logminer session(In mining database)
Don't specify here DICTFILENAME option. Execute the following,
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
Step 5- Query V$LOGMNR_CONTENTS:
You can follow for query as in How to use Oracle Logminer to analysis Logfile
Step 6- End the LogMiner Session:
EXECUTE DBMS_LOGMNR.END_LOGMNR;
Related Documents
How to use Oracle Logminer to analysis Logfile
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
Problem Description
While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment.
SQL> BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2
Cause of The Problem
The error occurs because the dictionary file produced by a database (testing) is different from the one that produced the logfiles (production). In order to use logminer you need to extract dictionary information from the source database(production) to the mining database(testing).
Solution of The Problem
Step 1.
Extract the dictionary to the redo logs in the production database by executing following command,
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Step 2
After executing commnad in step 1 the dictionary is extract into the redo logs and in fact they become archived instantly. Then check in which redo logs the dictionary is located by running this on production,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
Step 3
Tranfer the output of step 3 into the testing database. You can use scp or telnet to do this.
Step 4
Now in the mining database(testing) provide all the redo log files (including the one from which the transactions need to be mined) and start Log Miner (do not specify dictFileName):
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment.
SQL> BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2
Cause of The Problem
The error occurs because the dictionary file produced by a database (testing) is different from the one that produced the logfiles (production). In order to use logminer you need to extract dictionary information from the source database(production) to the mining database(testing).
Solution of The Problem
Step 1.
Extract the dictionary to the redo logs in the production database by executing following command,
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Step 2
After executing commnad in step 1 the dictionary is extract into the redo logs and in fact they become archived instantly. Then check in which redo logs the dictionary is located by running this on production,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';
Step 3
Tranfer the output of step 3 into the testing database. You can use scp or telnet to do this.
Step 4
Now in the mining database(testing) provide all the redo log files (including the one from which the transactions need to be mined) and start Log Miner (do not specify dictFileName):
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
Saturday, August 23, 2008
How to resolve ORA-4030 errors on UNIX
Reasons of ORA-04030
When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.
The ORA-4030 can come based on various reasons which is noted below.
1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available.
2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears.
3)Might be an oracle bug.
Solution of The Problem
1)Reduce the PGA(Program Global Area): If you don't have enough memory on your system then reducing the PGA can help you to avoid the error. In that case oracle process will not request more memory from OS and if needed more space it will use temporary tablespace segment. But lower the PGA can impact on database performance. If you don't have PGA_AGGREGATE_TARGET set then in order to set lower PGA set SORT_AREA_SIZE parameter to a lower value. You can set it dynamically by,
ALTER SYSTEM SET SORT_AREA_SIZE=10M;
If you have set PGA_AGGREGATE_TARGET then to reduce PGA you have to set PGA_AGGREGATE_TARGET toa lower value.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
2)If you did not enabled automatic PGA management then enable the automatic SQL execution memory management feature by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET then it will lessen to happen ORA-4030 error. As by setting automatic SQL execution memory management feature SORT_AREA_SIZE, HASH_AREA_SIZE are handled by oracle automatically and allocate/deallocate by demand.
ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO;
3)If you impose limit to oracle to use memory then increase the amount of memory a UNIX process can request and use from the operating system. The system administrator can see this soft limit
by issuing,
$limit
In order to see hard limit issue,
$limit -h
If you want to increase a specific resource soft limit by specifying it's name and the new value then on
C shell
use,
csh> limit -h datasize 524289
csh> limit -h datasize
To make all resource unlimited issue,
csh>unlimit
If you use
Bash and Korne Shell
then use,
$ulimit -a
In order to set all resource to unlimited.
4)Increase the amount of swap space available on the system. You should have 2-3 times the amount of physical memory available as swap space.
5)Increase the physical RAM on your system if you have lower RAM. If you don't have the scope to increase your RAM then lower the SGA settings. Lowering SGA size will allow to use more PGA.
6)If some PL/SQL procedures need much memory then you might think to rewrite it so that it needs less memory.
7)Install the latest patch set.
When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.
The ORA-4030 can come based on various reasons which is noted below.
1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available.
2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears.
3)Might be an oracle bug.
Solution of The Problem
1)Reduce the PGA(Program Global Area): If you don't have enough memory on your system then reducing the PGA can help you to avoid the error. In that case oracle process will not request more memory from OS and if needed more space it will use temporary tablespace segment. But lower the PGA can impact on database performance. If you don't have PGA_AGGREGATE_TARGET set then in order to set lower PGA set SORT_AREA_SIZE parameter to a lower value. You can set it dynamically by,
ALTER SYSTEM SET SORT_AREA_SIZE=10M;
If you have set PGA_AGGREGATE_TARGET then to reduce PGA you have to set PGA_AGGREGATE_TARGET toa lower value.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
2)If you did not enabled automatic PGA management then enable the automatic SQL execution memory management feature by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET then it will lessen to happen ORA-4030 error. As by setting automatic SQL execution memory management feature SORT_AREA_SIZE, HASH_AREA_SIZE are handled by oracle automatically and allocate/deallocate by demand.
ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO;
3)If you impose limit to oracle to use memory then increase the amount of memory a UNIX process can request and use from the operating system. The system administrator can see this soft limit
by issuing,
$limit
In order to see hard limit issue,
$limit -h
If you want to increase a specific resource soft limit by specifying it's name and the new value then on
C shell
use,
csh> limit -h datasize 524289
csh> limit -h datasize
To make all resource unlimited issue,
csh>unlimit
If you use
Bash and Korne Shell
then use,
$ulimit -a
In order to set all resource to unlimited.
4)Increase the amount of swap space available on the system. You should have 2-3 times the amount of physical memory available as swap space.
5)Increase the physical RAM on your system if you have lower RAM. If you don't have the scope to increase your RAM then lower the SGA settings. Lowering SGA size will allow to use more PGA.
6)If some PL/SQL procedures need much memory then you might think to rewrite it so that it needs less memory.
7)Install the latest patch set.
OUI-10020: A write lock cannot be obtained.
Problem Description
While using OUI it fails with following message,
OUI-10020:The target area /oracle/10gR2/oraInventory is being used by another session. A write lock cannot be obtained.
Cause of The Problem
There is some process or software that holds lock on the /oracle/10gR2/oraInventory file. Until that software release lock OUI continues to display message OUI-10020.
Solution of The Problem
1.Go to the oraInventory directory.
$cd /oracle/10gR2/oraInventory
2. See the contents of it.
$ls /oracle/10gR2/oraInventory
You will see the locks directory here.
3.Go to the lock directory and delete the read lock under the directory.
$cd locks
$ rm /oracle/10gR2/oraInventory/locks/reader0.lock
4.Now run your Oracle Universal Installer.
While using OUI it fails with following message,
OUI-10020:The target area /oracle/10gR2/oraInventory is being used by another session. A write lock cannot be obtained.
Cause of The Problem
There is some process or software that holds lock on the /oracle/10gR2/oraInventory file. Until that software release lock OUI continues to display message OUI-10020.
Solution of The Problem
1.Go to the oraInventory directory.
$cd /oracle/10gR2/oraInventory
2. See the contents of it.
$ls /oracle/10gR2/oraInventory
You will see the locks directory here.
3.Go to the lock directory and delete the read lock under the directory.
$cd locks
$ rm /oracle/10gR2/oraInventory/locks/reader0.lock
4.Now run your Oracle Universal Installer.
umask and permission in unix.
Many one got confused with the setting of umask in unix or linux system. Whenever I was a newbie in this field I also got a bit confused with this settings. In a nutshell umask defines what will be the default permission of a file whenever a user create the file. You already are familiar with the unix permission of 754. The first digit 7(4 read +2 write+1 execute) is for permission for owner, the second digit 5 is for permission for the group and third digit 4 is for permission for others.
In brief,
Now lets a look at ls -l after creating a file without setting any umask.
-bash-3.00$ touch without_umask.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
After omitting first digit as it indicates whether file or folder we get
first 3 digit after - is 110 that is 6 for user oracle permission.
second pair of 3 digits are 100 that is 4 which is for all users who are under oinstall group.
third pair of 3 digits are 100 that is 4 which is for others.
Now start with setting umask of 022. With this setting, Files (and directories) normally created with mode 777 become mode 755. Files (and directories) created with mode 666 become mode 644).
In a word umask just deducted its value(here is 022) from 777 (or other mode that a file created) and then it changes permission to the file.
-bash-3.00$ umask 022
-bash-3.00$ touch with_umask1.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 022 set permission to with_umask1.txt file to 644.
-bash-3.00$ umask 002
-bash-3.00$ touch with_umask2.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 002 set permission to with_umask2.txt file to 664.
-bash-3.00$ umask 000
-bash-3.00$ touch with_umask3.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rw-rw-rw- 1 oracle oinstall 0 Aug 24 00:06 with_umask3.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 000 set permission to the file (666-000)=666.
We can chnage its settings later by chmod if we wish.
-bash-3.00$ chmod 777 with_umask3.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rwxrwxrwx 1 oracle oinstall 0 Aug 24 00:06 with_umask3.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
In brief,
User class: Owner Group Others
character representation: rwx r-x r--
binary representation: 111 101 100
octal representation: 7 5 4
Now lets a look at ls -l after creating a file without setting any umask.
-bash-3.00$ touch without_umask.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
After omitting first digit as it indicates whether file or folder we get
first 3 digit after - is 110 that is 6 for user oracle permission.
second pair of 3 digits are 100 that is 4 which is for all users who are under oinstall group.
third pair of 3 digits are 100 that is 4 which is for others.
Now start with setting umask of 022. With this setting, Files (and directories) normally created with mode 777 become mode 755. Files (and directories) created with mode 666 become mode 644).
In a word umask just deducted its value(here is 022) from 777 (or other mode that a file created) and then it changes permission to the file.
-bash-3.00$ umask 022
-bash-3.00$ touch with_umask1.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 022 set permission to with_umask1.txt file to 644.
-bash-3.00$ umask 002
-bash-3.00$ touch with_umask2.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 002 set permission to with_umask2.txt file to 664.
-bash-3.00$ umask 000
-bash-3.00$ touch with_umask3.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rw-rw-rw- 1 oracle oinstall 0 Aug 24 00:06 with_umask3.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Here we see umask of 000 set permission to the file (666-000)=666.
We can chnage its settings later by chmod if we wish.
-bash-3.00$ chmod 777 with_umask3.txt
-bash-3.00$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask1.txt
-rw-rw-r-- 1 oracle oinstall 0 Aug 24 00:06 with_umask2.txt
-rwxrwxrwx 1 oracle oinstall 0 Aug 24 00:06 with_umask3.txt
-rw-r--r-- 1 oracle oinstall 0 Aug 24 00:05 without_umask.txt
Wednesday, August 20, 2008
Major Oracle Clusterware components
The Oracle Clusterware comprises several background processes that facilitate cluster operations. These processes or components are the main communication links between the Oracle Clusterware high availability components and the Oracle Database as well as they monitor and manage database operations.
Here is the list of major oracle clusterware components or processes.
1)Cluster Synchronization Services (CSS): It manages and controls which nodes are members of the cluster and notify members when a node joins or leaves the cluster.
2)Cluster Ready Services (CRS): It manages high availability operations within a cluster. The CRS process start, stop, monitor and failover operations. It generates events when a resource status changes. When you have installed Oracle RAC, crs monitors the Oracle instance, Listener, and so on, and automatically restarts these components when a failure occurs.
3)Event Management (EVM): It is a background process that publishes events that crs creates.
4)Oracle Notification Service (ONS): It publishes and subscribes service for communicating Fast Application Notification (FAN) events.
5)RACG: It runs server callout scripts when FAN events occur.
6)Process Monitor Daemon (OPROCD): This process is locked in memory to monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
Here is the list of major oracle clusterware components or processes.
1)Cluster Synchronization Services (CSS): It manages and controls which nodes are members of the cluster and notify members when a node joins or leaves the cluster.
2)Cluster Ready Services (CRS): It manages high availability operations within a cluster. The CRS process start, stop, monitor and failover operations. It generates events when a resource status changes. When you have installed Oracle RAC, crs monitors the Oracle instance, Listener, and so on, and automatically restarts these components when a failure occurs.
3)Event Management (EVM): It is a background process that publishes events that crs creates.
4)Oracle Notification Service (ONS): It publishes and subscribes service for communicating Fast Application Notification (FAN) events.
5)RACG: It runs server callout scripts when FAN events occur.
6)Process Monitor Daemon (OPROCD): This process is locked in memory to monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.
Configure Raw Devices for ASM in RAC.
Configuring RAW decides in RAC is just similiar as you do configure raw devices for voting disk and OCR. In this example, I have faced a different scenario where in
In racnode-1 disk sdb is mapped to sdf in ranode-2
In racnode-1 disk sdc is mapped to sdd in ranode-2
In racnode-1 disk sdd is mapped to sde in ranode-2
First I have created three raw partitions from my raw devices sdb, sdc and sdd of racnode-1 each of 80GB for ASM.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdb
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33): +80000M
Last cylinder or +size or +sizeM or +sizeK (9726-19581, default 19581):
Using default value 19581
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdc
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33):
Using default value 33
Last cylinder or +size or +sizeM or +sizeK (33-19581, default 19581): +80000M
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdd
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33):
Using default value 33
Last cylinder or +size or +sizeM or +sizeK (33-19581, default 19581): +80000M
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
For RACNODE-1
[root@racnode-1 ~]# vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdb2", RUN+="/bin/raw /dev/raw/raw6 %N"
ACTION=="add", KERNEL=="sdc2", RUN+="/bin/raw /dev/raw/raw7 %N"
ACTION=="add", KERNEL=="sdd2", RUN+="/bin/raw /dev/raw/raw8 %N"
KERNEL=="raw[6-8]*", OWNER="oracle", GROUP="oinstall", MODE="644"
Then reboot the node if it suggest as in this example. The new table will be used at the next reboot.
[root@racnode-1 ~]# reboot
For RACNODE-2
[root@racnode-2 ~]# vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdf2", RUN+="/bin/raw /dev/raw/raw6 %N"
ACTION=="add", KERNEL=="sdd2", RUN+="/bin/raw /dev/raw/raw7 %N"
ACTION=="add", KERNEL=="sde2", RUN+="/bin/raw /dev/raw/raw8 %N"
KERNEL=="raw[6-8]*", OWNER="oracle", GROUP="oinstall", MODE="644"
Then reboot the node if it suggest as in this example. The new table will be used at the next reboot.
[root@racnode-2 ~]# reboot
Related Documents
In racnode-1 disk sdb is mapped to sdf in ranode-2
In racnode-1 disk sdc is mapped to sdd in ranode-2
In racnode-1 disk sdd is mapped to sde in ranode-2
First I have created three raw partitions from my raw devices sdb, sdc and sdd of racnode-1 each of 80GB for ASM.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdb
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33): +80000M
Last cylinder or +size or +sizeM or +sizeK (9726-19581, default 19581):
Using default value 19581
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdc
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33):
Using default value 33
Last cylinder or +size or +sizeM or +sizeK (33-19581, default 19581): +80000M
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
[root@racnode-1 ~]# /sbin/fdisk /dev/sdd
The number of cylinders for this disk is set to 19581.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (33-19581, default 33):
Using default value 33
Last cylinder or +size or +sizeM or +sizeK (33-19581, default 19581): +80000M
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
For RACNODE-1
[root@racnode-1 ~]# vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdb2", RUN+="/bin/raw /dev/raw/raw6 %N"
ACTION=="add", KERNEL=="sdc2", RUN+="/bin/raw /dev/raw/raw7 %N"
ACTION=="add", KERNEL=="sdd2", RUN+="/bin/raw /dev/raw/raw8 %N"
KERNEL=="raw[6-8]*", OWNER="oracle", GROUP="oinstall", MODE="644"
Then reboot the node if it suggest as in this example. The new table will be used at the next reboot.
[root@racnode-1 ~]# reboot
For immediate affect if you don't see any reboot message,
#/bin/raw /dev/raw/raw6 /dev/sdb2
/bin/raw /dev/raw/raw7 /dev/sdc2
/bin/raw /dev/raw/raw8 /dev/sdd2
Adjust the permission settings by,
#chown oracle:oinstall /dev/raw/raw6
chown oracle:oinstall /dev/raw/raw7
chown oracle:oinstall /dev/raw/raw8
chmod 640 /dev/raw/raw6
chmod 640 /dev/raw/raw7
chmod 640 /dev/raw/raw8
For RACNODE-2
[root@racnode-2 ~]# vi /etc/udev/rules.d/63-oracle-raw.rules
ACTION=="add", KERNEL=="sdf2", RUN+="/bin/raw /dev/raw/raw6 %N"
ACTION=="add", KERNEL=="sdd2", RUN+="/bin/raw /dev/raw/raw7 %N"
ACTION=="add", KERNEL=="sde2", RUN+="/bin/raw /dev/raw/raw8 %N"
KERNEL=="raw[6-8]*", OWNER="oracle", GROUP="oinstall", MODE="644"
Then reboot the node if it suggest as in this example. The new table will be used at the next reboot.
[root@racnode-2 ~]# reboot
For immediate affect, if you don't see any reboot message
#/bin/raw /dev/raw/raw6 /dev/sdf2
/bin/raw /dev/raw/raw7 /dev/sdd2
/bin/raw /dev/raw/raw8 /dev/sde2
and adjust the permission settings by,
#chown oracle:oinstall /dev/raw/raw6
chown oracle:oinstall /dev/raw/raw7
chown oracle:oinstall /dev/raw/raw8
chmod 640 /dev/raw/raw6
chmod 640 /dev/raw/raw7
chmod 640 /dev/raw/raw8
Related Documents
Tuesday, August 19, 2008
Possible Limitations When Adding Datafiles to a Tablespace
Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.
And smaller value setting of this parameter impose limit to create another datafile.
So setting of DB_FILES parameter value is important.
Now let's look about possible limitations when adding datafiles to a tablespace.
Possible Limitations When Adding Datafiles to a Tablespace
•OS imposes a limit of the number of files a process can be opened at a time.
•OS imposes a limit of number and size of datafiles.
•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
•Number of datafiles can't exceed DB_FILES initialization parameter.
•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.
•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
Monday, August 18, 2008
MAXDATAFILES , DB_FILES parameters and ORA-00059
Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-01667-cannot-add-any-more.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/possible-limitations-when-adding.html
Whenever I try to create tablespace it fails with error message, ORA-00059.
SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.
The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.
And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.
Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.
If you use spfile to startup the database then issue,
SQL> alter system set db_files=300 scope=spfile;
System altered.
Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.
SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-01667-cannot-add-any-more.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/possible-limitations-when-adding.html
CRS Stack Fails to Start After Reboot ORA-29702 CRS-0184
Problem Description
An attempt to manually start the instance fails with,
ORA-29702: error occurred in Cluster Group Service operation
crs_stat produces the error,
/oracle/crs/bin/crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
CSS stack did not come up as the following command issued out of init.cssd
crsctl check boot
failed with error message
Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))
and return code 11.
The error message was also written to /var/log/messages on this node.
Note that in 10gR2 the message no longer gets written to /var/log/messages, instead it will be written to /tmp/crsctl.
Cause of the Problem
Error message 'Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))' indicated that CSS had a problem creating the listening endpoint for hostname node2
In node1 within the /etc/hosts file the name of the private interconnect exist. But on node2 somehow it is missing the private interconnect in the /etc/hosts file. That's why the problem occurs.
Solution of The Problem
1. Add the missing hostnames to /etc/hosts
2. Stop and start CRS stack
Related Documents
http://arjudba.blogspot.com/2010/03/cluvfy-fails-with-prvf-5436-prvf-9652.html
http://arjudba.blogspot.com/2010/03/in-11gr2-grid-rootsh-fails-with-crs.html
http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/oracle-rac-software-components.html
http://arjudba.blogspot.com/2008/08/oracle-clusterware-processes-on-unix.html
http://arjudba.blogspot.com/2008/08/configure-raw-devices-for-asm-in-rac.html
http://arjudba.blogspot.com/2008/08/crs-stack-fails-to-start-after-reboot.html
http://arjudba.blogspot.com/2008/08/configure-network-for-oracle-rac.html
http://arjudba.blogspot.com/2008/08/pre-installation-rac-environement-setup.html
http://arjudba.blogspot.com/2008/08/configure-server-to-install-oracle-rac.html
An attempt to manually start the instance fails with,
ORA-29702: error occurred in Cluster Group Service operation
crs_stat produces the error,
/oracle/crs/bin/crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
CSS stack did not come up as the following command issued out of init.cssd
crsctl check boot
failed with error message
Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))
and return code 11.
The error message was also written to /var/log/messages on this node.
Note that in 10gR2 the message no longer gets written to /var/log/messages, instead it will be written to /tmp/crsctl.
Cause of the Problem
Error message 'Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))' indicated that CSS had a problem creating the listening endpoint for hostname node2
In node1 within the /etc/hosts file the name of the private interconnect exist. But on node2 somehow it is missing the private interconnect in the /etc/hosts file. That's why the problem occurs.
Solution of The Problem
1. Add the missing hostnames to /etc/hosts
2. Stop and start CRS stack
Related Documents
http://arjudba.blogspot.com/2010/03/cluvfy-fails-with-prvf-5436-prvf-9652.html
http://arjudba.blogspot.com/2010/03/in-11gr2-grid-rootsh-fails-with-crs.html
http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/oracle-rac-software-components.html
http://arjudba.blogspot.com/2008/08/oracle-clusterware-processes-on-unix.html
http://arjudba.blogspot.com/2008/08/configure-raw-devices-for-asm-in-rac.html
http://arjudba.blogspot.com/2008/08/crs-stack-fails-to-start-after-reboot.html
http://arjudba.blogspot.com/2008/08/configure-network-for-oracle-rac.html
http://arjudba.blogspot.com/2008/08/pre-installation-rac-environement-setup.html
http://arjudba.blogspot.com/2008/08/configure-server-to-install-oracle-rac.html
How to Resize a Datafile
There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value
Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected
Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.
Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.
SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ARJU\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.
Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100
Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.
Current size is 10M we can increase it upto 15M by,
SQL> alter database datafile 6 resize 15M;
Database altered.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100
SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10
Here 12 is current size and 10 is creation time size.
Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.
To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;
If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,
ORA-03297: file contains
Or
ORA-03297: file contains used data beyond requested RESIZE value
Sunday, August 17, 2008
How to Find out or Check Linux Version Information
Before installing oracle clusterware it is necessary in which version of linux you are trying to install to. Because based on the version procedure may vary to install clusterware.
Here I mention several ways to check the version of linux.
Find out linux release information
1)On Red Hat Linux,
-bash-3.1$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
Alternatively,
# rpm -q redhat-release
redhat-release-4ES-2
2)On SUSE Linux,
$cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
3)On Debian Release,
debian:/home/Arju/Spark# cat /etc/debian_version
4.0
Find out linux Kernel Version
debian:/home/Arju/Spark# uname -s -r
Linux 2.6.18-4-686
debian:/home/Arju/Spark# uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
-bash-3.1$ uname -a
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
-bash-3.1$ uname -s -r
Linux 2.6.18-8.el5
Description of the output of uname -a
At the end but the before of OS name the output of "uname -a " shows the OS word-size.
- i686 i386 indicates 32-bit Linux on standard x86 microprocessor series hardware
- x86_64 indicates 64-bit Linux on standard AMD64 or Intel EM64T hardware
- ia64 indicates 64-bit Linux on the Itanium-2 processor developed jointly by Hewlett-Packard and Intel
- s390x indicates 64-bit Linux on IBM S/390 (31-bit) and zSeries (64-bit) hardware.
- ppc64 indicates 64-bit Linux on IBM power based systems that support Linux includes machines branded as pSeries, iSeries, System p5 and System i5.
The first word of the output shows the kernel name. We see the kernel name is linux.
Only itself can be seen by,
-bash-3.1$ uname -s
Linux
The second word of the output of " uname -a " shows the hostname. Here we see the hostname is debian for debian machine and racnode-1 for redhat linux machine.
Only itself can be seen by,
-bash-3.1$ uname -n
racnode-1
The third word of the output of "uname -a" prints the kernel release. Here for racnode-1 machine we see kernel release is 2.6.18-8.el5. Only itselef can be seen by uname -r
-bash-3.1$ uname -r
2.6.18-8.el5
The fourth word print the kernel version. For racnode-1 we see kernel version is #1 SMP Fri Jan 26 14:15:21 EST 2007. Only itself can be seen by, uname -v.
-bash-3.1$ uname -v
#1 SMP Fri Jan 26 14:15:21 EST 2007
The fifth one prints the machine hardware name. Only itself can be seen from ,
-bash-3.1$ uname -m
i686
The sixth one prints the processor type. Here it is seen by,
-bash-3.1$ uname -p
i686
The seventh one prints the hardware platform. Here it is
-bash-3.1$ uname -i
i386
The eighth one print the operating system. Here it is
-bash-3.1$ uname -o
GNU/Linux
In fact "uname -a" is the combination of all these 8. That is "uname -a" is equivalent to,
-bash-3.1$ uname -snrvmpio
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
Another Way by Cheking issue file
From the /etc/issue file you can also check the version information. In fact it is a text file which contains a message or system identification to be printed before the login prompt.
From here we see for debian machine it is 4.0 and for red hat racnode-1 it is 5.
debian:/home/Arju/Spark# cat /etc/issue
Debian GNU/Linux 4.0 \n \l
-bash-3.1$ cat /etc/issue
Red Hat Enterprise Linux Server release 5 (Tikanga)
Kernel \r on an \m
Related Documents
How to Identify OS or Oracle 64 bit or 32 bit on Unix
Here I mention several ways to check the version of linux.
Find out linux release information
1)On Red Hat Linux,
-bash-3.1$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4 (Tikanga)
Alternatively,
# rpm -q redhat-release
redhat-release-4ES-2
2)On SUSE Linux,
$cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
3)On Debian Release,
debian:/home/Arju/Spark# cat /etc/debian_version
4.0
Find out linux Kernel Version
debian:/home/Arju/Spark# uname -s -r
Linux 2.6.18-4-686
debian:/home/Arju/Spark# uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
-bash-3.1$ uname -a
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
-bash-3.1$ uname -s -r
Linux 2.6.18-8.el5
Description of the output of uname -a
At the end but the before of OS name the output of "uname -a " shows the OS word-size.
- i686 i386 indicates 32-bit Linux on standard x86 microprocessor series hardware
- x86_64 indicates 64-bit Linux on standard AMD64 or Intel EM64T hardware
- ia64 indicates 64-bit Linux on the Itanium-2 processor developed jointly by Hewlett-Packard and Intel
- s390x indicates 64-bit Linux on IBM S/390 (31-bit) and zSeries (64-bit) hardware.
- ppc64 indicates 64-bit Linux on IBM power based systems that support Linux includes machines branded as pSeries, iSeries, System p5 and System i5.
The first word of the output shows the kernel name. We see the kernel name is linux.
Only itself can be seen by,
-bash-3.1$ uname -s
Linux
The second word of the output of " uname -a " shows the hostname. Here we see the hostname is debian for debian machine and racnode-1 for redhat linux machine.
Only itself can be seen by,
-bash-3.1$ uname -n
racnode-1
The third word of the output of "uname -a" prints the kernel release. Here for racnode-1 machine we see kernel release is 2.6.18-8.el5. Only itselef can be seen by uname -r
-bash-3.1$ uname -r
2.6.18-8.el5
The fourth word print the kernel version. For racnode-1 we see kernel version is #1 SMP Fri Jan 26 14:15:21 EST 2007. Only itself can be seen by, uname -v.
-bash-3.1$ uname -v
#1 SMP Fri Jan 26 14:15:21 EST 2007
The fifth one prints the machine hardware name. Only itself can be seen from ,
-bash-3.1$ uname -m
i686
The sixth one prints the processor type. Here it is seen by,
-bash-3.1$ uname -p
i686
The seventh one prints the hardware platform. Here it is
-bash-3.1$ uname -i
i386
The eighth one print the operating system. Here it is
-bash-3.1$ uname -o
GNU/Linux
In fact "uname -a" is the combination of all these 8. That is "uname -a" is equivalent to,
-bash-3.1$ uname -snrvmpio
Linux racnode-1 2.6.18-8.el5 #1 SMP Fri Jan 26 14:15:21 EST 2007 i686 i686 i386 GNU/Linux
Another Way by Cheking issue file
From the /etc/issue file you can also check the version information. In fact it is a text file which contains a message or system identification to be printed before the login prompt.
From here we see for debian machine it is 4.0 and for red hat racnode-1 it is 5.
debian:/home/Arju/Spark# cat /etc/issue
Debian GNU/Linux 4.0 \n \l
-bash-3.1$ cat /etc/issue
Red Hat Enterprise Linux Server release 5 (Tikanga)
Kernel \r on an \m
Related Documents
How to Identify OS or Oracle 64 bit or 32 bit on Unix
Subscribe to:
Posts (Atom)