RMAN - 04015

RMAN Error:
RMAN-04005: error from target database:
ORA-06553: PLS-801: internal error [56319]
RMAN-04015: error setting target database character set to UTF8
Occurs when connecting to RMAN.

Cause: The database had not been shutdown.

Action: Shutdown the database and try to re-connect RMAN.

ORA - 00313, ORA -00312

ORA -00313 : open failed for members of log group string of thread string

Cause: The online log cannot be opened. May not be able to find file.

Action: See accompanying errors and make log available.

ORA - 00312
ORA-00312: online log 1 thread 1: '/u01/TESTDB3/oraredo/redo1a.log'
ORA-00312: online log 1 thread 1: '/u01/TESTDB3/oraredo/redo1b.log'

The above errors pop whenever you try to start the database.

Cause: Either the log file has been removed or the log file has been corrupted.

Action: Follow the steps below -

SQL> starup mount;
SQL> select status from v$log where group#=1;
If the status is not current them simply drop the log file as below:
SQL>alter database drop logfile group 1;
If required create another logfile group.
SQL>alter database add logfile group 3 '/u01/TESTDB3/oraredo/redo3a.log' size 10M;
SQL> recover database until cancel;
SQLalter database open resetlogs;

Oracle Release number format Description

Major Database Release Number - The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number - The second digit that represents the maintenance level. Some new features may be included.

Application Server Release Number - The third digit reflects the release level of the Oracle Application Server.

Component Specific Release Number - The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform Specific Release Number - The fifth digit identifies a platform - specific release number. Ususally this is a patch set.

How to check if the database is started using spfile or pfile?

Way 1: If the following command returns a value then the database has been started using a spfile, else pfile was used during startup.

SQL> show parameter spfile
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string

Way 2: Alter the system using the scope=spfile clause, if it returns error then pfile is used. Please see the following example.

SQL> alter system set open_cursors=300 scope=spfile;
alter system set open_cursors=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

How to set column width in sqlplus?

Step1: Make a note of the columns you would like to set the width for.

Step2: Use the following syntax to set the column widths:
For varchar2 columns

SQL> column <column_name> format a<width no.>
For numeric columns

SQL> column <column_name> format <width no.>

Creating Database manually

Note: This article is biased towards Windows

Step 1: Specifying the Instance's SID
I:\app\WELCOME\product\11.1.0>set ORACLE_SID=PREETI

Step 2: Creating the initialization parameter file.

db_name='PREETI'
memory_target=1G
processes = 150
#audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
#audit_trail ='db'
db_block_size=8192
#db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
#db_recovery_file_dest_size=2G
diagnostic_dest='I:\app\WELCOME\oradata\PREETI'
#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
os_authent_prefix=''
undo_management=auto
undo_tablespace=undotbs1
undo_retention=900
control_files = (I:\app\WELCOME\oradata\PREETI\control\control1.ctl,
                 I:\app\WELCOME\oradata\PREETI\control\control2.ctl)
compatible ='11.1.0'

Step 3: Starting the Instance

I:\app\WELCOME\product\11.1.0\db_1>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 29 20:45:06 2011

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

Connected to an idle instance.

SQL> startup nomount pfile=I:\app\WELCOME\product\11.1.0\db_1\dbs\initPREETI.ora

ORACLE instance started.

Total System Global Area  644468736 bytes

Fixed Size                  1335108 bytes

Variable Size             176160956 bytes

Database Buffers          461373440 bytes

Redo Buffers                5599232 bytes

Step 4: Database Creation

SQL>   create database PREETI

 logfile    group 1 ('I:\app\WELCOME\oradata\PREETI\redo\redo1a.log', 

                            'I:\app\WELCOME\oradata\PREETI\redo\redo1b.log') size 10M,

              group 2 ('I:\app\WELCOME\oradata\PREETI\redo\redo2a.log',

                            'I:\app\WELCOME\oradata\PREETI\redo\redo2b.log') size 10M

 national character set utf8

 datafile                'I:\app\WELCOME\oradata\PREETI\datafiles\system.dbf'

           size 50M

           autoextend on

           next 10M

           maxsize UNLIMITED

 undo tablespace undotbs1

          datafile       'I:\app\WELCOME\oradata\PREETI\datafiles\undotbs1.dbf'

          size 10M

 default temporary tablespace temp

          tempfile       'I:\app\WELCOME\oradata\PREETI\datafiles\temp.dbf'

          size 10M

 sysaux datafile      'I:\app\WELCOME\oradata\PREETI\datafiles\sysaux.dbf'

          size 10M

          autoextend on

          next 10M

          maxsize UNLIMITED;

Database created.

Step 5: Completing the Database Creation

SQL> @?/rdbms/admin/catalog.sql

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql

SQL> @?/rdbms/admin/pupbld.sql

Errors while creating the Database:

ORA-01092: ORACLE instance terminated. Disconnection forced

Solution: the alert log should be checked for the errors occurred during the database creation and act accordingly.

Ways to check the version of an Oracle Database

WAY 1 (Using the v$version view)
SQL> select * from v$version;


BANNER
-------------------------------------------------------------------------------


Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

WAY 2 ( When you connect to the database)

C:\Documents and Settings\WELCOME>sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jan 29 18:44:08 2011

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Manually dropping an Oracle Database

Export the Oracle SID before dropping it, making sure you are working on that required database.

Follow the steps as below:

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1334380 bytes

Variable Size             167773076 bytes

Database Buffers          360710144 bytes

Redo Buffers                5844992 bytes

Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Background Process Description

Process Monitor(PMON)

1. Process Monitor is an Oracle background process created when you start a database instance.
2. The process will free up resources if a user process fails.
3. It normally wakes up every 3 seconds to perform its housekeeping activities.
4. Pmon must always be running else the instance terminates.
5. To speed up the housekeeping the PMON can be manually wokeup as below:

           SQL> oradebug setmypid

           SQL> oradebug wakeup 2

           Assuming, PMON as process 2 here.

System Monitor(SMON)

1. Performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents and also monitors temporary segments.
2. Wakes up every 5 miuntes to perform activities.
3. In a non-failed instance can also perform failed instance recovery for other failed RAC instances.
4. In case of tablespace offline errors or any as such, the transactions are eventually recovered by SMON when taablespace or file is brought back online.
5. To check if the SMON process is running:

                      $ ps -ef | grep smon

                      oracle   31144     1  0 11:10 ?        00:00:00 ora_smon_orcl

 Archiver's Process (ARCH)

1. Created when you start an instance in ARCHIVE LOG MODE.
2. Writes filled redo logs to the archive log locations.
3. An instance can have upto 10 ARCn processes (ARC0 to ARC9).
4. The LGWR starts a new ARCn whenever current number of processes is insufficient. The alert file keeps a track of these.
5. The parameter used is: LOG_ARCHIVE_MAX_PROCESSES.

Log Writer (LGWR)
Created when you start the instance.
Writes the redo log buffers to the on-line redo log files.
If these files are mirrored, all the members of the group will be written out simultaneously.
LGWR writes:
A commit record when a user process commits a transaction.
Redo log buffers
Every three seconds
When the redo log buffer is one-third full.
When a DBWn process writes modified buffers to disk, if necessary.

Oracle DB Block Structure within the Database

Click on the image for a larger view