There are several ways to move the oracle datafiles from operating system file system to oracle ASM file system. Following section will demonstrate how we can achieve that through RMAN copy command.
Step 01: Create a tablespace and put corresponding datafile in the OS file system.
E:\Documents and Settings\Arju>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Dec 19 22:07:28 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace migrate_asm datafile 'F:\migrate_to_asm.dbf' size 10M;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='MIGRATE_ASM';
FILE_NAME
--------------------------------------------------------------------------------
F:\MIGRATE_TO_ASM.DBF
Step 02: Take the tablespace offline.
The datafiles to which you want to move to asm file system take the corresponding tablespace offline. If you have many datafiles under one tablespace and want to move single or several from them then take the corresponding datafile offline.
SQL> ALTER TABLESPACE MIGRATE_ASM OFFLINE;
Tablespace altered.
Step 03: Check the ASM datafile location.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
--------------------------------------------------------------------------------
+DATADG/racdb/datafile/users.dbf
+DATADG/racdb/datafile/sysaux.dbf
+DATADG/racdb/datafile/undotbs.dbf
+DATADG/racdb/datafile/system.dbf
F:\MIGRATE_TO_ASM.DBF
5 rows selected.
Step 04: Connect to RMAN session and execute the copy command.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
E:\Documents and Settings\Arju>rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Dec 19 23:07:44 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: A (DBID=3940131450)
RMAN> copy datafile 'F:\MIGRATE_TO_ASM.DBF' to '+DATADG';
RMAN> exit
Recovery Manager complete.
Step 05: Start a sqlplus session, rename the old file to new ASM file.
E:\Documents and Settings\Arju>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Dec 19 23:13:36 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>alter database rename file 'F:\MIGRATE_TO_ASM.DBF' to '+DATADG/racdb/datafile/MIGRATE_TO_ASM.DBF';
Database altered.
Step 06: Bring the tablespace online.
SQL> alter tablespace MIGRATE_ASM online;
Tablespace altered.
Step 07: Drop the file from OS file system.
rm F:\MIGRATE_TO_ASM.DBF
Related Documents
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
Saturday, December 19, 2009
Thursday, December 17, 2009
Enable Archive log Mode for RAC database
Whether it is single instance database or multiple instance database, all changes made to the database are written into online redo log files. In an Oracle RAC environment, each instance have its own set of online redolog files that is known as thread. Each Oracle instance will use its set of online redologs in a circular fashion. If there is log switch occur then it will write to next online redo log file. If the database is in "Archive Log Mode", Oracle will make a copy of the online redo log before it gets reused. A thread must contain at least two online redologs so that it can archive it properly before it is used.
In a RAC environment each instance have exclusive right access to its own set of redo log files. That means suppose I have a database named arju and arju database has two nodes, arju1 instance and arju2 instance. arju1 instance can't write to online redo log files of arju2 instance. However each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. So one instance must have read access of another online redo log files. It is therefore a requirement that online redo log files to be located on a shared storage device.
Whenever you create your database manually then by default database is in noarchive log mode. If you create your database with database configuration assistant (dbca) then there is a checkbox and check the box will enable your database archival mode. But if your database is in noarchivelog mode and you want to enable the archivelog mode then you must shutdown the database. So while enabling archivelog mode in RAC database you must shutdown your all instances. The following is the procedure to enable a database from noarchivelog mode to archivelog mode in RAC environment.
Step 01: Login to any instance and check the archival settings and location.
SQL> conn / as sysdba
SQL> archive log list
Step 02: If you need to change archival location then you can change it by
ALTER SYSTEM SET archival_parameter=new_value scope=both;
Step 03: Shutdown all RAC instances. The following command will shutdown all instances of orcl cluster database.
$ srvctl stop database -d orcl
Step 04: Connect to any of the local instance and MOUNT the database:
$ sqlplus "/ as sysdba"
SQL> startup mount
Step 05: Enable archive log mode.
SQL> alter database archivelog;
Step 06: Shutdown the local instance:
SQL> shutdown immediate
Step 07: Startup all the instances using srvctl.
$ srvctl start database -d orcl
Optionally, bring any services (i.e. TAF) back up using srvctl:
$ srvctl start service -d orcl
Step 08: Login to the local instance and verify Archive Log Mode is enabled:
$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 81
Next log sequence to archive 82
Current log sequence 82
After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.
Related Documents
In a RAC environment each instance have exclusive right access to its own set of redo log files. That means suppose I have a database named arju and arju database has two nodes, arju1 instance and arju2 instance. arju1 instance can't write to online redo log files of arju2 instance. However each instance can read another instance's current online redolog file to perform instance recovery if that instance was terminated abnormally. So one instance must have read access of another online redo log files. It is therefore a requirement that online redo log files to be located on a shared storage device.
Whenever you create your database manually then by default database is in noarchive log mode. If you create your database with database configuration assistant (dbca) then there is a checkbox and check the box will enable your database archival mode. But if your database is in noarchivelog mode and you want to enable the archivelog mode then you must shutdown the database. So while enabling archivelog mode in RAC database you must shutdown your all instances. The following is the procedure to enable a database from noarchivelog mode to archivelog mode in RAC environment.
Step 01: Login to any instance and check the archival settings and location.
SQL> conn / as sysdba
SQL> archive log list
Step 02: If you need to change archival location then you can change it by
ALTER SYSTEM SET archival_parameter=new_value scope=both;
Step 03: Shutdown all RAC instances. The following command will shutdown all instances of orcl cluster database.
$ srvctl stop database -d orcl
Step 04: Connect to any of the local instance and MOUNT the database:
$ sqlplus "/ as sysdba"
SQL> startup mount
Step 05: Enable archive log mode.
SQL> alter database archivelog;
Step 06: Shutdown the local instance:
SQL> shutdown immediate
Step 07: Startup all the instances using srvctl.
$ srvctl start database -d orcl
Optionally, bring any services (i.e. TAF) back up using srvctl:
$ srvctl start service -d orcl
Step 08: Login to the local instance and verify Archive Log Mode is enabled:
$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 81
Next log sequence to archive 82
Current log sequence 82
After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.
Related Documents
Wednesday, December 16, 2009
ORA-00600: internal error code, arguments: [2141]
Problem Description
While starting up or shutting down the database it fails with oracle error message ORA-00600: with the first argument 2141. A sample message from my screen,
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2141], [4192191924], [0], [], [], [], [], []
Cause of the Problem
The problem happened due to incorrect control files. If you have different versions of controlfile or if you have wrong controlfile pointed inside spfile/pfile then while startup the database above bug will fire. Similarly after starting up the database if you replace your controlfile by wrong one then while shutting down the database above bug will fire.
Solution of the Problem
The solution of this problem is to correct control_files parameter settings within spfile/pfile whenever you start up the database. Below procedures will help you.
1) Whenever you start up the database make sure whether you start the database by pfile or spfile and also be sure about their location.
2) If you are not sure which file is used then issue "startup nomount" command after connecting as sqlplus / as sysdba to the database.
3) If you start your database with spfile then issuing "show parameter spfile" will locate the location of your spfile location. So simply create a pfile by issuing "create pfile='new_pfile' from spfile" will give you pfile and you can work with that.
4) Whether you start your database with spfile or pfile if you issue "show parameter control_files" it will list the control_files that is denoted by the control_files initialization parameter inside your spfile/pfile (by which one you started your database).
5) Note those locations and check whether the locations are correct. If those are not correct and if you use spfile you can change the locations by,
SQL> alter system set control_files='/oradata1/arju/newcontrol01.ctl', '/oradata1/arju/newcontrol02.ctl' scope=spfile;
and then "startup force". If you use pfile then you need to change manually after opening the pfile with a text editor.
6)If you get error still then check one by one control files as described in the post http://arjudba.blogspot.com/2008/04/ora-00214-controlfile-version.html
If you get this error message while you shutting down your database, then do a shutdown abort and follow the same steps as it is written for startup the database procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/ora-01665-control-file-is-not-standby.html
http://arjudba.blogspot.com/2009/01/ora-00600-arguments-keltnfy-ldminit-46.html
http://arjudba.blogspot.com/2008/12/ora-07445-exception-encountered-core.html
http://arjudba.blogspot.com/2008/11/what-is-ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/10/ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/09/ora-07445-exception-encountered-core.html
While starting up or shutting down the database it fails with oracle error message ORA-00600: with the first argument 2141. A sample message from my screen,
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2141], [4192191924], [0], [], [], [], [], []
Cause of the Problem
The problem happened due to incorrect control files. If you have different versions of controlfile or if you have wrong controlfile pointed inside spfile/pfile then while startup the database above bug will fire. Similarly after starting up the database if you replace your controlfile by wrong one then while shutting down the database above bug will fire.
Solution of the Problem
The solution of this problem is to correct control_files parameter settings within spfile/pfile whenever you start up the database. Below procedures will help you.
1) Whenever you start up the database make sure whether you start the database by pfile or spfile and also be sure about their location.
2) If you are not sure which file is used then issue "startup nomount" command after connecting as sqlplus / as sysdba to the database.
3) If you start your database with spfile then issuing "show parameter spfile" will locate the location of your spfile location. So simply create a pfile by issuing "create pfile='new_pfile' from spfile" will give you pfile and you can work with that.
4) Whether you start your database with spfile or pfile if you issue "show parameter control_files" it will list the control_files that is denoted by the control_files initialization parameter inside your spfile/pfile (by which one you started your database).
5) Note those locations and check whether the locations are correct. If those are not correct and if you use spfile you can change the locations by,
SQL> alter system set control_files='/oradata1/arju/newcontrol01.ctl', '/oradata1/arju/newcontrol02.ctl' scope=spfile;
and then "startup force". If you use pfile then you need to change manually after opening the pfile with a text editor.
6)If you get error still then check one by one control files as described in the post http://arjudba.blogspot.com/2008/04/ora-00214-controlfile-version.html
If you get this error message while you shutting down your database, then do a shutdown abort and follow the same steps as it is written for startup the database procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/ora-01665-control-file-is-not-standby.html
http://arjudba.blogspot.com/2009/01/ora-00600-arguments-keltnfy-ldminit-46.html
http://arjudba.blogspot.com/2008/12/ora-07445-exception-encountered-core.html
http://arjudba.blogspot.com/2008/11/what-is-ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/10/ora-00600-internal-error-code.html
http://arjudba.blogspot.com/2008/09/ora-07445-exception-encountered-core.html
ORA-01665: control file is not a standby control file
Problem Description
While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
Cause of the Problem
The error occurred because there was an attempt to mount, recover, or activate a standby database without a standby controlfile. In order to recover standby database you need standby controlfile. If you don't have standby controlfile and use normal controlfile then above error will occurred.
Solution of the Problem
The solution is to create a standby controlfile before attempting to use the database as a standby database.
1)So if you don't have a standby controlfile for your standby first create a standby controlfile from source database using command,
SQL>alter database create standby controlfile as 'standbyctlfile.ctl';
2)Transfer this standby controlfile into standby database.
3)Edit the standby database pfile control_files parameter in order to effect the changes.
4)Startup the database in nomount stage.
SQL> startup nomount pfile='your_pfile_location';
5)Create spfile from the pfile.
SQL> create spfile from pfile='your_pfile_location';
6)Shutdown the database.
SQL>shutdown immediate;
7)Start the database in mount state.
SQL>startup mount;
8)On the standby database, start redo apply by following command.
SQL>alter database recover managed standby database disconnect from session;
Note that only step 1) need to be performed in primary database only. All other steps will be done in standby database.
Related Documents
http://arjudba.blogspot.com/2009/04/different-types-of-standby-database-in.html
http://arjudba.blogspot.com/2009/04/what-is-oracle-data-guard.html
While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
Cause of the Problem
The error occurred because there was an attempt to mount, recover, or activate a standby database without a standby controlfile. In order to recover standby database you need standby controlfile. If you don't have standby controlfile and use normal controlfile then above error will occurred.
Solution of the Problem
The solution is to create a standby controlfile before attempting to use the database as a standby database.
1)So if you don't have a standby controlfile for your standby first create a standby controlfile from source database using command,
SQL>alter database create standby controlfile as 'standbyctlfile.ctl';
2)Transfer this standby controlfile into standby database.
3)Edit the standby database pfile control_files parameter in order to effect the changes.
4)Startup the database in nomount stage.
SQL> startup nomount pfile='your_pfile_location';
5)Create spfile from the pfile.
SQL> create spfile from pfile='your_pfile_location';
6)Shutdown the database.
SQL>shutdown immediate;
7)Start the database in mount state.
SQL>startup mount;
8)On the standby database, start redo apply by following command.
SQL>alter database recover managed standby database disconnect from session;
Note that only step 1) need to be performed in primary database only. All other steps will be done in standby database.
Related Documents
http://arjudba.blogspot.com/2009/04/different-types-of-standby-database-in.html
http://arjudba.blogspot.com/2009/04/what-is-oracle-data-guard.html
Tuesday, December 15, 2009
System Requirements for running Magento
In order to run magento open source e-commerce software you need the following server requirements.
1)Operating System: Linux servers are only supported servers for magento software.
2)Web Servers: Apache 1.3.x or Apache 2.x or higher version of Apache are only supported versions of web servers for magento.
3)Development Language: Magento only supports PHP and version of PHP must be at least 5.2.0 or above.
4)Database: Mysql is only database language that is supported for magento and version must be 4.1.20 or above.
So your hosting server must support these specification. Before you hosting company check out their specifications. Also you need to enable following extensions.
- curl,
- dom,
- gd,
- hash,
- iconv,
- mcrypt,
- pcre,
- pdo,
- pdo_mysql,
- simplexml.
You can also check your hosting server requirements by following below steps.
1) Download magento-check.zip from http://www.magentocommerce.com/_media/magento-check.zip.
2) After unzipping the file and upload the php file to your Magento directory on the server.
3) In your browser navigate to this page. http://......../magento-check.php
It will say which requirements you are missing.
Related Documents
http://arjudba.blogspot.com/2009/12/magento-installation-does-not-proceed.html
1)Operating System: Linux servers are only supported servers for magento software.
2)Web Servers: Apache 1.3.x or Apache 2.x or higher version of Apache are only supported versions of web servers for magento.
3)Development Language: Magento only supports PHP and version of PHP must be at least 5.2.0 or above.
4)Database: Mysql is only database language that is supported for magento and version must be 4.1.20 or above.
So your hosting server must support these specification. Before you hosting company check out their specifications. Also you need to enable following extensions.
- curl,
- dom,
- gd,
- hash,
- iconv,
- mcrypt,
- pcre,
- pdo,
- pdo_mysql,
- simplexml.
You can also check your hosting server requirements by following below steps.
1) Download magento-check.zip from http://www.magentocommerce.com/_media/magento-check.zip.
2) After unzipping the file and upload the php file to your Magento directory on the server.
3) In your browser navigate to this page. http://......../magento-check.php
It will say which requirements you are missing.
Related Documents
http://arjudba.blogspot.com/2009/12/magento-installation-does-not-proceed.html
Monday, December 14, 2009
Wordpress or Joomla? Which one to choose?
Wordpress or Joomla? Which one you should use?
Both Joomla and Wordpress have a great demand now a days. The reason is simple- because site built by Joomla and Wordpress are better for SEO results because of their SEO friendly urls. Both of them are Free Open source Content Management systems that will help you manage your websites and also used for templates for the layout of your sites.
To make your blog as well as your website you might also choose one of them. But which one to choose? You first need to ask yourselves what type of website you will build? Here goes typical questions.
- Do you want to blog or build a blogging website? If so then choose wordpress. In case of blogging site you can't beat Wordpress. User, SEO friendly urls, tags feature of wordpress make the blogging job easy.
- Do you want to build a big website with lots of features? If your target is to build a big website with lots of features then choose Joomla. There is so many Joomla templates that make the things easy.
- Do you want to build a small website with small features? If so then choose Wordpress as cms.
- Do you want to build a large community site? If so then go for Joomla.
- Do you want to build a website with multilevel categories? If so then go for Wordpress.
Now let's have an idea about Wordpress and Joomla functions.
Wordpress has dashboard by which you can manage new post, adding categories, changing themes, links etc.
In Joomla control panel is much different than that of wordpress. There is lots of functions already built in Joomla. Once you have gone through the ins and outs of each Joomla and Wordpress you know better what fits for you from a technical point of view.
From my point of view, neither core Joomla nor core wordpress will fit for your needs. So you needs more functions, in other words you need plugins to accommodate your needs. For extra functionalities Joomla! uses three different kind of items, plugins, modules and components whereas WordPress uses Plugins and Widgets.
Joomla Components, Module and Plugins
In Joomla, a component is an extra piece of software that has is own control panel to handle the different options within such a function.
A module is to place some information on a certain position (determined by the template) to show on the front-page.
Plugins are mostly extra functions that create or deliver new back-end functions like a different WYSIWYG Editor.
Wordpress Plugins and Widgets
In WordPress plugins do the same functions as in Joomla, some give your more back-end controls like the Robots Meta plugin or they will have their own Control panel (setting). They comes with extra functions with their extra management panel.
Widgets are more like the Modules in Joomla, you can show pieces of information on a separate position, also determined by the Theme of your WordPress installation.
Whether you use Joomla or Wordpress you should install latest versions of it and you need to upgrade them. In their control panel you can have a look whether any update version is available.
Related Documents
Both Joomla and Wordpress have a great demand now a days. The reason is simple- because site built by Joomla and Wordpress are better for SEO results because of their SEO friendly urls. Both of them are Free Open source Content Management systems that will help you manage your websites and also used for templates for the layout of your sites.
To make your blog as well as your website you might also choose one of them. But which one to choose? You first need to ask yourselves what type of website you will build? Here goes typical questions.
- Do you want to blog or build a blogging website? If so then choose wordpress. In case of blogging site you can't beat Wordpress. User, SEO friendly urls, tags feature of wordpress make the blogging job easy.
- Do you want to build a big website with lots of features? If your target is to build a big website with lots of features then choose Joomla. There is so many Joomla templates that make the things easy.
- Do you want to build a small website with small features? If so then choose Wordpress as cms.
- Do you want to build a large community site? If so then go for Joomla.
- Do you want to build a website with multilevel categories? If so then go for Wordpress.
Now let's have an idea about Wordpress and Joomla functions.
Wordpress has dashboard by which you can manage new post, adding categories, changing themes, links etc.
In Joomla control panel is much different than that of wordpress. There is lots of functions already built in Joomla. Once you have gone through the ins and outs of each Joomla and Wordpress you know better what fits for you from a technical point of view.
From my point of view, neither core Joomla nor core wordpress will fit for your needs. So you needs more functions, in other words you need plugins to accommodate your needs. For extra functionalities Joomla! uses three different kind of items, plugins, modules and components whereas WordPress uses Plugins and Widgets.
Joomla Components, Module and Plugins
In Joomla, a component is an extra piece of software that has is own control panel to handle the different options within such a function.
A module is to place some information on a certain position (determined by the template) to show on the front-page.
Plugins are mostly extra functions that create or deliver new back-end functions like a different WYSIWYG Editor.
Wordpress Plugins and Widgets
In WordPress plugins do the same functions as in Joomla, some give your more back-end controls like the Robots Meta plugin or they will have their own Control panel (setting). They comes with extra functions with their extra management panel.
Widgets are more like the Modules in Joomla, you can show pieces of information on a separate position, also determined by the Theme of your WordPress installation.
Whether you use Joomla or Wordpress you should install latest versions of it and you need to upgrade them. In their control panel you can have a look whether any update version is available.
Related Documents
Best SEO tips to increase your website traffic
http://arjudba.blogspot.com/2009/07/how-to-setup-google-webmaster-tool-for.html
Sunday, December 13, 2009
Export Full=y fails with PLS-00201 ORA-06510 ORA-06512
Problem Description
In the database version 10.2.0.3, 10.2.0.4 original version of export with FULL=Y option fails with error PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared. From the log file error stack is shown below.
Cause of the Problem
The error occurred during full database export operation because the user who is performing export operation does not have execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION. Though the user might be a dba or sysdba user but it need explicit privilege to these two packages.
Solution of the Problem
Note that the role IMP_FULL_DATABASE, this is not enough to export FULL database schema in this case. So, it's necessary to grant the execute right over this package directly to the user and not through a role.
The solution is,
1) Connect to database as sys user.
SQL> conn / as sysdba
2) Explicitly grant the execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION to the user performing the export as shown below.
SQL> GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO <user_name>;
SQL> GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO <user_name>;
3) Run the export operation again.
Related Documents
Export/Import from Enterprise Manager
Original Export/Import
In the database version 10.2.0.3, 10.2.0.4 original version of export with FULL=Y option fails with error PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared. From the log file error stack is shown below.
Table DEF$_AQCALL will be exported in conventional path. . . exporting table DEF$_AQCALL EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204 ORA-06512: at "SYS.DBMS_SQL", line 323 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 Table DEF$_AQERROR will be exported in conventional path. . . exporting table DEF$_AQERROR EXP-00008: ORACLE error 6510 encountered ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 . . exporting table DEF$_CALLDEST EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204 ORA-06512: at "SYS.DBMS_SQL", line 323 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 . . exporting table DEF$_DEFAULTDEST EXP-00008: ORACLE error 6510 encountered ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 . . exporting table DEF$_DESTINATION EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1204 ORA-06512: at "SYS.DBMS_SQL", line 323
Cause of the Problem
The error occurred during full database export operation because the user who is performing export operation does not have execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION. Though the user might be a dba or sysdba user but it need explicit privilege to these two packages.
Solution of the Problem
Note that the role IMP_FULL_DATABASE, this is not enough to export FULL database schema in this case. So, it's necessary to grant the execute right over this package directly to the user and not through a role.
The solution is,
1) Connect to database as sys user.
SQL> conn / as sysdba
2) Explicitly grant the execute privilege on DBMS_DEFER_IMPORT_INTERNAL and DBMS_EXPORT_EXTENSION to the user performing the export as shown below.
SQL> GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO <user_name>;
SQL> GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO <user_name>;
3) Run the export operation again.
Related Documents
Export/Import from Enterprise Manager
Original Export/Import
Export fails with EXP-00002: error in writing to export file
Problem Description
I ran my script exp10g_zip.sh and I failed with error below.
After running script whenever I see ./exp10g_zip.sh: line 172: /usr/contrib/bin/gzip: No such file or directory I immediately thought that location of gzip is wrong and hence error returned but then after seeing EXP-00002: error in writing to export file I guessed that it might be the problem because of oracle failed to write dump in the operating file system.
Solution of the Problem
In order to solve "EXP-00002: error in writing to export file" error you need to look for following things:
1) The export file could not be written to disk anymore, probably because the disk is full or the device has an error.
Check the disk free usage by issuing command,
$df -h
And check whether device has any error or not by creating file into it and writing some.
$vi test_device.txt
and write some contents and save it. If it is ok then device has no problem in it.
2) It may be the cause because of your file systems do not support a certain limit (eg. dump file size > 2Gb). If you see dumpfile you might see like it's size like 1.99G and after exporting next table it fails because file system does not support file more than 2G. In that case you need to divide the dump into several files. For that include a filesize parameter as well as add more dumpfiles with the file parameter.
4) Note that while export operation, Export selects from the DUAL table to obtain the current date and other information. So when DUAL table has been truncated, you cannot select any data from it any more. If a select is not possible, "EXP-00002: error in writing to export file" will be produced.
To solve it,
i) Check who owns the DUAL table (should be SYS only) and if there is a public
synonym for it:
ii)Check if there is a row in dual, if not, add one:
But in my case these solution did not solve my problem. If you check the script
which gzip I see its location is /bin/gzip and so in the script I used following lines and it solved my problem.
Related Documents
Export/Import from Enterprise Manager
Original Export/Import
I ran my script exp10g_zip.sh and I failed with error below.
EAIAPP:/opt/oracle/admin/EAIAPP/scripts>./exp10g_zip.sh EAIAPP 1 Y ALL ./exp10g_zip.sh: line 172: /usr/contrib/bin/gzip: No such file or directory ERROR MSG: ./exp10g_zip.sh FAILED WITH AN ORACLE (EXP) ERROR ON HOST db1-eai.pst.hrn.clearitlab.com LOG FILE: /opt/oracle/admin/EAIAPP/logs/exp10g_zip.sh_21273.log RC: 1From the /opt/oracle/admin/EAIAPP/logs/exp10g_zip.sh_21273.log logfile (which is export logfile) it shows,
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set EXP-00002: error in writing to export file EXP-00000: Export terminated unsuccessfullyFrom the script logfile it says
INVOKING DATABASE BACKUP Sun, 13 Dec 2009 00:11:44. Export: Release 10.2.0.4.0 - Production on Sun Dec 13 00:11:44 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set EXP-00002: error in writing to export file EXP-00000: Export terminated unsuccessfully DATABASE BACKUP COMPLETE 00:11:44. EXPORT FILE /opt/oracle/admin/EAIAPP/exports/EAIAPP_20091213_001144.dmp ./exp10g_zip.sh FAILED WITH AN ORACLE (EXP) ERROR. SYNOPSIS WRITEN TO STDOUT FOR LSF. EXIT WITH RC=1 exit 1And my script looked like below,
if mkfifo $PIPE
then
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM
/usr/contrib/bin/gzip < $PIPE > $EXPFILE.Z&
if [ $EXP_USERS = "ALL" ] ; then
$ORACLE_HOME/bin/exp exp_dba/$DB_EXP_DBA file=$PIPE full=Y direct=Y rows=$EXP_DATA compress=N log=$EXP_LOG STATISTICS=NONE >> $LOGFILE 2>&1
else
$ORACLE_HOME/bin/exp exp_dba/$DB_EXP_DBA file=$PIPE OWNER=\(${EXP_USERS}\) direct=Y rows=$EXP_DATA compress=N log=$EXP_LOG STATISTICS=NONE >> $LOGFILE 2>&1
fi
/bin/rm $PIPE 2>/dev/null
fi
RC=$?
Cause of the ProblemAfter running script whenever I see ./exp10g_zip.sh: line 172: /usr/contrib/bin/gzip: No such file or directory I immediately thought that location of gzip is wrong and hence error returned but then after seeing EXP-00002: error in writing to export file I guessed that it might be the problem because of oracle failed to write dump in the operating file system.
Solution of the Problem
In order to solve "EXP-00002: error in writing to export file" error you need to look for following things:
1) The export file could not be written to disk anymore, probably because the disk is full or the device has an error.
Check the disk free usage by issuing command,
$df -h
And check whether device has any error or not by creating file into it and writing some.
$vi test_device.txt
and write some contents and save it. If it is ok then device has no problem in it.
2) It may be the cause because of your file systems do not support a certain limit (eg. dump file size > 2Gb). If you see dumpfile you might see like it's size like 1.99G and after exporting next table it fails because file system does not support file more than 2G. In that case you need to divide the dump into several files. For that include a filesize parameter as well as add more dumpfiles with the file parameter.
Example: exp arju/arju full=y file=exp%U.dmp log=export.log filesize=2000M3) Make sure no limitation is shown by issuing ulimit -a.
4) Note that while export operation, Export selects from the DUAL table to obtain the current date and other information. So when DUAL table has been truncated, you cannot select any data from it any more. If a select is not possible, "EXP-00002: error in writing to export file" will be produced.
To solve it,
i) Check who owns the DUAL table (should be SYS only) and if there is a public
synonym for it:
SQL> connect / as sysdba
SQL> select created,owner,object_type from dba_objects
where object_name='DUAL';
CREATED OWNER OBJECT_TYPE
----------------- ------------------------------ ------------------
29-JUL-2009 00:06 SYS TABLE
29-JUL-2009 00:06 PUBLIC SYNONYM
Also make sure, Only DUAL table exists under SYS schema. Delete other copies if there is any.ii)Check if there is a row in dual, if not, add one:
SQL> select * from dual;
no rows selected
SQL> insert into dual values ('X');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dual;
D
-
X
iii)Now re-run the export.But in my case these solution did not solve my problem. If you check the script
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM /usr/contrib/bin/gzip < $PIPE > $EXPFILE.Z&and error message it says at first /usr/contrib/bin/gzip: No such file or directory. Then I checked whether gzip already exist in the location /usr/contrib/bin/ and saw it is not there. By using,
which gzip I see its location is /bin/gzip and so in the script I used following lines and it solved my problem.
trap "/bin/rm $PIPE 2>/dev/null" HUP INT QUIT TERM /bin/gzip < $PIPE > $EXPFILE.Z&
Related Documents
Export/Import from Enterprise Manager
Original Export/Import
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML