APPSINFODB – Information Technology Group

Finding Solutions for DBA's to smile…….!!!!!!! DBA its just not Administration, its core of customer’s data integrity

  • Blog Stats

    • 36,980 hits
  • Visitors

    free counters
  • Vistors Access

  • Calendar

    September 2017
    M T W T F S S
    « Dec    
     123
    45678910
    11121314151617
    18192021222324
    252627282930  
  • Archives

  • Authors

Archive for the ‘Oracle 10g Database’ Category

Hot Database Refresh

Posted by appsinfo on July 27, 2009

Hot Database Refresh

This is with reference to the COLD Database Refresh. Here we will see how to refresh the test database from production instance which is online 24/7. (Basically Hot Database Refresh).

You have make sure that all the archive logs that are generated must be applied to the target instance upto the point you like to refresh the database.

1. Run Hot Backup script
alter database begin backup;
host copy E:\oracle\testdata\*  F:\BACKUP\data\
alter database end backup;

2. Copy all the dbfs  and Archive logs generated from the production instance to the target instance

3. Connect in nomount phase
   sqlplus “/as sysdba”
   startup nomount

4. Run the ctlfile.sql   @E:\ctlfile.sql
   Control file created

5. Recover the database using the control file.
SQL>recover database using backup controlfile until cancel
– apply the suggested archivelog file until you wish to recover the data, then specify cancel, which will return media recovery cancelled

6. Once you receive media recovery cancelled. Open the database using:
SQL>alter database open resetlogs.

7. Reuse the temp file
SQL> alter tablespace TEMP add tempfile ‘E:\oracle\uatdata\temp01.dbf’ size 520m reuse autoextend off;
Tablespace altered.

Note: Follow the other steps mentioned in COLD Database refresh.

Advertisements

Posted in Oracle 10g Database | Leave a Comment »

Oracle Database 10g DB Refresh

Posted by appsinfo on May 9, 2009

Database Refresh

Often there will be requirement for Database refresh from production instance for testing of specific modules, upgrades, patches, or data migration.

Database referesh from production to UAT. (COLD)

1. Prepare Control file at production instance using

alter database backup controlfile to trace as ‘F:\ctlfile.sql’;

2. Edit the controle ctlfile.sql and delete all the lines except the structure mentioned below and copy this file to the target instance.

When you are using the control file from the production instance you need to use ‘REUSE SET DATABASE <new sid>’

CREATE CONTROLFILE reuse set DATABASE “UAT” RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 7260
LOGFILE
  GROUP 1 (
    ‘E:\oracle\uatdata\LOG01A.DBF’,
    ‘E:\oracle\uatdata\LOG01B.DBF’
  ) SIZE 10M,
  GROUP 2 (
    ‘E:\oracle\uatdata\LOG02A.DBF’,
    ‘E:\oracle\uatdata\LOG02B.DBF’
  ) SIZE 10M
DATAFILE
  ‘E:\oracle\uatdata\SYSTEM01.DBF’,
  ‘E:\oracle\uatdata\SYSTEM02.DBF’,
  ‘E:\oracle\uatdata\SYSTEM03.DBF’,
  ‘E:\oracle\uatdata\SYSTEM04.DBF’,
  ‘E:\oracle\uatdata\SYSTEM05.DBF’,
  ‘E:\oracle\uatdata\SYSTEM06.DBF’,
  ‘E:\oracle\uatdata\SYSTEM07.DBF’,
  ‘E:\oracle\uatdata\SYSTEM08.DBF’,
  ‘E:\oracle\uatdata\SYSTEM09.DBF’,
  ‘E:\oracle\uatdata\SYSTEM10.DBF’,
  ‘E:\oracle\uatdata\SYSTEM11.DBF’,
  ‘E:\oracle\uatdata\UNDO01.DBF’,
  ‘E:\oracle\uatdata\A_ARCHIVE01.DBF’,
  ‘E:\oracle\uatdata\A_INT01.DBF’,
  ‘E:\oracle\uatdata\A_MEDIA01.DBF’,
  ‘E:\oracle\uatdata\A_NOLOG01.DBF’,
  ‘E:\oracle\uatdata\A_QUEUE01.DBF’,
  ‘E:\oracle\uatdata\A_QUEUE02.DBF’,
  ‘E:\oracle\uatdata\A_REF01.DBF’,
  ‘E:\oracle\uatdata\A_REF02.DBF’,
  ‘E:\oracle\uatdata\A_SUMM01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA02.DBF’,
  ‘E:\oracle\uatdata\A_TXN_DATA03.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND01.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND02.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND03.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND04.DBF’,
  ‘E:\oracle\uatdata\A_TXN_IND05.DBF’,
  ‘E:\oracle\uatdata\CTXD01.DBF’,
  ‘E:\oracle\uatdata\ODM.DBF’,
  ‘E:\oracle\uatdata\OLAP.DBF’,
  ‘E:\oracle\uatdata\OWAD01.DBF’,
  ‘E:\oracle\uatdata\PORTAL01.DBF’,
  ‘E:\oracle\uatdata\SYSAUX01.DBF’,
  ‘E:\oracle\uatdata\USER_DATA.ORA’,
  ‘E:\oracle\uatdata\USER_IDX.ORA’,
  ‘E:\oracle\uatdata\APPS_TS_TOOLS.ORA’,
  ‘E:\oracle\uatdata\CUSTOM01.DBF’
CHARACTER SET AR8MSWIN1256
;

3. Delete the datafiles from the UAT instance.

4. Copy all the dbfs from the production instance.

5. Connect in nomount phase

     sqlplus “/as sysdba”

     startup nomount

6. Run the ctlfile.sql   @E:\ctlfile.sql

7. reuse the temp file

 SQL> alter tablespace TEMP add tempfile ‘E:\oracle\uatdata\temp01.dbf’ size 520 m reuse autoextend off;

 Tablespace altered.

Posted in Oracle 10g Database | Leave a Comment »

Invalid Objects

Posted by appsinfo on March 11, 2009

When Invalid Objects are created

 

  1. Applying patches can create Invalid objects. 
  2. It has been experience that while applying some patches the patch fails this is due to invalid objects, complying invalid objects and reapplying will resolve the issue. 
  3. Technical consultant working on packages may generate invalid objects and require recompilcation 
  4.  Use adadmin utility to complie the apps schema for invalid objects related to apps user

 Simple query

 

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’;

 

Detailed Query

 

SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’ GROUP BY OWNER, OBJECT_TYPE;

 

Recompile and individual objects

 

Connect as application user “apps”

 

alter package <package_name> compile; (package specification)

alter package <package_name> compile body; (package body)

alter view <view_name> compile; (view)

 

If objects compile with warning use

 

show errors

or

select * from user_errors where name = ‘<OBJECT_NAME>’;

 

Using adadmin Utility

 

  1. Log on as Apps User (applmgr/applmgr)
  2. Source the enviornment file
  3. run adadmin
  4. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

Posted in Oracle 10g Database | Leave a Comment »

Oracle Upgrade 9.2.0.6 to 10.2.0.4

Posted by appsinfo on March 3, 2009

Install Oracle 10g Database (10.2.0.2)

 

  1. Log on as a member of the Administrators group to the computer on which you are going to install Oracle 10g Software. 
  2. Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe. 
  3. On the Welcome screen, click Next. 
  4. Select the Installation Method as Advance Installation and click next. 
  5. Here you need to select the Installation Type, select Enterprise Edition. 
  6. In the Specify Home Details Screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next. 
  7. The installer will perform Product-Specific Prerequisite Check, if every thing succeeded, Click Next. 
  8. On this screen you will be asked whether you need to perform Upgrade to an Existing Database, select No. 
  9. Select the Configuration Option as Install database Software only. 
  10. On the Summary screen, click Install

 Installing the Oracle Database 10g Patch Set (10.2.0.4) Interactively

 

To install the Oracle Database 10g patch set interactively:

 

  1. Log on as a member of the Administrators group to the computer on which you are going to install Oracle components. If you are installing on a Primary Domain Controller or a Backup Domain Controller, log on as a member of the Domain Administrators group. 
  2. Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe. 
  3. On the Welcome screen, click Next. 
  4. In the Specify Home Details Screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next. 
  5. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears. 
  6. On the Summary screen, click Install.

  When the installation is complete, the End of Installation screen appears

 

  1. On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

 Upgrading a Release 10.2 Database using Oracle Database Upgrade Assistant

 

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

 

1.      Log in with administrator privileges.

2.      Set the values for the environment variables ORACLE_HOME, ORACLE_SID, and PATH.

3.      For single-instance installations, if you are using Automatic Storage Management, start the Automatic Storage Management instance.

4.      For Oracle single-instance installations, start the listener as follows:

                  C:\> lsnrctl start

 

5.      Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode, in this post we will see interactive mode.

 

Interactive mode:

 

Start the Oracle Database Upgrade Assistant:

From the Start menu, select Programs, then Oracle – HOME_NAME, then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.

Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

 

1.      On the Welcome screen, click Next.

2.      On the Databases Screen, select the name of the Oracle database that you want to  update, then click Next.

3.      On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.

4.      If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.

5.      On the Summary screen, check the summary, then click Finish.

6.      On the End of upgradation screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.

Posted in Oracle 10g Database | 1 Comment »

Oracle Log File Switch

Posted by appsinfo on February 25, 2009

Oracle Log File Switch

More often changes are made to the data using DML statements, oracle keeps running log of these changes within “REDO” (Redo Logs). Every database consist of redo log files which are critical in recovery process, since they record changes to the data and provide the input to produce sets of archive logs which are used for point in time recovery. Whenever redo logs are filled to certain capacity and are ready for archival a log switch occurs that changes control from one redo log to the another. Redo Log Buffer – A circular buffer that stores all changes made in the database. It contains are transferred periodically from memory to the online redo log files on disk by the Log Writer (LGWR) background process. The contents of the redo log buffer are essential for instance recovery purposes. The size of the redo log buffer is determined by the LOG_BUFFER initialization parameter.

A single log switch entails stopping further acceptance of generated of redo, reading & writing to control files for redo log status and SCN, (System Change Number), information, flushing memory, and the closing & opening of individual redo log files.

There is no recorded amount of time where in a log switch takes place, not even from oracle. It is only experienced by users or applications and is recorded as a wait event at a session or system level. Switching too many redo logs is not advisable, the thumb rule is to switch a redo log every 25 to 35 minutes.  All you need to do is that to increase the redo log size to hold more redo’s and this will reduce the amount of log switching. Use the below SQL statement which shows how often log switching occur every hour.


SELECT to_char(first_time,’YYYY-MON-DD’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
/****************************************/
/* add more hours here to fill out the day */
/****************************************/
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
FROM v$log_history
GROUP by to_char(first_time,’YYYY-MON-DD’);

To remove contention of writing redo and reading redo for archival purpose. It has been noticed that the placement of redo log groups on disk is not optimal for the system, in this case each group member  should be assigned to its own disk in order to avoid contention. Check the status of the current configuration, size, status and use of redo logs using:

SELECT group#, bytes, archived, status, first_change#, first_time FROM v$log ORDER BY first_change#;

and use of redo logs in this SQL stament FIRST_CHANGE# is important which shows the first SCN number that was used for a particular redo log group. Make sure that redo log groups are not on the same disk that are used next to each other in the redo log rotation, then finally determine redo log groups on the disk using:

 

SELECT group#, status, member FROM v$logfile ORDER BY group#;

Once you have pinpointed them use disk monitoring utility such as iostat during high redo activities to determine if you have any contention for read and writes. Another way  you could use is disk cache to speed I/O operations. During the log switch operation, the control file is used quite extensively. It has always been recommended that practitioners multiplex control files through Oracle.

In this way the log switch operation must keep in sync all the control files defined and thus overhead is increased by a factor of the number of control files multiplexed. Mirroring control files at the operating system level will eliminate the reads and writes for each control file. To determine the extra amount of I/O you are incurring for each log switch (and many other operations) use:

 

SELECT group#, bytes, archived, status, first_change#, first_time

 FROM v$log ORDER BY first_change#;

 

If you choose this route, please schedule a backup of your control file to trace often enough for recoverability. Alternatively you could use a disk cache to speed I/O operations. To check location of control file use:

 

SELECT name FROM v$controlfile;


As data is manipulated and altered within an Oracle database, time must be spent recording and archiving these changes through redo log generation. The time spent recording and archiving can be reduced if proper consideration is given to the number of log switches occurring, the size of the redo logs, how they are cyclically used, and taking a look at supporting structures such as the control file or init.ora settings. Remembering to check periodically and around major application changes will safeguard your database from wasting time and waiting for a log file switch.


SELECT name, value  FROM v$parameter  WHERE name = ‘archive_lag_target’;

Posted in Oracle 10g Database | Leave a Comment »