Oracle in World: January 2010

Saturday, January 30, 2010

SP2-0734: unknown command beginning rest of line ignored

Problem Description
Running any script or SQL or PL/SQL fails with error SP2-0734 unknown command beginning "........" - rest of line ignored..
Following is some example generated while working with script, SQL or Pl/Sql.

Case 01:
Running an oracle script fails as,
SQL> @csminst.sql
SP2-0734: unknown command beginning "csminst.sq..." - rest of line ignored.

Case 02:
Running a procedure fails as,
SQL> my_proc();
SP2-0042: unknown command "my_proc()" - rest of line ignored.

Case 03:
Login to database using sql*plus even login to sql*plus fails with SP2-0042 as below.
$ sqlplus / nolog
SP2-0734: unknown command beginning "yyy..." rest of line ignored.

Case 04:
When trying to purge a table from the recyclebin that is issuing SQL fails with,
SQL> purge table t;
SQL> purge recyclebin;
"SP2-0734: unknown command beginning "purge tabl..." - rest of line ignored."

Solution of the Problem

Case 01 Solution
This is due to the display terminal keyboard configuration of the kill character. Because script is ok and unknown command shown the script name.

The problem is common with unix environment with the display terminal keyboard settings. The sqlplus session had trouble interpreting the "@" sign, because it was assigned in the terminal to the "kill" setting. The csminst.sql script was supposed to be run as "@ csminst.sql" and since the "@" sign had a completely different meaning for this OS session, sqlplus only saw "csminst.sql" and hence it throws error.

There is two solution exists for this type of scenario. One is to change the display terminal keyboard setting of the kill character to something else. For example:

# stty kill ^u

After making this change the script is interpreted correctly and runs as it should.

Another is, run the script using "start" keyword instead of "@" sign.

SQL> start csminst.sql

Case 02 Solution
This is happened because of incorrect way to execute a stored procedure. When calling a PL/SQL stored procedure from SQL*plus you must call it using the EXECUTE (or EXEC) command or via a PL/SQL BEGIN-END block.

Following is the correct examples.

1) EXEC my_proc ();

or

2) BEGIN
my_proc();
END;
/

Case 03 Solution
In this case any invalid entries in glogin.sql file causes this issue. The glogin.sql script gets executed when users invoke sqlplus, even with nolog option.

Check if there is any invalid commands defined in glogin.sql (which usually resides in $ORACLE_HOME/sqlplus/admin).

If not, then check if there is any issue with this file itself. Also rename the glogin.sql file to glogin_bak.sql and try invoking sqlplus again.

Case 04 Solution
This problem happened whenever you connect to the database 10g using SQL*Plus version 9i or lower. The purge command is new in rel 10g, so older versions of SQL*Plus do not recognize it.
To implement the solution, execute the following steps:

1. Connect to the database using SQL*Plus rel 10g, either by logging in directly to the server, or by running SQL*Plus from a 10g client installation.

2. Rerun the purge command. As long as you connect from a 10g version of SQL*Plus, the command will work.

Special Case
Note that, a common SP2-0734 problem happened during controlfile creation. After you have issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE you have generated controlfile script. Whenever you run the script it fails as below.

SQL> CREATE CONTROLFILE REUSE DATABASE "A" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ARJU\ORADATA\A\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\APP\ARJU\ORADATA\A\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\APP\ARJU\ORADATA\A\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  'D:\APP\ARJU\ORADATA\A\SYSTEM01.DBF',
  'D:\APP\ARJU\ORADATA\A\SYSAUX01.DBF',
  'D:\APP\ARJU\ORADATA\A\UNDOTBS01.DBF',
  'D:\APP\ARJU\ORADATA\A\USERS01.DBF',
  'D:\APP\ARJU\PRODUCT\11.1.0\DB_1\DATABASE\DATA01.DBF',
  'F:\MIGRATE_TO_ASM.DBF'
CHARACTER SET WE8MSWIN1252
;

SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SP2-0734: unknown command beginning "'D:\APP..." - rest of line ignored.

This happened due to blank line before the DATAFILE clause and after -- STANDBY LOGFILE , remove that space line as well as remove line -- STANDBY LOGFILE and re run the script, it should be fixed. Fixed one will look like,

SQL> CREATE CONTROLFILE REUSE DATABASE "A" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ARJU\ORADATA\A\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\APP\ARJU\ORADATA\A\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\APP\ARJU\ORADATA\A\REDO03.LOG'  SIZE 50M
DATAFILE
  'D:\APP\ARJU\ORADATA\A\SYSTEM01.DBF',
  'D:\APP\ARJU\ORADATA\A\SYSAUX01.DBF',
  'D:\APP\ARJU\ORADATA\A\UNDOTBS01.DBF',
  'D:\APP\ARJU\ORADATA\A\USERS01.DBF',
  'D:\APP\ARJU\PRODUCT\11.1.0\DB_1\DATABASE\DATA01.DBF',
  'F:\MIGRATE_TO_ASM.DBF'
CHARACTER SET WE8MSWIN1252
;

Related Documents
http://arjudba.blogspot.com/2008/12/understanding-execution-plan-statistics.html
http://arjudba.blogspot.com/2008/11/formatting-sqlplus-reports.html
http://arjudba.blogspot.com/2008/12/controlling-autotrace-report-in-sqlplus.html
http://arjudba.blogspot.com/2008/12/formatting-sqlplus-reports-part-2.html
http://arjudba.blogspot.com/2008/11/use-of-bind-variables-in-sqlplus.html
http://arjudba.blogspot.com/2008/11/communicate-with-user-through-accept.html
http://arjudba.blogspot.com/2008/11/working-with-sqlplus-scripts.html
http://arjudba.blogspot.com/2008/11/sqlplus-basics-and-tips.html
http://arjudba.blogspot.com/2008/08/error-45-initializing-sqlplus-internal.html
http://arjudba.blogspot.com/2008/05/how-to-see-explain-plan-from-sqlplus.html
http://arjudba.blogspot.com/2008/05/change-prompt-in-sqlplus.html
http://arjudba.blogspot.com/2008/05/how-to-set-environmental-variable-to.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-host.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-in.html
http://arjudba.blogspot.com/2008/05/how-can-one-pass-operating-system.html
http://arjudba.blogspot.com/2008/05/where-is-my-column-data-sqlplus-does.html
http://arjudba.blogspot.com/2008/05/automatic-recovery-during-applying-logs.html
http://arjudba.blogspot.com/2008/05/purpose-and-restriction-of-recover.html
http://arjudba.blogspot.com/2008/04/shutdown-modes-in-oracle.html

RMAN-06900, RMAN-06901, ORA-19921 maximum number of 64 rows exceeded

Problem Description
RMAN backups has been successfully completed but from the backup logs the following error have been generated.
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010

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

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 64 rows exceeded

A variation of the above error is,
Recovery Manager: Release 11.1.0.7.0 - Production on Fri Jan 29 00:01:15 2010

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

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded

Analysis And Solution of the Problem
As soon as you get error message RMAN-06900 and RMAN-06901 you immediately look for associated error messages. The associated error message should tell you more information and you need to look for those messages in order to solve this error. For example here we are getting additional error "ORA-19921: maximum number of 128 rows exceeded". So our solution will lie on ORA-19921.

RMAN gives a warning message of RMAN-6900, RMAN-6901 ORA-19921 when the output is too huge and oracle is not able to write the log output into v$rman_output. There are several reasons when oracle will not be able to write the log output into V$RMAN_OUTPUT. For example, one of your control file becomes corrupted, hence oracle is unable to write the the log output to RMAN view/table. The another major reasons cause this problem to happen is due to oracle bug.

You get oracle error "ORA-19921: maximum number of 64 rows exceeded" due to oracle Bug 465973.
You get oracle error "ORA-19921: MAXIMUM NUMBER OF 128 ROWS EXCEEDED" due to oracle Bug 8264365.

The bug 465973 is fixed in Oracle 10.2.0.4 and Oracle 11G. So if you see oracle only gives warning message and backup successfully done then you can simply ignore error messages or upgrade oracle or apply patch where bug is fixed.

However if you see your backup is not done due to these errors then immediately check for additional messages. If it happened due to controlfiles then take care of those errors.

Related Documents:

How to Restore the Controlfile from Backup.

ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown

Controlfile in Oracle Database.

New Feature of 10.2g: Eliminate Control File Re-Creation

Creating controlfile fails with ORA-01503, ORA-01161

Backup Database control file -User Managed

Recover database after only lose of all controlfiles

Friday, January 29, 2010

Fatal error: Allowed memory size of 33554432 bytes exhausted

Problem Description
In the wordpress dashboard Plugins, Incoming Links section does not load and loading fails with message,
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 1966080 bytes) in /home/nextlew4/public_html/arju-on-it.com/wp-includes/class-simplepie.php on line 5409

Also, whenever you try to upgrade any plugin it fails with message,
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 2350382 bytes) in /home/nextlew4/public_html/arju-on-it.com/wp-includes/http.php on line 1365

Cause of the Problem
From the wordpress release of 2.5, within wp-settings.php file there is parameter WP_MEMORY_LIMIT which allows you to specify the maximum amount of memory that can be consumed by PHP. The default value for WP_MEMORY_LIMIT is limit to 32MB. So by default, WordPress will attempt to increase memory allocated to PHP to 32MB (you can find this code at beginning of wp-settings.php).

Note that, this setting imposes memory limit to usage memory of PHP only for WordPress, not other applications.

Solution of the Problem
If wordpress PHP needs more memory than the value set inside wp-settings.php you will receive a message such as "Allowed memory size of xxxxxx bytes exhausted".

So the setting in wp-config.php should reflect something higher than 32MB.

You will find the wp-config.php file under wordpress home installation directory. If you open the file you will see the following lines at the beginning of the file.

if ( !defined('WP_MEMORY_LIMIT') )
define('WP_MEMORY_LIMIT', '32M');

Increase the limit to somewhere bigger value. For example to increase PHP Memory to 64MB

define('WP_MEMORY_LIMIT', '64M');

Increase PHP Memory size to 100MB
define('WP_MEMORY_LIMIT', '100M');

Please note that, this setting may not work if your host does not allow for increasing the PHP memory limit. If after increasing limit from wp-settings.php you still get "Fatal error: Allowed memory size of 33554432 bytes exhausted" contact your host to increase the PHP memory limit. Note that many hosts set the PHP limit at 8MB.

Related Documents
http://arjudba.blogspot.com/2010/01/how-to-add-logo-to-wordpress-site.html
http://arjudba.blogspot.com/2010/01/how-to-transfer-wordpress-site-to-new.html
http://arjudba.blogspot.com/2010/01/introducing-wordpress-theme.html
http://arjudba.blogspot.com/2010/01/how-to-install-wordpress.html

Sunday, January 24, 2010

LOG_ARCHIVE_FORMAT in Oracle

If you have enabled archive log mode in your database then LOG_ARCHIVE_FORMAT parameter will come into role. If your database is in archivelog mode then redo log files will be archived and the parameter LOG_ARCHIVE_FORMAT determines the name of the archived log files.

LOG_ARCHIVE_FORMAT uses a text string and variables to specify the format of the archived files.

The following variables can be used with the LOG_ARCHIVE_FORMAT

1) %s : log sequence number

2) %S : log sequence number, zero filled

3) %t : thread number

4) %T : thread number, zero filled

5) %a : activation ID

6) %d : database ID

7) %r : resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros.

Following is an example of how we can set LOG_ARCHIVE_FORMAT in a database.

SQL> ALTER SYSTEM SET log_archive_format='VSPRODP_%s_%t_%r.arch' SCOPE=spfile;

System altered.

Note that, neither LOG_ARCHIVE_DEST nor LOG_ARCHIVE_FORMAT have to be complete file or directory specifiers themselves; they only need to form a valid file path after the variables are substituted into LOG_ARCHIVE_FORMAT and the two parameters are concatenated together.

For example, we are setting the following values to log_archive_dest and log_archive_format parameters.

SQL> alter system set log_archive_dest='E:\oracle';

System altered.

SQL> alter system set log_archive_format='arju_%s_%t_%r.arch' scope=spfile;

System altered.

SQL> col name for a30
SQL> col value for a30
SQL> select name, value from v$spparameter where name in ('log_archive_dest','log_archive_format');

NAME                           VALUE
------------------------------ ------------------------------
log_archive_dest               E:\oracle
log_archive_format             arju_%s_%t_%r.arch

Friday, January 22, 2010

How to add a logo to wordpress site

Step 01: Login to wordpress admin panel.
Step 02: Under the Appearance section click on Editor.

Step 03: The header.php file controls the logo section of a wordpress site. So open the header.php file for edit.

Step 04: Find out the section <div id="header"> within header.php. Within <div section, id="header"> you will see a line something similar.

<h1><a href="<?php echo get_option('home'); ?>"><?php bloginfo('name'); ?></a></h1>

Step 05: Delete the part
<?php bloginfo('name'); ?>
from above line. If you want to know what does <?php bloginfo('name'); ?> mean then please have a look at temaplate tags from Introducing wordpress theme.

And replace the deleted part with this line:

<img src="the url to image" alt="site logo" />

If your image name is logo.jpg and you keep it under images folder then it should look like,

<h1><a href="<?php echo get_option('home'); ?>">
<img src="images/logo.jpg" alt="site logo" />
</a></h1>

Step 06: Update the file and check load the url of your wordpress home page to see how it looks. You can use image height, width property to resize your logo but it is always better first resize by photoshop/gimp and then use it as logo.

Related Documents
http://arjudba.blogspot.com/2010/01/how-to-transfer-wordpress-site-to-new.html
http://arjudba.blogspot.com/2010/01/introducing-wordpress-theme.html
http://arjudba.blogspot.com/2010/01/how-to-install-wordpress.html

Thursday, January 21, 2010

How to transfer wordpress site to new domain or new location - Way 1

Wordpress stores two address urls inside the database.
One address url determines the location of your blog files.
Another address url determines the location of main index.
If you do default install then both blog address and main index urls will be same.

There are two reasons when you need to change one of the two address urls.

1) You want to change the wordpress url or blog url or you have tried to change the Blog URL or WordPress URL in Settings, and an error has occurred.

2) You want to move/copy your wordpress site to a new domain/new hosting server.

Following is the step by steps procedure about how to transfer wordpress site to new domain or new location.

Step 01: Backup/Export full database from your old wordpress site.
Note that this step is not required if you want to move/transfer your wordpress site in your same hosting provider cpanel even if you want to move to a new domain in the same hosting server. This step is only required if you want to transfer your wordpress site to a new hosting server or if you want to copy wordpress site across your new domain.

You can use command line tool and any GUI tool to backup database. If you use command line mysqldump tool then you can issue following command, to export a mysql database - named wordpress_01 where username is root and password is prema and dumpfile name is d:\dump.sql

E:\>mysqldump -u root -pprema wordpress_01 >d:\dump.sql

If you use phpmyadmin GUI browser then you can do:
- Login to phpmyadmin
- From the left side click on your desired database that you want to export.
- Database Structure is displayed. Click on Export tab.
- Select all the tables and select radio button as SQL (though these two are selected by default)
- You can choose Compression to None or "zipped" or "gzipped". And then click Go.
- You have now your wordpress database backup.

Step 02: Create a new Wordpress database in the new hosting server.
Note that this step is not required if you want to move/transfer your wordpress site in your same hosting provider cpanel even if you want to move to a new domain in the same hosting server. This step is only required if you want to transfer your wordpress site to a new hosting server or if you want to copy wordpress site across your new domain.

You can use command line tool and any GUI tool to create a new wordpress database in the hosting server where you want to copy wordpress site or transfer wordpress site.
To create a new mysql database named wordpress_new using command line tool issue following command after login as admin user,
mysql> create database wordpress_new;
Or you can use your cpanel to create new mysql database. After login to cpanel click on the MySQL Databases and then create database.

After you create new database,
- Add New User. Create a new username and password. It is easy in cpanel. If you are good at command line interface have a look at Create user in mysql.

- After you create user assign the user to the new database created.

Step 03: Download and upload (Transfer/Copy/Move) all WordPress files & folders to new site:
Copy/Move/Transfer whole wordpress directory from your old site to your new site. You can use any ftp tool to do that for example FileZilla Ftp Client. If you have ssh access to your server you can scp/cp files from your old site to your new site. Note that you must transfer whole wordpress site so it should include your theme files, template files, plugin files.

Step 04: Modify file wp-config.php:
If you open wp-config.php file you will see lines like,
/** The name of the database for WordPress */
define('DB_NAME', 'mysql_arju');
/** MySQL database username */
define('DB_USER', 'mysql_arju');
/** MySQL database password */
define('DB_PASSWORD', 'arju');
/** MySQL hostname */
define('DB_HOST', 'localhost');

Now change these values of mysql database, username and password to accommodate values for new site mysql database. In this example, it should be
define('DB_NAME', 'wordpress_new');
define('DB_USER', 'arju');
define('DB_PASSWORD', 'test');
Note that above change of DB_NAME, DB_USER, DB_PASSWORD is not required if you want to transfer site in the same server or if you want new server but there you have database, username, password with the same name as it was in old site.

Adding the following lines are extremely important if you like to change your domain name or to change your wordpress/blog location in the same domain.

define('WP_HOME','http://arju-on-it.com');
define('WP_SITEURL','http://arju-on-it.com');

where http://arju-on-it.com will be the new address url for my both wordpress home and blog site.

Step 05: Import (upload) the previously backup database.
Note that this step is not required if you want to move/transfer your wordpress site in your same hosting provider cpanel even if you want to move to a new domain in the same hosting server. This step is only required if you want to transfer your wordpress site to a new hosting server or if you want to copy wordpress site across your new domain.
Using command line tool or using graphical user interface you can import the previous exported database.
If you use phpmyadmin then just select your database, click the Import tab, click Choose file button and choose the sql file that you exported previously (if you have gzipped/zipped version then you need to unzip it) and then click Go. You can check list of tables imported into database.
If you use command tool then you can use mysqlimport tool to load tables from the sql files into mysql database.

Step 06: Change the Urls in the database wp_options and wp_posts tables:
You need to change url addresses from database to adapt the urls of new domain/new location.

To fix URLs of the WordPress posts and pages issue,

UPDATE wp_posts SET guid = REPLACE (guid,'http://old_site_url.com','http://new_site_url.com');

If you have linked internally within blog posts or pages with absolute URLs, these links will point to wrong locations after you move your site. Use the following SQL commands to fix all internal links to own blog in all WordPress posts and pages:

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old_site_url.com', 'http://www.new_site_url.com');

Update WordPress options with the new site url, by using following SQL command:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old_site_url.com', 'http://www.new_site_url.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Wednesday, January 20, 2010

RMAN-00571, RMAN-00569, RMAN-00571, RMAN-03002, RMAN-05021

Problem Description
In the standby database rman configuration of configure retention policy fails with error message RMAN-05021 as below.
RMAN> configure retention policy to redundancy 1;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 01/19/2010 04:00:23
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file

Cause of the Problem:
It is attempted to modify the configuration which cannot be changed for a BACKUP or STANDBY control file while the mounted control file was either BACKUP or STANDBY.

As it is standby database and database is mounted using standby control file so we can't change the retention policy using standby controlfile.

The following configurations can be changed only when connected to primary database instance that has CURRENT/CREATED control file type mounted:
1) CONFIGURE RETENTION POLICY
2) CONFIGURE EXCLUDE
3) CONFIGURE ARCHIVELOG DELETION POLICY

Solution of the Problem:
In order to change retention policy, configure exclude and archivelog deletion policy you must connect to primary database instance and execute the command.
Related Documents:

How to Restore the Controlfile from Backup.

ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown

Controlfile in Oracle Database.

New Feature of 10.2g: Eliminate Control File Re-Creation

Creating controlfile fails with ORA-01503, ORA-01161

Backup Database control file -User Managed

Recover database after only lose of all controlfiles

Tuesday, January 19, 2010

How to delete duplicate rows from a excel file

The method demonstrated here is applicable for
- Microsoft Office Excel 2003 and
- Microsoft Excel 2002.
A duplicate row or a duplicate record means all values in the row are an exact match of all the values in another row.

In this post I will filter excel list for unique rows, delete the original list, and then replace it with the filtered list. So before proceeding I recommend you backup the original excel file because existing excel file will be overwritten.

Step by step solution is provided.

Step 01: Open the excel document. Select all the rows, including the column headers in the list.

Step 02: On the Data menu, point to Filter, and then click Advanced Filter.

Step 03: In the Advanced Filter dialog box, click Filter the list, in place.

Step 04: Select the Unique records only check box, and then click OK.

The filtered list is displayed and the duplicate rows are hidden.

Step 05: On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

Step 06: Copy the filtered list. You filtered list should be still selected and you just need to press CTRL+C or just click Copy button.

In this stage the filtered list is highlighted with bounding outlines and the selection appears as an item at the top of the Clipboard.

Step 07: In this step just delete the original list because we want to overwrite it. To delete the original list on the Data menu, point to Filter, click Show All, original list will be displayed and then press DELETE key.

Step 08: In the Clipboard, click on the filtered list item. The filtered list appears in the same location as the original list.

Related Documents
http://arjudba.blogspot.com/2009/11/helpctrexe-application-error.html
http://arjudba.blogspot.com/2009/07/disable-skype-from-using-opening-or.html
http://arjudba.blogspot.com/2009/07/how-to-check-or-identify-which-process.html
http://arjudba.blogspot.com/2009/07/how-to-know-process-id-or-pid-on.html
http://arjudba.blogspot.com/2009/07/different-ways-to-open-office-docx.html
http://arjudba.blogspot.com/2009/04/automatic-startup-issues-of-oracle.html
http://arjudba.blogspot.com/2009/03/how-to-get-gui-from-windows-to.html
http://arjudba.blogspot.com/2009/01/copy-files-between-unix-and-windows.html
http://arjudba.blogspot.com/2008/11/resolve-of-you-may-be-victim-of.html
http://arjudba.blogspot.com/2008/10/who-is-connected-to-your-windows.html
http://arjudba.blogspot.com/2008/10/open-application-using-keyboard.html
http://arjudba.blogspot.com/2008/08/microsoft-management-console-keyboard.html
http://arjudba.blogspot.com/2008/07/internet-explorer-keyboard-shortcuts.html
http://arjudba.blogspot.com/2008/07/windows-explorer-keyboard-shortcuts.html

Fix ORA-01092, ORA-24324, ORA-01041 internal error

Problem Description
Both database startup and shutdown raise error message. Startup fails with ORA-01092 and shutdown fails with ORA-24324 and ORA-01041 as follows.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 1268508 bytes
Variable Size 1124074724 bytes
Database Buffers 1006632960 bytes
Redo Buffers 15507456 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

Even connection as sysdba fails.
SQL> connect / as sysdba
ORA-01041: internal error. hostdef extension doesn't exist

Cause of the Problem
1) It may happen that domain administrator account is used to startup/shutdown oracle database instance and it throws above error message. But if the local system account is used, connection as sysdba works fine.

2) Some datafiles were offline. ORACLE instance terminated. Disconnection forced reported because some of the datafiles were offline.

Solution of the Problem

1) If it happens that only domain administrator account fails to startup/shutdown oracle database instance or connection as sysdba fails then the reason would be the system time was set incorrectly.
The system time did not match the time on the domain server causing the authentication to fail. Solution is set the system time correctly and then reboot the server.

2) If the problem happened due to offline datafiles try to find if there are any offline files by checking the v$recover_file view.

SQL> startup mount;
SQL> select * from v$recover_file;
12 OFFLINE OFFLINE 2489123132 30-DEC-09
23 OFFLINE OFFLINE 2489123132 30-DEC-09

Recover the offline datafiles that were appearing from above select statement,

SQL> recover datafile 12,23;

Media recovery complete.

Bring these datafiles back online,
SQL> alter database datafile 12,23 online;

Database altered.

Ensure that no more files just check again the v$recover_file view, then you can safely open the database.

SQL> select * from v$recover_file;
no rows selected

Now open the database.
SQL> alter database open;

3) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html
http://arjudba.blogspot.com/2010/01/shutdown-fails-with-ora-24324-ora-24323.html

Shutdown fails with ORA-24324, ORA-24323, ORA-01090

Problem Description
Shut down oracle database (shutdown normal/shutdown immediate) fails with following errors.
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted

Cause of the Problem
The problem happened because background processes are hanging/not stared correctly during the previous startup of this database. Hence the smeaphores and shared memory segements are not getting detached properly now during shutdown.

Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 1200 issue,
$ kill -9 1200

2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments and semaphores.
A) checking and removing shared memory.
Verify the shared memory segment by,
$ ipcs -mt

To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.

B) checking and removing semaphores
Check the semaphores by,
$ipcs -sbt

To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.

3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.
$ cd $ORACLE_HOME/dbs
$ rm lk{db_name}

4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.
$ cd $ORACLE_HOME/dbs
$ rm sgadef{SID}.dbf

5) Exit sql*plus window, re login to sql*plus and try to bring up oracle one step at a time.
$ sqlplus /nolog
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;

Note that while performing above steps it is recommended to first bring down all the other instances running on the server and then perform the above steps one by one. Re-booting the server will also fix this problem.
Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html

ORA-01102: cannot mount database in EXCLUSIVE mode

Problem Description
While I start oracle database instance it fails with following error.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 877574740 bytes
Fixed Size 651436 bytes
Variable Size 502653184 bytes
Database Buffers 263840000 bytes
Redo Buffers 10629120 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

Problem Investigation
After you hit ORA-01102 error you should immediately check your database alert log for further analysis. Following is the sample example error messages generated in the alert log after you hit ORA-01102.

Alert log Error Message Version 01
ALTER DATABASE MOUNT
Wed Oct 22 03:40:21 2009
scumnt: failed to lock /dba/oracle/product/920/dbs/lkARJU exclusive
Wed Oct 22 03:40:21 2009
ORA-09968: scumnt: unable to lock file
Linux Error: 11: Resource temporarily unavailable
Additional information: 26165
Wed Oct 22 03:40:29 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT

Alert log Error Message Version 02
ALTER DATABASE MOUNT
Mon Mar 6 15:31:21 2009
scumnt: failed to lock /apps/oracle/product/9.2/dbs/lkARJU exclusive
Mon Mar 6 15:31:21 2009
ORA-09968: scumnt: unable to lock file
Compaq Tru64 UNIX Error: 13: Permission denied
Additional information: 1246156
Mon Mar 6 15:31:21 2009
ORA-1102 signalled during: ALTER DATABASE MOUNT...

Cause of the Problem
This ORA-01102 error indicates an instance tried to mount the database in exclusive mode, but some other instance has already mounted the database in exclusive or parallel mode. By default a database is started in EXCLUSIVE mode. The real cause of ORA-01102 would be found in the alert log file where you will find additional information. The common reasons causing error ORA-01102 are as follows.

1) The processes for Oracle (pmon, smon, lgwr and dbwr) still exist. You can search them by ps -ef |grep YOUR_DB_NAME_HERE.

2) Shared memory segments and semaphores still exist even though the database has been shutdown.

3) There exists a file named "$ORACLE_HOME/dbs/lk{db_name}" where db_name is your actual database name.

4) A file named "$ORACLE_HOME/dbs/sgadef{sid}.dbf" exists where sid is your actual database SID.

5) You have two databases in your host. Now starting anyone of these causes error ORA-01102 if the other one is already started. If one is shutdown, the other database can be started successfully. This happened as while starting up, both the databases are trying to lock the same file. This is obvious if within the parameter files for these databases have the same entries for control_files and db_name. For example you have two databases named dba1 and dba2. Now inside the spfile/pfile of both databases that is inside initDBA1.ora and initDBA2.ora (in case of pfile) you have the similar entries like below.

...
*.control_files='xxx/control01.ctl','xxx/control02.ctl','xxx/control03.ctl'
*.db_name=DBA1
...

Solution of the Problem
1) Verify that there are no background processes owned by "oracle"
$ ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix "kill" command.
For example to kill a process ID number 7818 issue,
$ kill -9 7818

2) Verify that no shared memory segments and semaphores that are owned by "oracle" still exist.
Verify by command,
$ ipcs -b

If there are shared memory segments and semaphores owned by "oracle", remove the shared memory segments.
To remove shared memory segment issue,
$ ipcrm -m Shared_Memory_ID_Number
where Shared_Memory_ID_Number must be replace by shared memory id number.

To remove the semaphores issue,
$ ipcrm -s Semaphore_ID_Number
where Semaphore_ID_Number must be replaced by your Semaphore ID Number.

3) Verify that file $ORACLE_HOME/dbs/lk{db_name} does not exist where db_name is your actual database name.

4) Verify that file "$ORACLE_HOME/dbs/sgadef{sid}.dbf" does not exist where sid is your actual database SID.

5) If you see you have several databases in your machine and both of them uses have same entry in the parameter control_files and db_name then use correct values belonging to the individual databases.

In the sql*plus nomount stage you can issue,
show parameter db_name;
show parameter control_files;
in order to verify the entry.

6) From alert log if you see error like "Compaq Tru64 UNIX Error: 13: Permission denied" then ensure that in the file/directory oracle has permission and ensure that oracle is owner of the file. With chmod and chown you can change permission and ownership respectively.

Note that The "lk{db_name}" and "sgadef{sid}.dbf" files are used for locking shared memory. It may happen that even though no memory is allocated, Oracle thinks memory is still locked. By removing the "sgadef" and "lk" files you remove any knowledge oracle has of shared memory that is in use. So after removing those two file you can try to startup database.

Related Documents
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/11/ora-01033-oracle-initialization-or.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-00444.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html
http://arjudba.blogspot.com/2008/04/ora-01034-ora-27101-shared-memory-realm.html

Monday, January 18, 2010

Magento Add Product fails with failed to open stream: No such file or directory

Problem Description
In the Magento Admin Panel under Catalog menu you click Manage Products. And now you try to Add a new product after clicking Add Product button. After giving product information whenever you try to save the product it fails with following errors.

Warning: include(Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend\Tierprice.php) [function.include]: failed to open stream: No such file or directory in E:\xampp\htdocs\magento\lib\Varien\Autoload.php on line 93

Cause of the Problem
As the error says the system could not find file Tierprice.php in the location Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend and hence it throws error.

Solution of the Problem
Go to the directory and you will see the file named as Tierprice.php0000664. You need to rename that file to Tierprice.php and then try to add product in magento.
Here from windows XP machine I used xampp and from command line I renamed the file. Following is the copy paste information from the command line windows command prompt.

E:\Documents and Settings\Arju>cd E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend

E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend>dir
 Volume in drive E is New Volume
 Volume Serial Number is 889B-18A3

 Directory of E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend

12/06/2009  04:09 PM    <DIR>          .
12/06/2009  04:09 PM    <DIR>          ..
09/24/2009  12:52 AM             1,635 Gallery.php
09/24/2009  12:52 AM             2,405 Image.php
09/24/2009  12:52 AM             7,430 Media.php
09/24/2009  12:52 AM             3,168 Tierprice.php0000664
09/24/2009  12:52 AM             1,862 Urlkey.php
               5 File(s)         16,500 bytes
               2 Dir(s)   3,835,740,160 bytes free

E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend>ren Tierprice.php0000664 Tierprice.php

E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend>dir
 Volume in drive E is New Volume
 Volume Serial Number is 889B-18A3

 Directory of E:\xampp\htdocs\magento\app\code\core\Mage\Catalog\Model\Resource\Eav\Mysql4\Product\Attribute\Backend

01/18/2010  03:00 PM    <DIR>          .
01/18/2010  03:00 PM    <DIR>          ..
09/24/2009  12:52 AM             1,635 Gallery.php
09/24/2009  12:52 AM             2,405 Image.php
09/24/2009  12:52 AM             7,430 Media.php
09/24/2009  12:52 AM             3,168 Tierprice.php
09/24/2009  12:52 AM             1,862 Urlkey.php
               5 File(s)         16,500 bytes
               2 Dir(s)   3,833,298,944 bytes free

Sunday, January 17, 2010

File Deletion Policy from Flash Recovery Area (FRA)

Flash Recovery Area (FRA) is used to set up a disk area where the database can create and manage a variety of files related to backup and recovery. If database level flashback is on then oracle creates flashback logs into flash recovery area. Also oracle creates archived logs in the flash recovery area. RMAN can store its backup sets and image copies in the flash recovery area too.

By default if flash recovery area is enabled then any backups of tablespace /datafile /database/ spfile/ controlfile/ archived logs go to flash recovery area. The site of flash recovery area is set by the parameter db_recovery_file_dest_size parameter and its location is specified by db_recovery_file_dest parameter. Now if the total file size inside flash recovery area is about to reach the it's limit then the files which to be deleted to reclaim space is determined by RMAN backup retention policy. Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose. Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape. When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.

Based on the space in your flash recovery area oracle database will generate alert. The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%. When the recovery area is completely full and oracle sees there is no files for deletion from the flash recovery area, it will throw following errors,

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim nnnn bytes disk space from mmmm limit

where nnnn is the number of bytes required and mmm is the disk quota.

So before you hit this error you should know which files from FRA should be eligible for deletion and which are not. I have made a list based on deletion policy.

1) Permanent files are never eligible for deletion. For example multiplexed redo log files and control files are part of database, that means they are permanent files and so they are never eligible for deletion.

2) Files that are obsolete under the retention policy are eligible for deletion. For example you set up CONFIGURE RETENTION POLICY TO REDUNDANCY 1 in RMAN configuration parameter but you have more than 1 copy backup and your FRA is under space pressure, then oracle can delete older copies of backup. In the same way if you configure RMAN to recovery window of 7 days and you have backups more than 7 days and those older backups are not needed to recover database in any point within 7 days then those older files than 7 days are eligible for deletion from FRA.

3) Transient files that have been copied to tape are eligible for deletion.

4) Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements ( If Archived Redo Log Deletion Policy is set ).
Note that, the default settings of archive log is
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
which mean by default archive logs are never eligible for deletion.

5) Foreign archived logs that have been mined by a LogMiner session on a logical standby database are eligible for deletion. Unlike an ordinary archived redo log, a foreign archived redo log has a different DBID.

6) If retention policy is set to none then no files will be eligible for deletion. But note that
if files are never considered obsolete, then a file can only be deleted from the flash recovery area if it has been backed up to some other disk location or to a tertiary storage device such as tape.

You can monitor flash recovery area space by following two views

A) V$RECOVERY_FILE_DEST : To find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.

B) V$FLASH_RECOVERY_AREA_USAGE : To find out the percentage of the total disk quota used by different types of files, how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.

Related Documents
RMAN-06091 no channel allocated for maintenance (of an appropriate type)
RMAN-06429: TARGET database is not compatible with this version of RMAN
How to take RMAN backup on a remote disk/ location
RMAN-00554,RMAN-04005,ORA-0103 when remote connection by rman
Set Date format inside RMAN environment
How to skip a tablespace for restore operation
How to debug Backup, Restore Session in RMAN
Restore operation fails with RMAN-11003 ORA-01511 ORA-01516
Recover database after only lose of all controlfiles
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value
How to restore an Spfile from autobackup older than 7 days
RMAN-06172: no autobackup found
Creating a Duplicate Database on a Remote Host -Part1
Database Duplication Fails Missing Log RMAN-06053 RMAN-06025
How to perform Database Point in time Recovery DBPITR

RMAN-06091 no channel allocated for maintenance (of an appropriate type)

Problem Description
RMAN DELETE OBSOLETE command fails with following error stack.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 01/17/2010 02:15:40
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Problem Investigation
This error occurs if you attempt to delete obsolete backup from tape but channel had not being allocated on tape. And also these error stacks will appear if you attempt to delete obsolete backup from disk but channel had not being allocation on disk.

Note that if you allocate channel to disk and then attempt to delete obsolete it should work if your backups are in disk. We assume that backups are in disk and let's try to delete it after allocating channel to disk.

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

allocated channel: ORA_MAINT_DISK_2
channel ORA_MAINT_DISK_2: sid=149 devtype=DISK

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           3739   Dec 24 2009 04:36:02
  Backup Piece       6037   Dec 24 2009 04:36:02 lml1m82u_1_2
Backup Set           3738   Dec 24 2009 04:36:02
  Backup Piece       6036   Dec 24 2009 04:36:02 lnl1m82u_1_2
Backup Set           3743   Dec 24 2009 04:38:06
  Backup Piece       6038   Dec 24 2009 04:38:06 c-3269547898-20091224-01
.
.
.
Backup Set           3857   Jan 11 2010 04:25:21
  Backup Piece       6217   Jan 11 2010 04:25:21 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0111-00
Backup Set           3862   Jan 12 2010 04:58:50
  Backup Piece       6223   Jan 12 2010 04:58:50 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0112-00
Backup Set           3867   Jan 13 2010 04:40:29
  Backup Piece       6228   Jan 13 2010 04:40:29 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0113-00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 01/17/2010 02:15:40
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

But no help. It fails still.

Solution of the Problem
Using show all command check the rman configuration parameter settings.

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'ora_cf%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/db/app/oracle/product/10.2.0/db/dbs/snapcf_OMSRPS.f'; # default

From the above configuration we see that channels are allocated to disk.
Using the following command verify that whether the backup sets are on tape or in disk.

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3743    Full    7.25M      SBT_TAPE    00:01:55     Dec 24 2009 04:38:06
        BP Key: 6038   Status: AVAILABLE  Compressed: NO  Tag: TAG20091224T043611
        Handle: c-3269547898-20091224-01   Media:
  Standby Control File Included: Ckp SCN: 1318360946   Ckp time: Dec 24 2009 04:32:46
  SPFILE Included: Modification time: Aug 25 2009 03:53:48

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3746    Incr 1  228.05M    SBT_TAPE    00:08:49     Dec 25 2009 04:18:25
        BP Key: 6045   Status: AVAILABLE  Compressed: YES  Tag: LEVEL_1_122509
        Handle: lvl1ot00_1_2   Media:
  List of Datafiles in backup set 3746
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  1    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/system01.dbf
  2    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/undotbs01.dbf
  3    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/sysaux01.dbf
  7    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/rman_ts.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3747    Incr 1  136.52M    SBT_TAPE    00:17:24     Dec 25 2009 04:27:00
        BP Key: 6044   Status: AVAILABLE  Compressed: YES  Tag: LEVEL_1_122509
        Handle: lul1ot00_1_2   Media:
  List of Datafiles in backup set 3747
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  4    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/users01.dbf
  5    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/mgmt.dbf
  6    1  Incr 1320551570 Dec 25 2009 03:40:48 /db/app/oradata/OMSRPS/mgmt_ecm_depot1.dbf

From the above command we see the backups are on SBT_TAPE. So you have to issue delete command from the tape.

So our command will be as follows.
RMAN> allocate channel for maintenance device type 'sbt_tape' PARMS '...';
Here please change '...' to your actual tape params

RMAN> delete obsolete;

or issue following command to delete obsolete backups from tape.
RMAN> allocate channel for maintenance type sbt_tape;

However if your backups are on disk then your command will look like below.

RMAN> allocate channel for maintenance type disk;

allocated channel: ORA_MAINT_DISK_4
channel ORA_MAINT_DISK_4: sid=79 devtype=DISK

And then,

RMAN> delete obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           3792   Jan 01 2010 04:25:18
  Backup Piece       6115   Jan 01 2010 04:25:18 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0101-00
Backup Set           3799   Jan 02 2010 04:25:44
  Backup Piece       6126   Jan 02 2010 04:25:44 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0102-00
.
.
.
  Backup Piece       6217   Jan 11 2010 04:25:21 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0111-00
Backup Set           3862   Jan 12 2010 04:58:50
  Backup Piece       6223   Jan 12 2010 04:58:50 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0112-00
Backup Set           3867   Jan 13 2010 04:40:29
  Backup Piece       6228   Jan 13 2010 04:40:29 /db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-2010                                               0113-00

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100101-00 recid=6115 stamp=7071                                               13518
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100102-00 recid=6126 stamp=7071                                               99944
deleted backup piece
.
.
.
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100112-00 recid=6223 stamp=7080                                               65929
deleted backup piece
backup piece handle=/db/app/oracle/product/10.2.0/db/dbs/ora_cfc-3269547898-20100113-00 recid=6228 stamp=7081                                               51228
Deleted 13 objects

If you want to delete both obsolete and expired backups from disk then you can issue,

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
RMAN> run
{
report obsolete;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
DELETE EXPIRED BACKUP;
DELETE EXPIRED COPY;
delete obsolete;
}
release channel;

Saturday, January 16, 2010

ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files

Problem Description:
From alert log, we find the following errors

Sat Jan 16 03:08:36 2010
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Jan 16 03:08:36 2010
ORACLE Instance OMSRPS - Archival Error
Sat Jan 16 03:08:36 2010
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
Sat Jan 16 03:08:36 2010
Errors in file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc:
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'

If we check the content of trace file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc we see,

ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available.
*** 2010-01-16 03:23:54.463
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
*** 2010-01-16 03:23:54.464 62692 kcrr.c
ARC4: Error 19809 Creating archive log file to '/backup/flash_recovery_area/OMSRPS/archivelog/2010_01_16/o1_mf_1_11181_%u_.arc'
*** 2010-01-16 03:23:54.464 60970 kcrr.c
kcrrfail: dest:1 err:19809 force:0 blast:1
ARCH: Connecting to console port...
ARCH: Connecting to console port...
*** 2010-01-16 03:23:54.513 21373 kcrr.c
ORA-16038: log 6 sequence# 11181 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'

Cause of the Problem
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,

SQL> set lines 120
SQL> col name format a50
SQL> select name
,floor(space_limit / 1024 / 1024) "Size MB"
,ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/

NAME                                            Size MB      Used MB
-------------------------------------------     ---------    ------------
/backup/flash_recovery_area                     95232        95152

We see in the flash recovery area space limit and space used are almost same and no more space for archival. Hence we get error.

Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.

Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.

On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k

Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,

SYS@OMSRPS> show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/flash_recovery_area
db_recovery_file_dest_size big integer 93G

or by issuing query,

SQL> select value from v$parameter where name='db_recovery_file_dest_size';

VALUE
-----------------------------------------------------------------------------
99857989632

In order to increase the size of the parameter issue following query.
SYS@OMSRPS> alter system set db_recovery_file_dest_size = 95G scope=both;

System altered.

Here, I have increased to 95G. Ensure by issuing,

SYS@OMSRPS> show parameter db_recovery_file

NAME                                    TYPE         VALUE
------------------------------------ -----------     --------------------------
db_recovery_file_dest                   string       /backup/flash_recovery_area
db_recovery_file_dest_size              big integer  95G

Now check your alert log file and you see Archiver process freed from errors.

Sat Jan 16 03:37:31 2010
ALTER SYSTEM SET db_recovery_file_dest_size='95G' SCOPE=BOTH;
Sat Jan 16 03:38:25 2010
Archiver process freed from errors. No longer stopped
Sat Jan 16 03:38:25 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[9]: No standby redo logfiles of size 512000 blocks available
Sat Jan 16 03:38:27 2010
Media Recovery Waiting for thread 1 sequence 11186 (in transit)

Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';

System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.

Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.

a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';

b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;

c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,

SQL> Drop restore point restore_point_name;

d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.

To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.

Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312

Interact with RMAN Client.

Issues between RMAN Client and Database

About Issuing RMAN Commands

The LIST, REPORT, and DELETE Commands in RMAN

Friday, January 15, 2010

Introducing Wordpress Theme

In the post Wordpress Admin Panel I have introduced about wordpress admin panel. In this post I will discuss about wordpress theme. Wordpress theme is a collection of files that work together to produce the graphical interface with an underlying design for the website.

Wordpress theme uses combination of three things.
A)Template Files.
B)Template Tags.
C)CSS Style Sheets.

A)Template Files: Template files are the files (building blocks) which come together to create the site. The header, sidebar, content, footer are all individual template files.

The following are the template files typically included within a Theme.

i) 404 Template = 404.php
ii) Archive Template = archive.php
iii) Archive Index Page = archives.php
iv) Comments Template = comments.php
v) Footer Template = footer.php
vi) Header Template = header.php
vii) Links = links.php
viii) Main Template = index.php
ix) Page Template = page.php
x) Popup Comments Template = comments-popup.php
xi) Post Template = single.php
x) Search Form = searchform.php
xi) Search Template = search.php
xii) Sidebar Template = sidebar.php

B) Template Tags: Template Tags are small piece of code which provide instructions and requests for information stored within the WordPress database. It instructs WordPress to "do" or "get" something. For example after login to wordpress Dashboard if you open template file header.php ( from Appearance>Editor) you will notice template tags like below.

<?php language_attributes(); ?>
<?php bloginfo('html_type'); ?>
<?php bloginfo('charset'); ?>
<?php bloginfo('name'); ?>
<?php bloginfo('stylesheet_url'); ?>
<?php bloginfo('pingback_url'); ?>
<?php wp_get_archives('type=monthly&format=link'); ?>
<?php //comments_popup_script(); // off by default ?>
<?php wp_head(); ?>
<?php bloginfo('url'); ?>

Each of these template tags instructs wordpress to "do" or "get" something. Let's introduced with bloginfo temaplte tag with some parameters that it take.
1)Site Name <?php bloginfo('name'); ?> : The bloginfo template tag with name parameter is used to display the site name that is set by admin via Settings> General subpanel.

2)Site Description <?php bloginfo('description'); ?> : The bloginfo template tag with description parameter displays some descriptive sentence that says the blog/site about. Generally it is called tagline. This is set by admin via Settings> General subpanel.

3)Site url <?php bloginfo('url'); ?> : The bloginfo template tag with url parameter is used to display the URL or website address for your WordPress site. This is set by admin via Settings> General subpanel.

4) Admin Email <?php bloginfo('admin_email'); ?> : bloginfo template tag with admin_email parameter is used to display email of the administrator.

5) Wordpress Version : The bloginfo template tag with version parameter display the version of WordPress you are using.

C)CSS Style Sheets: CSS Style Sheets make the things all together. With your css you can move the building block structures around, make your header very long, float the sidebar left or right. The css stylesheet instruction are found under style.css file.

Wednesday, January 13, 2010

How to check whether it is binary or ascii file in Linux

With file command you can determine the file type in unix. Thus with file command you can check whether a file is binary file or ascii file. Before going into much about file command let us check some simple example.

# file .bash_history
.bash_history: ASCII text
# file .dns
.dns: ASCII text, with no line terminators
# file .cvsignore
.cvsignore: empty
# file .ftpquota
.ftpquota: character Computer Graphics Metafile
# file nextlevelrealty.nextlevelre.com
nextlevelrealty.nextlevelre.com: ASCII text, with very long lines
# file memd*
memd: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped
# file pppd
pppd:            ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, stripped

Note that by invoking file command it is shown the file type.
File type can be come as,

1) Text: The file will be text file if they contain one of the words text (the file contains only printing characters and a few common control characters and is probably safe to read on an ASCII terminal)

2) Executable: After invoking file command if you get text executable then the file contains the result of compiling a program in a form understandable to some UNIX kernel or another, which means binary file.

3) Data: If after invoking file command there comes data keyword then it means data is in 'binary' or non-printable format.

So we see if after invoking file command there appears word "text" or "ascii" then it is text or ascii file otherwise it is binary.

Following shell script will tell you whether a file is binary file or text file. This script takes an argument and then prints its type.

file "$1" | grep executable
if [ $? -eq 0 ];then
   echo "File is binary"
else
    file "$1" |egrep "ascii|text"
    if [ $? -eq 0 ];then
        echo "File is ascii"   
    fi
fi