This is a simple step-by-step of how I created my main database in the Oracle 11g server running in a Linux environment. It is by no means comprehensive, but it may serve as a good starting point should you be looking for such a quick guide.
Increase Shared Memory Size
I am running Oracle Enterprise Linux, and on all Linux flavors, the total shared memory (shm) size is limited to 2gb. I am looking for a maximum size of 3gb, so I need to perform the following preparatory steps to set up a memory file system before setting up my database. You may or may not review this section.
[oracle@dbserver ~]$ su - root
Password:
[root@dbserver ~]# nano /etc/fstab
In nano (or your favorite editor), add or modify your entry for “/dev/shm” so that it looks like this:
[root@dbserver ~]# more /etc/fstab
...
tmpfs /dev/shm tmpfs size=3g 0 0
...
At this point, you have the option of either running the following command to increase “/dev/shm” size to 3gb at runtime, or simply restart the server so that the change you have just made in fstab will take effect.
[root@dbserver ~]# mount -t shm tmpfs -o size=3g /dev/shm
Either after running “mount” or after restarting, “df -k” should reveal the following. If so, you are ready to set up the database. Note you should now use the “oracle” user instead of the “root” user.
[oracle@dbserver ~]$ dk -k
Filesystem 1K-blocks Used Available Use% Mounted on
...
tmpfs 3145728 0 3145728 0% /dev/shm
...
Database Configuration Assistant (DBCA)
You may launch DBCA as follows.
[oracle@dbserver ~]$ dbca
Step 1: Operations — Choose “Create a Database”
Step 2: Database Templates — Choose the appropriate template. “General Purpose or Transaction Processing” is likely the one you will select.
Step 3: Database Identification — Set “Global Database Name” to “db_name.yorktel.com” and “SID” to “db_name”
Step 4: Management Options — Check “Configure Enterprise Manager”, check “Enable Alert Notifications”, and configure the SMTP server and email address.
Step 5: Database Credentials — Enter passwords for each system user, or choose “Use the Same Administrative Password for All Accounts” to use only one password.
Step 6: Storage Options — In my environment, I chose “File System”; your system might be different.
Step 7: Database File Locations — Since I chose “File System” in the previous step, in this current step I chose “Use Common Location for All Database Files” and set the path to “/u02/app/oracle/oradata”. Once the database is created, my database files will thus end up in “/u02/app/oracle/oradata/db_name”.
Step 8: Recovery Configuration — Check “Specify Flash Recovery Area” and set the “Flash Recovery Area Size”; in my environment, I use the very large value of “81920” (in mb) because I have the space available to waste. In this step, also check “Enable Archiving”.
Step 9: Database Content — No change necessary in my case
Step 10: Initialization Parameters — Under “Memory” tab, set “Memory Size (SGA and PGA)” to 3072mb (which is 3gb); if you cannot move beyond this step due to the 2gb limitation, please see the first section of this guide. Knowing my environment and the high number of individual user sessions, under “Sizing” tab, I changed the “Processes” parameter to 220 to suit my environment’s needs. I then clicked on “All Initialization Parameters” and “Show Advanced Parameters” to update/confirm the following; you may use the list for reference, but you may need to do different configurations for your environment.
- db_flashback_retention_target: I set it to “5760” for setting up the flashback database later
- db_unique_name: I set this to “unique_db_name”
- memory_max_target: I set this to “3G”
- processes: I confirmed this is “250”
- standby_file_management: I set this to “AUTO”
- undo_management: I set this to “AUTO” for setting up the flashback database later
- undo_retention: I set this to “3600” for setting up the flashback database later
Step 11: Security Settings — No change necessary in my case
Step 12: Automatic Maintenance Tasks — No change necessary in my case
Step 13: Database Storage — You should confirm that the file locations are correct
Step 14: Creation Options — Make appropriate selections and “Finish” the DBCA process
The “Finish” button starts the database creation process.
Database Backup with Enterprise Manager
Once DBCA database creation is completed, the database will be set up and will be available for use. At this point, I logged on to Enterprise Manager to perform some final set up items regarding database backup. Again, this may or may not be fitting with your requirements, but it may present a good reference for you to review. To go into Enterprise Manager (EM), go to the URL “https://dbserver:1158/em/” and log on as sysdba.
Once I got into EM, I went to the “Availability” tab and clicked on “Recovery Settings”. I checked “ARCHIVELOG Mode” and changed the first archive log file destination to a path I prefer, such as “/u02/app/oracle/oradata/flash/db_name”. I checked “Enable Flashback Database” and restarted the database for this to take effect.
After the database was restarted, I logged back into EM (again as sysdba), I returned to “Availability” tab, and clicked on “Schedule Backup”. I clicked on the “Schedule Oracle-Suggested Backup” button and chose “Disk” in step 1 and configured the date and time in step 3. I set it up so that it runs a full database backup regularly during periods that typically sees low usage. The RMAN script hidden behind this GUI is the following.
run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA$OEM_LEVEL_0';
backup incremental level 1 cumulative copies=1 for recover of copy with tag
'ORA$OEM_LEVEL_0' database;
}
Next, again in “Availability”, I clicked on “Manage Current Backups” and scheduled “Crosscheck All”, “Delete All Obsolete”, and “Delete All Expired” jobs so that obsolete and expired backup sets will be removed regularly in free up space for current backup sets; note to schedule it to run regularly, you should choose the “repeating” radio button. The RMAN scripts hidden behind the GUI are as follows.
-- Crosscheck
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;
-- Delete Obsolete
DELETE NOPROMPT OBSOLETE;
-- Delete Expired
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
