Difference between Cloning and Refreshing of a database.

Are they same? Well let’s have a look on the above activities that helps in finding the differences between them.

What is a Database Clone?

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e. Having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

What is a Database Refresh?

* A Database Refresh is also referred to as a database clone. However, we don’t clone Oracle Home rather we clone the Database as refresh.

* Refreshing a database is something like applying the changes or updates of production database to the database where the database is already cloned. i.e. let’s say you have cloned a database a month back, and now you are asked for doing refresh of a database, then you will perform the backup of database and prepare the clone the instance again on test server. This is nothing but refreshing.

* Refreshing of a particular table, group of tables, schema, or tablespace will be done using traditional export/import, transportable Tablespaces, or data pump methods.

* When an Oracle patch is applied on Production System, or in doubt, you have to prepare and clone the database again with the copy of Oracle Home (Directories and Binaries) Backup and Database (Database related files) Backup to prepare the instance.

* The difference between Cloning and Refreshing is that cloning process includes Oracle Home and database Clone; where as Refreshing process only includes database clone.

* If seen, the words, Clone and Refresh are used interchangeably for the sake of convenient.

When and why we Clone a Database?

* Generally production (PROD) database is cloned for various reasons and needs i.e. for something to be tested or something to be developed later those to be moved to production.

* It’s normal and quite common thing is that whenever there is any change or update to be performed and do not know the impact or effect after applying it on production (PROD), it’s required to be applied and tested on *NON* production database first (TEST or DEV), after the confirmation of change success, given by the users, then the changes will be moved to production.

* A Cloned test instance (TEST) for testing team/environment is exclusively used for testing the changes or issues which will become severe on Production. Oracle Support gives the solution as fix when there is an issue in the database, so this fix needs to perform or apply on test/development databases.

* A Cloned development instance (DEV) for development team/environment is used for developing the new changes and then deploying the same on Production.

* A Cloned patch instance is used for patching to know the impact and the time required to apply the same on Production.

For more click on:-

Enjoy:-)

Flashback Transaction Query and Flashback Version Query:

You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.

Let us exploit it with an example….

connect hr/hr

CREATE TABLE emp

   (empno number primary key, empname varchar2(16), salary number);

INSERT INTO emp VALUES (111, 'Mike', 555);

COMMIT;

CREATE TABLE dept (deptno number, deptname varchar2(32));

INSERT INTO dept VALUES (10, 'Accounting');

COMMIT;

At this point, emp and dept have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111 from table emp:

UPDATE emp SET salary = salary + 100 where empno = 111;

INSERT INTO dept VALUES (20, 'Finance'); 

DELETE FROM emp WHERE empno = 111;

COMMIT;

Subsequently, a new transaction reinserts employee id 111 with a new employee name into the emp table.

INSERT INTO emp VALUES (111, 'Tom', 777);

UPDATE emp SET salary = salary + 100 WHERE empno = 111;

UPDATE emp SET salary = salary + 50 WHERE empno = 111;

COMMIT;

At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in theemp table that correspond to empno 111.

SELECT versions_xid XID, versions_startscn START_SCN,

  versions_endscn END_SCN, versions_operation OPERATION,

  empname, salary FROM hr.emp

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

  where empno = 111;

XID              START_SCN  END_SCN   OPERATION  EMPNAME    SALARY

---------------- ---------- --------- ---------- ---------- ----------

0004000700000058 113855               I          Tom        927

000200030000002D 113564              D          Mike       555

000200030000002E 112670     113564    I          Mike       555

3 rows selected

SELECT  xid, start_scn START, commit_scn COMMIT, 

        operation OP, logon_user USER, 

        undo_sql FROM flashback_transaction_query

        WHERE xid = HEXTORAW('000200030000002D');

XID               START   COMMIT  OP       USER   UNDO_SQL

----------------  -----   ------  --       ----   ---------------------------

000200030000002D  195243  195244  DELETE   HR     insert into "HR"."EMP" 

("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

000200030000002D  195243  195244  INSERT   HR     delete from "HR"."DEPT" 

where ROWID = 'AAAKD4AABAAAJ3BAAB';

000200030000002D  195243  195244  UPDATE   HR     update "HR"."EMP" 

set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';

000200030000002D  195243  113565  BEGIN  HR

4 rows selected

How to startup the database if you lost both SPFILE and PFILE.

It is very rare but sometimes happened that both spfile and pfile gets deleted and also you have no backup of none of the files.

What to do now?

Solution1: You can take init.ora from another database and change only the parameters such as db_name, instance_name, archive_dest, background_dump, core_dump, user_dump, and control_files.

Solution 2: If you can’t implement the above one then surely you have to create a new pfile from scratch. But the question arises how?

Well, alert log is the answer! Just open the alert log file and extract the below information from it:

  processes                = 150

  timed_statistics         = TRUE

  shared_pool_size         = 50331648

  large_pool_size          = 8388608

  java_pool_size           = 33554432

  control_files            = D:\oracle\oradata\test\CONTROL01.CTL,   D:\oracle\oradata\test\CONTROL02.CTL, D:\oracle\oradata\test\CONTROL03.CTL

  db_block_size            = 8192

  db_cache_size            = 25165824

  compatible               = 9.2.0.0.0

  db_file_multiblock_read_count= 16

  fast_start_mttr_target   = 300

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  undo_retention           = 10800

  remote_login_passwordfile= EXCLUSIVE

  db_domain                =

  instance_name            = test

  dispatchers              = (PROTOCOL=TCP) (SERVICE=testXDB)

  job_queue_processes      = 10

  hash_join_enabled        = TRUE

  background_dump_dest     = D:\oracle\admin\test\bdump

  user_dump_dest           = D:\oracle\admin\test\udump

  core_dump_dest           = D:\oracle\admin\test\cdump

  sort_area_size           = 524288

  db_name                  = test

  open_cursors             = 300

  star_transformation_enabled= FALSE

  query_rewrite_enabled    = FALSE

  pga_aggregate_target     = 25165824

---Now remove the parameter   “db_domain” from it.

---Save the file as <dbname>.ora

---create spfile from pfile=’ <dbname>.ora’;

---startup

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

---Take the backup of both files for future.


Solution 3: If you are using 11g then you also have the below command:


create the pfile='c:\test.ora' from memory

JJ Enjoy.

What are Fixed Size and Variable Size in SGA?

                            

SQL> show sga

Total System Global Area  431038464 bytes

Fixed Size                  1333676 bytes

Variable Size             306185812 bytes

Database Buffers          117440512 bytes

Redo Buffers                6078464 bytes

In the above result Database Buffers is belongs to component “db_block_size”

and Redo Buffers to “log buffer” but what about  Fixed Size  and  Variable Size                                          

Let’s find out them:

The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release.The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters.The size of the fixed SGA is something over which we have no control and it is generally very small.Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

The variable SGA is the sum of the "named" pools -- large pool, java pool and shared pool.

The variable size is MOST affected by java_pool_size + large_pool_size + shared_pool_size but other parameters will contribute to it (eg: every

control_file will consume 256 bytes of variable size memory.  If you have 4 controlfiles, the SGA will have 1024 bytes set aside for them) in a small way.

:-) 

For more click on:

Simplified database file creation using OMF

OMF (Oracle Managed Files) in oracle is used to simplify creation of database files as Oracle does all OS operations and file naming. It helps:

·         Automatic cleanup of the filesystem when database objects are dropped.

·         Standardized naming of database files.

·         Increased portability since file specifications are not needed.

·         Simplified creation of test systems on differing operating systems.

·         No unused files wasting disk space.

The location of database files is defined using the DB_CREATE_FILE_DEST parameter

ALTER SYSTEM SET DB_CREATE_FILE_DEST=’D:\oracle\oradata\testdata\OMF\’;

Files typically have a default size of 100M and are named using the following formats where u% is a unique 8 digit code, g% is the logfile group number, 

and %t is the tablespace name:

File Type	Format
Controlfiles	ora_%u.ctl
Redo Log Files	ora_%g_%u.log
Datafiles	ora_%t_%u.dbf
Temporary Datafiles	ora_%t_%u.tmp

Example:

Create tablespace test_omf;

The resultant datafiles will have a default size of 100M and AUTOEXTEND 

UNLIMITED.

CREATE TABLESPACE test_omf DATAFILE SIZE 150M;

ALTER TABLESPACE test_omf ADD DATAFILE;

DROP TABLESPACE test_omf

:-)

Extracting “ORA“ messages from alert log in oracle 11g

Sometimes it is very difficult for us to examine the alert log file in lieu for finding error messages starting with “ORA-“.Obviously there is a long text in the alert log and we need to search the error one by one. What if we can have a file created from alert log containing only the error message as per timestamp.

Well below post is the answer of the above question!

In oracle 11g we have X$DBGALERTEXT view through which we can query the alert log contents as per our requirement.

SQL>spool on

SQL>spool c:\alert_error.txt

SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like '%ORA-%';

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

07-MAY-11 12.09.22.609 PM +05:30                                               

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...                      

                                                                               

07-MAY-11 12.09.55.937 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                             

ORA-00313: open failed for members of log group 1 of thread 1                  

ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO01.LOG'      

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                           

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

07-MAY-11 12.10.00.000 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                              

ORA-00313: open failed for members of log group 2 of thread 1                  

ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO02.LOG'      

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                    

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

07-MAY-11 12.10.01.984 PM +05:30                                               

Errors in file d:\oracle\diag\rdbms\testdata\testdata\trace\testdata_ora_3268.tr

c:                                                                             

ORA-00313: open failed for members of log group 3 of thread 1                  

ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\TESTDATA\REDO03.LOG'       

ORA-27041: unable to open file                                                 

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

OSD-04002: unable to open file                                                 

O/S-Error: (OS 2) The system cannot find the file specified.                   

                                                                               

16-MAY-11 11.34.29.927 AM +05:30                                               

ORA-1119 signalled during: create undo tablespace un datafile ' D:\ORACLE\ORADAT

A\TESTDATA\un01.dbf' size 4m...                                                

                                                                               

16-MAY-11 11.36.18.193 AM +05:30                                               

ORA-1089 : opidrv aborting process J000 ospid (2460_384)                       

ORIGINATING_TIMESTAMP                                                          

---------------------------------------------------------------------------    

MESSAGE_TEXT                                                                   

--------------------------------------------------------------------------------

                                                                                

16-MAY-11 11.45.09.177 AM +05:30                                               

ORA-1089 : opidrv aborting process J000 ospid (2460_2500)                      

                                                                                

16-MAY-11 03.14.36.771 PM +05:30                                               

WARNING: inbound connection timed out (ORA-3136)                               

                                                                                

8 rows selected.

SQL> spool off

The file alert.txt will contains only the error message information that you really looking for.

EnjoyJ

Tips and Tricks with Windows OS :-)

How to Rename the Recycle Bin

To change the name of the Recycle Bin desktop icon, open Regedit and go to:
HKEY_CLASSES_ROOT/CLSID/{645FF040-5081-101B-9F08-00AA002F954E}

and change the name "Recycle Bin" to whatever you want (don't type any quotes).

Rename a Series of Files

When you download photos from your digital camera, they often have unrecognizable names. You can rename several similar files at once with the following procedure. This also works for renaming other types of files.

1.Open the My Pictures folder. (Click Start, and then click My Pictures.) Or open another folder containing files that you want to rename. 
2.Select the files you want to rename. If the files you want are not adjacent in the file list, press and hold CTRL, and then click each item to select it. 
3.On the File menu, click Rename. 
4.Type the new name, and then press ENTER.

All of the files in the series will be named in sequence using the new name you type. For example, if you type Birthday, the first will be named Birthday and subsequent files in the series will be named Vishu (1), Vishu (2), and so on. To specify the starting number for the series, type the starting number in parentheses after the new file name. The files in the series will be numbered in sequence starting with the number you type. For example, if you type Vishu (10), the other files will be named Vishu (11), Vishu (12), and so on.

Change the text in Internet Explorers title bar to anything you want

In regedit navigate to this key:

HKEY_CURRENT_USERSoftwareMicrosoftInternet ExplorerMain

change the value of the string "Window Title" to whatever you want on the title bar of Internet Explorer - to have no title except the title of the web pages you are browsing do not enter anything for a value.

Speed Up Browsing

When you connect to a web site your computer sends information back and forth. Some of this information deals with resolving the site name to an IP address, the stuff that TCP/IP really deals with, not words. This is DNS information and is used so that you will not need to ask for the site location each and every time you visit the site. Although Windows XP and Windows XP have a pretty efficient DNS cache, you can increase its overall performance by increasing its size. You can do this with the registry entries below:

Windows Registry Editor Version 5.00 
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dnscache\Parameters]
"CacheHashTableBucketSize"=dword:00000001
"CacheHashTableSize"=dword:00000180
"MaxCacheEntryTtlLimit"=dword:0000fa00
"MaxSOACacheEntryTtlLimit"=dword:0000012d

Make a new text file and rename it to dnscache.reg. Then copy and paste the above into it and save it. Merge it into the registry.

ENJOY :-)