Oracle in World

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: