World of Oracle DBA

Overview A powerful feature of RMAN is the ability to duplicate (clone), a database from a backup. It is possible to create a duplicate database on: A remote server with the same file structure A remote server with a different file structure The local server with a different file structure A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options. To prepare for database duplication, you must first create an auxiliary instance....

Problem Scenario You have cold backup of oracle database directory structure along with oracle software. Now your Windows OS got corrupted. So you reinstall your windows operating system and you try to restore the database. After installing software you set the ORACLE_SID and ORACLE_HOME environmental variable and whenever you create oracle service with oradim it fails with below message. C:\>oradim -new -sid orcl -intpwd orcl -startmode manual -pfile 'F:\oracle10g\pr oduct\10.2.0\db_1\database\initorcl.ora' Instance created. DIM-00019: create service error O/S-Error: (OS 2) The system cannot find the file specified. Solution of the Problem On linux environment you don't need these things. But in windows environment you need to do a lot of things more than restoring files. Whenever a windows OS gets corrupted you loose the Oracle Universal Installer repository and the regedit entries along with oracle home structure. So to solve the problem it is better to install new o...

Problem Description While creating a startup database using dbca the database creation GUI gives error message in a pop up window, ORA-00845: MEMORY_TARGET not supported on this system from where you can ignore the error message. The similar scenario also occur whenever you try to start your database then startup shows error message like below. SQL> STARTUP ORA-00845: MEMORY_TARGET not supported on this system Cause of the Problem •Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET. •On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system. •And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET. •The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process. •The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g. •And MEMORY_MAX_TARGET parameter in...

Problem Description You are trying to create or drop a database link to the same database name. This may be true if you have a database that was cloned from another database on a different machine and now you try to create or drop a database link with the name of the original database. Below is an example. SQL> create database link tiger; create database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier SQL> drop database link tiger; drop database link tiger * ERROR at line 1: ORA-02082: a loopback database link must have a connection qualifier Cause of the Problem This is an expected behavior if database global name match with the database link creation name. Now let's see the global_name of the database. SQL> select * from global_name; GLOBAL_NAME --------------------------------------- TIGER.REGRESS.RDBMS.DEV.US.ORACLE.COM We see the global name of the database is started with...

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, maestro is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec maestro.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But su...

Problem Description $expdp parfile=pfile_maxim_history_sel.par directory=d Export: Release 10.2.0.1.0 - 64bit Production on Monday, 05 January, 2009 17:23:52 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: system Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=pfile_maxim_history_sel.par directory=d Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 964 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPO...

It is sometimes a time consuming task if you like to create index with much number of rows. For example you are asked to created an index over 1 billion of data. It may take over 6 hours on your computer and you want to make it faster. With providing several options while creating index you can improve index creation speed dramatically. 1)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster. On a server that have 6 CPUs you may give parallel 5 as below. create index table_1_I on table_1(id,code) parallel 5; 2)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance. create index ...