Pubudu: Data and Decisions

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 t...

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      ...

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 - Alpha...

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...

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 ...

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

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;

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;

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;