Oracle in World: 1/24/10

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

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle