Feeds:
Posts
Comments

Posts Tagged ‘Database’

Hi Friends,

Hope all of you doing well, here I have one more live scenario (Mainly for those who are new in DBA world and wanted to know what kind of issue came in PRD).

Yesterday I was doing one Database refresh activity and I came across this issue where while recovering database it was asking for archive file which was missing.

generally in this case, if you don’t have archive file and any how you want to open database i.e incomplete recovery then please follow the below steps.

While doing incomplete recovery we first find the which is current log file in DB.

Then try to apply those log file when it is asking for specific archive file.

In this scenario I try to apply the log file which was having status “CURRENT” but not luck.

Then I apply alternatively other log file as well to try luck and how wonder it took one of log file and recovery went successfully.

Luckily I copied those logs for all of you guys and , please find same one as below.

 

SQL> @control_CRP.sql.orig_noz ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started.

Total System Global Area 1.3095E+10 bytes Fixed Size                  2231848 bytes Variable Size            4362076632 bytes Database Buffers         8724152320 bytes Redo Buffers                6447104 bytes

Control file created.

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g18m1.dbf ORA-00310: archived log contains sequence 145139; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g18m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g17m1.dbf ORA-00310: archived log contains sequence 145138; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g17m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g16m1.dbf ORA-00310: archived log contains sequence 145137; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g16m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g15m1.dbf ORA-00310: archived log contains sequence 145136; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g15m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g14m1.dbf Log applied. Media recovery complete. SQL> alter database open resetlogs;

Database altered.

SQL> =============================================================================== Please find the log status during this recovery from other session…. FYI…:)

SQL> select GROUP#,BYTES/1024/1024,MEMBERS,STATUS from v$log;

    GROUP# BYTES/1024/1024    MEMBERS STATUS ———- ————— ———- —————-          1              50          2 INACTIVE          2              50          2 INACTIVE          3              50          2 INACTIVE          4              50          2 INACTIVE          5              50          2 INACTIVE          6              50          2 INACTIVE          7              50          2 INACTIVE          8              50          2 CURRENT

8 rows selected.

SQL> col MEMBER for a40 SQL> col TYPE for a12 SQL> select GROUP#,MEMBER,TYPE from v$logfile;

    GROUP# MEMBER                                   TYPE ———- —————————————- ————          8 /oracle/CRQ/origlogB/log_g18m1.dbf       ONLINE          8 /oracle/CRQ/mirrlogB/log_g18m2.dbf       ONLINE          7 /oracle/CRQ/origlogA/log_g17m1.dbf       ONLINE          7 /oracle/CRQ/mirrlogA/log_g17m2.dbf       ONLINE          6 /oracle/CRQ/origlogB/log_g16m1.dbf       ONLINE          6 /oracle/CRQ/mirrlogB/log_g16m2.dbf       ONLINE          5 /oracle/CRQ/origlogA/log_g15m1.dbf       ONLINE          5 /oracle/CRQ/mirrlogA/log_g15m2.dbf       ONLINE          4 /oracle/CRQ/origlogB/log_g14m1.dbf       ONLINE          4 /oracle/CRQ/mirrlogB/log_g14m2.dbf       ONLINE          3 /oracle/CRQ/origlogA/log_g13m1.dbf       ONLINE

    GROUP# MEMBER                                   TYPE ———- —————————————- ————          3 /oracle/CRQ/mirrlogA/log_g13m2.dbf       ONLINE          2 /oracle/CRQ/origlogB/log_g12m1.dbf       ONLINE          2 /oracle/CRQ/mirrlogB/log_g12m2.dbf       ONLINE          1 /oracle/CRQ/origlogA/log_g11m1.dbf       ONLINE          1 /oracle/CRQ/mirrlogA/log_g11m2.dbf       ONLINE

16 rows selected.

SQL>

 

Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

 

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for success, always fight even knowing your defeat is certain….!!!!

Read Full Post »

Friends after long time back to blog …. due to lots of up / down in life I was away from blog …. but promise from here onward will be in touch with you guys and will always reply your queries and suggestions …

Most of Jr. DBA while chating ask the question abt archive enable / disable query , so just coming with basic but most useful post for Jr.DBA.

Also please note , whenever we enabling the DB in archive mode as DBA we need to monitor the archive space as well.

In large database environmnet we need to take regular archive so backup , so generaly DBA use to schedule it to keep the free space in archive location.

Otherwise DB may go in hung state if archive location is fulled.

In oraganisation some time archive file is manually deleted by DBA it self or we schedule it to delete once it is backup.

This is how to enable archiving:
 

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Feb 26 09:57:43 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 – 64bit Production
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/SID/oraarch
Oldest online log sequence     2442
Current log sequence           2444

 SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
Total System Global Area 289406976 bytes
 Fixed Size 1248576 bytes
 Variable Size 96469696 bytes
 Database Buffers 184549376 bytes
 Redo Buffers 7139328 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            /oracle/SID/oraarch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
 SQL>
 
=================================================
 
This is how to disable archiving:
 
SQL> shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup mount
 ORACLE instance started.
 
Total System Global Area 289406976 bytes
 Fixed Size 1248576 bytes
 Variable Size 100664000 bytes
 Database Buffers 180355072 bytes
 Redo Buffers 7139328 bytes
 Database mounted.
 SQL> alter database noarchivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oracle/SID/oraarch
Oldest online log sequence     26
Current log sequence           28
SQL>

 

I Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

 

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for suceess, always fight even knowing your defeat is certain….!!!!

 

Read Full Post »

As promise in Oracle 9i Architecture post here is new post for how SQL statement processe internaly. This is for them those whi are planning to start career as Oracle DBA. If they want to make clear picture of Oracle Architecture then first of all they need to understand how sql statement procees in oracle.These are the statement which we are going to run.

Sqlplus scott/tiger@prod

SQL>select * from emp;

SQL>update emp set sallary=30000 where empid=10;

SQL>commit;

So we will understand what is happening internaly

  1. Once we hit sqlplus statement as above client process(user) access sqlnet listener.
  2. Sql net listener confirms that DB is open for buisness & create server process.
  3. Server process allocates PGA.
  4. ‘Connected’ Message returned to user.
  5. SQL>select * from emp;
  6. Server process checks the SGA to see if data is already in buffer cache.
  7. If not then data is retrived from disk and copied into SGA (DB Cache).
  8. Data is returned to user via PGA & server process.
  9. Now another statement is   SQL>Update emp set sallary=30000 where empid=10;
  10. Server process (Via PGA) checks SGA to see if data is already there in buffer cache.
  11. In our situation chances are the data is still in the SGA (DB Cache).
  12. Data updated in DB cache and mark as ‘Dirty Buffer’.
  13. Update employee placed into redo buffer.
  14. Row updated message returned to user
  15. SQL>commit;
  16. Newest SCN obtained from control file.
  17. Data in DB cache is marked as ‘Updated and ready for saving’.
  18. commit palced into redo buffer.
  19. LGWR writes redo buffer contents to redo log files & remove from redo buffer.
  20. Control file is updated with new SCN.
  21. Commit complete message return to user.
  22. Update emp table in datafile & update header of datafile with latest SCN.
  23. SQL>exit;
  24. Unsaved changes are rolled back.
  25. Server process deallocates PGA.
  26. Server process terminates.
  27. After some period of time redo log are archived by ARCH process.    

Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.

Experts are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/02/17/oracle-9i-architecture/

Read Full Post »

The listener.log file grows and its not possible to just move the file whilst the listener is up. The tnslsnr process always hold this log file. This can be confirmed using the command fuser.

Test $> cd /oracle/product/10.2.0/network/log
Test $> ls -lrt
total 1522
-rw-r—–   1 oracle   dba       763075 Mar  7 22:40 listener_old.log
-rw-r—–   1 oracle   dba          259 Mar  7 23:08 listener.log

Test $> fuser listener.log
listener.log:    13389o
Test $> ps -ef |grep 13389
  oracle 19417 18886   0 23:15:30 pts/1       0:00 grep 13389
  oracle 13389     1   0   Nov 19 ?           2:03 /oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit

Test $> lsnrctl

LSNRCTL for Solaris: Version 10.2.0.4.0 – Production on 07-MAR-2011 22:46:03

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set Log_status off
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter “log_status” set to OFF
The command completed successfully
LSNRCTL> exit

Test $> mv listener.log  listener_old.log
Test $> ls -lrt
total 1520
-rw-r—–   1 oracle   dba       763075 Mar  7 22:40 listener_old.log
Test $>  touch listener.log
Test $>
Test $> ls -lrt
total 1520
-rw-r—–   1 oracle   dba       763075 Mar  7 22:40 listener_old.log
-rw-r–r–   1 oracle   dba            0 Mar  7 22:47 listener.log
Test $> lsnrctl

LSNRCTL for Solaris: Version 10.2.0.4.0 – Production on 07-MAR-2011 22:47:13

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

Welcome to LSNRCTL, type “help” for information.

LSNRCTL> set Log_Status on
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL> exit

2.The below will empty your listener.log file. ” > ” is part of the command. It just redirect null into the file

Test $> > listener.log

3.set LOGGING_LISTENER=OFF in the listener.ora file to stop the listener logging.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Recently we did database upgrade from 10g to Oracle 11g.I would like share that activity with you.

Pre-Requisite:

You should have the Oracle database 10g, which you want to migerate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0

then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0

Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command

./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst

Screen 1 – Select Product Install
select “Oracle Database 11g”

Screen 2 – Select Installation Method
Choose “Advanced Installation”

Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Screen 4 – Select Installation Type
Choose “Enterprise Edition”

Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME

Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Screen 7 – Upgrade an Existsing Database
Choose “No”

Screen 8 – Select Configuration Option
Choose “Install Software Only”

Screen 9 – Privileged system groups

Based on the group of oracle user, this value has to be set.

Screen 10 – Summary
Click on “Install”

At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

[oracle]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Most of the time issue use to come up with time zone….

Then login to the 10g oracle database and run the above sql you copied.

Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.

The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 2

SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;


VERSION
———-
2

If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.

Check the database version

SQL> select banner from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls

readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

Copy the .dat files

[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again

SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;

VERSION
———-
4

WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN

Gather Dictionary stats:

Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

PL/SQL procedure successfully completed.

Step 4) Run Pre-Upgrade Utility again

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.

If every thing looks fine, Shut down the database from 10g Oracle Home

This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql

Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57

PL/SQL procedure successfully completed.

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile

SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;

COUNT(*)
———-
1576

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;

COUNT(*)
———-
0

This completes the upgrade.

Read Full Post »

When implementing a backup and recovery strategy, you have the following solutions available:

– Recovery Manager (RMAN)

This tool integrates with sessions running on an Oracle database to perform a
range of backup and recovery activities, including maintaining an RMAN
repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

– User-managed backup and recovery

In this solution, you perform backup and recovery with a mixture of host
operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery.

RMAN performs the same types of backup and recovery available through user-managed techniques more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.
Most of this manual focuses on RMAN-based backup and recovery.

User-managed backup and recovery techniques are covered in Section VIII, “Performing User-Managed Backup and Recovery.” RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

– Incremental backups

An incremental backup stores only blocks changed since a previous backup.
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery. If you enable block change tracking, then you can improve performance by avoiding full scans of every input datafile. You use the BACKUP INCREMENTAL command to perform incremental backups.

– Block media recovery

You an repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup. You use the RECOVER command to perform block media recovery.

– Unused block compression

In unused block compression, RMAN can skip data blocks that have never been used and, in some cases, used blocks that are currently unused.

– Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups.

– Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format. To create encrypted backups on disk, the database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option.
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility.

You can later use Data Pump Import to re-create data after restore and recovery. Logical backups are for the most part beyond the scope of the backup and recovery documentation.

– Oracle Flashback Technology

As explained in Oracle Database Concepts, Oracle Flashback Technology complements your physical backup and recovery strategy. This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

– Logical Flashback Features

Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

– Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

Oracle Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start and end time, operation, and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

Oracle Database Advanced Aplication Developer’s Guide explains how to use this feature.

– Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions uring a period of time. Oracle Database Advanced Application Developer’s Guide xplains how to use this feature.

– Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction, and any transactions that could be dependent on it, had never happened. Oracle
Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past
without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining
associated attributes such as current indexes, triggers, and constraints, and in this way enabling you to avoid finding and restoring database-specific properties.

– Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. “Rewinding a DROP
TABLE Operation with Flashback Drop” on page 16-7 explains how to use this
feature.

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

– Flashback Database

At the physical level, Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but is generally much faster because it does not require restoring datafiles from backup and requires less redo than media recovery.

Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a flash recovery area for a database because the flashback logs can only be stored there. Flashback logging is not enabled by default. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the flash recovery area.

Oracle Database also supports restore points in conjunction with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

– Data Recovery Advisor

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

A database failure usually manifests itself as a set of symptoms: error messages, alerts, trace files and dumps, and failed data integrity checks. Data Recovery Advisor automatically diagnoses and informs you of these failures. Within the context of Data Recovery Advisor, a failure is a persistent data corruption that can be directly mapped to a set of repair actions. Each failure has a status of open or closed. Each failure also has a priority of critical, high, or low.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it with respect to the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

Read Full Post »

What is Latch?

Well, the hardware definition of latch is – a window or door lock. The electronic definition of latch is – an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration – might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

Older Posts »