Oracle DataBase Cloning

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
 

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
 

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

IBM AIX Commands for Oracle DBAs

Hi friends.. these are all the commands i collected for reference, hope it will be useful to you too.. 

i will be updating this post as soon as i come across new commands.. 

thanks for reading and keep in touch.

General Commands in AIX

oslevel:   Returns operating system level

bootinfo -y:To display if the hardware is 32-bit or 64-bit, type:

bootinfo –r

or

lsattr -E1 sys0 -a realmem: To display real memory in kilobytes (KB), type one of the following bootinfo -k: To display if the kernel is 32-bit enabled or 64-bit enabled, type: prtconf: lscfg | grep proc:  to list the no of processors in the system

whence (program):    Returns full path of program

whereis (program): Returms full path of program

what (program):    Displays identifying info from the executable like version number, when

compiled. 

lslpp -L all:    list all installed software

lslpp -L (program set name): Check if software installed

lslpp -f:    Lists filesets vs packages

lslpp -ha:    Lists installation history of filesets

instfix -ik (fix number eg IX66617): Checks id fix is installed

Uname –p : Displays the chip type of the system. For example, PowerPC

Uname –r : Displays the release number of the operating system

Uname –s : Displays the system name. For example, AIX.

Uname –n: Displays the name of the node.

Uname –a : Displays the system name, nodename, version, machine ID.

Uname –M : Displays the system model name. For example, IBM, 9114-275.

Uname –v : Displays the operating system version.

Uname –m: Displays the machine ID number of the hardware running the system.

Uname  -u : Displays the system ID number.

• Examples :

instfix -ik 4330-02_AIX_ML

compress -c file.txt > file.Z: Create a compressed file.

ar -v -t (archive file):  List contents of an archive

ar -v -x (archive file): Extracts the archive.

alog -o -t boot: View the boot log

chtz (timezone eg GMT0BST): Change time zone.

chlang (language eg En_GB): Changes the language in /etc/environment file

Terminal Commands

tty:Displays what the tty/pty number of the terminal is.

chdev -l (device eg tty1) -a term=vt100: Sets tty to a vt100 terminal type

lscons: Displays the console device

Network Related Commands

host (ip or hostname): Resolves a hostname / ip address

hostname: Displays hostname

hostname (hostname): Sets the hostname until next reboot

chdev -l (device name) -a hostname=(hostname): Changes hostname permanently Examples :chdev -l inet0 -a hostname=thomas

ifconfig (device name): Displays network card settings

ifconfig (device name): up Turns on network card

ifconfig (device name): down Turns off network card

ifconfig (device name): detach Removes the network card from the network interface list

netstat –i: Displays interface statistics

traceroute (name or ipaddress): Displays all the hops from source to destination supplied.

ping -R (name or ipaddress): Same as traceroute except repeats.

Volume Management

Lspv: To display the number of hard disks on your system, type

Lspv hdisk1: To find details about hdisk1.

Crfs: create file system

            Eg: The following command will create, within volume group testvg, a jfs2 file system of 10MB with mounting point /fs2 and having read-only permissions:

crfs -v jfs2 -g testvg -a size=10M -p ro -m /fs2    

Chfs: change size of file system

                Eg: To increase the /usr file system size by 1000000 512-byte blocks, type:

chfs -a size=+1000000 /usr

Mount: command to display information about all currently mounted file systems:

Options--- examples

1.       Mount cd rom: mount -V cdrfs -o ro /dev/cd0  /cdrom

2.       Mount file system: mount /dev/fslv02 /test

3.       Unmount filesystem: umount /test

4.       Mount all filesystem:mount  {–a|all}

Defragfs: defragment file system

Performance Monitoring

Lsps –a : amount of paging space allocated and in use

increase a paging space:-

You can use the chps -s command to dynamically increase the size of a paging space. For example, if you want to increase the size of hd6 with 3 logical partitions, you issue the following command:

chps -s 3 hd6

I reduce a paging space:-

You can use the chps -d command to dynamically reduce the size of a paging space. For example, if you want to decrease the size of hd6 with four logical partitions, you issue the following command:

chps -d 4 hd6

ASM Architecture

In ASM Architecture, Oracle database utilizes  a separate smaller database instance, which is installed  in a separate oracle home and created during database  setup. An ASM  instance  manages the metadata  that is needed to make ASM files available to regular database instances. ASM instance and   database instances have access to a common set of disks called disk groups.


fig show difference between conventional storage and asm storage.


ASM Instance Background Processes 

There are at least two new background processes added for an ASM instance:
   - RBAL - coordinates rebalance activity for disk groups 
   - ORB0, ORB1… - These perform the actual rebalance data extent movements.
     There can be many of these at a time

Database Instance ASM Background Processes 

Any database instance that is using an ASM disk group will contain a background process called OSMB. The OSMB process is responsible for communicating with the ASM instance. A second additional background process, called RBAL (just like in the ASM Instance) performs a global open on ASM disks. A global open means that more than one database instance can be accessing the ASM disks at a time.


For certain database operations like file creation, ASM intervention is required and the database foreground connects directly to the ASM instance. Whenever a connection is made to the ASM instance, the OSMB process is started dynamically. Database instances are only allowed to connect to one ASM instance at a time, so they have at most one OSMB background process.


Disk Group:- A basic component of ASM is the disk group. ASM is configured by creating disk groups, which in turn database instances can use as the default location for files created in the database. Oracle provides SQL statements to create and manage disk groups, their contents, and their metadata.
For information regarding commands to administer ASM go to ASM Administration post from home menu. 


Allocation Units (AU):-ASM introduces the concept of an allocation unit (AU), the smallest contiguous disk space allocated by the ASM. The typical value for an AU is 1MB and is not user configurable. ASM does not allow physical blocks to be split across allocation units.

ASM Installation

Automatic Storage Management is installed by default with Oracle Universal Installer. The Database Configuration Assistance (DBCA) looks for an existing ASM instance and if it does not find one, there is the option of creating and configuring one during the installation process. It is available  in both the Enterprise Edition and Standard edition Installations.

Starting Up ASM Instance

An ASM instance is started like any other database instance, except that the initialization parameter file contains the parameter instance_type=ASM.

For ASM instances, the mount option does not try to mount a database, but tries to mount the diskgroups that are specified by the asm_diskgroups parameter. ASM instance requires smaller SGA (64MB) and usually brought up automatically on server reboots.

NOMOUNT:- does not mount any disk groups, but starts up the instance.

MOUNT:- mounts the disk goups specified by asm_diskgroups.

OPEN:- not valid for ASM instance.

FORCE:-issues the command shutdown abort to the instance and starts it as in normal database instance.

Shuting down ASM Instance
NORMAL:-ASM waits for any in-progress SQL to complete before performing an orderly dismount of all of the disk groups.
ASM waits for all of the currently connected users to disconnect from the instance.
If any database instances are connected to the ASM instance, then the SHUTDOWN command returns an error.

IMMEDIATE or TRANSACTIONAL:-ASM waits for any in-progress SQL to complete before performing an orderly dismount of all of the disk groups.
ASM does not wait for users currently connected to the instance to disconnect.
If any database instances are connected to the ASM instance, then the SHUTDOWN command returns an error.
Because the ASM instance does not contain any transactions, the TRANSACTIONAL mode is the same as the IMMEDIATE.ABORT:-The ASM instance immediately shuts down without the orderly dismount of disk groups.
This causes recovery to occur upon the next ASM startup.
If any database instance is connected to the ASM instance, then the database instance aborts.

Initialization Parameter Files for an ASM Instance

Below are the list of asm parameters, i'll only explain the relevant ones.

ASM_DISKGROUPS:-Name of the disk groups created.

ASM_DISKSTRING:- limits the set of disks that ASM considers for discovery.

ASM_POWER_LIMIT:- The maximum power on an ASM instance for disk rebalancing. Possible values range from 1 to 11 with 11 being the fastest.

INSTANCE_TYPE:- Must be set to ASM, this is the only required parameter. All other parameters assume defaults values for most environments.

LARGE_POOL_SIZE:- Internal packages used  by ASM utilize the Large Pool. The value of large_pool_size should be set ot a value greater than 8MB.

DB_CACHE_SIZE:-
DIAGNOSTIC_DEST:-

PROCESSES:-
REMOTE_LOGIN_PASSWORDFILE:-
SHARED_POOL_SIZE:-

ASM_PREFERRED_READ_FAILURE_GROUPS:-

ASM Administration

For ASM installation,startup and shutdown information go to  ASM Installation post.

Disk groups are created using the CREATE DISKGROUP statement.

eg:

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY

     FAILGROUP failure_group_1 DISK

       '/devices/diska1' NAME diska1,

       '/devices/diska2' NAME diska2,

     FAILGROUP failure_group_2 DISK

       '/devices/diskb1' NAME diskb1,

       '/devices/diskb2' NAME diskb2;

The failgroup clause in the above example is for redundancey.

ie,

* NORMAL REDUNDANCY - Two-way mirroring, requiring two failure groups.

* HIGH REDUNDANCY - Three-way mirroring, requiring three failure groups.

* EXTERNAL REDUNDANCY - No mirroring for disks that are already protected using hardware mirroring or RAID.

The above  is therefore in normal redundancy.

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement.

-- Add disks.

   ALTER DISKGROUP disk_group_1 ADD DISK

     '/devices/disk*3',

     '/devices/disk*4';

-- Drop a disk.

   ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement.

   -- Resize a specific disk.

   ALTER DISKGROUP disk_group_1

     RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.

   ALTER DISKGROUP disk_group_1

     RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.

   ALTER DISKGROUP disk_group_1

     RESIZE ALL SIZE 100G;

The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.

   ALTER DISKGROUP ALL DISMOUNT;

   ALTER DISKGROUP ALL MOUNT;

   ALTER DISKGROUP disk_group_1 DISMOUNT;

   ALTER DISKGROUP disk_group_1 MOUNT;

Types of Joins

join types

INNER JOIN

All of the joins that you have seen so far have used the natural join syntax—for example, to produce a list of customers and dates on which they placed orders. Remember that if this syntax is available, it will automatically pick the join attributes as those with the same name in both tables (intersection of the schemes). It will also produce only one copy of those attributes in the result table.

        SELECT cFirstName, cLastName, orderDate
        FROM customers NATURAL JOIN orders;

• The join does not consider the pk and fk attributes you have specified. If there are any non-pk/fk attributes that have the same names in the tables to be joined, they will also be included in the intersection of the schemes, and used as join attributes in the natural join. The results will certainly not be correct! This problem might be especially difficult to detect in cases where many natural joins are performed in the same query. Fortunately, you can always specify the join attributes yourself, as we describe next.

• Another keyword that produces the same results (without the potential attribute name problem) is the inner join. With this syntax, you may specify the join attributes in a USING clause. (Multiple join attributes in the USING clause are separated by commas.) This also produces only one copy of the join attributes in the result table. Like the NATURAL JOIN syntax, the USING clause is not supported by all systems.

        SELECT cFirstName, cLastName, orderDate
        FROM customers INNER JOIN orders
          USING (custID);

• The most widely-used (and most portable) syntax for the inner join substitutes an ON clause for the USING clause. This requires you to explicitly specify not only the join attribute names, but the join condition (normally equality). It also requires you to preface (qualify) the join attribute names with their table name, since both columns will be included in the result table. This is the only syntax that will let you use join attributes that have different names in the tables to be joined. Unfortunately, it also allows you to join tables on attributes other than the pk/fk pairs, which was a pre-1992 way to answer queries that can be written in better ways today.

        SELECT cFirstName, cLastName, orderDate
        FROM customers INNER JOIN orders
          ON customers.custID = orders.custID;

• You can save a bit of typing by specifying an alias for each table name (such as c and o in this example), then using the alias instead of the full name when you refer to the attributes. This is the only syntax that will let you join a table to itself, as we will see when we discuss recursive relationships.

        SELECT cFirstName, cLastName, orderDate
        FROM customers c INNER JOIN orders o
          ON c.custID = o.custID;

• All of the join statements above are specified as part of the 1992 SQL standard, which was not widely supported for several years after that. In earlier systems, joins were done with the 1986 standard SQL syntax. Although you shouldn’t use this unless you absolutely have to, you just might get stuck working on an older database. If so, you should recognize that the join condition is placed confusingly in the WHERE clause, along with all of the tests to pick the right rows:

        SELECT cFirstName, cLastName, orderDate
        FROM customers c, orders o
        WHERE c.custID = o.custID;

OUTER JOIN

One important effect of all natural and inner joins is that any unmatched PK value simply drops out of the result. In our example, this means that any customer who didn’t place an order isn’t shown. Suppose that we want a list of all customers, along with order date(s) for those who did place orders. To include the customers who did not place orders, we will use an outer join, which may take either the USING or the ON clause syntax.

        SELECT cFirstName, cLastName, orderDate
        FROM customers c LEFT OUTER JOIN orders o
          ON c.custID = o.custID;

All customers and order dates

cfirstname	clastname	orderdate
Tom	Jewett
Alvaro	Monge	2003-07-14
Alvaro	Monge	2003-07-18
Alvaro	Monge	2003-07-20
Wayne	Dick	2003-07-14

• Notice that for customers who placed no orders, any attributes from the Orders table are simply filled with NULL values.

• The word “left” refers to the order of the tables in the FROM clause (customers on the left, orders on the right). The left table here is the one that might have unmatched join attributes—the one from which we want all rows. We could have gotten exactly the same results if the table names and outer join direction were reversed:

        SELECT cFirstName, cLastName, orderDate
        FROM orders o RIGHT OUTER JOIN customers c
          ON o.custID = c.custID;

• An outer join makes sense only if one side of the relationship has a minimum cardinality of zero (as Orders does in this example). Otherwise, the outer join will produce exactly the same result as an inner join (for example, between Orders and OrderLines).

• The SQL standard also allows a FULL outer join, in which unmatched join attributes from either side are paired with null values on the other side. You will probably not have to use this with most well-designed databases.

EVALUATION ORDER

Multiple joins in a query are evaluated left-to-right in the order that you write them, unless you use parentheses to force a different evaluation order. (Some database systems require parentheses in any case.) The schemes of the joins are also cumulative in the order that they are evaluated; in RA, this means that

r1  r2  r3 = (r1  r2)  r3.

• It is especially important to remember this rule when outer joins are mixed with other joins in a query. For example, if you write:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
        FROM customers LEFT OUTER JOIN orders 
          USING (custID)
          NATURAL JOIN orderlines;

you will lose the customers who haven’t placed orders. They will be retained if you force the second join to be executed first:

        SELECT cFirstName, cLastName, orderDate, UPC, quantity
        FROM customers LEFT OUTER JOIN 
          (orders NATURAL JOIN orderlines)
          USING (custID);

OTHER JOIN TYPES

For sake of completeness, you should also know that if you try to join two tables with no join condition, the result will be that every row from one side is paired with every row from the other side. Mathematically, this is a Cartesian product of the two tables, as you have seen before. It is almost never what you want. In pre-1992 syntax, it is easy to do this accidently, by forgetting to put the join condition in the WHERE clause:

        SELECT cFirstName, cLastName, orderDate
        FROM customers, orders;

• If your system is backward-compatible (most are), you might actually try this just to prove to yourself that the result is pure nonsense. However, if you ever have an occasion to really need a Cartesian product of two tables, use the new cross join syntax to prove that you really mean it. Notice that this example still produces nonsense.

        SELECT cFirstName, cLastName, orderDate
        FROM customers CROSS JOIN orders;

• It is possible, but confusing, to specify a join condition other than equality of two attributes; this is called a non-equi-join. If you see such a thing in older code, it probably represents a WHERE clause or subquery in disguise.

• You may also hear the term self join, which is nothing but an inner or outer join between two attributes in the same table. We’ll look at these when we discuss recursive relationships.