ORA-12170

ORA-12170: TNS:Connect timeout occurred

1.Firewall is disable.
2.configure sqlnet.inbound_connect_timeout in SQLNET.ORA file
3.if you install ORACLE EXPRESS EDITION then configure TNSNAMES.ORA file for specified SERVICE_NAME.


C:\>tnsping fak

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-MAR-2
007 19:23:24

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

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
COL=TCP)(HOST=192.168.100.106)(PORT=1521)))
TNS-12535: TNS:operation timed out

C:\>tnsping fak

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-MAR-2
007 19:52:18

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

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.100.64)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = fak)))
OK (90 msec)


SQL> conn sys@fak as sysdba
Enter password:
ERROR:
ORA-12170: TNS:Connect timeout occurred


SQL> conn sys@fak as sysdba
Enter password:
Connected.
SQL>


Note: on my system i am install Oracle 10gr1 or Oracle XE. but service_name FAK i am just configure TNSNAMES.ORA file belong to Oracle 10gr1 instead of Oracle XE. WHEN i configure TNSNAMES.ORA file of Oracle XE and add service_name (FAK) PROBLEM SOLVE.

All Control File Missing Without Backup

Database 10.1.0.2.0
OS Windows
Archive log : Disable.



SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info


alert_db01.log file contents

Wed Mar 28 20:01:02 2007
ORA-00202: controlfile: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


My all control files deleted.


SQL> ED
Wrote file afiedt.buf

1 CREATE CONTROLFILE REUSE DATABASE "db01" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 7271
7 LOGFILE
8 GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO01.LOG' SIZE 10M,
9 GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO02.LOG' SIZE 10M,
10 GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\REDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\SYSTEM01.DBF' SIZE 600M,
14 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\UNDOTBS01.DBF' SIZE 65M,
15 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\SYSAUX01.DBF' SIZE 330M,
16 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\USERS01.DBF' SIZE 185600K,
17 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\db01\EXAMPLE01.DBF' SIZE 150M
18* CHARACTER SET WE8MSWIN1252
SQL> /

Control file created.


SQL> alter database open;

Database altered.



Note : You must use SIZE clause with DATAFILE size specification.

Control File Inconsistent

Database Version : 10.1.0.2.0
OS Windows
NoArchivelog Mode



SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00214: controlfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL02.CTL'
version 1302 inconsistent with file
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL' version 1292


SQL> shutdown abort
ORACLE instance shut down.
SQL> --FIRST DELETE "control01.ctl" and create new one from "control02.ctl"
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL>

Control File Corrupted.

Database 10gr1.
NoArchiveLog Mode
OS Windows



SQL> create tablespace test
2 datafile 'c:\test01.dbf' size 1m;
create tablespace test
*
ERROR at line 1:
ORA-00227: corrupt block detected in controlfile: (block 1, # blocks 1)
ORA-00202: controlfile: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB01\CONTROL01.CTL'


SQL> shutdown abort;
ORACLE instance shut down.
SQL> --First Delete control01.ctl file and create new copy through control02.ctl

SQL> host del c:\oracle\product\10.1.0\oradata\db01\control01.ctl
ystem32\cmd.exe /c clsSQL> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> exit

C:\oracle\product\10.1.0\Db_1\BIN>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 28 19:21:42 2007

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string C:\ORACLE\PRODUCT\10.1.0\ORADA
TA\DB01\CONTROL01.CTL, C:\ORAC
LE\PRODUCT\10.1.0\ORADATA\DB01
\CONTROL02.CTL, C:\ORACLE\PROD
UCT\10.1.0\ORADATA\DB01\CONTRO
L03.CTL

Status Pending

When we receive above error ... then first place to check tns_entry.
Are you able to connect "user/pwd@tns_entry".

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 27 10:46:05 2007

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

SQL> conn system@db01
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Now i am troubleshoot ORA-12154 error and check my OEM status again.
SQL> conn system/oracle@db01
Connected.

1.emoms.trc
2.emagent.trc
Location : $ORACLE_HOME/hostname.domainname_SID/sysman/log

You can also check "agent status"



Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 18.61%


If number of XML files pending upload if greater than 0 then you should wait to load all pending files then try to change OEM status.



Note : May be more reason but i am face all this error so i share my exprience.

Oracle Certified Professional

Oracle 10g --->Oracle Certified Professional

The Oracle Database 10g OCP is a credential for serious database professionals who have the skills to implement and manage complex Oracle Database 10g functions and the know-how to keep the database running at maximum efficiency. Acquiring an OCP credential is a real professional accomplishment that requires training and hands-on experience.

Paper Require : OCA + IZO-043 Oracle Database 10g Administration II
Exam Details :
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=44&lang=US&p_exam_id=1Z0_043

Hand On Course Requirement
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=99



Oracle 10g OCM

Compare Two Database Schemas

You can use:

1) Oracle Change Mnagement Pack (need separate license)
2) Erwin
3) TOAD
4) Write your own custom script based on data dictionary to compare schemas
5) Many other third party tools

Oracle Certified Associate

Oracle 10g --->Oracle Certified Associate
The Oracle Database 10g OCA is the first step in the Oracle Database 10g certification track and is the first step in building a successful career as an Oracle professional.

Paper Require : IZO-042 Oracle Database 10g Administration I
Exam Details :
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_org_id=44&lang=US&p_exam_id=1Z0_042


Oracle 10g OCP

Network Error (dns_unresolved_hostname)

Network Error (dns_unresolved_hostname)


Your requested host "taj.domainname.local" could not be resolved by DNS.


For assistance, contact Customer Support.

When we see above error during em webpage then first place to check "hosts" file exists in below place according OS.

Windows 95/98/Me c:\windows\hosts

Windows NT/2000/XP Pro c:\winnt\system32\drivers\etc\hosts

Windows XP Home c:\windows\system32\drivers\etc\hosts

(you may need administrator access for Windows NT/2000/XP)

if IP ADDRESS and COMPUTER/DOMAIN NAME is correct.
DISABLE PROXY SETTING.




Internet Explorer --->>> Tools --->>> Internet Options --->>> Connections ---> LAN SETTINGS ---> PROXY SERVER <<>>

Change Computer or Domain Name.

If you change computer or domain name then you have to first update below file on WINDOWS platforms

"c:\windows\system32\drivers\etc\HOSTS

# For example:
#
# 102.54.94.97 rhino.acme.com # source server
# 38.25.63.10 x.acme.com # x client host

127.0.0.1 localhost
192.168.100.255 taj.domainname

edit your hosts file according your chagned computer name or domain name and re-create oem repository.

Installation

Shift_Oracle_Server_From_One_Machine_to_Another_Machine

OLD OS WINDOWS 2003 SERVER
NEW OS WINDOWS XP
ORACLE SERVER 10.1.0.2.0
ARCHIVE LOG DISABLED


DATABASE NAME ORAMFE

STEP :
1.TAKE COLD BACKUP OF DATABASE
INCLUDING BELOW FILES
---->All *.dbf Files
---->All *.log Files (REDO LOGS)
---->All *.ctl Files
---->SPFILE.ora,INIT.ora

2.TRANSFER ALL FILES FROM OLD OS SERVER TO NEW OS.

3.ON NEW SERVER.
EDIT INIT.ora FILE BELOW PARAMETERS

1.Change Location of below PARAMETERS according NEW OS SERVER.
1.BDUMP
2.UDUMP
3.CDUMP
4.CONTROL FILES
5.FLASHBACK RECOVERY AREA



Create Oracle Services through ORADIM utility.

C:\>set oracle_sid=oramfe

C:\>orapwd file=c:\oracle\product\10.1.0\db_1\database\pwdoramfe.ora password=or
acle entries=5 force=y

C:\>oradim -new -sid ORAMFE -startmode manual -spfile
Instance created.

C:\>oradim -edit -sid ORAMFE -startmode auto -srvcstart system

C:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup mount pfile=c:\oracle\product\10.1.0\db_1\database\initORAMFE.ora
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 69990384 bytes
Database Buffers 96468992 bytes
Redo Buffers 524288 bytes
Database mounted.

6.Change Location of REDO LOGS and DATAFILES.
NOTE :- FIRST CHANGE DATAFILES LOCATION THEN CHANGE REDO
LOG LOCATION.
NOTE :- QUERY IN V$DATAFILE , V$LOGFILE FOR PREVIOUS LOCATION OF REDOLOG AND DATAFILES.

SQL>SELECT NAME FROM V$DATAFILE;;

DATAFILES
SQL>ALTER DATABASE
RENAME FILE
'E:\ORAMFE\USERS01.DBF',
'E:\ORAMFE\SYSTEM01.DBF',
'E:\ORAMFE\SYSAUX01.DBF'

TO
'C:\ORAMFE\USERS01.DBF',
'C:\ORAMFE\SYSTEM01.DBF',
'C:\ORAMFE\SYSAUX01.DBF'

;

SQL>SELECT MEMBER FROM V$LOGFILE;

REDOLOGS

ALTER DATABASE
RENAME FILE
'F:\ORAMFE\REDO01.LOG',

TO
'D:\ORAMFE\REDO01.LOG',

;

7.OPEN DATABASE.

SQL>ALTER DATABASE OPEN;

8.CREATE NEW TEMPORARY TABLESPACE AND DROP OLD TEMP TABLESPACES.


SQL>CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE 'C:\ORAMFE\TEMP02.DBF' SIZE 20M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL;


9.Make it Default Temporary Tablespace.

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

10.Drop OLD Temporary Tablespace.

SQL>DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

11.Physically Delete Tempfile From Location.

NOTE> MUST FIRST REALLOCATE DATAFILES BEFORE REDOLOG FILES.
OTHERWISE WE WILL GET BELOW ERRORS.

ORA-00341: log 1 of thread 1, wrong log # 3 in header
ORA-00312: online log 1 thread 1: 'C:\ORAMFE\REDO03.LOG'


If you get above error you should follow below step.


SQL>RECOVER DATABASE UNTIL CANCEL;
SQL>ALTER DATABASE OPEN RESETLOGS;


DATABASE IN NO ARCHIVELOG MODE SO NO PROBLEM WITH DATA LOST.

IF YOU TRYING TO REALLOCATE TEMPFILE THEN YOU WILL BE GET BELOW ERROR MESSAGE.

ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile
"C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORAMFE\TEMP01.DBF"


AFTER THAT YOU MUST CONFIGURE NETWORK CONFIGURATION THROUGH NETCA
AND ALSO YOU HAVE TO CONFIGURE OEM REPOSITORY

hope this helps
Taj

Physical Standby Database

Physical Standby Database.
Windows XP sp2
Oracle Version : 10.1.0.2.0 Enterprise Edition.

Primary Database : ORCL
Standby Database : ORCLSTDY


Preparing the Primary Database for Standby Database Creation
1.Enable Forced Logging

SQL> conn taj as sysdba
Enter password:
Connected.
SQL> --first check database in FORCE_LOGGING mode .
SQL> select FORCE_LOGGING from v$database;

FOR
---
NO

SQL> alter database FORCE LOGGING;

Database altered.

2.Create a Password File
Note: if PRIMARY DATABASE password file is not exists then create new one.
C:\> ORAPWD FILE=%ORACLE_HOME%/database/pwdORCL.ora password=ORACLE entries=5

C:\>dir c:\oracle\product\10.1.0\db_1\database\pwd*.ora
Volume in drive C has no label.
Volume Serial Number is 78B6-6F7D

Directory of c:\oracle\product\10.1.0\db_1\database

03-14-2007 09:42 AM 1,536 PWDorcl.ora
1 File(s) 1,536 bytes
0 Dir(s) 19,444,805,632 bytes free

3.Setting Primary Database Initialization Parameters
Database
ORCL
ORCLSTDY

DB_UNIQUE_NAME
ORCL
ORCLSTDY

Oracle Net Service Name
ORCL
ORCLSTDY

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> alter system set DB_UNIQUE_NAME=orcl scope=spfile;

System altered.

SQL> alter system set SERVICE_NAMES=orcl scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=c:\archive_orcl
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=orcl';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCLSTDY
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=orclstdy';

System altered.



SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=enable;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

System altered.

SQL> alter system set LOG_ARCHIVE_FORMAT='ARC%T_%R_%S.log' SCOPE=spfile;

System altered.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive SCOPE=spfile;

System altered.

Primary Database: Standby Role Initialization Parameters

SQL> alter system set FAL_SERVER=ORCLSTDY;

System altered.

SQL> alter system set FAL_CLIENT=ORCL;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\ORCL',
3 'c:\oracle\product\10.1.0\oradata\ORCLSTDY' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\ORCL',
3 'c:\oracle\product\10.1.0\oradata\ORCLSTDY' scope=spfile;

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

4.Enable Archiving
If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Note: If ARCHIVELOG enable then you just shutdown + startup oracle server for effect above parametes settings.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP

6.Creating a Physical Standby Database
Create a Backup Copy of the Primary Database Datafiles

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> host COPY -
> c:\oracle\product\10.1.0\oradata\ORCL\*.DBF
c:\oracle\product\10.1.0\oradata\ORCL\EXAMPLE01.DBF
0 file(s) copied.


SQL> host COPY -
> c:\oracle\product\10.1.0\oradata\ORCL\*.DBF -
> c:\oracle\product\10.1.0\oradata\ORCLSTDY
c:\oracle\product\10.1.0\oradata\ORCL\EXAMPLE01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\SYSAUX01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\SYSTEM01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\TEMP01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\UNDOTBS01.DBF
c:\oracle\product\10.1.0\oradata\ORCL\USERS01.DBF


Create a Control File for the Standby Database

SQL> startup mount
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> alter database CREATE STANDBY CONTROLFILE AS -
> 'c:\oracle\product\10.1.0\oradata\ORCLSTDY\control01.ctl';

Database altered.

SQL> alter database open;

Database altered.

Prepare an Initialization Parameter File for the Standby Database

SQL> create PFILE='c:\oracle\product\10.1.0\db_1\database\INITorclstdy.ora'
2 from SPFILE;

File created.

Modifying Initialization Parameters for a Physical Standby Database
Note: Just Modify BOLD parameters.

*.background_dump_dest='C:\oracle\product\10.1.0\admin\ORCLSTDY\bdump'

*.compatible='10.1.0.2.0'

*.control_files='C:\oracle\product\10.1.0\oradata\orclstdy\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.1.0\admin\orclstdy\cdump'

*.db_block_size=8192

*.db_cache_size=25165824

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='c:\oracle\product\10.1.0\oradata\ORCL','c:\oracle\product\10.1.0\oradata\ORCLSTDY'

*.db_name='orcl'

*.db_unique_name='ORCLSTDY'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.fal_client='ORCLSTDY'

*.fal_server='ORCL'

*.java_pool_size=50331648

*.job_queue_processes=10

*.large_pool_size=8388608

*.log_archive_dest_1='LOCATION=c:\archive_ORCLSTDY
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orclstdy'

*.log_archive_dest_2='SERVICE=ORCL
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'


*.log_archive_format='ARC%T_%R_%S.log'

*.log_file_name_convert='c:\oracle\product\10.1.0\oradata\ORCL','c:\oracle\product\10.1.0\oradata\ORCLSTDY'

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.service_names='ORCLSTDY'

*.shared_pool_size=83886080

*.sort_area_size=65536

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.1.0\admin\orclstdy\udump'

INSTANCE_NAME=ORCLSTDY

7.Set Up the Environment to Support the Standby Database

Step 1 Create a Windows-based service.

C:\>oradim -NEW -SID orclstdy -INTPWD oracle -STARTMODE manual
Instance created.

Enable broken connection detection on the standby system.
Add SQLNET.EXPIRE_TIME=2 in SQLNET.ORA FILE.

Create Oracle Net service names.
Add below lines in TNSNAMES.ORA file.

ORCLSTDY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.255)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclstdy)
)
)
Note: YOu can also configure through NET CONFIGURATION ASSISTANT (netca).

Create a server parameter file for the standby database.
C:\>set oracle_sid=ORCLSTDY

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 14 10:54:34 2007

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

Enter password:
Connected to an idle instance.

SQL> create SPFILE from PFILE;

File created.

Start the Physical Standby Database

SQL> STARTUP OPEN READ ONLY;

ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

To add temporary files to the physical standby database

SQL> select tablespace_name from dba_tablespaces
2 where contents = 'TEMPORARY';

TABLESPACE_NAME
------------------------------
TEMP

Note:
To create temporary files on the physical standby database that match the temporary files on the primary database, query the V$TEMPFILE view on the primary database to obtain complete information about the primary database temporary files.

SQL> conn taj@ORCL as sysdba
Enter password:
Connected.
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF

SQL> conn sys@ORCLSTDY as sysdba
Enter password:
Connected.

SQL> alter tablespace TEMP
2 add TEMPFILE 'c:\oracle\product\10.1.0\oradata\ORCLSTDY\temp01.dbf'
3 SIZE 100M REUSE;

Tablespace altered.

Start Redo Apply.

SQL> alter database RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

Test archival operations to the physical standby database
SQL> conn taj@ORCL as sysdba
Enter password:
Connected.
SQL> alter system switch logfile;

System altered.

Verify the Physical Standby Database Is Performing Properly
On the standby database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Test archival operations to the physical standby database.
SQL>conn taj@ORCL as sysdba
Password :

SQL> alter system switch logfile;

System altered.

Verify the Physical Standby Database Is Performing Properly
On the standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Force a log switch to archive the current online redo log file.
On the primary database

ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify the new redo data was archived on the standby database.
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Verify new archived redo log files were applied.
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

Hope this helps
Taj

Clone_database_NOARCHIVELOG_windows

Oracle Version : 10.1.0.2.0
OS Platform : Windows XP sp2

C:\>set ORACLE_SID=orcl

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Mar 8 11:36:07 2007

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

SQL> conn taj as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> HOST XCOPY c:\oracle\product\10.1.0\oradata\ORCL -
> \\192.168.100.255\tajsoft\Clone

C:\oracle\product\10.1.0\oradata\ORCL\CONTROL01.CTL
C:\oracle\product\10.1.0\oradata\ORCL\CONTROL02.CTL
C:\oracle\product\10.1.0\oradata\ORCL\CONTROL03.CTL
C:\oracle\product\10.1.0\oradata\ORCL\EXAMPLE01.DBF
C:\oracle\product\10.1.0\oradata\ORCL\REDO01.LOG
C:\oracle\product\10.1.0\oradata\ORCL\REDO02.LOG
C:\oracle\product\10.1.0\oradata\ORCL\REDO03.LOG
C:\oracle\product\10.1.0\oradata\ORCL\SYSAUX01.DBF
C:\oracle\product\10.1.0\oradata\ORCL\SYSTEM01.DBF
C:\oracle\product\10.1.0\oradata\ORCL\TEMP01.DBF
C:\oracle\product\10.1.0\oradata\ORCL\UNDO02.DBF
C:\oracle\product\10.1.0\oradata\ORCL\UNDOTBS02.DBF
C:\oracle\product\10.1.0\oradata\ORCL\USERS01.DBF
C:\oracle\product\10.1.0\oradata\ORCL\USERS02.DBF
14 File(s) copied


SQL> REM also COPY configuration files
SQL> HOST XCOPY c:\oracle\product\10.1.0\db_1\NETWORK\admin\*.ORA -
> \\192.168.100.255\tajsoft\Clone
C:\oracle\product\10.1.0\db_1\NETWORK\admin\listener.ora
C:\oracle\product\10.1.0\db_1\NETWORK\admin\sqlnet.ora
C:\oracle\product\10.1.0\db_1\NETWORK\admin\tnsnames.ora
3 File(s) copied

SQL> REM also COPY spfile.ora,pfile.ora,pwd.ora
SQL> HOST XCOPY c:\oracle\product\10.1.0\db_1\DATABASE\*.ORA -
> \\192.168.100.255\tajsoft\Clone
C:\oracle\product\10.1.0\db_1\DATABASE\INITorcl.ORA
C:\oracle\product\10.1.0\db_1\DATABASE\PWDorcl.ora
C:\oracle\product\10.1.0\db_1\DATABASE\SNCFORCL.ORA
C:\oracle\product\10.1.0\db_1\DATABASE\SPFILEORCL.ORA
4 File(s) copied

After Copy ALL neccessary file CLONE DESTINATION
On Clone destination
1.Install Oracle Software Without DATABASE.
2.Create all neccessary folders.

C:\>mkdir c:\oracle\product\10.1.0\admin\ORCL\bdump

C:\>mkdir c:\oracle\product\10.1.0\admin\ORCL\udump

C:\>mkdir c:\oracle\product\10.1.0\admin\ORCL\cdump

C:\>mkdir c:\oracle\product\10.1.0\admin\ORCL\pfile

C:\>mkdir c:\oracle\product\10.1.0\oradata\ORCL

Note: Must sure all files are exists in correct location ...

3.EDIT NETWORK CONFIGURATION FILES
1.LISTENER.ORA edit HOST string change IP ADDRESS according current machine)
2.TNSNAMES.ORA edit HOST string change IP ADDRESS according current machine)

4.Create Oracle Service
C:\>REM create oracle services through ORADIM

C:\>ORADIM -NEW -SID ORCL
Instance created.

C:\>ORADIM -EDIT -SID ORCL -STARTMODE AUTO

C:\>set ORACLE_SID=ORCL

C:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Thu Mar 8 13:08:09 2007

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788168 bytes
Variable Size 166459704 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl


Hope this helps
Taj

Logical_bkp_EXPORT_windows

Oracle Version : 10.1.0.2.0
OS system : Windows XP sp2

SQL> create or replace directory DATA as 'c:\data';

Directory created.

SQL> grant read,write on directory data to hr;

Grant succeeded.


SQL> spool c:\logical_bkp\orcl.sql
SQL> column today new_val dt
SQL> select to_char(sysdate,'ddmmyy') today from dual;

TODAY
------
070307

SQL> PROMPT host expdp system/oracle@orcl DIRECTORY=data DUMPFILE=orcl_&dt..dmp-

> LOGFILE=orcl_&dt..log SCHEMAS=hr
host expdp system/oracle@orcl DIRECTORY=data DUMPFILE=orcl_070307.dmp LOGFILE=or
cl_070307.log SCHEMAS=hr
SQL>PROMPT exit;
exit
SQL> spool off


SQL> host type c:\logical_bkp\orcl.bat
sqlplus -s system/oracle@orcl @c:\logical_bkp\orcl.sql

NOTE: Schedule ORCL.BAT file through SCHEDULE TASK utility of windows.

TNS-12545

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 07-MAR-2007 10:33
:20

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=alwarid-0a49914.fakhrudd
in.local)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
TNS-12560: TNS:protocol adapter error
TNS-00515: Connect failed because target host or object does not exist
32-bit Windows Error: 1004: Unknown error

Clause :
1.My computer name is taj not Oa49914.
C:\>hostname
taj

Suggestion :
1.Always use IP ADDRESS instead of COMPUTER NAME.
2.Change computer name Oa49914 to taj.

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 07-MAR-2007 10:38
:27

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.64)(PORT=152
1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date 07-MAR-2007 10:38:19
Uptime 0 days 0 hr. 0 min. 10 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.1.0\Db_1\network\admin\listener.o
ra
Listener Log File C:\oracle\product\10.1.0\Db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

C:\>

SQL> conn scott/tiger@orcl
Connected.

User_managed_HOT_BACKUP_linux

Oracle Version : 10.2.0.1.0
Linux Version : Ver 3 update 4

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 19:44:02 2007

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

SQL> conn sys/oracle as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
SQL> REM change archive log mode.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 83887744 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> REM archive log generate in FLASH_RECOVERY_AREA. >>i am configure FRA for database<<<
SQL> REM must sure DB_RECOVERY_FILE_DEST_SIZE have enough space.

SQL> alter system set db_recovery_file_dest_size = 5g;

System altered.

SQL> REM archive location is
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/oracle/product/10
.2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 5G

SQL> REM archive log format
SQL> show parameter log_archive_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> set heading off echo off verify off feedback off linesize 150 pagesize 0
SQL> spool /home/oracle/hotbackup.sql
SQL> select 'alter tablespace ' ||tablespace_name||' begin backup;'
2 from dba_data_files;
alter tablespace USERS begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace SYSTEM begin backup;
SQL> select 'host cp '||name||' /home/oracle/backup/hot '
2 from v$datafile;
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/system01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/undotbs01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/sysaux01.dbf /home/oracle/backup/hot
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/users01.dbf /home/oracle/backup/hot

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control01.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control01.ctl';

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control02.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control02.ctl';

SQL> select 'alter database backup controlfile to ''/home/oracle/backup/hot/control03.ctl'';'
2 from dual;

alter database backup controlfile to '/home/oracle/backup/hot/control03.ctl';



SQL> select 'alter tablespace '||tablespace_name||' end backup;'
2 from dba_data_files;
alter tablespace USERS end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace SYSTEM end backup;
SQL> spool off
SQL> set heading on echo on verify on feedback on
SQL> exit
[oracle@localhost ~]$ vi hotbkp.sh
[oracle@localhost ~]$ chmod 755 hotbkp.sh
[oracle@localhost ~]$ crontab -e
crontab: installing new crontab
[oracle@localhost ~]$ crontab -l
#Cold backup script every day 06:24 pm.log file generated in backup.log
24 19 * * * /home/oracle/coldbkp.sh > /home/oracle/backup.log

#Hot backup script every day 08:06 pm. log file generated in backup1.log
29 20 * * * /home/oracle/hotbkp.sh > /home/oracle/backup1.log


hope this helps
Taj

COLD_BACKUP_linux

Oracle Version : 10.2.0.1.0
RedHat Linux : Ver 3 update 4


shutdown.sql


[oracle@localhost ~]$ cat shutdown.sql
SQL> PROMPT startup force;
startup force
SQL> PROMPT shutdown normal;
shutdown normal

SQL>PROMPT exit;
exit
[oracle@localhost ~]$


coldbackup.sql

[oracle@localhost ~]$ cat coldbackup.sql
SQL> select ' host cp '||name||' /home/oracle/backup/' from v$datafile
2 union all
3 select ' host cp '||name||' /home/oracle/backup/' from v$tempfile
4 union all
5 select ' host cp '||member||' /home/oracle/backup/' from v$logfile
6 union all
7 select ' host cp ' ||name||' /home/oracle/backup/' from v$controlfile;
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/system01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/undotbs01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/sysaux01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/users01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/temp01.dbf /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo03.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo02.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/redo01.log /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control01.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control02.ctl /home/oracle/backup/
host cp /home/oracle/oracle/product/10.2.0/db_1/oradata/oracle/control03.ctl /home/oracle/backup/

SQL> PROMPT host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

host cp /home/oracle/oracle/product/10.2.0/db_1/dbs/spfileoracle.ora /home/oracle/backup

SQL> PROMPT exit;
exit
[oracle@localhost ~]$


startup.sql

[oracle@localhost ~]$ cat startup.sql
SQL> PROMPT startup
startup

SQL>PROMPT exit;
exit
[oracle@localhost ~]$



coldbkp.sh

[oracle@localhost ~]$ cat coldbkp.sh
#!/bin/bash

ORACLE_SID=oracle
ORACLE_HOME=/home/oracle/oracle/product/10.2.0/db_1
export ORACLE_SID
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s sys/oracle as sysdba @/home/oracle/shutdown.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/coldbackup.sql
sqlplus -s sys/oracle as sysdba @/home/oracle/startup.sql


[oracle@localhost ~]$


Automatic Schedule through CRONTAB utility

[oracle@localhost ~]$ crontab -l
24 19 * * * /home/oracle/coldbkp.sh > /home/oracle/backup.log


NOTE:
1.Change ORACLE_SID,ORACLE_HOME,PATH variable according your side.
2.Connect with ORACLE user NOT root.
3.Give 755 premission to COLDBKP.SH script >>>chmod 755 coldbkp.sh<<<
4.Check backup.log file for any errors. >>>/home/oracle/backup.log<<<
5.Must test your backup on TEST SERVER.

hope this helps
Taj

ORA-12516,ORA-12519

TNS-12516 TNS:listener could not find instance with matching protocol stack
TNS-12519 TNS:no appropriate service handler found


Clause : Lower value of PROCESSES parameter.
Suggestion : Increase value of PROCESSES parameter.


Check LISTENER.LOG file also.

ORA-12500

ORA-12500: TNS:listener failed to start a dedicated server process

For Above error there is couple of reason check below point.

1.Oracle Services is start.
Windows > c:\>net start OracleService
Unix > $ps -ef | grep ora_smon

2.Listener Services is start.

3.Take a look on LISTENER.LOG file {%ORACLE_HOME%/network/log}

4.if you received WINEXEC FORMAT ERROR 8

C:\>net helpmsg 8

Not enough storage is available to process this command.
Contact OS vendor for more information.

5.Sometimes it is happen due to PGA RAM shortage on database.
You have to set below parameters.
pga_aggregate_target, sort_area_size and hash_area_size.

6.TNS-00517: Lost contact
32-bit Windows Error: 54: Unknown error
If your received above error
C:\>net helpmsg 54

The network is busy.

7.TNS-00507: Connection closed
32-bit Windows Error: 109: Unknown error

C:\>net helpmsg 109

The pipe has been ended.

8.Recreate OracleService service through ORADIM utility

ORADIM -DELETE -SID
ORADIM -NEW -SID

HOPE THIS HELPS
Taj

Error-1053

C:\>set ORACLE_SID=db01

C:\>emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://alwarid-0a49914.fakhruddin.local:5504/em/console/aboutApplication
The OracleDBConsoledb01 service is not started.

More help is available by typing NET HELPMSG 3521.


C:\>net stop OracleServiceDB01
The service could not be controlled in its present state.

More help is available by typing NET HELPMSG 2189.


C:\>NET HELPMSG 2189

The service could not be controlled in its present state.


EXPLANATION

The service is not currently accepting requests. If
the service is starting, it cannot process requests until it is fully started.

ACTION

Try the operation again in a minute or two.

If this problem persists, the service may be stuck in a partially running state.
Contact technical support. Be prepared to give the name of the service and othe
r information about the system, such as the services and applications that were
running, and the type and amount of network activity on the computer at the time
of the problem.



C:\>NET HELPMSG 3521

The *** service is not started.



C:\>rem after few minutes

C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://alwarid-0a49914.fakhruddin.local:5504/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The OracleDBConsoledb
01 service is starting.........................................
The OracleDBConsoledb01 service was started successfully.


C:\>net start OracleServiceDB01
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.