How to Create Database Link in Oracle

— Add the following entry in tnsnames.ora file:

SERVICENAME1=
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = hostname1)(PORT = 1521))
       )
       (CONNECT_DATA = (SERVICE_NAME = SERVICENAME1))
      )

–Connect to the database where you want to create db link and run the following command:

CREATE DATABASE LINK link_name
CONNECT TO myusername IDENTIFIED BY mypassword
USING 'SERVICENAME1';

Note:
myusername
->is an username by which you can connect SERVICENAME1 database
mypassword->is myusername‘s password

–Check if it works:

SELECT sysdate
FROM dual@link_name

If it returns sysdate, means it works:)

Expdp/Impdp fails,version incompatibility issue(Installing OLAP)

Real World Scenario

Exchanging data between Oracle 11.1.0 and Oracle 11.2.0 using export/import data pump caused the following error.

PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_LOOP' must be declared
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END' must be declared

Sometimes this happens when you are exchanging data not just between these specific versions of Oracle but between other versions too, especially between higher and lower versions.

One solution for this is to use exp instead of expdp. It really works, but if you still want to use expdp then you should manually install OLAP on that oracle version, which doesn’t have it.

To determine if OLAP is installed or not just run DBCA(located in $ORACLE_HOME/bin). Choose “Configure Database Options”,press Next>> 3 times and if you see this, which means OLAP is not installed

Oracle OLAP is disabled

Do the following steps:

Step 1:

–Connect database as sysdba

export ORACLE_SID=db_sid
Sqlplus sys/sys_pass as sysdba

–Run the following query

SELECT value
FROM v$option
WHERE parameter = 'OLAP';

if the value is TRUE, OLAP is turned on. Go to Step 2.
If the value is FALSE, then shutdown DB and turn it on:

For Linux:

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk olap_on
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ioracle

For Windows:

1. All oracle processes must be stopped. This includes the database (if any), listener, http (apache) server, intelligent agent, etc..
2. Change to the %ORACLE_HOME%\bin directory
3. Rename oraolapop10.dll.OFF to oraolapop10.dll
4. Restart the database

Step 2:

Verify that ‘JServer JAVA Virtual Machine’ is present and VALID in the database instance:

–Run the following query

SELECT comp_id, comp_name, status, substr(version,1,10) as version
FROM dba_registry
WHERE comp_id like 'JAVAVM';

–If the output is NOT like this:

Verify that 'JServer JAVA Virtual Machine' is present and VALID in the database instance


–Then run the following commands when connected as SYSDBA:

SQL>@?/javavm/install/initjvm.sql;
SQL>@?/xdk/admin/initxml.sql;
SQL>@?/xdk/admin/xmlja.sql;
SQL>@?/rdbms/admin/catjava.sql;

Note: ? is $ORACLE_HOME

Step 3:

Now we need to check or possibly  install the components for the XML Database

–Run the following query

select comp_name, version,status
from dba_registry
where comp_id in ('XDB','XML');

–If the output is NOT like this:

check components for the XML Database

Then you should run:

SQL> conn / as SYSDBA
SQL> @?/rdbms/admin/catqm.sql change_on_install XMLDB TEMP;

Step 4:

Check other  requirements:

–Run the following query

select comp_name, version,status
 from dba_registry
 where comp_name='Oracle Expression Filter'

–If the output is NOT like this:

Verify that Oracle Expression Filter is valid

Then run the following as SYS:

–This file is located in $ORACLE_HOME/rdbms/admin/

@catexf.sql

Step 5:

Finally to install  OLAP.

–Run the following script as sysdba

SQL>$ORACLE_HOME/olap/admin/olap.sql

Another Method to install OLAP is with DBCA

–Run DBCA and Click Next>>
Runing DBCA

–Choose “Configure Database Options” and click Next>>
DBCA(Configure Database Options)

–Choose Database SID and click Next>>
DBCA(select database to configure)


— Choose Enterprise Manager should be installed or not.Depends on your needs(I have unchecked it)

DBCA(Uncheck Configure Enterprise Manager)


–If you see this(OLAP is disabled),
DBCA(Oracle OLAP is disabled)


–Then press the button “Standard Database Components…”
Standard Database Components(Oracle JVM option)


–Check “Oracle JVM” and click OK

Standard Database Components(Checked Oracle JVM option)

–Now you should see this
DBCA(Oracle OLAP is enabled)


–Check the option “Oracle OLAP” and click Next>>

SQL Access Advisor(Tuning a single sql)

SQL Access Advisor Overview

The SQL Access Advisor gives you the recommendations about performance tuning. For example it may recommend you to create materialized views, partitions, and/or indexes..You should just give it sql/sqls which are somehow problematic.For example sql which needs big amount of time to execute.

Now  I will discuss Quick Tune, because it is a simple way to tune sqls and also it is widely used . But its “disadvantage” may be counted that it tunes just one sql than multiple sqls(which can be performed by workloads).

Privileges Needed to Use the SQL Access Advisor

You need to have the ADVISOR privilege to manage or use the SQL Access Advisor.

If that user does not have SELECT privileges to a particular table, the SQL Access Advisor neglects  the statement referencing the table. To avoid missing recommendations, the current database user must have SELECT privileges on the tables.

Performing a Quick Tune

QUICK_TUNE procedure accepts as its input a task_name and a SQL statement text or id. It will then create a task and workload and execute that task.

BEGIN
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
                        'MY_QUICKTUNE_TASK',
                        'SELECT COUNT(*)
                         FROM CLIENTS');
END;

To see the result:

SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('MY_QUICKTUNE_TASK')
FROM dual;

How to know that the database was started by spfile or pfile?

–Run the following command

SQL> show parameter spfile;

–Output

NAME  | TYPE |VALUE
——————|——————|—————————————————
spfile|string|

If the output is this(value is empty), means that oracle database was started by pfile.

NOTE :One reason oracle database was started by pfile is that it didn’t find spfile.Another is that you indicated pfile during the startup.

Sql server 2005 change collation for database

Collation is a set of rules how to compare and sort data in a database.Changing database collation may cause some problems especially:

Error-5030 The database could not be exclusively locked to perform the operation.

To resolve this problem you should do the following from command line:

–Uses a trusted connection instead of requesting a password.

C:\>osql -E
1>use djbadmin
2>GO

–Specifies that only one user can connect to the database at a time. By rolling back other connected users immediately.

1>ALTER DATABASE djbadmin SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2>GO

–Changes collation for database to Latin1_General_CI_AS

1>ALTER DATABASE djbadmin COLLATE Latin1_General_CI_AS
2>GO

–Returns database to its usual state(Available for multiple users).

1>ALTER DATABASE djbadmin SET MULTI_USER
2>GO

Oracle Errors(ORA-…)

Here is discussed just one variant how to solve the problem.

ORA-01033; ORA-01034; ORA-01035;  ORA-01203; ORA-02069; ORA-12518;

ORA-01034 and ORA-27101; ORA-19809 and ORA-19804; ORA-27101;
RMAN-06059 & ORA-19625 & ORA-27041

Error

ORA-27101: shared memory realm does not exist

Action

SQL> startup
SQL> shutdown immediate

(It helped me,I hope it will help you too )

Error

ORA-01033: ORACLE initialization or shutdown in progress

Cause

Database is in mount or nomount mode.

Action

–If it is in nomount mode:

SQL>alter database mount;
SQL>alter database open;

–if it is in mount mode:

SQL>alter database open;

Error

ORA-01035:  ORACLE only available to users with RESTRICTED SESSION privilege

Cause

Instance started in restricted mode.

Action

–Ask sysdba to disable restricted session

SQL>alter system disable restricted session;

–Or ask sysdba to give you RESTRICTED SESSION system privilege.

SQL>grant restricted session to yourUserName

Error

ORA-01034: ORACLE not available

Cause

The database and the instance are not started.

Action

SQL>startup;

Error

ORA-12518: TNS:listener could not hand off client connection tips

Action

–On windows

Start->Run->services.msc->OracleServiceMFD manually start it

Note: MFD is instance name

Error:

ORA-02069: global_names parameter must be set to TRUE for this operation

Cause:

You are trying to make DML operation on the remote database using local function.

This is the “Oracle Bug”, it should work but it doesn’t.

Example (for better understanding):

–Assume that we have two databases DB1 and DB2

–On DB1 we have function fun1

create function fun1 return number is
begin
return 1;
end;

–On DB1 we have a database link referring to DB2 called, for simplicity, DB2.

–Check that it works.

select *
from dual@DB2

–If the output is the following, then it works.

DUMMY
-----
X

–Let’s create test table in DB2(connect to DB2 database)

create table tesTable(
id         number,
testColumn number
);

–Let’s make some DML operation, which should cause this ORA-02069 error.

insert into testable@DB2(id,testColumn)
values(1, fun1);

 

“ORA-02069: global_names parameter must be set to TRUE for this operation”

Now, when you already know in what situation this error occurs let’s write the solution. It has two solutions:

Solution one:

1. Set the global_names parameter to true, it can be done on the system level or session level(consider that session level sometimes is not available)

-On DB1

alter session set global_names=true;

2. Create database link on the remote database, in our case on DB2, which will refer to the database DB1(make link name the same as the database global name, because setting global_names parameter to true requires it).

–On DB2

Create database link DB1 connect to <username> identified by <password>
using ‘DB1’;

Now it should work, but I should mention that creating database link may not be preferable, because it is not secure (You should guess why, because if you do this you will be able to connect to DB1 with some user through database link…if it doesn’t matter for you then use itJ).

Solution  two:

1.  Create temporary table on the local database.

2.  Insert row into the temporary table.

3.  Insert the temporary row from the temporary table to the remote database.

4.  Delete the temporary row.

Note that this solution is slower than the first one. But it also solves the problem and is much more secure.

Error

ORA-01203 wrong incarnation of this file – wrong creation SCN

Cause

The SCN in datafile is not the same as the SCN in the control file.
This is probably a copy of a file that was dropped.

Action

Restore the current  copy of the datafile.

Error

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

Action

Check ORACLE_SID if it is correctly set.. For example, you may need to set this parameter with uppercase letters or lowercase letters…Try one of them

 

Error


RMAN-03009: failure of backup command on ORA_DISK_1 channel at …
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 104857600 limit

Cause

The flash recovery area is full.

Action

Run the following command to identify limit and used space:

   select name
          ,floor(space_limit / 1024 / 1024) "Limit(MB)"
          ,ceil(space_used  / 1024 / 1024) "Used(MB)"
   from	v$recovery_file_dest

You can change the limit:

 alter system set db_recovery_file_dest_size= scope=both;

You can continue.. backuping

 

Error


RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file string
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Cause

RMAN attempted to backup an archive log file, but couldn’t find it.

Action

Restore missing file or run the following:

change archivelog all crosscheck;

RMAN makes a check and if any archivelog file is missing will be marked as unavailable.