Client IP Address From Server.

How to find Client IP address from Oracle Server?

We can use PLSQL package UTL_INADDR.

There is two procedure 1. GET_HOST_NAME or 2. GET_HOST_ADDRESS for find out local or remote HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)

from v$session

where type = 'USER' and username is not null

order by sid;

If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

Modified code

select sid, machine,

UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip

from v$session

where type='USER' and username is not null

order by sid;

How to Recover Dropped Tablespace

Oracle Ver: 10gr1/Win 2003
Backup Method: User Managed (hot Bkp)

SQL> conn sys/oracle as sysdbaConnected.

SQL> alter database begin backup;
Database altered.

SQL> ---copy all datafiles to bkp location

SQL> alter database end backup;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter database backup controlfile to 'd:\bkp\control01.ctl';
Database altered.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> ---oops by mistake drop production tablespace

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
select count(*) from a
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn sys/oracle as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ---delete all datafiles & controlfile.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 789100 bytes
Variable Size 94631316 bytes
Database Buffers 197132288 bytes
Redo Buffers 1048576 bytes

SQL> --restore controlfile from bkp

SQL> alter database mount;
Database altered.

SQL> --restore all datafiles from bkp

SQL> --now perform time based recovery.

SQL> --check ALERT.LOG file for exact time when tablespace was dropped.

SQL> --perform TIME BASED recovery just before dropped tablespace.

SQL> recover database using backup controlfile UNTIL TIME '2008-03-25 08:47:00';
ORA-00279: change 461791 generated at 03/25/2008 08:43:31 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001
ORA-00280: change 461791 for thread 1 is in sequence #2
Specify log: {=suggested filename AUTO CANCEL}
auto

ORA-00279: change 461827 generated at 03/25/2008 08:44:45 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001
ORA-00280: change 461827 for thread 1 is in sequence #3
ORA-00278: log file'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001' no longer needed for this recovery
ORA-00308: cannot open archived log'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001'
ORA-27041: unable to open fileOSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> --Open database with RESETLOGS

SQL> alter database open resetlogs;
Database altered.

SQL> --now check dropped tablespace is exist

SQL> select name from v$datafile where name like '%TEST%';
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
COUNT(*)
----------
10000

=======================================================

Oracle 10gr1/Win2003
Backup Method: RMAN

C:\>rman target sys
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1178009698)
RMAN> run
2> {
3> backup database plus archivelog;
4> backup current controlfile;
5> }
Starting backup at 25-MAR-08
current log archived using target database controlfile instead of recovery catalog

ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupsetincluding current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0CJC4UJK_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAR-08

SQL> conn sys/oracle as sysdba

Connected.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

SQL> ---oops dropped production tablespace

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

C:\>REM delete all controlfile and datafiles
C:\>rman target sys/oracle
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 789100 bytes

Variable Size 94631316 bytes

Database Buffers 197132288 bytes

Redo Buffers 1048576 bytes

NOTE: restore controlfile from backupset.

RMAN> restore controlfile from 'D:\oracle\product\10.1.0\Db_1\database\0CJC4UJK_1_1';
Starting restore at 25-MAR-08

allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL

Finished restore at 25-MAR-08

RMAN> alter database mount;
database mounted

released channel: ORA_DISK_1
RMAN> run

2> {

3> set UNTIL TIME "to_date('2008-03-25 09:19:44','YYYY-MM-DD HH24:MI:SS')";

4> restore database;

5> recover database;

6> }
executing command: SET until clause
Starting restore at 25-MAR-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF

restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF

restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 tag=TAG20080325T091205

channel ORA_DISK_1: restore complete

Finished restore at 25-MAR-08
Starting recover at 25-MAR-08using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG

archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG

archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG thread=1 sequence=2

archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG thread=1 sequence=3

media recovery complete

Finished recover at 25-MAR-08

RMAN> alter database open resetlogs;

database opened

NOTE:

1. I am using Controlfile Instead of Recovery catalog for RMAN repository

2. Don't use AUTOBACKUP controlfile option becuase we need backup controlfile for incomplete recovery not current controlfile.

Oracle 11g Password CaSe SEnsiTivE

New Capability
Now Oracle 11g Database password is Case Sensitive.

C:\Users\ian>sqlplus CHEST/norma@m

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 10 22:56:47 2008

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


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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\ian>sqlplus CHEST/NORMA@m

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 10 22:56:59 2008

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

ERROR:
ORA-01017: invalid username/password; logon denied

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217

And in oracle 10g

C:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Mar 23 10:12:46 2008

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


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

SQL> conn scott/TIGER
Connected.

How can disable password case sensitive option in oracle 11g

In 11g there is one password for enable / disable password case sensitive option.

sec_case_sensitive_logon

http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams211.htm#REFRN10299

ORA-01996 & ORA-01994

ORA-01996 & ORA-01994

This error occurred when password file “entries” value is reached to maximum size.

First understand what “ENTRIES” parameter is.
Entries parameter is use for specify numeric value multiple of four.
We can assign any value but it is always use multiple of four.
Like 4,8,12 so on

Password files use for SYSDBA password. And ENTRIES clause use for HOW many database users can connect with SYSDBA privilege or how many times we can grant SYSDBA privilege to any database user.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDorcl.ORA' is full

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=shared is set.

Solution:
1. Recreate PASSWORD FILE with MAXIMUM ENTRIES=N VALUE.
2. Restart Oracle Service After recreate PWD file.

Thank You for Visiting my Blog.
Keep Visiting :)

"SYS" is a magic user

“SYS” is a magic User?

Answer: Yes, but why? Why everyone recommended to “don’t use sys” user for database activities.

Because:

1. SYS (SYSDBA) is super and top most privileged user in Oracle Database.

2. We Can’t LOCK user SYS account.

Consider:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user scott account lock;

User altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user SYS account LOCK;

User altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

3. We Can’t set TRANSACTION READ ONLY for SYS user.

Consider:
SQL> conn scott/tiger
Connected.
SQL> create table X as select * from all_objects where rownum =1;

Table created.

SQL> set TRANSACTION READ ONLY;

Transaction set.

SQL> delete from X;
delete from X
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
transaction


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create table X as select * from all_objects where rownum =1;

Table created.

SQL> set TRANSACTION READ ONLY;

Transaction set.

SQL> delete from X;

1 row deleted.

SQL> commit;

Commit complete.

4. When we use SYS (SYSDBA) user.

1. When we can’t do database work with ANY OTHER user except SYS (SYSDBA)

1. Database Full Recovery (FULL, UNTIL CANCEL, UNTIL TIME, UNTIL SCN)
2. Change Database Character Set
3. Create Database
4. Drop Database

Note: For above work we must need SYSDBA (SYS) user.

When we use SYS (SYSOPER) user

1. Database Full Recovery (Not incomplete recovery)
2. Perform Database SHUTDOWN/STARTUP
3. Create SPFILE
4. Alter database OPEN/MOUNT/BACKUP
5. Includes the RESTRICTED SESSION privilege
6. Alter database ARCHIVELOG

Note: For above work we should use SYSOPER (SYS) user. And except above work if we want to perform normal database activity operation then Create Separate User and Grant DBA role and use that user.

Network DBA

Today I discuss some NETWORK related issues which we database administrator usually faced.

First understand which network files is important for oracle network connectivity.
1. LISTENER.ORA file
It is server side files which created only on ORACLE SERVER not CLIENT side. It content information regarding listener.

2. TNSNAMES.ORA file
It is Client side files which created in both sites SERVER/CLIENT.

3. SQLNET.ORA file
It is Client side files which created in both sites SERVER/CLIENT. It is use for user AUTHENTICATION purpose.

NOTE: Above all three files is located in $ORACLE_HOME/network/admin DIRECTORY.

Always remembers LISTENER.ORA, TNSNAMES.ORA file syntax must be correct otherwise you will get a lot of error because of this mistake.

Example of LISTENER.ORA file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomainname) (PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)
)

If you have any syntax error with listener.ora file then you always get error message.

Now in windows and linus you have to set LISTENER services.
ON Windows:
cmd>set ORACLE_HOME=oracle-home-path
cmd>set PATH=%ORACLE_HOME%/bin
cmd>set TNS_ADMIN=%ORACLE_HOME%/network/admin
cmd>lsnrctl
LSNRCTL>start

ON linus/solaris/unix:
$]export ORACLE_HOME=oracle-home-path
$]export PATH=$ORACLE_HOME/bin:$PATH
$]export TNS_ADMIN=$ORACLE_HOME/network/admin
$]./lsnrctl
$]LSNRCTL>start

Example of TNSNAMES.ORA file

TAJ = #it is “TNS_ENTRY”
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.100.13) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)


NOTE: As you see there is two names “TAJ” it’s called “TNS_ENTRY” which we use with connect string like “username/pwd@TNS_ENTRY”” & “ORCL” is service_name (instance_name)


1. ORA-12154 : TNS:could not resolve the connect identifier specified
This error we received when we use “wrong” TNS_ENTRY” or “TNS_ENTRY” is not exist in TNSNAMES.ORA file
Just take above tnsnames.ora file there “tns_entry” is “TAJ” and service_name is “ORCL” now see…

SQL> conn scott/tiger@taj
Connected.
User is connected because TAJ is exist in tnsnames.ora file.
SQL> conn scott/tiger@orcl
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
User is not connected because ORCL is not a tns_entry.

2. ORA-12170 : TNS:Connect timeout occurred
This error is very generic and difficult to figure out. But most common reason for above error is “Firewall is enable between CLIENT/SERVER” so first disable firewall setting and try.
Also check “use_shared_socket=TRUE” in registry, this must be set to TRUE.

3. ORA-12203 : TNS:unable to connect to destination
This error is occurring when client machine is not able to connect with server machine. It is just because of NETWORK connectivity.
So first check you can able to ping server from client.
cmd>ping SERVER_IP_ADDRESS

4. ORA-12500 : TNS:listener failed to start a dedicated server process
This error is occurring because of SGA memory is not sufficient to handle client request or “PROCESSES” parameter is set to low value. So increase SGA size or increase PROCESSES parameter (we need to restart ORACLE SERVER because PROCESSES is static parameter)
http://dbataj.blogspot.com/2007/03/ora-12500.html
5. ORA-12502 : TNS:listener received no CONNECT_DATA from client
This error is occurring because of wrong configuration of TNSNAMES.ORA file just RECREATE tnsnames.ora file through NETCA tools.

6. ORA-12505 : TNS:listener could not resolve SID given in connect descriptor
http://dbataj.blogspot.com/2007/02/ora-12505.html
7. ORA-12508 : TNS:listener could not resolve the COMMAND given
This error occurring when multiple oracle home install on same machine and multiple listeners are running must set all enviourment variable correctly to point recent version oracle home. And recreate listener.ora file through NETCA tools.

8. ORA-12514 : TNS:listener does not currently know of service requested in connect descriptor
This error is very generic, but most common reason it when database is not OPEN stage.
SQL>select status from v$instance;
http://dbataj.blogspot.com/2007/02/ora-12514.html

9. ORA-12515 : TNS:listener could not find a handler for this presentation
*Cause: None of the listener's known service handlers are registered
as supporting the presentation protocol required by the connecting client.
*Action: Check that the destination service is configured to accept the
presentation protocol.

10. ORA-12516 : TNS:listener could not find available handler with matching protocol stack
This error sometime happen due to user connection reach processes parameter limit so just increase processes parameter and try.

11. ORA-12518 : TNS:listener could not hand off client connection
This error occurring because of memory so just increase oracle sga memory and try.

12. ORA-12519 : TNS:no appropriate service handler found
This error occurring when listener not register database services so try to register database manually to listener.

13. ORA-12520 : TNS:listener could not find available handler for requested type of server
Common reason for this error in user connection reaches “PROCESSES” parameter value. So increase processes parameter and try.
http://dbataj.blogspot.com/2007/01/ora-12520.html
14. ORA-12533 : TNS:illegal ADDRESS parameters
This error occurring because of TNSNAMES.ORA file content SYNTAX error so best solution is just recreate TNSNAMES.ORA file through NETCA tools.

15. ORA-12535 : TNS:operation timed out
http://www.dbmotive.com/oracle_error_codes.php?errcode=12535
16. ORA-12538 : TNS:no such protocol adapter
This error occurring because of TNSNAMES.ORA file content “(PROTOCAL= )” value is blank so just recreate TNSNAMES.ORA file and try.

17. ORA-12541 : TNS:no listener
This error occurring when client machine didn’t find “LISTENER” on server, possible reason 1. A listener service is not started so first start and tries.

2. We have two oracle home of different version so TNS_ADMIN parameter didn’t point right network directory.
Suppose: we have install oracle 9i or oracle 10g on same machine and TNS_ADMIN variable set to point oracle 9i home. And we trying to connect from client using oracle 10g client to oracle 10g server and we received “above” error means client not able to find 10g listener because TNS_ADMIN point to oracle 9i.

So set TNS_ADMIN variable to point oracle 10g directory and try.

18. ORA-12542 : TNS:address already in use
Check Metalink Note:1017513.102

19. ORA-12545 : Connect failed because target host or object does not exist
Common reason when we use “HOSTNAME” instead of “IP ADDRESS” in tnsnames.ora so just change HOSTNAME with IP ADDRESS.

20. ORA-12546 : TNS:permission denied
This error when user is not belongs to ORA_DBA group then just add OS user to ORA_DBA group and try.

21. ORA-12547 : TNS:lost contact
Check Metalink Note:102893.1

22. ORA-12560 : TNS:protocol adapter error
Common error when oracle database service is not started.
cmd>set ORACLE_SID=sidname
cmd>net start OracleService and try

23. ORA-12564 : TNS:connection refused
Cause: The connect request was denied by the remote user (or TNS software).
Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation.


24. ORA-12571 : TNS:packet writer failure
Cause: An error occurred during a data send.
Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation. If error persists, contact Oracle Customer Support.

25. ORA-12638 : Credential retrieval failed
http://dbataj.blogspot.com/2007/01/ora-12638-credential-retrieval-failed.html
26. ORA-28545 : error diagnosed by Net8 when connecting to an agent
Cause: An attempt to call an external procedure or to issue SQL to a
non-Oracle system on a Heterogeneous Services database link failed at connection initialization. The error diagnosed by Net8 NCR software is reported separately.
Action: Refer to the Net8 NCRO error message. If this isn't clear, check
connection administrative setup in tnsnames.ora and listener.ora
for the service associated with the Heterogeneous Services
database link being used, or with 'extproc_connection_data' for an external procedure call.

27. ORA-28546 : connection initialization failed, probable Net8 admin error
Cause: A failure occurred during initialization of a network connection
from the Oracle server to a second process: The connection was
completed but a disconnect occurred while trying to perform
protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 adminitrative setup for
database links or external procedure calls. The most frequent
specific causes are: -- Database link setup for an
Oracle-to-Oracle connection instead connects to a Heterogeneous
Services agent or an external procedure agent. -- Database link
setup for a Heterogeneous Services connection instead connects
directly to an Oracle server. -- The extproc_connection_data
definition in tnsnames.ora connects to an Oracle instance
instead of an external procedure agent. -- Connect data for a
Heterogeneous Services database link, usually defined in
tnsnames.ora, does not specify (HS=). -- Connect data for an
Oracle-to-Oracle database link, usually defined in tnsnames.ora,
specifies (HS=).
Action: Check Net8 administration in the following ways: -- When using
TNSNAMES.ORA or an Oracle Names server, make sure that the
connection from the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host
machine to assure that this service name or SID connects to the
correct program. -- Confirm in TNSNAMES.ORA or the equivalent
service definition that sevice 'extproc_connection_data' does NOT contain (HS=), or that the service definition used by a Heterogeneous Services database link DOES contain (HS=).

28. ORA-28547 : connection to server failed, probable Oracle Net admin error
Cause: A failure occurred during initialization of a network connection
from a client process to the Oracle server: The connection was
completed but a disconnect occurred while trying to perform
protocol-specific initialization, usually due to use of different network protocols by opposite sides of the connection. This usually is caused by incorrect Net8 administrative setup for database links or external procedure calls. The most frequent specific causes are: -- The connection uses a connect string which refers to a Heterogeneous Services agent instead of an Oracle server. -- The connection uses a connect string which includes an (HS=) specification.
Action: Check Net8 administration in the following ways: -- When using
TNSNAMES.ORA or an Oracle Names server, make sure that the client connection to the ORACLE server uses the correct service name or SID. -- Check LISTENER.ORA on the connection end point's host machine to assure that this service name or SID refers to the correct server. -- Confirm in TNSNAMES.ORA or the equivalent
service definition that the connect string does NOT contain (HS=).

29. ORA-29260 : network error: %s
http://www.dbmotive.com/oracle_error_codes.php?errcode=29260
NOTE: Above are some more famous error related to NETWORK.

TNS_ADMIN enviourment variable
It is very important parameter for network files.
Also set this parameter to correct network files directory where LISTENER.ORA, TNSNAMES.ORA, SQLNET.ORA file exists.

Backup DBA

What is most important work for DBA’s ?
It is Database BACKUP.

There are simple three methods which you can use to take database backup.
1. COLD backup / Consistent backup / Offline backup
2. HOT backup / Inconsistent backup / Online backup / User Managed backup
3. RMAN / Inconsistent backup / Online backup / Server Managed backup

How to take COLD backup? ( Database running in NO ARCHIVELOG MODE)
1. conn with sysdba user.
SQL>shutdown abort;
SQL>startup restrict;
SQL>shutdown normal;
2. take copy of all datafiles,controlfile,redologfiles & parameter files through OS copy command.
3. startup database
SQL>startup

First use "SHUTDOWN IMMEDIATE" command to shutdown database server without "SHUTDOWN ABORT" but if "SHUTDOWN IMMEDIATE" command is HANG then use "SHUTDOWN ABORT" + "STARTUP RESTRICT" + "SHUTDOWN NORMAL"



How to take HOT backup?
1. conn with sysdba user.
SQL> alter database begin backup;
2. copy all datafiles except “tempfiles” through OS COPY command.
SQL> alter database end backup;
SQL> alter system switch logfile;
SQL> alter database backup controlfile to ‘OS_PATH’;
SQL> create pfile=’OS_PATH\INIT.ORA’ from spfile;

How to take RMAN backup?
1. cmd> set ORACLE_SID=
2. conn with RMAN prompt
cmd>rman target /
RMAN> run
{
Backup database;
Backup archivelog all;
}


NOTE: above are just simple commands which you follow to take database backup.

Except above you can use EXPORT/IMPORT/DATAPUMP to take logical backup of DATABASE TABLES, SCHEMAS, DATABASE.

At database level
cmd>exp username/pwd@tns_entry file=OS-PATH FULL=y

At schema level
cmd>exp username/pwd@tns_entry file=OS-PATH OWNER=username(scott)

At table level
cmd>exp username/pwd@tns_entry file=OS-PATH TABLES=tablename(emp)