11G – SQL*PLUS Error logging

The SQL*Plus interface has several interesting 11g release innovations, including the new error logging feature and the incorporation of default SQL*Plus settings in the SQL*Plus executable itself instead of in the traditional glogin.sql file.

SQL*Plus Error Logging

When you’re troubleshooting code errors, it’s common to use the show errors command in SQL*Plus to identify the errors in a PL/SQL statement. Other than this, there was no way to check code errors, and the errors were not stored for later examination. In Oracle Database 11g,there’s a new SQL*Plus command called set errorlogging, which stores all errors resulting from the execution of any SQL, PL/SQL, and even SQL*Plus commands in a special error logging table.

By default, the set errorlogging command causes any query errors to be written to the default table SPERRORLOG. You can specify your own table name for the error logging table, instead of using this default table name. For each error, the error logging feature logs the following bits of information:

• The username

• The time when the error occurred

• The name of the script that contains the query, if a script was used

• A user-defined identifier

• The ORA, PLS, or SP2 error message

• The query statement that caused the error

By default, error logging is turned off, as you can see from the following query:

SQL> show errorlogging
errorlogging is OFF

You can turn error logging on with the set errorlogging command, as shown here:

SQL> set errorlogging on;

If you issue the show errorlogging command again to ensure that error logging has been successfully turned on, you’ll see something interesting:

SQL> connect hr/hr
SQL> show errorlogging
errorlogging is ON TABLE HR.SPERRORLOG

Not only does the database turn error logging on, but it also creates a new table called hr.sperrorlog to hold the error messages. The prefix to the error table is the same as the name of the schema owner who sets error logging on. In this case, we logged in as the user hr, so the error log is created in the hr schema.

The following example shows how to query the error logging table, sperrorlog, to retrieve the error messages and the SQL statements or PL/SQL code that generated those error messages:

SQL> select username,statement,message from sperrorlog;
USERNAME STATEMENT                                                       MESSAGE
——– ————————–                                                  ————————–
HR       create table employees as select * from employees ORA-00955: name is already used by an existing object

The first message indicates that the table creation statement failed because there is already a table with an identical name. The second message shows that the error was because of the presence of an invalid column name in a select statement.

11G – Active Database Duplicate

Parameters to change :

==============

DB_NAME
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
CONTROL_FILES
DB_RECOVERY_FILE_DEST
DB_BLOCK_SIZE

For example :

initdup.ora
————

*.audit_file_dest=’/u01/app/oracle/admin/dup/adump’
*.audit_trail=’db’
*.compatible=’11.1.0.0.0′
*.control_files=’/u01/database/dup/control01.ctl’,'/u01/database/dup/control02.ctl’,'/u01/database/dup/control03.ctl’
*.db_block_size=8192
*.db_domain=”
*.db_name=’dup’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=dupXDB)’
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=419430400
*.nls_language=’ENGLISH’
*.nls_territory=’UNITED KINGDOM’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
DB_FILE_name_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’)
LOG_FILE_NAME_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’)

2. Create password file for auxillary instance.

$orapwd file=orapwdup entries=10

3. Make sure that the auxiliary instance can be connect using ORACLE NET. For this configure the tnsnames.ora accordingly. Confirm the connection to both the target and the auxiliary instance using sqlplus.

4. Start the auxiliary instance in NOMOUNT state

SQL > starup nomount pfile=’$ORACLE_HOME/dbs/initdup.ora

5. Start RMAN and connect to the database.

$ rman

RMAN> TARGET sys / sys@<targets database>;

RMAN> connect AUXILIARY sys/sys@<Auxiliary db>;

RMAN> connect CATALOG rman/rman@<Catalog db>;

6. Run the DUPLICATE command.

RMAN>DUPLICATE TARGET DATABASE TO ‘DUP’ FROM ACTIVE DATABASE DB_FILE_name_CONVERT=(‘/u01/database/dev/’,'/u01/database/dup’);

11G – Create PFILE / SPFILE from Memory

In 11G , you can create pfile or spfile from memory of running instance.

SQL> create pfile from memory;
File created.

You can also use the memory clause to create an spfile, as shown here:

SQL> create spfile from memory;
File created.

You need to have instance up and running to be able to create pfile or spfile from memory.