SQL> set ORACLE_SID=ORCL SQL> startup nomount ORACLE instance started. Total System Global Area 263639040 bytes Fixed Size 1332552 bytes Variable Size 222300856 bytes Database Buffers 33554432 bytes Redo Buffers 6451200 bytes C:\>lsnrctl services LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production Service "ORCL" has 2 instance(s). Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:59 LOCAL SERVER Instance "ORCL", status BLOCKED, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "ORCL_XPT" has 1 instance(s). Instance "ORCL", status BLOCKED, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Want Solution Do the following : you can add the new TNS connect string (UR = A) to the tnsnames entry. ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) (UR = A) ) )
Month: October 2012
FRM-92102 : A network error has occured
FRM-92102 is Gerneric Error maybe occur for more than one reasons :
1-Network
2-Proxy
3-http
4-Session Time
But today i will discuss the problem On oracle Application server 10g .
Description for the problem like the following when you try to connect on your deploy application On OAS 10g it’s gives the above error from 1-5 minutes.
I will give you more than one solution maybe it will be related to the above problem and you try them separately to see which one will be valid for you :
You will find it $ORACLE_HOME/forms/server/default.env
Just increase the value .
Sqlnet.Inbound_connection_timeout = 0
Note : if your can’t find this parameter in the SQLNET.ORA you cant add it .
Hope this will work
Prevent developers from using TOAD,other tools on production databases
When I was Browsing On internet Today i saw Amazing Article Talking about how to prevent Developers From using Toad and other tools on production Database its Small script (After Logon Trigger ) On database level :
CONNECT / AS SYSDBA; CREATE OR REPLACE TRIGGER block_tools_from_prod AFTER LOGON ON DATABASE DECLARE v_prog sys.v_$session.program%TYPE; BEGIN SELECT program INTO v_prog FROM sys.v_$session WHERE audsid = USERENV('SESSIONID') AND audsid != 0 -- Don't Check SYS Connections AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in THEN RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.'); END IF; END; / SHOW ERRORS
Where I saw it : http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm
Oracle Real Application Cluster Lesson # 1
Sometimes we need Solutions to keep our database Available all the time, There are lot of solutions one of these solutions called Oracle Real Application Cluster (RAC)/High Availability
As Lesson Number One i will take on Oracle Real Application Cluster Basics .
Lets Start :
Hardware
At the hardware level, each node in a RAC cluster shares three things:
- Access to shared disk storage
- Connection to a private network
- Access to a public network.
Shared Disk Storage
Oracle RAC relies on a shared disk architecture. The database files, online redo logs, and control files for the database must be accessible to each node in the cluster. The shared disks also store the Oracle Cluster Registry and Voting Disk (discussed later). There are a variety of ways to configure shared storage including direct attached disks (typically SCSI over copper or fiber), Storage Area Networks (SAN), and Network Attached Storage (NAS).
Supported Shared Storage In RAC :
1-Oracle Cluster File System (OCFS) is a shared file system designed specifically for Oracle Real Application Cluster OCFS eliminates the requirement that Oracle database files be linked to logical drives and enables all nodes to share a single Oracle Home
2-ASM
3-RAW Device
Private Network
Each cluster node is connected to all other nodes via a private high-speed network, also known as the cluster interconnect or high-speed interconnect (HSI). This network is used by Oracle’s Cache Fusion technology to effectively combine the physical memory (RAM) in each host into a single cache. Oracle Cache Fusion allows data stored in the cache of one Oracle instance to be accessed by any other instance by transferring it across the private network. It also preserves data integrity and cache coherency by transmitting locking and other synchronization information across cluster nodes.
The private network is typically built with Gigabit Ethernet, but for high-volume environments, many vendors offer proprietary low-latency, high-bandwidth solutions specifically designed for Oracle RAC. Linux also offers a means of bonding multiple physical NICs into a single virtual NIC (not covered here) to provide increased bandwidth and availability.
Public Network
To maintain high availability, each cluster node is assigned a virtual IP address (VIP). In the event of node failure, the failed node’s IP address can be reassigned to a surviving node to allow applications to continue accessing the database through the same IP address.
Configuring the Cluster Hardware
There are many different ways to configure the hardware for an Oracle RAC cluster. Our configuration here uses two servers with two CPUs, 1GB RAM, two Gigabit Ethernet NICs, a dual channel SCSI host bus adapter (HBA), and eight SCSI disks connected via copper to each host (four disks per channel). The disks were configured as Just a Bunch Of Disks (JBOD)—that is, with no hardware RAID controller.
Software
At the software level, each node in a RAC cluster needs:
- An operating system
- Oracle Clusterware
- Oracle RAC software
- An Oracle Automatic Storage Management (ASM) instance (optional).
Operating System
Oracle RAC is supported on many different operating systems. This guide focuses on Linux. The operating system must be properly configured for the OS–including installing the necessary software packages, setting kernel parameters, configuring the network, establishing an account with the proper security, configuring disk devices, and creating directory structures. All these tasks are described in this guide.
Oracle Cluster Ready Services becomes Oracle Clusterware
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.
Clusterware maintains two files: the Oracle Cluster Registry (OCR) and the Voting Disk. The OCR and the Voting Disk must reside on shared disks as either raw partitions or files in a cluster filesystem. This guide describes creating the OCR and Voting Disks using a cluster filesystem (OCFS2) and walks through the CRS installation.
Oracle RAC Software
Oracle RAC 10g Release 2 software is the heart of the RAC database and must be installed on each cluster node. Fortunately, the Oracle Universal Installer (OUI) does most of the work of installing the RAC software on each node. You only have to install RAC on one node—OUI does the rest.
Oracle Automatic Storage Management (ASM) / Or other shared Storage .
ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove “hot spots.” It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.
1-Crossover cables are not supported (use a high-speed switch).
2-Use at least a gigabit Ethernet for optimal performance.
3-Increase the UDP buffer sizes to the OS maximum.
4-Turn on UDP checksumming.
5-Oracle Support strongly recommends the use of UDP (TCP for WIndows )
6-SSH Connectivity .
How To Check Oracle Physical Standby is in Sync with the Primary or Not?
On Primary
set pages 1000
set lines 120
column DEST_NAME format a20
column DESTINATION format a35
column ARCHIVER format a10
column TARGET format a15
column status format a10
column error format a15
select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest
where DESTINATION is NOT NULL
At Physical Standby
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP
select process,status,sequence# from v$managed_standby
Compare the output of the query its should be equal .
ORA-16139: media recovery required
During switch physical standby database to primary database i received following error message on (physical standby database)
ٍٍSQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*ERROR at line 1:ORA-16139: media recovery required
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
——————–
SWITCHOVER LATENT
SQL> RECOVER MANAGED STANDBY DATABASE FINISH;
Media recovery complete.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Metalink Note:[ID 219554.1]
Dealing With Database Gaurd
Database Switchover
Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:
SQL> alter database commit to switchover to standby;
SQL> shutdown immediateSQL> startup nomountSQL> alter database mount standby database;SQL> alter database recover managed standby database disconnect;
SQL> alter database recover managed standby database cancel;SQL> alter database commit to switchover to primary;SQL> shutdown immediateSQL> startup
Activating a Standby Database
If the primary database is not available the standby database can be converted into the primary database as follows:
SQL> alter database recover managed standby database cancel;SQL> alter database activate standby database;SQL> shutdown immediateSQL> startup
Opening the Standby Database in Read Only Mode
SQL> alter database recover managed standby database cancel;SQL> alter database open read only;
SQL> shutdown immediateSQL> startup nomountSQL> alter database mount standby database;SQL> alter database recover managed standby database disconnect;
How to check whether the Standby Database is in Sync
On the primary server:
SQL> SELECT max(sequence#) AS “PRIMARY” FROM v$log_history;
On the standby server:
SQL> SELECT max(sequence#) AS “STANDBY”, applied
FROM v$archived_log GROUP BY applied;
The standby database is in sync with the primary database if the above PRIMARY value matches the above STANDBY value where applied = ‘YES’.
ORA-16018 ORA-16019
Cause :
These two errors come whenever LOG_ARCHIVE_DEST is set as archival location and you want to set DB_RECOVERY_FILE_DEST
Solution :
1- You need to see Archive log destination First .
SQL > archive log list ;Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
2-You Can Check Archive Location By Check Database Parameter
SQL> show parameter DB_RECOVERY_FILE_DESTNAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /oradata
db_recovery_file_dest_size big integer 10G
3-Set New Archive Log Location
SQL> alter system set log_archive_dest=’/u01′;alter system set log_archive_dest=’/u01′
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
4- If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .
SQL> alter system set DB_RECOVERY_FILE_DEST=”;System altered.
SQL> alter system set log_archive_dest=’/u01′;System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
5-Also if you now want to set DB_RECOVERY_FILE_DEST ORA-16019: will occur.
SQL> alter system set DB_RECOVERY_FILE_DEST=’/u02′;alter system set DB_RECOVERY_FILE_DEST=’/u02′
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST
6-To set DB_RECOVERY_FILE_DEST first reset LOG_ARCHIVE_DEST.
SQL> alter system set log_archive_dest=”;System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST=’/u02′;System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217To set multiple location of archival destination set another log_archive_dest_n parameter like,
SQL> alter system set log_archive_dest_3=’LOCATION=/u02′;
System altered.
Changing Archive Log Destination
Today I am Gonna Show you How to change Archive log Destination in Two Ways :
1.Temporarily Changing the Destination Using SQL*Plus
sqlplus / as sysdba
sql> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 9285
Next log sequence to archive 9287
Current log sequence 9287
To change the location
sql>ARCHIVE LOG START ‘/u01/arch’;To Verify your changes:
sql> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /oracle/archOldest online log sequence 9285Next log sequence to archive 9287Current log sequence 9287
2-Permanently Changing the Destination Using SQL*Plus
sqlplus / as sysdba
ALTER SYSTEM SET log_archive_dest =’/oradata/arch’ scope=both;
To Verify your changes:
sql> archive log list;
Database log mode Archive ModeAutomatic archival EnabledArchive destination /oracle/archOldest online log sequence 9285Next log sequence to archive 9287Current log sequence 9287
To change the size of archive log
SQL> alter system SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID=’orcl’;
System altered.
IMP-00003 With ORA-03113/ORA-03114
Error :
“IMP-00003: ORACLE error 3113 encountered, ORA-03113: end-of-file on communication channel”
OR
“IMP-00003: ORACLE error 3114 encountered, ORA-03114: not connected to ORACLE”
Cause :
database corruption or deletion of any critical files from the Oracle database which further makes the data stored in the database inaccessible , this error can appear on any platform .
Solution :
- Login as “sys” in SQLPLUS and run the below Sqls.
- $OH/rdbms/admin/prvtread.plb.
- $OH/rdbms/admin/dbmsread.sql.
Retry Your Import .