ORA-00214: Controlfile Version Inconsistent on Startup or Shutdown

This error is giving because Oracle detects an inconsistency between the mirrored copies of the control file.

All copies of the control file must have the same internal sequence number for oracle to start up the database.

Solution

To fix the error you have to start your database with single copy of the control file and then shut the database down and then copy the good copy of control file onto the other mirror copies.

Step 01 - If database is still up do a shutdown immediate.

Step 02 - If you use pfile then edit the CONTROL_FILES parameter from init.ora and modify it to include just one copy of control file.

You may want to issue create pfile from spfile to create a pfile before editing this parameter.

Step 03 - Start the database in restricted mode. startup restrict or mount the database. startup mount
If it is fine go to step 04.

Step 04 - Shut the database down.
SQL>shutown

Step 05 - Copy the good mirrored copy of the control file that you just used to bring the database up onto all other copies, as originally listed in the CONTROL_FILES parameter of your init.ora file.

Step 06 - Edit the init.ora file's CONTROL_FILES parameter to include all mirror copy again.

Issue create spfile from pfile command in SQL prompt if you are using spfile to start the database.

Step 07 - Start the database.

error ORA-09817: Write to audit file failed. / ORA-00257: archiver error. Connect internal only, until freed.

When you try to login as sys using sys/password as sysdba, you may get the error ORA-09817: Write to audit file failed.

And If you tried to login as a normal user you may get  the error - ORA-00257: archiver error. Connect internal only, until freed.

These errors are giving most probably because your Archive destination is full.

Check archive destionation : 

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1324060
Next log sequence to archive   1324066
Current log sequence           1324066

SQL> show parameter DB_RE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2000G

Try to check the free space with Linux commands (# df -h) and free up space in Archive location.

Better to put a cron job to delete old archives leaving up to 3-4 days archives in your database.

How to generate random numbers in Oracle

You may want to generate random numbers when using Oracle.As many other development tools/languages Oracle also comes with the facility of generating random numbers.

The DBMS_RANDOM package of Oracle will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the <ORACLE_HOME>/rdbms/admin directory.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers. 

VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.

STRING - generate strings in upper case, lower case or alphanumeric format. 

The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.

U - Upper case

L - Lower case

A - Alphanumeric

X - Alphanumeric with upper case alphabets.

P - Printable characters only. 

Examples:

Below are some examples of using the package.

E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

If above results comes as fractional values use ROUND function to round up those values:

Eg:

select round(dbms_random.value(100000000000, 999999999999))num from dual;

EXP-00056: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty

Did you get the above error when trying to use exp command in Oracle..?

#### HOW to solve it.

## To check the default temporary tablespace of the database:
SQL> select property_name, property_value from database_properties;

## check DEFAULT_TEMP_TABLESPACE, if it use wrong temporary tablespace, run:
SQL> alter database default temporary tablespace temp;

## check user with temporary tablespace, run command:
SQL> select username, temporary_tablespace, account_status from dba_users;

## change temporary tablespace:
SQL> create temporary tablespace temp1 tempfile '/data/u01/app/oracle/oradata/TIS/temp_b.dbf' size 100m;

SQL> alter database default temporary tablespace temp1;

SQL> drop tablespace temp including contents and datafiles;

SQL> create temporary tablespace temp tempfile '/data/u01/app/oracle/oradata/TIS/temp01.dbf' size 100m autoextend off extent management local uniform size 1m;

SQL> alter database default temporary tablespace temp;

SQL> drop tablespace temp1 including contents and datafiles;

Recreate Control files

We will have to recreate control files due to various reasons.
Corrupted control files
Control files get deleted accidentally etc...


Below are the steps to recreate a control file.

1. sql>alter database backup controlfile to trace noresetlogs;
2. shutdowm immediate
3. startup nomount;
4. create control file from trace file which we created in step one. To do so go to /udump directory and using vi editor open most latest trace log...Copy control file info from Startup mount till what oracle locale is used. then create an empty file using touch controlfile.sql file and paste copied data from trace file. Now we have a controlfile in text format created from trace file
5. rename all original control files to something. (If old control files exists)

6. startup mount
7. @path to controlfile.sql (ignore if says ORA-01081: cannot start already-running ORACLE - shut it down first) at the end it should say file created successfully.
8. recover database
9. alter database open (Thats it...it should open successfully)
10. To make sure all is good....shutdown immediate
11. startup

However if all of your control files were deleted or missing you will have to create that  .sql file using another available Oracle database and edit it according to your current database settings.

Anyway always remember as a best practice to put your 3 control files in 3 different locations (3 disks are better) when you are creating a database.So it'll save you lot of trouble when it comes to situations where your hard disk fails etc.


Note: You can also mention an optional location to backup control file as below.
         

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE  
        AS '/home/oracle/create_ctl.sql';  

Check Oracle & Linux versions

How to check Oracle version..

[oracle@stardb ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 18 10:50:21 2011

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options



How to check Linux version...

[oracle@stardb admin]$ uname -m
x86_64

or

[oracle@stardb admin]$ file /usr/bin/file
/usr/bin/file: 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

ORA-28000: the account is locked !!

This error message will display when an User Account is locked.You have to unlock the account to before reusing that user again.


You can also set FAILED_LOGIN_ATTEMPTS Parameter to Unlimited to disable account locking  due to failed login attempts in future.



select * from dba_users where USERNAME = 'user_name'

ALTER USER <user_name> ACCOUNT UNLOCK;

View Account Status of a User:

SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='SCOTT';

View Profile Assigned to a User:

SELECT PROFILE FROM DBA_USERS WHERE USERNAME='SCOTT';

View Limits Set for the Profile: 

SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

 Alter FAILED_LOGIN_ATTEMPTS Parameter: 
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;



Alter PASSWORD_LIFE_TIME Parameter: 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Insert some records selected from another table plus some other data in to a new table

Eg:
Select a,b,c from test;
insert into test_bak values (a,b,c,'ttt','123');
This 'ttt' and '123' should be repeated across selected rows.

Insert statement:

insert into test_bak
select a, b, c, 'ttt', '123'
from test;

DB Link

Following query can be used to create a DB link from one oracle database to another..


CREATE PUBLIC DATABASE LINK <linkname>
CONNECT TO <username> IDENTIFIED BY <password>
USING '<hostname>'

Query as below to check about DB links...


SELECT NAME,HOST,USERID,PASSWORD FROM SYS.LINK$;

select owner,db_link,host from dba_db_links;