OCM Preparation - 3 - Create the Database

Graphically

Documentation Library ->  Administrator's Guide - > Creating and Configuring an Oracle Database -> Creating a Database with DBCA


Command Line

Documentation Library ->  Administrator's Guide - > Creating and Configuring an Oracle Database -> Creating a Database with the CREATE DATABASE Statement

Recommended Method: Create the database with DBCA

OCM Preparation - 2

Following steps will create the basic virtual environment to play with.

Download VMware Server and install it. 
Download Oracle Linux Release 5.4 (32-bit) at Oracle Software Delivery Cloud.
Create a virtual machine in VMware Server and install Oracle Linux. Check this out.
Download 11.2.0.3.0 patch set.
Patch ID=10404530. If you are not sure which files to download, Check this out.

Installation Type	Zip File
Oracle Database (includes Oracle Database and Oracle RAC)	p10404530_112030_platform_1of7.zip p10404530_112030_platform_2of7.zip
Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)	p10404530_112030_platform_3of7.zip
Oracle Database Client	p10404530_112030_platform_4of7.zip
Oracle Gateways	p10404530_112030_platform_5of7.zip
Oracle Examples	p10404530_112030_platform_6of7.zip
Deinstall	p10404530_112030_platform_7of7.zip

Install Oracle Database 11g Enterprise Edition Release 11.2.0.3.0  32-bit (Software Only).
Installation Guides: Oracle Oracle-Base

 Notes:
VNC Server is used.
Resize /dev/shm more than the value you want for MEMORY_MAX_TARGET.
mount -t tmpfs shmfs -o size=1.1G /dev/shm
To change tmpfs value permanently, change /etc/fstab as follows.

#tmpfs /dev/shm tmpfs defaults 0 0
tmpfs /dev/shm tmpfs size=1g 0 0

OCM Preparation - 1

Exam Environment

• Oracle Linux Release 5.4 (32-bit)
• Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 (32-bit)
• Oracle Enterprise Manager 11g Grid Control Release 1 (11.1.0.1.0)  (32-bit)
• Non-Searchable Documentation

Exam Topics

Server Configuration

• Create the database
• Determine and set sizing parameters for database structures
• Create and manage temporary, permanent, and undo tablespaces
• Stripe data files across multiple physical devices and locations
• Configure the database environment to support optimal data access performance
• Create and manage database configuration files
• Create and manage bigfile tablespaces
• Create and Manage a tablespace that uses NFS mounted file system file  
• Create and manage multiple network configuration files
• Create and configure a listener
• Configure the database instance to support shared server connections
• Set up network tracing
• Manage Oracle network processes  
• Configure the network environment to allow connections to multiple databases
• Use configurationless connections
• Use OPatch to install a patch
• Use Grid Infrastructure to manage oracle databases and other resources
• Use Enterprise Manager Configuration Assistant(EMCA) utility

Enterprise Manager Grid Control

• Install and Patch Enterprise Manager Grid Control software
• Configure the Enterprise Manager repository
• Create Enterprise Manager Grid Control users
• Use Enterprise Manager to modify a database configuration
• Configure Enterprise Manager to modify database availability
• Create and manage jobs
• Create and monitor alerts
• Create notifications
• Implement Grid Control and Database Control
• Choose the appropriate tablespace type for the intended use
• Create Scheduler jobs
• Create schedules
• Assign jobs to windows
• Create programs
• Create job classes
• Install the Enterprise Manager Grid Control infrastructure
• Deploy Enterprise Manager Grid Control agents
• Configure Grid Control for business requirements

Managing Database Availability

• Mantain recovery catalogs
• Configure Recovery Manager
• Use Recovery Manager to perform database backups
• Use Recover Manager to perform complete database restore and recovery operations
• Configure RMAN
• Create different types of RMAN backups to cater for different performance and retention requirements
• Set Flashback Database parameters
• Configure a Fast Recovery Area
• Perform various recovery operations using Flashback technology

Data Management

• Manage Materialized Views to improve rewrite and refresh performance
• Configure and manage distributed materialized views
• Create and Manage encrypted tablespaces
• Manage Transport of tablespaces across platforms
• Configure a schema to support a star transformation query
• Administer external tables
• Implement Data Pump export and import jobs for data transfer
• Implement Data Pump to and from remote databases  
• Configure and use parallel execution for queries
• Use SQL*Loader
• Administer, manage and tune parallel execution

Data Warehouse Management  

• Administer partitioned tables and indexes using appropriate methods and keys 
• Perform partition maintenance operations  
• Maintain indexes on a pa rtitioned table
• Implement securefile LOB
• Create and manage LOB segments  
• Implement fine-grained access control  
• Create and manage contexts
• Administer flashback data archive and schema evolution

Performance Management  

• Administer Resource Manager 
• Use Result Cache 
• Use multi column statistics 
• Gather statistics on a specific table without invalidating cursors 
• Use partitioned indexes 
• Administer and tune schema object to support various access methods 
• Interpret execution plan  
• Use SQL tuning tools and features 
• Use SQL Tuning Advisor 
• Use SQL Access Advisor 
• Use SQL Performance Analyzer
• Configure baseline templates  
• Use SQL Plan Management feature
• Implement instance caging 

Grid Infrastructure and ASM

• Install Oracle Grid Infrastructure 
• Create ASM Disk Groups 
• Create and manage as ASM instance
• Implement ASM failure groups
• Creating ACFS File System   
• Start, stop, configure and administer Oracle Grid Infrastructure

Real Application Clusters  

• Install the Oracle Database 11gR2 software
• Configure ASM for the shared disks and create a clustered database   
• Configure archiving  
• Configure services using both Manual and Policy Managed methods 

Data Guard  

• Create Physical Standby Database with real-time apply. 
• Configure the data guard environment to reduce overheads of fast incremental backups on the primary database 
• Configure the Observer 
• Switchover and switch back  
• Configure connect time failover
• Convert the standby to a snapshot standby 
• Configure archivelog deletion policy for the Dataguard configuration

This will be used as the base documentation for this series, hereafter.

Get distinct datafile paths.

Code:

select distinct substr(file_name,1,instr(file_name,'/',-1)) from dba_data_files;

List tables which have specific column with number of rows for a given requirement.

select q'[select ']'||table_name||q'[' as table_name, count(*) from ]'||table_name||q'[ where COL1='VAL1';]' from dba_tab_columns where column_name='COL1' and upper(owner) ='USER';

Exclude specific tables from expdp

Here is the command

expdp user/password@sid schemas=schema_name include=TABLE:"IN ('TABLE1', 'TABLE2')" 

directory=DATAPUMP_DIR dumpfile=user_date.dmp logfile=user_date.log

Some operating systems need special characters to be escaped

include=TABLE:\"IN (\'TABLE1\', \'TABLE2\')"

Or

include=TABLE:\"IN \(\'TABLE1\', \'TABLE2\'\)"
  

Update a table column using a sequence

create table duplicate as select * from original
alter table duplicate add ID number;
create sequence seq1

set pagesize 0

set linesize 200

spool update.sql
select 'update duplicate set ID='||seq1.nextval||q'[ where rowid=']'||rowid||q'[';]' from duplicate;

spool off

>@update.sql