DB | DBA Knowledge Base

How to Create Database Link in sql server 2005

January 17, 2011 4 Comments

Database link can be done in oracle(click here to see how).But if we want to do it in SQL server 2005 we should use linked servers.

Permissions to connect linked sever:

Port: 1433 must be open.

Create linked server:

1. Click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. In the Connect to Server dialog box, specify the name of SQL Server, and click Connect.

3.In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

4. Click General, choose SQL Server option ,type the name of sql server in Linked server field

5. Click Security , Choose Be made using the security context, fill Remote login and With password fields and click OK

6. The syntax to query data from linked server is the following:

select *
from [server name].[database name].[owner name].[table name]

–In our case

select *
from [LINKED_SQLSERVER_NAME].[database name].[owner name].[table name]

Filed under DB Tagged with DB, sql server 2005

How to Create Database Link in Oracle

January 13, 2011 Leave a comment

— 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:)

Filed under DB Tagged with DB, oracle

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

January 12, 2011 Leave a comment

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

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:

–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:

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:

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

–Choose “Configure Database Options” and click Next>>

–Choose Database SID and click Next>>

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

–If you see this(OLAP is disabled),

–Then press the button “Standard Database Components…”

–Check “Oracle JVM” and click OK

–Now you should see this

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

Filed under DB Tagged with data pump, expdp/impdp, OLAP, oracle

SQL Access Advisor(Tuning a single sql)

January 10, 2011 Leave a comment

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;

Filed under DB Tagged with oracle, tuning

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

January 6, 2011 Leave a comment

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

Filed under DB Tagged with oracle

Sql server 2005 change collation for database

January 6, 2011 Leave a comment

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

Filed under DB Tagged with collation, sql server 2005

Oracle Errors(ORA-…)

January 5, 2011 1 Comment

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.

Filed under DB Tagged with oracle

Put Multiple Rows in a Single Row

December 31, 2010 Leave a comment

In my working experience selecting multiple rows into one row was in “high-demand” query. So I decided to write here these queries as in Oracle also in SQL Server 2005:

First of all let’s create table and fill it with desired rows.

————————————————————Oracle—————————————————————–

–Create table

CREATE TABLE testTable(
deptno number,
ename varchar2(50)
);

–Fill table with data

insert into testTable
values(1,'Mari');

insert into testTable
values(1,'Gio');

insert into testTable
values(1,'Anna');

insert into testTable
values(2,'Sopo');

insert into testTable
values(2,'Vaso');

insert into testTable
values(2,'Temo');

–Let see the data in testTable

select *
from testTable

–Output

–But our desired output is the following:

–To achieve this,we should write the following query:

SELECT deptno
       ,LTRIM(SYS_CONNECT_BY_PATH(ename,','),',') as list
FROM(SELECT deptno
            ,ename
            ,ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS seq
     FROM  testTable)
WHERE  connect_by_isleaf = 1
CONNECT BY seq = PRIOR seq +1 AND deptno = PRIOR deptno
START WITH seq = 1;

———————————————————–Sql Server 2005——————————————————-

–Create table

CREATE TABLE testTable(
deptno numeric(18) ,
ename varchar(50)
);

–Fill table with data

insert into testTable
values(1,'Mari');

insert into testTable
values(1,'Gio');

insert into testTable
values(1,'Anna');

insert into testTable
values(2,'Sopo');

insert into testTable
values(2,'Vaso');

insert into testTable
values(2,'Temo');

–Let see the data in testTable

select *
from testTable

–Output

–But our desired output is the following:

–To achieve this,we should write the following query:

SELECT DISTINCT deptno,
       STUFF(value_list, 1, 1, '') AS list
FROM testTable AS A
CROSS APPLY (
     SELECT ',' + ename
     FROM testTable AS B
     WHERE B.deptno =A.deptno
     FOR XML PATH('')
) AS t(value_list);

–Let’s discuss the following functions:

*STUFF-It will replace substring started by position 1 with length 1 by '',
       which means substring started by position 1 with length 1 will be removed.
Note:  We need to remove first character,because it is extra comma(output was ,Mari...)
* CROSS APPLY-this function will return corresponding rows from right table expression
              to left table expression.
* FOR XML PATH-returns a single string in xml format.

Filed under DB Tagged with oracle, sql server 2005, usefull queries

Interested Transaction Lists (ITL) Waits

December 23, 2010 Leave a comment

When I’ve generated AWR report, there I’ve noticed “Segments by ITL Waits” at this moment I‘ve not had any idea what was that. But after searching it on the internet I found very good information…Now, I want to share it with you.

First of all let’s discuss what ITLs is:

When a row is locked by a transaction, this information is placed in the block(header), where this row exists. The portion of the block header, which saves this information(transaction address and rowid) is called ITLs(Interested Transaction Lists).

ITLs contains several slots, initial number of slots are defined by the parameter INITRANS, which is indicated during the creation of the object (table, index) and can be increased till MAXTRANS.

Example:

CREATE TABLE test_tab
( COL1 NUMBER,
  COL2 VARCHAR2(50))
INITRANS 1
MAXTRANS 1

When one transaction tries to modify row, one slot will be filled by the info about this transaction(Assume that transaction is not finished, means commit or rollback is not performed). If another transaction will try to change the row within this block, this process will wait till the first transaction will not end.

Question is why?

Because for the second transaction there(in the block header) must be allocated another free slot, but free slot doesn’t exist and also slots number can’t increase because of MAXTRANS is 1 which equals to the number of already allocated slot. At this time increasing MAXTRANS will solve the problem, to change MAXTRANS do as follows:

–Maximum 10 slots can be allocated(10 transaction can be placed)

Alter table test_tab MAXTRANS 10;

What is ITL wait?

When slot can’t be increased and free slots still not available… Following transaction will wait until any slot will not be available, as we have had previously… This wait is called ITL waits. We solved this waits by increasing MAXTRANS but there may happen another problem. That ,for example we have initially 1 slot(INITRANS is 1) and can be allocated maximum 10 slots (MAXTRANS is 10), info about one transaction is placed in one slot, then comes another transaction and its info can’t be placed in the next slot(causes waiting) but as we know we can have 9 other available slots, still can’t be placed, why?

Let me draw some pictures so that you can easily guess what happens.

–Newly created block with initially one slot(during creation table we indicated INITRANS 1) looks like this:

–Three rows are inserted in the block.

–One transaction modified one row and did not commit. The Info is written to the available slot.

–Another transaction comes and wants to modify row, consequently info about this transaction should be placed in another free slot(do not forget we have initially one slot and number of this slots can be increased till 10)

Note:In this case there is not empty space for new slot, that is why it can’t be allocated… If we have had indicated initially more slots another transaction would be able to be placed, as you can guess to solve this problem you should indicate bigger INITRANS during creation of object, or alter object and change this parameter…For the blocks which are already full as I can guess it will not affect, but for future it will be usefull. However this also means that there is less space in the block for actual data.

Filed under DB Tagged with oracle, tuning

Apex Uninstall

December 21, 2010 Leave a comment

It is a very simple to uninstall apex.

1.Just go to the folder where apex exists(where you unzipped Oracle Application Express software).

2.Connect to the database as a SYS user:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password:SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL>CONNECT SYS as SYSDBA
Enter password: SYS_password

3. Execute the following command:

SQL> @apxremov.sql

Filed under DB Tagged with apex, oracle

← Older posts

Newer posts →

DBA Knowledge Base

How to Create Database Link in sql server 2005

How to Create Database Link in Oracle

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

SQL Access Advisor(Tuning a single sql)

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

Sql server 2005 change collation for database

Oracle Errors(ORA-…)

Put Multiple Rows in a Single Row

Interested Transaction Lists (ITL) Waits

First of all let’s discuss what ITLs is:

Example:

Question is why?

What is ITL wait?

Apex Uninstall

Database Administrator Jobs

Recent Posts

Archives

Categories

Meta

Subscribe to Blog via Email