In order to access the APEX application, either the embedded PL/SQL gateway or Oracle HTTP server with mod_plsql is needed. For simplicity, I’ve decided to go with the former. By using the embedded PL/SQL gateway, it will run using the Oracle XML DB HTTP server which is already in Oracle database, so there is no need to install a separate HTTP server. The Oracle’s document here explains about this as well as provides the detailed information on the post-installation.
1. To configure the embedded PL/SQL gateway:
Go to the $ORACLE_HOME/apex directory.
2. Use SQL/Plus to connect as SYS to 11g database where APEX is installed.
SYS AS SYSDBA@DB11G> @apxconf
Enter values below for the XDB HTTP listener port and the password for the Application Express ADMIN user.
Default values are in brackets [ ].
Press Enter to accept the default value
Enter a password for the ADMIN user  admin_password
Enter a port for the XDB HTTP listener [ 8080]
…changing HTTP Port
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
…changing password for ADMIN
PL/SQL procedure successfully completed.
3. Unlock the ANONYMOUS account.
SYS AS SYSDBA@ DB11G > ALTER USER ANONYMOUS ACCOUNT UNLOCK;
4. Enable Oracle XML DB HTTP server
SYS AS SYSDBA@ DB11G > EXEC DBMS_XDB.SETHTTPPORT(8080);
PL/SQL procedure successfully completed.
SYS AS SYSDBA@ DB11G > COMMIT;
5. We’re now ready to access APEX.
http://host:port/apex/apex_admin — for admin page
Port in this case is 8080 which is the default.
As described in “Oracle Database Upgrade Guide 11g Release 1 (11.1)“, there are three upgrade methods offered while you want to upgrade database from 10g to 11g.
- Database Upgrade Assistant (DBUA)
- Manual Upgrade (Oracle provided scripts)
- Export/Import (exp/imp or expdp/impdp)
Besides benefits mentioned in above upgrade document, I’m thinking we are beneficial from using export/import approach. The reasons we decided using this method are:
- We want to set up identical testing databases on development server first
- We want to keep original production databases unchanged anyway for safety reason
With this approach, we first installed Oracle 11g software on development server and created empty database by using provided standard template New_Database.dbt, which could be found at $ORACLE_HOME/assistants/dbca/templates. After that, we followed the following steps to move data from 10g database to newly created 11g database.
Step 1: Pre-create tablespaces on target 11g database
- Using the Generate_Create_Tablespaces.sql to generate tablespace script
- Editing generated script to remove entries for SYSTEM, USERS tablespaces
Step 2: Export full database of source 10g database
expdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=export.log
Step 3: Copy dumpfile over network to development server
Step 4: Grant IMPORT_FULL_DATABASE system privilege to user SYSTEM of target 11g database
Step 5: Import full database to target 11g database
Note: If you have co-existing oracle home/version on development server, please make sure switching to Oracle 11g environment, which will assure to use data pump at 11g version to import data.
Impdp system@database directory=DATA_PUMP_DIR full=y dumpfile=export.dmp logfile=import.log
I’ve been using VMWare for a while now and I always get asked some common questions about it. One of those is how to add a new virtual disk to a Linux virtual machine. So in response to that, here are the steps to adding a new SCSI based virtual disk to a CentOS Linux virtual machine. The steps for adding a disk to a Windows machine is very much the same except you would use the Disk Management utility from the Control Panel.
Step 1: Open virtual machine settings
Select your virtual machine, as you can see from the photo I selected the Infrastructure virtual machine. Next press the “Edit virtual machine settings’ to open the Virtual Machine Settings dialog.
Step 2: Add new hardware
From the “Virtual Machine Settings” dialog select the “Add…” button at the bottom of the screen. From this dialog you can also modify how much memory you dedicate to the machine when it boots.
Step 3: Start hardware wizard
Next we will walk through the “Add Hardware Wizard” the process makes it very simple to add additional hardware to a predefined virtual machine. From this screen we want to select the “Next >” button.
Step 4: Select new hard disk
From this screen we can see the many types of hardware we can add to a virtual machine. You can emulate just about any piece of hardware that one can expect in a modern operating system. It definitely makes testing with different configurations and devices much easier. For our example we want to select “Hard Disk” and then select the “Next >” button.
Step 5: Create the virtual disk
In the next screen we see the three options for adding a new disk. We can “Create a new virtual disk”, this will create a brand new disk on the guest operating system. The second option, “Use an existing virtual disk”, allows you to mount a disk from another virtual machine. I like to do this with my “source” drive. I have one virtual disk that I’ve made that has all the Oracle and Linux CDs on it, that way I can just mount it to the machine I need when I have to do a new install instead of copying the binaries I need across disks, its definitely a big time saver. The last option is to “Use a physical disk”, this allows you to mount a local physical disk to the operating system. This option is akin to NFS mounting a drive to a virtual machine. To add a new disk we select the “Create a new virtual disk” option and select the “Next >” button.
Step 6: Select type of disk
Next we want to select the type of disk. I’ve been using VMWare for a long time and agree that the recommended Virtual Disk Type should be SCSI. I don’t know why, but I’ve had much better success with the SCSI virtual disks than the IDE ones. So in this step we want to select “SCSI (Recommended)” and the “Next >” button.
Step 7: Set disk size and options
Now we want to set the size of the disk we are creating. One of the nice features of VMWare is that you don’t have to allocate all of the disk when you create it. So if you create a 40 GB disk it doesn’t have to take it all right away, the disk will grow as your virtual machine needs it. I will say this is a big performance hit you take when the disk has to extend, but for most applications its OK. Also, I will warn that if the virtual disk grows and there is no physical disk left on the host operating system you will see a catastrophic failure and in most cases both the host and guest operating systems lock up and become unusable. (Don’t say I didn’t warn you) Lastly, you can split the files into 2GB sizes, while this isn’t necessary, it just makes all the disks much easier to manage and move around. For this step we want to set our disk size (12 GB in this case), I chose not to allocate the disk space right now (the machine has a 300 GB drive and has only 20 GB on it) and Split disk into 2 GB files.
Step 8: Name the disk file
This is actually pretty simple in that you decide what you want to physically call the disk and where to put it. .vmdk is the extension for VMWare virtual disks. After we name the disk we can select the “Finish” button which adds the disk to the virtual machine.
Step 9: Ensure new disk exists
So now we can see that the new disk has been added to the “Virtual Machine Settings” within the selected virtual machine. From here the disk acts just like it would if you added a new disk to a standalone server. So we select the “OK” button to continue.
Step 10: Boot the virtual machine
From here we just start the virtual machine like we would normally, either by selecting the button on the toolbar or selecting the “Start this virtual machine” link.
Step 11: Virtual machine start up
The machine boots normally as it would any other time.
Step 12: Create the Partition
After we’ve logged in and accessed a terminal window as root (or another user with root/sudo privs) we first want to run fdisk on the newly created drive. In Linux the first SCSI drive is sda, the second sdb, the third sdc, etc. since this was the second SCSI drive we added to the system, the device is known as /dev/sdb
The first command we want to run is
fdisk /dev/sdb (NOTE: Thanks to everyone that caught my typo here) this utility works very much like the DOS utility of the old days and allows you to create and manage partitions. To create a new partition we enter the command
n to create a new partition. This is going to be a primary partition
p, and the first partition number
1. Because I want this disk to consume the full 12 GB I specified earlier we start at the first cylinder and end it at the last cylinder. We then want to write the partition table with the new partition we have just created so we enter the command
w which writes the new table and exits fdisk.
Step 13: Format the partition
Now that we’ve create the partition, we now want to format the first with the new file system. I’ve decided to use ext3 filesystem for this disk, ext3 provides all the features of the classic ext2 file system plus journaling which helps to prevent disk corruption in the event of an improper shutdown and speeds up the recovery process. For a good overview of Linux standard file systems check out this article: http://linux.org.mt/article/filesystems So, to format the new partition we enter the command
mkfs -t ext3 /dev/sdb1. This command makes a new files system with the type
t ext3 on the
/dev/sdb1 partition, this is the first partition on the sdb disk.
Step 14: Create the mount point
Determine where you want to add the new virtual disk you’ve created. I like to create a partition specifically for all the software I install after the basic Linux install called
/software to do that we run
mkdir /software, just a simple make directory command. Once that is complete we then want to mount the newly created partition. Because we haven’t added the partition to the
/etc/fstab yet we have to mount it manually. To do that we run
mount -t ext3 /dev/sdb1 /software. To break down this command we run mount with the ext3 filesystem type, the partition /dev/sdb1 to the directory /software. Pretty simple and straight forward. To check that the partition is properly mounted we run
df -k which shows us the mounted partitions and the amount of available space.
Step 15: Open the fstab file
The fstab file holds all of the used disks and partitions, and determines how they are supposed to be used by the operating system. So we edit the file to add the newly created partition
Step 16: Modify the fstab for the new partition
After we open the fstab file in the previous step we add the following line:
/dev/sdb1 /software ext3 defaults 1 1
The first column is the partition name, the second is the default mount point, the third is the filesystem type. The fourth is the mount options, in this case I used default which mounts the drive rw, suid, dev, exec, auto, nouser and asynchronous. The 5th and 6th options are for the dump and fsck options. If dump is set to 1 the filesystem is marked to be backed up, if you are going to have sensitive material on the drive its a good idea to set it to 1. If fsck is set to greater than 1, then the operating system uses the number to determine in what order fsck should be run during start up. If it is set to 0 it will be ignored such as in the case of a cdrom drive since its a solid state disk. For more information on the fstab file check out this article: http://www.tuxfiles.org/linuxhelp/fstab.html
Lastly, we write and quit the file with the :wq command.
So now that the fstab has been written the drive will be mounted and unmounted when the machine is either started or shutdown. So there you have it, the quick and dirty process for adding a brand new disk to a virtual machine.
The example files below are relevant for an Oracle installation and instance with the following values.
The listerner.ora file contains server side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” directory on the server. Here is an example of a listener.ora file from Windows 2000.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORCL.WORLD) (ORACLE_HOME = /u01/app/oracle/product/9.2.0) (SID_NAME = ORCL) ) )
After the “listener.ora” file is amended the listener should be restarted or reloaded to allow the new configuation to take effect.
C:> lsnrctl stop C:> lsnrctl start C:> lsnrctl reload
The “tnsnames.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” or “$ORACLE_HOME/net80/admin” directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a “tnsnames.ora” file.
ORCL.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL.WORLD) ) )
The “sqlnet.ora” file contains client side network configuration parameters. It can be found in the “$ORACLE_HOME/network/admin” or “$ORACLE_HOME/net80/admin” directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of an “sqlnet.ora” file.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) NAMES.DEFAULT_DOMAIN = WORLD # The following entry is necessary on Windows if OS authentication is required. SQLNET.AUTHENTICATION_SERVICES= (NTS)
Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (ORCL).
$ sqlplus scott/tiger@orcl
Please open the below file.
Please check the setting of “Account Expiration and Locking” in Administration > Manage Service > Set Workspace Preferences.
DB File Sequential Read Wait Event
This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.
Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as ‘db file sequential read’.
Check the following V$SESSION_WAIT parameter columns:
- P1 – The absolute data file number
- P2 – The block being read
- P3 – The number of blocks (should be 1)
A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read).
DB File Scattered Read Wait Event
db file scattered read is a wait event. It’s a multiblock read into many discontinuous SGA buffers
This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations.
A scattered read is usually a multiblock read.
It can occur for a fast full scan (of an index) in addition to a full table scan.
The db file scattered read wait event identifies that a full scan is occurring.
When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other.
Such reads are called scattered read calls, because the blocks are scattered throughout memory.
Solution: Increase the size of Buffer Cache.
RMAN DUPLICATE command creates duplicate database or clone from backups of the target database while retaining the original target database. The cloned database is a identical copy or original database.
The duplicate database can be identical to the target database or contain only a subset of the table-spaces in the target database. The target site and the duplicate site can be on separate hosts or on the same host.
The Duplicate DB can run independently and used for several reasons for example:
- Test backup and recovery procedures
- Export data such as a table that was inadvertently dropped from the production database, and then import it back into the production database
- cloned database can be used to transfer from production server to testing server or moving cloned database from one location to another.
1. Duplicate and Standby databases
- A duplicate database is distinct from a standby database, both are created with the DUPLICATE command.
- A standby DB is a copy of the primary database that continually update with archived logs from the primary DB.
- If the primary database is damaged or destroyed, then you can perform fail-over to the standby database and transform it into the new primary database.
- While duplicate database is not intended for fail-over scenarios and does not support the various standby recovery and fail-over options.
2. Process of Duplication
- Creates a control file for the duplicate database
- Restores the target data-files into the duplicate database and performs incomplete recovery using all available archived log and incremental backups.
- Shuts down and starts the auxiliary instance
- Opens the duplicate DB with the RESETLOGS option after incomplete recovery to create the online redo logs.
- Generates a new, unique database identifier for the duplicate database
During duplication, RMAN must perform incomplete recovery because the online redo logs in the target are not backed up and cannot be applied to the duplicate database. The farthest that RMAN can go in recovery of the duplicate database is the most recent redo log archived by the target database.
3. Creating a Duplicate Database with RMAN:
- You must connect RMAN to both the target (primary) database and an auxiliary instance started in NOMOUNT mode
- Allocate at least one auxiliary channel on the auxiliary instance.
- This starts a server session on the duplicate host.
- This channel then restores the necessary backups of the primary database and initiates recovery
4. Backup and archive log required for Duplicate:
- Last full backup and archive log are required for duplication.
- Parameters file for starting new instance.
- RMAN client can run on any host. All backups and archived logs, must be accessible on duplicate host
- If the duplicate host is not the same as the target host, then copy backups and archive log to duplicate host with the same full path as in the primary database. Two options can be used
- Manually transfer files from the primary host to the remote host to an identical path.
- Manually transfer the backups from the primary host to the duplicate host at a new location. Run the CATALOG command to add these copies to the RMAN repository at the duplicate host.
Steps to perform cloning:
STEP – 0 Installation of Oracle Database software on duplicate host
Install oracle database software on duplicate host if required where you want to create duplicate database. For example
|Screen 1: Type of installation = Enterprise Edition
Screen 2: HOME = C:\oracle\product\10.2.0\db_1
Screen 3: No Action
Screen 4: Install DATABASE software only
Screen 5: Click Install
STEP – 1 Create an Oracle Password File for the Auxiliary Instance
Create a password file on duplicate host using password file utility. This password file will be used in creation of new auxiliary instance.
Orapwd file=C:\oracle\product\10.2.0\db_1\DATABASE\PWDLHDUP.ora password=DUP
STEP – 2 Create an Initialization Parameter File for the Auxiliary Instance
Use following list of commands to create parameters file using target DB. This file will be used in creation of new auxiliary instance.
Login to Target DB
|SQL > SYS/****@LHR AS SYSDB
SQL > Create pfile=’C:\initLHDUP.ora’ from spfile;
The file will be created on target host copy or move newly created file to duplicate host following location.
Modify parameters. All path parameter should be accessible on duplicate host.
db_block_size=8192# The block size for the auxiliary database must
match that of the target DB
Create directory structure on duplicate host
Directory structure for target host (will be used to keep backups and archive logs for duplication)
New Directory structure for duplicate host (or use batch file provided)
STEP – 3 Create Auxiliary Instance
ORADIM -NEW -SID LHDUP -intpwd change_on_install -startmode auto
ORADIM -NEW -SID LHDUP -startmode auto -pfile
ORADIM -DELETE -SID LHDUP # to delete existing instance
STEP – 4 Establish Oracle Net Connectivity to the Auxiliary Instance ( on duplicate host)
- Add listener on duplicate host if not already added using “Oracle net configuration assistant” wizard
- Modify Listener.ora file using “Net manager utility“ to add auxiliary instance.
|LISTENER >DATABASE services >ADDDATABASE
Global DATABASE Name = LHDUP
SID = LHDUP
Oracle home directoy = C:\oracle\product\10.2.0\db_1
- Save configuration and exit ( restart listener service)
- Modify tnsnames.ora file to add following services
|LHDUP = # Net connection for Auxiliary instance (required)
(SID = LHDUP) ) )
LHR = #Net connection for target database (required)
(SID = LHDB) ) )
DEVDB = # Net connection for recovery catalog (optional)
(SID = DEVDB)) )
STEP – 5 Start Auxiliary Instance
|C:\> sqlplus “sys/change_on_install@lhdup as sysdba”
SQL> startup nomount;
STEP – 6 Mount or Open the Target Database (optional if target Db is not opened)
|C:\> sqlplus “sys/*****@lhr as sysdba”
SQL> startup open;
STEP – 7 Available Necessary backups and archive logs
Ensure that you have current level 0 backup and level 1 backup and archive logs after level 0 backup.
STEP – 8 Login to Target and Auxiliary instance
RMAN > CONNECT TARGET SYS@LHR
RMAN > CONNECT CATALOG RMANLH/CAT@DEVDB (this IS optional don’t USE)
RMAN > CONNECT AUXILIARY SYS/DUP@LHDUP
STEP – 9 RUN RMAN DUPLICATE command
allocate auxiliary channel ch1 type disk;
duplicate target DATABASETO lhdup