8. Data Guard
ocm8  Use Data Guard Manager and DGMGRL
https://shenh.tripod.com/Oracleblog/index.blog/1825057/ocm8-use-data-guard-manager-and-dgmgrl/

Dataguard Reference:

http://www.ahmedbaraka.com/computer/docs/oracle_10g_dg_1.0.pdf

http://www.oracle.com/technology/deploy/availability/pdf/TWP_DataGuard_10gR2.pdf

http://static7.userland.com/oracle/gems/alejandroVargas/DataGuardPhysicalStandbystep.pdf

http://blogs.oracle.com/alejandroVargas/gems/DataGuardBrokerandobserverst.pdf

http://static7.userland.com/oracle/gems/alejandroVargas/DataGuardBrokerandobserverst.pdf

http://blogs.oracle.com/AlejandroVargas/

http://blogs.oracle.com/AlejandroVargas/gems/

http://blogs.oracle.com/AlejandroVargas/newsItems/viewFullItem$251/

ocm8: Use Data Guard Manager and DGMGRL

dataguard db:
DGMGRL


grid control
1): Database List: Type --> Database Instance: Primary | Physical Standby

2): Instance Detail Pages
  High Availability    

3): --> maintenance --> High Availability --> Data Guard

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

. oenv sid

dgmgrl
> CONNECT /

>help
>help show
> SHOW CONFIGURATION;
> SHOW CONFIGURATION VERBOSE;


> SHOW DATABASE db_unique_name;
> SHOW DATABASE VERBOSE db_unique_name ;
-- database name from show configuration

> SHOW DATABASE sid_stdg1;
> SHOW DATABASE verbose sid_stdg1;

> SHOW DATABASE sid_stdg2;
> SHOW DATABASE verbose sid_stdg2;

> SHOW INSTANCE instance_name_from_database_name;
-- instance name from show database

> show instance sid  on database sid_stdg1;
> show instance verbose sid  on database sid_stdg1;

> show instance sid  on database sid_stdg2;
> show instance verbose sid  on database sid_stdg2;

show instance verbose sid  on database sid_prd;

> SHOW INSTANCE VERBOSE;

sqlplus:
show parameter  service_names
show parameter  db_unique_name
show parameter  db_name
show parameter  db_domain
show parameter  DG_BROKER_START
show parameter  DG_BROKER_CONFIG
show parameter  log_archive_config
show parameter  log_archive_dest
show parameter  fal_server
show parameter  fal_client
show parameter  standby_file_management
show parameter  db_file_name_convert
show parameter  log_file_name_convert
show parameter  db_create_file_dest
show parameter  remote_login_passwordfile

select host_name, status, DATABASE_STATUS, INSTANCE_ROLE from v$instance;

select name, DB_UNIQUE_NAME,
DATABASE_ROLE,
GUARD_STATUS, DATAGUARD_BROKER,
SWITCHOVER_STATUS,
OPEN_MODE, LOG_MODE,
PROTECTION_MODE, PROTECTION_LEVEL
from v$database; 

Configuration of 10g Data Guard Broker and Observer for Switchover
Alejandro Vargas
Principal Support Consultant
Oracle Advanced Support Services

Configuring Data Guard Broker for Switchover, General Review. ......2
The Enviroment...2
Implementation notes:...................................2
Step by Step Implementation of Data Guard Broker................3
Enable Data Guard Broker Start on the Primary and Standby databases ................................3
Setup the Local_Listener parameter on both the Primary and Standby databases..................3
Setup the tnsnames to enable communication with both the Primary and Standby databases3
Check and edit database properties .............8
Enabling the configuration and databases..10
Enabling Fast Start Failover and the Observer ..................12
Ensure standby redo logs are configured on all databases ......12
Ensure the LogXptMode Property is set to SYNC .........13
Specify the FastStartFailoverTarget property ................14
Upgrade the protection mode to MAXAVAILABILITY, if necessary. ...................................14
Enable Flashback Database on the Primary and Standby databases. ...............................14
Enable fast start failover..........................16
Start the observer....................................17
Verify the fast-start failover configuration18
Perform a Switchover..............................19
Reference:.........23

Configuring Data Guard Broker for Switchover, General Review.
on this document I'm adding how to configure the broker and observer, setup the database to Maximum Availability and
managing switchover from Data Guard Manager, DGMGRL.

Data Guard Broker permit to manage a Data Guard Configuration, from both the Enterprise Manager Grid Control console, or from a
terminal in command line mode. In this document I will explore command line mode.

Pre requisites include the use of 10g Oracle server, using spfile on both the primary and standby and a third server for the Observer,
and configure the listeners to include a service for the Data Guard Broker.

The Enviroment

• 2 Linux servers, Oracle Distribution 2.6.9-55 EL i686 i386 GNU/Linux, the Primary and Standby databases are located on these
servers.
• 1 Linux server, RH Linux 2.6.9-42.ELsmp x86_64 GNU/Linux, The Data Guard Broker Observer is located on this server
• Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
ssh is configured for user oracle on both nodes
• Oracle Home is on identical path on both nodes
• Primary database WHITEOWL
• Standby database BLACKOWL

Implementation notes:
Once you have your primary and standby databases up and running these are the steps to follow:

1. Enable Data Guard Broker Start on the Primary and Standby databases.
2. Setup the Local_Listener parameter if the listener port is not the standard 1521, on both the Primary and Standby databases.
3. Setup the listener and tnsnames to enable communication with both the Primary and Standby databases.
4. Setup the Broker configuration files
5. Check and edit properties
6. Enable the configuration and databases
7. Enable Fast Start Failover and the Observer

Step by Step Implementation of Data Guard Broker

Enable Data Guard Broker Start on the Primary and Standby databases
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

Setup the Local_Listener parameter on both the Primary and Standby databases
SQL> ALTER SYSTEM SET LOCAL_LISTENER='LISTENER_VMRACTEST' SCOPE=BOTH;

Setup the tnsnames to enable communication with both the Primary and Standby databases

The listener.ora should include a service named global_db_nameDGMGRL to enable the broker to start the databases on the event of
switchover. This configuration needs to be included on both servers.
Listener.ora on Node 1
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_VMRACTEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = whiteowl)
(ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
(SID_NAME = whiteowl)
)
(SID_DESC =
(SID_NAME=whiteowl)
(GLOBAL_DBNAME = whiteowl_DGMGRL)
(ORACLE_HOME = /oradisk/app01/oracle/product/10gDB )
)
)
Listener.ora on Node 2
LISTENER_VMRACTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)

Tnsnames.ora on Node 1, 2 and the observer node
whiteowl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = whiteowl_DGMGRL)
)
)
blackowl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = blackowl_DGMGRL)
)
)

Setup the Broker configuration files

The broker configuration files are automatically created when the broker is started using
ALTER SYSTEM SET DG_BROKER_START=TRUE.

The default destination can be modified using the parameters
DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 On Primary:

SQL> show parameters DG_BROKER_CONFIG
NAME TYPE VALUE
---------------- ----------- ------------------------------
dg_broker_config_file1 string /oradisk/app01/oracle/product/
10gDB/dbs/dr1whiteowl.dat
dg_broker_config_file2 string /oradisk/app01/oracle/product/
10gDB/dbs/dr2whiteowl.dat

On Standby:

SQL> show parameters DG_BROKER_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /oradisk/app01/oracle/product/
10gDB/dbs/dr1blackowl.dat

dg_broker_config_file2 string /oradisk/app01/oracle/product/
10gDB/dbs/dr2blackowl.dat

Next create from within the DGMGRL the configuration
[vmractest1] > dgmgrl
DGMGRL> CONNECT sys/oracle@whiteowl

DGMGRL> CREATE CONFIGURATION WHITEOWL AS
> PRIMARY DATABASE IS whiteowl
> CONNECT IDENTIFIER IS whiteowl;

Configuration "whiteowl" created with primary database "whiteowl"
Add the standby to the configuration and check it

DGMGRL> ADD DATABASE blackowl AS
> CONNECT IDENTIFIER IS blackowl
> MAINTAINED AS PHYSICAL;

Database "blackowl" added
DGMGRL> SHOW CONFIGURATION;

Configuration
Name: whiteowl
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
whiteowl - Primary database
blackowl - Physical standby database
Current status for "whiteowl":
DISABLED
Check and edit database properties
In this case parameter DbFileNameConvert has an error, it lacks the leading backslash:

DGMGRL> SHOW DATABASE VERBOSE blackowl;
Database
Name: blackowl
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
blackowl
Properties:
InitialConnectIdentifier = 'blackowl'
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/, /oradisk/od01/BLACKOWL/datafile/'
LogFileNameConvert = '/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/, /oradisk/od01/BLACKOWL/onlinelog/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'vmractest2.partnergsm.co.il'
SidName = 'blackowl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=vmractest2)(PORT=1522)(IP=FIRST))'
StandbyArchiveLocation = '/oradisk/od01/BLACKOWL/archives/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "blackowl":
DISABLED

DGMGRL> EDIT DATABASE "blackowl" SET PROPERTY 'DbFileNameConvert' =
'/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/, /oradisk/od01/BLACKOWL/datafile/';

Property "DbFileNameConvert" updated

The parameter DbFileNameConvert will be updated on the database also on the next restart.
Enabling the configuration and databases

DGMGRL> enable configuration;
DGMGRL> show configuration;

Configuration
Name: whiteowl
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
whiteowl - Primary database
blackowl - Physical standby database
Current status for "whiteowl":
SUCCESS

DGMGRL> enable database blackowl;
DGMGRL> SHOW DATABASE VERBOSE blackowl;

Database
Name: blackowl
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
blackowl
Properties:
InitialConnectIdentifier = 'blackowl'
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/ , /oradisk/od01/BLACKOWL/datafile/'
LogFileNameConvert = '/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/, /oradisk/od01/BLACKOWL/onlinelog/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'vmractest2.partnergsm.co.il'
SidName = 'blackowl'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=vmractest2)(PORT=1522)(IP=FIRST))'
StandbyArchiveLocation = '/oradisk/od01/BLACKOWL/archives/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "blackowl":
SUCCESS

Enabling Fast Start Failover and the Observer
These are the steps required to enable and check Fast Start Failover and the Observer:
1. Ensure standby redo logs are configured on all databases.
2. Ensure the LogXptMode Property is set to SYNC.
3. Specify the FastStartFailoverTarget property.
4. Upgrade the protection mode to MAXAVAILABILITY, if necessary.
5. Enable Flashback Database on the primary and standby databases, if necessary.
6. Enable fast start failover.
7. Start the observer.
8. Verify the fast-start failover configuration.
9. Performing a Switchover Operation

Ensure standby redo logs are configured on all databases

This database is using Oracle Managed files so it is not necessary to pass a member name, i.e.:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;

SQL> set lines 120
SQL> col member for a80
SQL> select type,member from v$logfile order by type
2 /
TYPE MEMBER
------- --------------------------------------------------------------------------------
ONLINE /vmasmtest/whiteowl/recovery/WHITEOWL/onlinelog/o1_mf_2_3hkno95x_.log
ONLINE /vmasmtest/whiteowl/recovery/WHITEOWL/onlinelog/o1_mf_1_3hkno7jb_.log
ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_3hkno747_.log
ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_3hknoby2_.log
ONLINE /vmasmtest/whiteowl/recovery/WHITEOWL/onlinelog/o1_mf_3_3hknocmw_.log
ONLINE /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_3hkno8rq_.log
STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log
STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log
STANDBY /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log
9 rows selected.

Ensure the LogXptMode Property is set to SYNC
These commands will succeed only if database is configured with standby redo logs.

DGMGRL> connect sys/oracle@whiteowl

DGMGRL> EDIT DATABASE whiteowl SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> EDIT DATABASE blackowl SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

Specify the FastStartFailoverTarget property
DGMGRL> EDIT DATABASE whiteowl SET PROPERTY FastStartFailoverTarget='blackowl';
Property "faststartfailovertarget" updated

DGMGRL> EDIT DATABASE blackowl SET PROPERTY FastStartFailoverTarget='whiteowl';
Property "faststartfailovertarget" updated

Upgrade the protection mode to MAXAVAILABILITY, if necessary.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Operation requires shutdown of instance "whiteowl" on database "whiteowl"
Shutting down instance "whiteowl"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "whiteowl" on database "whiteowl"
Starting instance "whiteowl"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "whiteowl"
You must start instance "whiteowl" manually

Enable Flashback Database on the Primary and Standby databases.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;

On Both databases
To enter the standby into Flashback mode you must shutdown the both databases, then while the primary is down execute the
following commands on the standby:

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;
SQL> startup mount;
SQL> ALTER DATABASE FLASHBACK ON;

Enable fast start failover

[vmractest1] > dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@whiteowl

DGMGRL> SHOW CONFIGURATION;
Configuration
Name: whiteowl
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
whiteowl - Primary database
blackowl - Physical standby database
Current status for "whiteowl":
SUCCESS

DGMGRL> SHOW DATABASE blackowl;
Database
Name: blackowl
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
blackowl
Current status for "blackowl":
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

DGMGRL> SHOW CONFIGURATION;
Configuration
Name: whiteowl
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
whiteowl - Primary database
blackowl - Physical standby database
- Fast-Start Failover target
Current status for "whiteowl":
Start the observer
Start the observer from a third server on background. You may use a script like this:

---------------- script start on next line --------------------
#!/bin/ksh
# startobserver
export ORACLE_BASE=/vmractst3/app01/oracle
export ORACLE_HOME=/vmractst3/app01/oracle/product/10.2
export
BASE_PATH=/vmractst3/app01/oracle/scripts/general:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/loc
al/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH

dgmgrl << eof
connect sys/oracle@whiteowl
START OBSERVER;
eof

---------------- script end on previous line --------------------
[vmractest3] > nohup ./startobserver &
[2] 22182
<oracle> /vmractst3/app01/oracle/product/10.2/dbs > DGMGRL for Linux: Version 10.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> Connected.

DGMGRL> Observer started
Verify the fast-start failover configuration.

DGMGRL> SHOW CONFIGURATION VERBOSE;
Configuration
Name: whiteowl
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
whiteowl - Primary database
blackowl - Physical standby database
- Fast-Start Failover target
Fast-Start Failover
Threshold: 30 seconds
Observer: vmractest3
Current status for "whiteowl":
SUCCESS

DGMGRL> SHOW DATABASE blackowl;
Database
Name: blackowl
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
blackowl
Current status for "blackowl":
SUCCESS

Perform a Switchover

Connect to DGMGRL on the observer server:
ORA Environement Variables:
ORACLE_BASE=/vmractst3/app01/oracle
ORACLE_HOME=/ vmractst 3/app01/oracle/product/10.2

[vmractst3] > dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@whiteowl
DGMGRL>
Check that primary and standby are healthy
This check must return 'SUCCESS' as the status for both databases,
otherwise it means there is a configuration problem.

DGMGRL> show database whiteowl;
Database
Name: whiteowl
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
whiteowl
Current status for "whiteowl":
SUCCESS

DGMGRL> show database blackowl;
Database
Name: blackowl
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
blackowl
Current status for "blackowl":
SUCCESS
Execute the switchover

DGMGRL> SWITCHOVER TO blackowl;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "whiteowl" on database "whiteowl"
Shutting down instance "whiteowl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "blackowl" on database "blackowl"
Shutting down instance "blackowl"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "whiteowl" on database "whiteowl"
Starting instance "whiteowl"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "blackowl" on database "blackowl"
Starting instance "blackowl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "blackowl"

DGMGRL> show database blackowl
Database
Name: blackowl
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
blackowl
Current status for "blackowl":
SUCCESS

DGMGRL> show database whiteowl
Database
Name: whiteowl
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
whiteowl
Current status for "whiteowl":
SUCCESS

Reference:
Oracle® Data Guard Concepts and Administration
10g Release 2 (10.2)
Part Number B14239-04
http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#BGBFEAFG 

ocm8  Create and utilize a physical standby database
https://shenh.tripod.com/Oracleblog/index.blog/1825058/ocm8-create-and-utilize-a-physical-standby-database/

http://www.itpub.net/636810,1.html

alter database force logging;

alter database force nologging;

dataguard manager: primary database must use spfile;

alter database create standby controlfile as '\...\standyctf.con';

init.ora for standby db
db_file_name_covert
log_file_name_convert
log_archive_format
standby_file_management=auto vs. manual
standby_archive_dest
log_archive_start=true vs. manual
remote_archive_enable=true false send receive

on standby db:
startup mountpfile-stby_init.ora
alter database add standby logfile ( ..., ... ) size 500K;
alter database recover managed standy database disconnect;

on primary db:
alter system set log_archive_dest_2='service=standby mandatory';
alter system set log_archive_dest_stat_2=enable defer reset alternate;

archive_lag_target=1800 ( seconds ) max wait for a log switch to occur

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

ocm8  Create and utilize a logical standby database
https://shenh.tripod.com/Oracleblog/index.blog/1825059/ocm8-create-and-utilize-a-logical-standby-database/

249594.1
Dataguard Configuration: LOG_ARCHIVE_CONFIG and VALID_FOR in Oracle 10G
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=249594.1

http://vongates.itpub.net/post/2553/243187

arch vs. lgwr
sync vs. async ( lgwr only )
affirm vs. noaffirm

alter database set standby to maximize protection;
( must use sync, must use standby redo logs )


alter database set standby to maximize performance;

alter database set standby to maximize availibility;

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

*.DB_UNIQUE_NAME=10gstandby

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(10gpri,10gstandby)'

*.LOG_ARCHIVE_DEST_1='LOCATION=/archlog2/10gstandby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'

*.LOG_ARCHIVE_DEST_2='SERVICE=dbpri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

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

*.FAL_SERVER=dbpri
*.FAL_CLIENT=dbstandby

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

*.DB_FILE_NAME_CONVERT=
'/data1/powererp/','/data13/powererp/',
'/data2/powererp/','/data13/powererp/',
'/data3/powererp/','/data13/powererp/',
'/data4/powererp/','/data14/powererp/',
'/data5/powererp/','/data14/powererp/',
'/data6/powererp/','/data14/powererp/',
'/data1/olddata/','/data13/olddata/data1/',
'/data2/olddata/','/data13/olddata/data2/',
'/data3/olddata/','/data13/olddata/data3/',
'/data4/olddata/','/data14/olddata/data4/',
'/data5/olddata/','/data14/olddata/data5/',
'/data6/olddata/','/data14/olddata/data6/',
'/data1/oradata/ora8/','/data13/oradata/ora8/',
'/data2/oradata/ora8/','/data13/oradata/ora8/',
'/data3/oradata/ora8/','/data14/oradata/ora8/',
'/data1/timms/','/data13/timms/',
'/data6/statspack/','/data14/statspack/'

*.LOG_FILE_NAME_CONVERT=
'/archlog/10gpri/','/archlog2/10gstandby/'

*.STANDBY_FILE_MANAGEMENT=AUTO

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

ocm8  Configure the network environment to allow communication between the standby database and the primary database
https://shenh.tripod.com/Oracleblog/index.blog/1825061/ocm8-configure-the-network-environment-to-allow-communication-between-the-standby-database-and-the-primary-database/

global_db_name_dgrmgrl.domain 

configure static double service names: 

ocm8  Open the physical standby database in a "ready-only" state
https://shenh.tripod.com/Oracleblog/index.blog/1825062/ocm8-open-the-physical-standby-database-in-a-readyonly-state/

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10823/manage_ps.htm#1017113

http://www.databasejournal.com/features/oracle/article.php/3682421

1):
SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby
SQL> atler database mount standby database;

2):
SQL> recover standby database;
AUTO

SQL> alter database open read only;

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

put it back in normal standby mount state

SQL> shutdown immediate;
SQL> startup nomount pfile=/export/home/oracle/admin/SEAN/pfile/initSEAN.standby
SQL> atler database mount standby database;

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

ocm8 Perform a switchover operation and a failover operation
https://shenh.tripod.com/Oracleblog/index.blog/1825063/ocm8-perform-a-switchover-operation-and-a-failover-operation/

1): switchover
sql>alter database commit to switchover to physical standby;
sql>alter database commit to switchover to logical standby;

primary to standby:
a): ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;

b): ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

standby to primary:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN WAIT;
SQL> SHUTDOWN;
SQL> STARTUP;


2): failover

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY
DATABASE;

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY
DATABASE;

Failover for Physical Standby Databases Using SQL
To perform failover for physical standby databases using SQL:

1. Initiate the failover operation on the standby database. If a failure occurs at the
primary database, a failover allows you to salvage incomplete standby redo logs
using the following statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
FINISH;

2. Convert the physical standby database to the primary role. You must now change the
standby database into the new primary database by issuing the following statement:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Register the missing redo logs. Once the archived standby redo logs have been
received and recovered on all destinations, the other standby databases are ready to
receive redo logs from the new primary database. To register the copied archived
redo logs, issue the following statement on each standby database:
ALTER DATABASE REGISTER
LOGFILE ’/standby/arch_dest/arch_1_101.arc’;

ocm8  Implement Data Guard
https://shenh.tripod.com/Oracleblog/index.blog/1825064/ocm8-implement-data-guard/

Posted by shenh at 11:33 PM EST

Updated: Wednesday, 7 January 2009 12:34 AM EST

Share This Post

Permalink

7. Real Application Clusters
ocm7  Install Oracle Clusterware
https://shenh.tripod.com/Oracleblog/index.blog/1825049/ocm7-install-cluster-ready-services/ 

./runclufy.sh stage -pre crsinst -n node1,node2 -orainv dba

installing crs:
cd /u01/stage/10gR2/clusterware

./runInstaller

...............................

RAC patch for 10.2.4:

run the following script:
  CRS_home/install/root102.sh 

...............................

sh orainstRoot.sh
sh root.sh

...............................

./runcluvfy.sh stage -post crsinst -n node1,node2

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

 10gr1:

/etc/init.d/init.crs start #start

ocm7  Install the Oracle Database 10g software
https://shenh.tripod.com/Oracleblog/index.blog/1825050/ocm7-install-the-oracle-database-10g-software/ 

installing db software:
./runcluvfy.sh stage -pre dbinst -n node1,node2 -osdba dba

cd /u01/stage/10gR2/
./runInstaller

.. ..............................

root.sh

-- ------------------------------
create db:
./runcluvfy.sh stage -pre dbcfg -n node1,node2 -d /u01/app/oracle/product/10g
./dbca &

ocm7  Implement ASM failure groups
https://shenh.tripod.com/Oracleblog/index.blog/1825051/ocm7-implement-asm-failure-groups/

external: 1 disk

normal: 2 disks

high: 3 disks

http://www.globusz.com/ebooks/Oracle/00000020.htm

ocm7  Create and manage as ASM instance
https://shenh.tripod.com/Oracleblog/index.blog/1825052/ocm7-create-and-manage-as-asm-instance/ 

1): using dbca to create an asm instance 

AU: Allocation Unit ( 1M default, uniform for whole ASM DB in 10g, can vary in 11g )

ocm7  Create and manage ASM disk groups
https://shenh.tripod.com/Oracleblog/index.blog/1825053/ocm7-create-and-manage-asm-disk-groups/

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

diskground:
CREATE DISKGROUP DG1 NORMAL REDUNDANCY
FAILGROUP FLGRP1 DISK
'/dev/raw/raw3',
'/dev/raw/raw4',
FAILGROUP FLGRP2 DISK
'/dev/raw/raw5',
'/dev/raw/raw6',

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

SQL> STARTUP NOMOUNT

SQL> CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY

FAILGROUP controller1 DISK
'/devices/diska1' name testdisk size 100G,
'/devices/diska2',
'/devices/diska3'
FAILGROUP controller2 DISK
'/devices/diskb1',
'/devices/diskb2',
'/devices/diskb3'

ALTER DISKGROUP dgroup1 ADD DISK
'/devices/diska5' NAME diska5,
'/devices/diska6' NAME diska6;

ALTER DISKGROUP dgroup1 ADD DISK
'/devices/diska*';

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

ALTER DISKGROUP DG1 ADD DISK 'dev/raw/raw7' NAME disk5;

ALTER DISKGROUP DG1 DROP DISK 'dev/raw/raw7';

ALTER DISKGROUP DG1 RESIZE DISK 'dev'raw'raw6' SIZE 500M;

ALTER DISKGROUP DG1 REBALANCE POWER 5 WAIT;

select path, reads, writes, read_time, write_time,
read_time/decode(reads,0,1,reads) "AVGRDTIME",
write_time/decode(writes,0,1,writes) "AVGWRTIME",
from v$asm_disk_stat;

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

asm tools:

asmcmd
asmcmd>cd|du|exit|help|ls|find|mkalias|lsct|lsdg|mkdir|rm|rmalias

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

asm ftp:

ftp
ftp> eopm node1 7777

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

/etc/init.d/oracleasm configure

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

V$ASM_OPERATION

ocm7  Configure ASM for the shared disks, and create a clustered database
https://shenh.tripod.com/Oracleblog/index.blog/1825054/ocm7-configure-asm-for-the-shared-disks-and-create-a-clustered-database/

step 1):  ./runcluvfy.sh stage -pre dbcfg -n node1,node2 -d /u01/app/oracle/product/10g

step 2): dbca &  then follow gui.

ocm7  Configure archiving
https://shenh.tripod.com/Oracleblog/index.blog/1825056/ocm7configure-archiving/

http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle11gRAC/CLUSTER_30.shtml

Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:
$ sqlplus "/ as sysdba"
SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

Shutdown all instances accessing the clustered database:
$ srvctl stop database -d orcl

Using the local instance, MOUNT the database:
$ sqlplus "/ as sysdba"
SQL> startup mount

Enable archiving:
SQL> alter database archivelog;

Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:
SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

Shutdown the local instance:
SQL> shutdown immediate

Bring all instance back up using srvctl:
$ srvctl start database -d orcl

(Optional) Bring any services (i.e. TAF) back up using srvctl:
$ srvctl start service -d orcl

Login to the local instance and verify Archive Log Mode is enabled:
$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     83
Next log sequence to archive   84
Current log sequence           84
After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs!

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

http://archive.netbsd.se/?ml=oracle-l&a=2006-04&t=1960607

Steps:
0. ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
1. Shutdown all instances cleanly (normal, immediate, transactional)
2. "Startup mount" one instance
3. ALTER DATABASE ARCHIVELOG;
4. ALTER DATABASE OPEN;
5. ALTER SYSTEM SET CLUSTER_DATABASE=TRUE;
6. SHUTDOWN IMMEDIATE
7. Startup all the instances. Do not forget to set log_archive_dest_n
for all instances. Otherwise you'll find archived logs in $ORACLE_HOME/dbs

NOTE: do not forget to specify %t or %T in log_archive_format. That
would help to distinguish logs from different instances.

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

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SHUTDOWN IMMEDIATE;

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

Starting with 10.2 you no longer need to modify the CLUSTER_DATABASE parameter setting to
change the archiving mode in RAC. You can change archivelog mode as long as
the database is mounted in the local instance and not open in any instances.

http://el-caro.blogspot.com/2007/01/rac-archivelog-mode-and-clusterdatabase.html

elephant-> srvctl stop database -d F8902PRD

elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is not running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
SQL> alter database noarchivelog;

elephant-> srvctl stop instance -d F8902PRD -i F8902PRD1
Now lets try to change back to archivelog mode by mounting the database from more
than one instance.

elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o mount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> show parameter cluster_database

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 3
SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/backup/F8902PRD/arch/
Oldest online log sequence 1103
Next log sequence to archive 1104
Current log sequence 1104

elephant-> srvctl stop database -d F8902PRD
elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o mount
elephant-> srvctl start instance -d F8902PRD -i F8902PRD2 -o open ==> I made a typo here. It should have been F8902PRD3
instead of F8902PRD2 but notice srvctl does not generate any errors. So u don't know if instance 2 has actually opened the database.
elephant-> srvctl start instance -d F8902PRD -i F8902PRD3 -o open

SQL> select inst_id,open_mode from gv$database;

INST_ID OPEN_MODE
---------- ----------
1 MOUNTED
2 MOUNTED
3 READ WRITE

So instance 3 has the db open while instances 1 and 2 have just mounted the database.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

*****************************************************************************************
However if you need to recreate the controlfile you need to set cluster_database to false

elephant-> srvctl stop database -d F8902PRD
elephant-> srvctl start instance -d F8902PRD -i F8902PRD1 -o nomount
elephant-> srvctl status database -d F8902PRD
Instance F8902PRD1 is running on node elephant
Instance F8902PRD2 is not running on node rhino
Instance F8902PRD3 is not running on node hippo

SQL> select inst_id,DATABASE_STATUS from gv$instance;

INST_ID DATABASE_STATUS
---------- -----------------
1 ACTIVE

SQL> @controlfile.sql
CREATE CONTROLFILE REUSE DATABASE "F8902PRD" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

ocm7  Configure Services with a primary instance and an available instance
https://shenh.tripod.com/Oracleblog/index.blog/1825055/ocm7-configure-services-with-a-primary-instance-and-an-available-instance/

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

select name, network_name, creation_date, goal, dtp, aq_ha_notification, clb_goal from v$services;

srvctl config service -d prod -s sales.mydomain.com

sales.mydomain.com PREF: prod1 AVAIL: prod2

four ways to manage services:
1): DBCA

2): EM web tool

3): DBMS_SERVICES.*

4): server control ( srvctl )

example:
srvctl add service -d database_name -s service_name -r preferred_instance -a available_instance -P taf_policy
 
srvctl add service -d prod -s test -r prod,prod2 -a prod3
srvctl start service -d prod -s test

execute dbms_service.create_service ( service_name =>'sales.mydomain.com' -
,aq_ha_notifications => true -
,failover_method     => dbms_service.failover_method_basic -
,failover_type       => dbms_service.failover_type_select -
,failover_retries    => 180 -
,failover_delay      => 5 -
, clb_goal           => dbms_service.clb_goal_long );

execute dbms_service.start_service ( 'sales.mydomain.com' );

startup and shutdown sequence:

srvctl stop service -d db_name [-s service_name_list [-i inst_name]]

srvctl stop instance -d db_name -i inst_name

srvctl stop database -d db_name

srvctl stop asm -n node

srvctl stop listener -n node [-l listenername]

srvctl stop nodeapps -n node

CRS_home/bin/crsctl stop crs

Posted by shenh at 11:32 PM EST

Updated: Wednesday, 7 January 2009 12:45 AM EST

Share This Post

Permalink

6. Performance Management
ocm6  Install Statspack
https://shenh.tripod.com/Oracleblog/index.blog/1825027/ocm6-install-statspack/

http://ist.uwaterloo.ca/~baumbach/ORACLEnotes/ST_statspack.html

sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;

-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

@spreport.sql -- Enter two snapshot id's for difference report

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


spauto.sql - Schedule a dbms_job to automate the collection of Statspack statistics.
spcreate.sql - Installs the Statspack user, tables and package on a database (Run as SYS).
spdrop.sql - Deinstall Statspack from database (Run as SYS).
sppurge.sql - Deletes snapshots which fall between the begin and end range of Snapshot Id's specified.
spuexp.par - Export parameter file i.e. exp userid=perfstat/perfstat parfile=spuexp.par.
spreport.sql - Report on differences between values recorded in two snapshots.
sptrunc.sql - Removes ALL data from Statspack tables.


execute statspack.snap (i_snap_level=>5, i_modify_parameter=>'true');

execute statspack.modify_statspack_parameter -
(i_snap_level=>5, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);

execute statspack.snap(i_session_id=>3);



sqlplus sys
--
-- Install Statspack
-- Enter tablespace names when prompted
--
@?/rdbms/admin/spcreate.sql
--
-- Drop Statspack
-- Reverse of spcreate.sql
--
-- @?/rdbms/admin/spdrop.sql
--

sqlplus perfstat
--
-- Take a performance snapshot
--
execute statspack.snap;
--
-- Get a list of snapshots
--
column snap_time format a21
select snap_id,to_char(snap_time,'MON dd, yyyy hh24:mm:ss') snap_time
from sp$snapshot;
--

sqlplus perfstat
--
-- Statspack Report
--
-- You will be prompted for:
-- a) The beginning snapshot Id
-- b) The ending snapshot Id
-- c) The name of the report text file to be created
--
-- The report will then calculate and print ratios,
-- increases etc. for all statistics between the two snapshot periods.
--
--
-- Get a list of snapshots to report on:
--
column snap_time format a21
select snap_id,to_char(snap_time,'MON dd, yyyy hh24:mm:ss') snap_time
from sp$snapshot;
--
-- Run the Statspack report:
--
@?/rdbms/admin/spreport.sql
--

ocm6  Adjust the default interval for statistics collection
https://shenh.tripod.com/Oracleblog/index.blog/1825028/ocm6-adjust-the-default-interval-for-statistics-collection/

1. maybe for statspack

sql> execute statspack.modify_statspack_parameter -

( i_snap_level=>5,i_buffer_gets_th=>100000, i_modify_parameter=>'true');

sql> execute statspack.snap;

sql> @ORACLE_HOME/rdbms/admin/spreport ; ( sprepsql)

2. for awr
execute dbms_workload_repository.create_snapshot;

select snap_interval, retention
from dba_hist_wr_control;

-- retention: 2 days
-- interval: 20 minutes
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;

rem -- change ADDM sensitiveness back to normal
exec dbms_advisor.set_default_task_parameter ( 'ADDM', 'DB_ACTIVITY_MIN', 300);

more sensitive:
exec dbms_advisor.set_default_task_parameter ( 'ADDM', 'DB_ACTIVITY_MIN', 30);

ocm6  Customize the events that are monitored
https://shenh.tripod.com/Oracleblog/index.blog/1825030/ocm6-customize-the-events-that-are-monitored/

best:
http://www.globusz.com/ebooks/Oracle/00000013.htm

SELECT ADVISOR_NAME, PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS

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

Oracle assumes the value of the parameter (not intialization parameter) DBIO_EXPECTED is 10 milliseconds.

SELECT PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE ADVISOR_NAME='ADDM'
AND PARAMETER_NAME='DBIO_EXPECTED'

If your hardware is significantly different, you can set the parameter value one time for all subsequent ADDM executions:

DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM','DBIO_EXPECTED', 8000); 
 

ocm6  Use ADDM to analyze statistics and to identify performance bottlenecks and fix them
https://shenh.tripod.com/Oracleblog/index.blog/1825031/ocm6-use-addm-to-analyze-statistics-and-to-identify-performance-bottlenecks-and-fix-them/

http://download-west.oracle.com/docs/cd/B13789_01/appdev.101/b10802/d_advis.htm


BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '970_1032_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 970 to 1032.');

-- Set the start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 970);

DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 1032);

-- Execute the task.
DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24

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

The relevant AWR snapshots can be identified using the DBA_HIST_SNAPSHOT view.

Related Views
The following views can be used to display the ADDM output without using Enterprise Manager or the GET_TASK_REPORT function:

DBA_ADVISOR_TASKS - Basic information about existing tasks.
DBA_ADVISOR_LOG - Status information about existing tasks.
DBA_ADVISOR_FINDINGS - Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS - Recommendations for the problems identified by an existing task.

-- -----------------------------------------------------------
@my_addmrpt.sql
@addmrpt.sql

ocm6  Implement automatic shared memory management
https://shenh.tripod.com/Oracleblog/index.blog/1825032/ocm6-implement-automatic-shared-memory-management/

good:
http://www.oracleadvice.com/10g/10g_autosharedmemory.htm


show parameter sga

show parameter pga

sga_target=4096M

To enable automatic shared memory management, set sga_target to a non-zero value up to the value of sga_max_size. The default value is 0, meaning automatic shared memory management is disabled.

Oracle will automatically tune the following SGA components:
• Db_cache_size # default 0 when sga_target > 0
• Shared_pool_size # default 0 when sga_target > 0
• Large_pool_size # default 0 when sga_target > 0
• Java_pool_size # default 0 when sga_target > 0

You must manually tune
• Log_buffer
• Keep & recycle buffer caches
• Non-standard block size buffer caches
• The new streams pool
• The new ASM storage management buffer cache

It is important to understand the rules that Oracle uses to allocate memory from sga_target.

1. If sga_target is set higher than sga_max_size, then sga_max_size will be adjusted upwards to match sga_target.
2. Deduct an overhead for fixed-size SGA structures (Eg. the new ASH - Active Session History buffer)
3. Deduct the size of the manually-configured SGA areas (log_buffer, keep & recycle pools etc.)
4. The remaining shared memory is then divided between db_cache_size, shared_pool_size, large_pool_size and java_pool_size
5. If the auto-tune parameters have been assigned values, then these are used as a minimum size. Use the default of 0 to allow Oracle to determine an appropriate size.
6. When Oracle must determine a size for the auto-tune parameters it starts off low, using a function based on cpu_count, and gradually increases the size to an optimum value. Each time it does this, it will save the new value in the SPFILE to use as a minimum the next time the instance is started. If an init.ora parameter file is used instead of an SPFILE, then performance could suffer slightly more than usual shortly after the instance is restarted.

The current values of auto-tune parameters can be viewed in V$SGA_DYNAMIC_CONTENTS

ocm6  Tune memory using manual memory management
https://shenh.tripod.com/Oracleblog/index.blog/1825034/ocm6-tune-memory-using-manual-memory-management/

5. If the auto-tune parameters have been assigned values, then these are used as a minimum size. Use the default of 0 to allow Oracle to determine an appropriate size.

ocm6  Monitor and tune SGA areas that are not automatically tuned
https://shenh.tripod.com/Oracleblog/index.blog/1825034/ocm6-tune-memory-using-manual-memory-management/

• Log_buffer
• Keep & recycle buffer caches
• Non-standard block size buffer caches
• The new streams pool
• The new ASM storage management buffer cache

ocm6  Implement automatic SQL execution memory management
https://shenh.tripod.com/Oracleblog/index.blog/1825036/ocm6-implement-automatic-sql-execution-memory-management/sql

memory:  shared_pool_size and large_pool_size for shared_server

shared_pool_size
shared_pool_reserved_size
open_cursors
session_cached_cursors
cursor_space_for_time
cursor_sharing

-- ------------------------------------------------------------------------
select namespace, gethitratio from v$librarycache;

select * frin v$sys_time_model  where stat_name like '%parse%;
select * frin v$sess_time_model where stat_name like '%parse%;

select * from v$sqlstats;

select v$db_object_cache;

-- ------------------------------------------------------------------------
ora-04031: unable to allocate string bytes of shared memory
Cause:    More shared memory is needed than was allocated in the shared pool.
Action:    If the shared pool is out of memory,
        either use the dbms_shared_pool package to pin large packages,
        reduce your use of shared memory, or increase the amount of
        available shared memory by increasing the value of the INIT.ORA
        parameters "shared_pool_reserved_size" and "shared_pool_size".

        If the large pool is out of memory,
        increase the INIT.ORA parameter "large_pool_size".

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

dbms_shared_pool.keep ( 'package_name' );

@dbmspool.sql
@prvtpool.plb


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

init.ora
cursor_sharing=exact|similar|force

avoid hard parse
avoid soft parse

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

 

sga_max

sga_target

• Db_cache_size # default 0 when sga_target > 0
• Shared_pool_size # default 0 when sga_target > 0
• Large_pool_size # default 0 when sga_target > 0
• Java_pool_size # default 0 when sga_target > 0

ocm6  Tune the PGA using work area size parameters
https://shenh.tripod.com/Oracleblog/index.blog/1825037/ocm6-tune-the-pga-using-work-area-size-parameters/ 

pga_aggregate_size=700m

workarea_size_policy =AUTO|manual

--> sort_area_size 

ocm6  Use the PGA Advisor
https://shenh.tripod.com/Oracleblog/index.blog/1825038/ocm6-use-the-pga-advisor/ 

best picture for DB control OEM:
http://www.dba-oracle.com/oracle10g_tuning/t_memory_advisor.htm


http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm

From the Home Page, under related links, click Advisor Central, then Click Memory Advisor.

ocm6  Create a Database Resource Manager plan with directives (active session count, max execution time)
https://shenh.tripod.com/Oracleblog/index.blog/1825039/ocm6-create-a-database-resource-manager-plan-with-directives/ 

Resource Manager ( active session pool )
http://www.oracle-base.com/articles/10g/ResourceManagerEnhancements10g.php
http://www.oracle-base.com/articles/9i/ResourceManagerEnhancements9i.php
http://www.oracle-base.com/articles/8i/ResourceManager8i.php

DBMS_RESOURCE_MANAGER package.
Dbms_Resource_Manager.Create_Plan_Directive
Dbms_Resource_Manager.Set_Initial_Consumer_Group('web_user', 'web_cg');

Dbms_Resource_Manager_Privs
Dbms_Resource_Manager_Privs.Grant_Switch_Consumer_Group

Dbms_Session.Switch_Current_Consumer_Group

DBA_RSRC_PLANS
DBA_RESRC_PLAN_DIRECTIVES

V$RSRC_CONSUMER_GROUP


ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = hybrid_plan;

SQL> SELECT VALUE FROM V$PARAMETER
SQL> WHERE name = 'resource_manager_plan';
VALUE
---------
PEAKTIME


-- Set users' initial resource groups
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
user => 'HR'
,consumer_group => 'OLTP'
);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(
user => 'SLSMGR'
,consumer_group => 'DSS'
);


BEGIN
-- Switch the consumer group for a specific session
-- of the HR user (found by querying V$SESSION)
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS(
session_id => '17'
,session_serial => '11'
,consumer_group => 'DSS'
);

-- Switch the consumer group for all sessions belonging to
-- the specified users
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER(
USER => 'SLSMGR'
,consumer_group => 'DSS'
);
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER(
USER => 'HR'
,consumer_group => 'OLTP'
);
END;
/


http://www.dbasupport.com/oracle/ora9i/DRM.shtml
http://www.dbasupport.com/oracle/ora9i/DRM02.shtml
http://www.dbasupport.com/oracle/ora9i/DRM03_1.shtml
http://www.dbasupport.com/oracle/ora9i/DRM03_2.shtml

== =======================================
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1007878

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#sthref3411

http://www.psoug.org/reference/dbms_res_mgr.html

http://www.globusz.com/ebooks/Oracle/00000017.htm

ocm6  Create consumer groups
https://shenh.tripod.com/Oracleblog/index.blog/1825040/ocm6-create-consumer-groups/ 

dbms_resource_manager.create_consumer_group(
consumer_group IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2 DEFAULT 'ROUND-ROBIN');

-- alternate cpu_mth is RUN-TO-COMPLETION

https://twiki.cern.ch/twiki/bin/view/LHCb/Rsrc_alloc

http://www.oraclesource.net/10g/Oracle_10g_System_res_mgr.pdf

http://www.psoug.org/reference/dbms_res_mgr.html

http://www.nocoug.org/download/2002-11/db_consolidation.ppt

http://cdacosta.free.fr/Slides/Visio-upg9i_06_RESS-MAN.pdf

http://www.ss64.com/orap/DBMS_RESOURCE_MANAGER.html

http://www.adp-gmbh.ch/ora/plsql/dbms_resource_manager.html

init.ora
RESOURCE_MANAGER_PLAN='SIEBEL_PLAN';

alter system set resource_manager_plan='SIEBEL_PLAN';
alter system set resource_manager_plan='';

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

plan, consumer group, plan_directive:

dbms_resource_manager.* package:
plan:create,update,delete,cascade
consumer_group:create,update,delete
plan_directive:create,update,delete
pending_area:create, validate, clear, submit
switch: set_initial, switch groups

1. pending area
dbms_resource_manager.create_pending_area
dbms_resource_manager.validate_pending_area
dbms_resource_manager.clear_pending_area
dbms_resource_manager.submit_pending_area

-- tie plan and consumer group
exec dbms_resource_manager.creative_plan_directive -
 ( plan => 'new_plan', -
   group_or_subplan => 'sales', -
   conmment => 'sales groups', -
   cpu_p1 => 60 -
   parallel_degree_limit_p1 => 4, -
   max_idle_time => 600, -
   max_idle_blocker_time => 300 );
-- ----------------------------------------------------

-- tie user and consumer groups:

select username, initial_rsrc_consumer_group
from dba_users;

SQL> exec dbms_resource_manager.set_inital_consumer_group ( -
     user => 'NOEL',
     consumer_group => 'OLTP' );

-- manually switching sessions and users
SQL> exec dbms_resource_manager.switch_consumer_group_for_sess ( -
 SESSION_ID => 'session_id',
 SESSION_SERIAL => 'session serial number ',
 CONSUMER_GROUP => 'group_name' );

SQL> exec dbms_resource_manager.switch_consumer_group_for_user ( -
 USER => 'user name',
 CONSUMER_GROUP => 'group_name' );

-- tie session and consumer groups:
SQL> exec dbms_resource_manager.set_consumer_group_mapping ( -
 dbms_resource_manager.oracle_user, 'scott', 'dev_group' );

SQL> exec dbms_resource_manager.set_consumer_group_mapping ( -
 dbms_resource_manager.client_os_user, 'SAM', 'dev_group' );

-- tie session and consumer groups:
SQL> dbms_resource_manager.set_consumer_group_mapping_pri ( -
  explicit => 1, -
  client_machine => 2, -
  module_name => 3, -
  oracle_iser => 4, -
  service_name => 5, -
  client_os_user => 6, -
  client_program => 7, -
  module_name_action => 8, -
  service_moduile => 9, -
  service_module_action => 10 );

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

10g: switch back after call end

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(
plan => 'Day_Plan' ,
Group_or_subplan => 'DSS_GROUP',
Comment => 'Switch back example',
Switch_group => 'LONGRUN_GROUP',
Switch_time => 600,
Cpu_p1 => 100,
Cpu_p2 => 0,
Switch_back_at_call_end => true
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(
Plan => 'Day_Plan',
Group_or_subplan => 'LONGRUN_GROUP',
Comment => 'Switch Back Example',
Cpu_p1 => 0,
Cpu_p2 => 100,
Switch_back_at_call_end => true
);

-- any user session that is idle for more than 400 seconds
   will be forcibly killed by the PMON background process.

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(
Plan => 'DAY_PLAN',
Group_or_subplan =>'DSS_GROUP',
Comment => 'Limit idle time',
Max_idle_time => 400,
Max_idle_blocker_time => 100

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

);

ocm6  Configure consumer group mappings
https://shenh.tripod.com/Oracleblog/index.blog/1825041/ocm6-configure-consumer-group-mappings/ 

-- tie session and consumer groups:
SQL> exec dbms_resource_manager.set_consumer_group_mapping ( -
 dbms_resource_manager.oracle_user, 'scott', 'dev_group' );

abstract:

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

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan',
COMMENT => 'Resource plan/method for bug users sessions');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Online_group',
COMMENT => 'Resource consumer group/method for online bug users sessions');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Online_group',
COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
PARALLEL_DEGREE_LIMIT_P1 => 8);

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

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

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'bugdb_plan',
COMMENT => 'Resource plan/method for bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'maildb_plan',
COMMENT => 'Resource plan/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'mydb_plan',
COMMENT => 'Resource plan/method for bug and mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Online_group',
COMMENT => 'Resource consumer group/method for online bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Batch_group',
COMMENT => 'Resource consumer group/method for batch job bug users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Bug_Maint_group',
COMMENT => 'Resource consumer group/method for users sessions for bug db maint');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Users_group',
COMMENT => 'Resource consumer group/method for mail users sessions');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Postman_group',
COMMENT => 'Resource consumer group/method for mail postman');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'Mail_Maint_group',
COMMENT => 'Resource consumer group/method for users sessions for mail db maint');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Online_group',
COMMENT => 'online bug users sessions at level 1', CPU_P1 => 80, CPU_P2=> 0,
PARALLEL_DEGREE_LIMIT_P1 => 8);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Batch_group',
COMMENT => 'batch bug users sessions at level 1', CPU_P1 => 20, CPU_P2 => 0,
PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'Bug_Maint_group',
COMMENT => 'bug maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 100,
PARALLEL_DEGREE_LIMIT_P1 => 3);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'bugdb_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Postman_group',
COMMENT => 'mail postman at level 1', CPU_P1 => 40, CPU_P2 => 0,
PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Users_group',
COMMENT => 'mail users sessions at level 2', CPU_P1 => 0, CPU_P2 => 80,
PARALLEL_DEGREE_LIMIT_P1 => 4);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'Mail_Maint_group',
COMMENT => 'mail maintenance users sessions at level 2', CPU_P1 => 0, CPU_P2 => 20,
PARALLEL_DEGREE_LIMIT_P1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'maildb_plan',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'all other users sessions at level 3', CPU_P1 => 0, CPU_P2 => 0,
CPU_P3 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
GROUP_OR_SUBPLAN => 'maildb_plan',
COMMENT=> 'all mail users sessions at level 1', CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'mydb_plan',
GROUP_OR_SUBPLAN => 'bugdb_plan',
COMMENT => 'all bug users sessions at level 1', CPU_P1 => 70);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

ocm6  Manage Services
https://shenh.tripod.com/Oracleblog/index.blog/1825042/ocm6-manage-services-oracle-database-10g-release-2/ 

show parameter service_names

or RAC

ocm6  Create and manage objects to accommodate different data access methods (schema tuning)
https://shenh.tripod.com/Oracleblog/index.blog/1825043/ocm6-create-and-manage-objects-to-accommodate-different-data-access-methods-schema-tuning/

ocm6  Use the SQL Tuning Advisor
https://shenh.tripod.com/Oracleblog/index.blog/1825044/ocm6-use-the-sql-tuning-advisor/ 

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '2uvvchaax0wzx',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 600,
task_name => 'tuning_task_1',
description => 'Tuning task for statement 2uvvchaax0wzx');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_1');

-- EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'tuning_task_1');

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'LINK';

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('tuning_task_1') AS recommendations FROM dual;
SET PAGESIZE 24

execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_task_1', replace => TRUE);


--> SQL profile "SYS_SQLPROF_0144aaae2c088000" used for this statement

-- ----------------------------------------------------------
SELECT decode(r.type,
'STATISTICS', 1,
'SQL PROFILE', 2,
'INDEX', 3, 4) as type,
count(*)
FROM dba_advisor_findings f,
dba_advisor_recommendations r
WHERE f.task_id = r.task_id AND r.task_id = :tid AND
f.finding_id = r.finding_id AND
f.object_id = :oid
GROUP BY r.type
ORDER BY type
/

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

begin
dbms_sqltune.accept_sql_profile (
task_name => 'FOLIO_COUNT',
name => 'FOLIO_COUNT_PROFILE'
description => 'Folio Count Profile',
category => 'FOLIO_COUNT');
end;



DBMS_SQLTUNE.drop_tuning_task (task_name => 'tuning_task_1');

== ==================================

10G New Features Series 2: Introduction to SQL Tuning Advisor
http://www.itpub.net/331977.html

#
# Cleanup ADDM snapshot settings
#
sqlplus -s /NOLOG <<EOF>> /tmp/cleanup_dina.log 2>&1

connect / as sysdba

rem -- change INTERVAL setting to 30 minute
execute dbms_workload_repository.modify_snapshot_settings(interval => 60);

rem -- change ADDM sensitiveness back to normal
exec dbms_advisor.set_default_task_parameter('ADDM','DB_ACTIVITY_MIN',300);

connect sh/sh

drop index sales_time_idx;

create bitmap index sales_time_bix
on sales(time_id)
tablespace example
local nologging compute statistics;


EOF

#
# Cleanup sql profile
#
sqlplus -s /NOLOG <<EOF> /tmp/cleanup_dina.log 2>&1

connect / as sysdba

set head off
set timing off
set feedback off;
set pagesize 0

spool /tmp/drop_dyn.sql;

select q'#connect / as sysdba;#' from dual;

select q'#execute dbms_sqltune.drop_sql_profile('#' || name || q'#') ;#'
from dba_sql_profiles ;

select q'#execute dbms_advisor.delete_task('#' || task_name || q'#') ;#'
from user_advisor_tasks
where CREATED > SYSDATE-(1/24);

select q'#connect system/oracle;#' from dual;

select q'#execute dbms_advisor.delete_task('#' || task_name || q'#') ;#'
from user_advisor_tasks
where CREATED > SYSDATE-(1/24);

spool off

@/tmp/drop_dyn.sql

EOF

ocm6  Use the SQL Access Advisor
https://shenh.tripod.com/Oracleblog/index.blog/1825045/ocm6-use-the-sql-access-advisor/

http://www.oracle.com/technology/oramag/oracle/04-jul/o44tech_talking.html

http://www.oracle-base.com/articles/10g/SQLAccessAdvisor10g.php

http://youngcow.net/doc/oracle10g/server.102/b14211/advisor.htm

Grid Control: Advisor Central --> SQL Access Advisor -->

SQL Access Advisor:
Mostly recommend for index and Materialized Views
 

ocm6  Gather Optimizer statistics
https://shenh.tripod.com/Oracleblog/index.blog/1825046/ocm6-gather-optimizer-statistics/

http://www.akadia.com/services/ora_gather_statistics.html

http://www.psoug.org/reference/dbms_stats.html

http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14211/stats.htm

http://decipherinfosys.wordpress.com/2008/02/19/exportimport-of-optimizer-statistics-in-oracle/

1):

exec dbms_stats.gather_table_stats ( -
     ownname          => 'SCOTT', -
     tabname          => 'EMP', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
/

exec dbms_stats.gather_schema_stats ( -
     ownname          => 'SCOTT', -
     options          => 'GATHER', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
/

BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
  METHOD_OPT => 'FOR COLUMNS SIZE 10 quantity_on_hand');
END;
/

DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'CASE_HDR',
method_opt => 'for all indexed columns',
cascade => true,
stattab => 'CASE_HDR_STAT_TAB',
statid => 1);

BEGIN
  DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'INVENTORIES',
  METHOD_OPT => 'FOR COLUMNS SIZE 20 warehouse_id');
END;
/

2): auto stats
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

init.ora
STATISTICS_LEVEL=TYPICAL|ALL

3):
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
  DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/

4):
DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_SYSTEM_STATS

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

stats export and import:
DBMS_STATS.CREATE_STATS_TABLE (ownname => USER, stattab => 'MY_STAT_TABLE', tblspace => 'USERS');
exec dbms_stats.export_database_stats('MY_STAT_TABLE','my_good_stats_001','SYSTEM');
execute dbms_stats.delete_table_stats('SCOTT','EMP')
execute dbms_stats.delete_table_stats('SCOTT','DEPT')
exec dbms_stats.import_schema_stats('SCOTT','MY_STAT_TABLE','my_good_stats_001','system');
exec dbms_stats.export_database_stats('MY_STAT_TABLE','my_good_stats_002','SYSTEM');

DBMS_STATS.EXPORT_TABLE_STATS(ownname => USER, tabname => 'CASE_HDR',
                                 stattab => 'CASE_HDR_STAT_TAB', statid => 1, cascade => true);

DBMS_STATS.IMPORT_TABLE_STATS(ownname => USER, tabname => 'CASE_HDR',
         stattab => 'CASE_HDR_STAT_TAB', statid => 1, cascade => true, no_invalidate => true);

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

ocm6  Interpret execution plan
https://shenh.tripod.com/Oracleblog/index.blog/1825047/ocm6-interpret-execution-plan/ 

http://www.psoug.org/reference/dbms_xplan.html

1): create plan table


2):
explain plan for ... ...

3):
set autotrace on

set autotrace on traceonly

4): dbms_xplan

dbms_xplan.display(
table_name VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id VARCHAR2 DEFAULT NULL,
format VARCHAR2 DEFAULT 'TYPICAL',
filter_preds VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;

http://www.psoug.org/reference/dbms_xplan.html

SELECT * FROM TABLE(dbms_xplan.display_cursor('cpm9ss48qd32f', 0));

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

also in sqlplus, set autotrace on trace only;

dbms_monitor.*

tkprof

trcsess

10046 event:

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

ocm6  Use SQL tuning tools and features
https://shenh.tripod.com/Oracleblog/index.blog/1825048/ocm6-use-sql-tuning-tools-and-features/

Posted by shenh at 11:31 PM EST

Updated: Tuesday, 20 January 2009 11:50 AM EST

Share This Post

Permalink

ocm5  Create external tables (Data Loader and Data Pump drivers)
https://shenh.tripod.com/Oracleblog/index.blog/1825019/ocm5-create-external-tables/

external table:
http://www.globusz.com/ebooks/Oracle/00000012.htm#4


http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_et.htm

ocm5  Implement Data Pump export and import jobs for data transfer
https://shenh.tripod.com/Oracleblog/index.blog/1825020/ocm5-implement-data-pump-export-and-import-jobs-for-data-transfer/

data pump:
http://www.globusz.com/ebooks/Oracle/00000012.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#i1009203

http://www.databasejournal.com/features/oracle/article.php/3489491

ocm5  Implement Data Pump to and from remote databases
https://shenh.tripod.com/Oracleblog/index.blog/1825021/ocm5-implement-data-pump-to-and-from-remote-databases/

1): dblinks and network 

> impdp hr/hr@inst1 DIRECTORY=dpump_dir DUMPFILE=hr.dmp TABLES=employees  

> impdp hr/hr TABLES=employees DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link EXCLUDE=CONSTRAINT  

ocm5  Implement Replication/Streams
https://shenh.tripod.com/Oracleblog/index.blog/1825022/ocm5-implement-replicationstreams/

Could you please let me know the procedure to set up multi-master replication in Oracle 10g?You can use Oracle Advanced Replication to do this.

However, this is being replaced by newer technology called Streams and this is what you should be using. Have a look at the following links for more details.

http://www.oracle.com/technology/products/dataint/htdocs/streams_fo.html

http://download-west.oracle.com/docs/cd/B14099_19/idmanage.1012/b14082/rep_process.htm

-- --------------------------------------------
D17333GC10: Implement Streams

Configure an Oracle Streams environment
Monitor the capture, propagation, and apply of events
Alter the Streams environment to add new sites or objects
Configure conflict handling for data replication
Transform the data being replicated between two sites
Enqueue and dequeue messages using Oracle Streams
Perform basic troubleshooting of a Streams environment

Introduction and Overview
Database Configuration
Streams Basic Objects
Rules
System-Created Rules
Capture Process Concepts
Configuring Capture
Instantiation
Propagation Concepts and Configuration
Apply: Concepts and Configuration
Apply Handlers
Administering a Streams Environment
Extending the Streams Environment
Data Conflicts
Conflict Resolution
Transformations
User-Enqueued Events
Operational Issues
Troubleshooting Oracle Streams
Integrating with Oracle Streams

ocm5  Configure and manage master replication
https://shenh.tripod.com/Oracleblog/index.blog/1825023/ocm5-configure-and-manage-master-replication/

good:
http://www.dbazine.com/oracle/or-articles/burleson15


http://www.dbasupport.com/oracle/ora9i/ors.shtml

http://www.adventnet.com/products/webnms/help/administrator_guide/dbase/db_replication.html

http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87499/repoverv.htm


http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14227/rarmanmaster.htm

http://www.dbasupport.com/oracle/ora9i/ors.shtml

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

http://orafaq.com/faqrepl.htm

http://www.oracle.com/technology/books/pdfs/book_rep_chap6_ce2.pdf

http://www.wisdomforce.com/dweb/resources/docs/advanced_replication.pdf

http://www.oracle.com/technology/products/dataint/pdf/twp_streams_replication_10gr2.pdf

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

http://download.oracle.com/docs/cd/B19306_01/server.102/b14227/rarrepsite.htm

http://www.adp-gmbh.ch/ora/admin/replication/multi_master_replication.html

http://www.colestock.com/blogs/2008/03/oracle-multi-master-replication-example.html

http://download.oracle.com/docs/cd/B19306_01/server.102/b14227/toc.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14226/toc.htm

http://www.oracle.com/technology/books/pdfs/book_rep_chap6_ce2.pdf

http://www.cisco.com/univercd/cc/td/doc/product/access/acs_soft/rpms/rpms_1-0/rpmsconf/app_db2.pdf

http://www.dba-oracle.com/art_dbazine_mm_repl.htm
http://www.oraclenotes.com/Articles/Advance%20Replication.ppt
http://www.orafaq.com/wiki/Scripts#Oracle_Advanced_Replication_Scripts

1): The number of replicated tables is modest and manageable
    ( < 100K not 10,000 or more )

2): DBA has full conrol over the designof the tables.
   Every replicated table must have primary key,
   and additional column and triggers need added for conflict resolution.

-- --------------------------------------------------------
repadmin  multi-master
oemapp console &
Master Groups
-- --------------------------------------------------------

Adding and Removing Oracle replication Objects

Adding and removing objects is easy, provided they are not large. 
Simply stop replication activity. 
Add the object to the master group at the master definition site and restart replication. 
The new object will be propagated to all master sites in the replication environment.

connect repadmin/repadmin@navdb

BEGIN
      DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(
      gname => '"GROUP1"');
END;
/

begin
  dbms_repcat.create_master_repobject(
    'RENE', 'OBJ_1', 'TABLE', gname=>'rep_objects');

  dbms_repcat.create_master_repobject(
    'RENE', 'OBJ_2', 'TABLE', gname=>'rep_objects');
end;
/

REM Add master desination sites
execute dbms_repcat.add_master_database('MYREPGRP', 'TD2.world');

or
begin
  dbms_repcat.add_master_database (
     gname                => 'rep_objects',
     master               => 'otherdb.foo.invalid',
     use_existing_objects => true,
     copy_rows            => false,
     propagation_mode     => 'ASYNCHRONOUS');
end;
/

REM Generate replication support for objects within the group
execute dbms_repcat.generate_replication_support('SCOTT', 'EMP', 'table');

or
begin
  dbms_repcat.generate_replication_support('RENE','OBJ_1','TABLE');
  dbms_repcat.generate_replication_support('RENE','OBJ_2','TABLE');
end;
/

select count(*) from dba_repcatalog; ==> make sure no outstanding requests

BEGIN
      DBMS_REPCAT.RESUME_MASTER_ACTIVITY(
      gname => '"GROUP1"');

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

dbms_defer_sys.execute_error:
re-execute procedure calls in the error log
after fixing the problem that caused the error.

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

dbms_reputil.replication_off
do manual update or other DML
dbms_reputil.replication_on

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

if dbms_reputil.remote_site = false then
...
end if;

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

1): adding a column
BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'hr',
      oname => 'countries',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE hr.countries ADD (timestamp DATE)');
END;
/

BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'hr',
      oname => 'countries',
      type => 'TABLE',
      min_communication => TRUE);
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'hr',
      oname => 'regions',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE hr.regions ADD (site VARCHAR2(20))');
END;
/

BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'hr',
      oname => 'regions',
      type => 'TABLE',
      min_communication => TRUE);
END;
/

2): adding a trigger
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'hr_repg',
      type => 'TRIGGER',
      oname => 'insert_time',
      sname => 'hr',
      ddl_text => 'CREATE TRIGGER hr.insert_time
                      BEFORE
                         INSERT OR UPDATE ON hr.countries FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIMESTAMP := SYSDATE;
                         END IF;
                      END;');
END;
/

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

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

Conflict Resolution:

In this code, we execute dbms_repcat.add_update_resolution to direct
Oracle to use the “latest timestamp” method for conflict resolution
for updates to the EMP table.

execute dbms_repcat.add_update_resolution( -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        sequence_no => 1,               -
        method => 'LATEST TIMESTAMP',   -
        parameter_column_name => 'EMPNO');

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

DBA_REPCATLOG view

DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN

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

http://www.dba-oracle.com/art_dbazine_mm_repl.htm

Conflict Resolution Mechanisms
Here are the most common mechanisms at your disposal for resolving conflicts:

* Latest Timestamp Value.
  With this simple technique, you apply updates as they are received. Based on timestamp value, the most recent updates overlays prior updates. This approach can result in situations where one user’s update gets overlaid by a more recent update.

* Earliest Timestamp Value.
 This mechanism is the opposite of the latest timestamp value, in that the first update overlays subsequent updates. As you’d expect, not many shops use this method, but it is an option.

* Minimum and Maximum Value.
 This mechanism may be used when the advanced replication facility detects a conflict with a column group. The advanced replication facility calls the minimum value conflict resolution method and then compares the new value from the originating site with the current value from the destination site for a designated column in the column group. You must designate that column when you select the minimum value conflict resolution method.

* Additive and Average Value.
 When you’re dealing with replicated numeric values, this additive method adds a new value to the existing value using the following formula: (current value = current value + (new value - old value)). The average method averages the conflicting values into the existing value using the formula (current value = (current value + new value)/2).

* Groups priority Value.
 Using this method, some groups have priority (a higher rank) over other groups. Therefore, the update associated with the highest-ranked group gets the update.

* Site Priority Value.
  In this method, all master sites are NOT created equal. Some remote sites will have priority over other sites.

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

deftran, deferror, defcall

dbms_repcat.create_master_repgroup
do_deferred_repcat_admin
dbms_repcat.create_master_repobject

dbms_repcat.make_column_group(optional)
dbms_repcat.add_master_database( gname, master=>dblink)

dba_repsites: master sites will be in dba_repsites

rem -----------------------------------------------------------------------
rem Filename:   repprep.sql
rem Purpose:    Setup users, DB Links and schedules for Oracle Advanced
rem             replication. Run this script on all replication sites.
rem             In this example replication is between sites TD1 and TD2
rem Date:       03-Oct-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
spool repprep

connect sys

-- @?/rdbms/admin/catrep.sql

init.ora
      shared_pool_size=10M
      global_names=true
      job_queue_processes=4

REM Check if INIT.ORA parameters are OK for replication
select name, value from sys.v_$parameter
where  name in ('job_queue_processes', 'job_queue_interval',
                'global_name')

REM Assign global name to the current DB
alter database rename global_name to TD1.world;    -- Change to your DB name + domain

REM Create public db link to the other master databses
create public database link TD2.world using 'TD2';

REM Create replication administrator / propagator / receiver
create user repadmin identified by repadmin
        default   tablespace USER_DATA
        temporary tablespace TEMP
        quota unlimited on USER_DATA;

REM Grant privs to the propagator, to propagate changes to remote sites
execute dbms_defer_sys.register_propagator(username=>'REPADMIN');

REM Grant privs to the receiver to apply deferred transactions
grant execute any procedure to repadmin;

REM Authorise the administrator to administer replication groups
execute dbms_repcat_admin.grant_admin_any_repgroup('REPADMIN');

REM Authorise the administrator to lock and comment tables
grant lock any table to repadmin;
grant comment any table to repadmin;

connect repadmin/repadmin

REM Create private db links for repadmin
create database link TD2.world
        connect to repadmin identified by repadmin;

REM Schedule job to push transactions to master sites
execute dbms_defer_sys.schedule_push(        -
        destination   => 'TD2.world',        -
        interval      => 'sysdate+1/24/60',  -
        next_date     => sysdate+1/24/60,    -
        stop_on_error => FALSE,              -
        delay_seconds => 0,                  -
        parallelism   => 1);

REM Schedule job to delete successfully replciated transactions
execute dbms_defer_sys.schedule_purge(       -
        next_date     => sysdate+1/24,       -
        interval      => 'sysdate+1/24');

REM Test database link
select global_name from global_name@TD2.world;

spool off

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

rem -----------------------------------------------------------------------
rem Filename:   repdef.sql
rem Purpose:    Define replication groups with replication objects
rem             Run on Master Definition site only. Run repprep.sql
rem  on all your databases before running this script.
rem Date:       03-Oct-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
spool repdef

connect repadmin/repadmin

REM Create replication group for MASTERDEF site
execute dbms_repcat.create_master_repgroup('MYREPGRP');

REM Register objects within the group
execute dbms_repcat.create_master_repobject('SCOTT', -
        'EMP', 'TABLE', gname=>'MYREPGRP');

execute dbms_repcat.create_master_repobject('SCOTT', -
        'DEPT', 'TABLE', gname=>'MYREPGRP');

REM Add master desination sites
execute dbms_repcat.add_master_database('MYREPGRP', 'TD2.world');

REM Generate replication support for objects within the group
execute dbms_repcat.generate_replication_support('SCOTT', 'EMP', 'table');

exec dbms_repcat.resume_master_activity(gname=>'MYREPGRP');

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

rem -----------------------------------------------------------------------
rem Filename:   repdef.sql
rem Purpose:    Slightly more advanced setup
rem Date:       03-Oct-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
spool repdef

connect repadmin/repadmin

REM Create replication group for MASTERDEF site
execute dbms_repcat.create_master_repgroup('MYREPGRP');

REM Register objects within the group
execute dbms_repcat.create_master_repobject('SCOTT', -
        'EMP', 'TABLE', gname=>'MYREPGRP');

execute dbms_repcat.make_column_group(  -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        list_of_column_names => 'EMPNO');

execute dbms_repcat.add_update_resolution( -
        sname => 'SCOTT',               -
        oname => 'EMP',                 -
        column_group => 'EMP_COLGRP',   -
        sequence_no => 1,               -
        method => 'LATEST TIMESTAMP',   -
        parameter_column_name => 'EMPNO');

REM Add master desination sites
execute dbms_repcat.add_master_database('MYREPGRP', 'TD2.world');

REM Generate replication support for objects within the group
execute dbms_repcat.generate_replication_support('SCOTT', 'EMP', 'table');

exec dbms_repcat.resume_master_activity(gname=>'MYREPGRP');

-- -------------------------------------------------------------
rem -----------------------------------------------------------------------
rem Filename:   repmon.sql
rem Purpose:    Monitor replication status, sites and groups
rem Date:       03-Oct-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

connect repadmin/repadmin

set pages 50000

col sname     format a20 head "SchemaName"
col masterdef format a10 head "MasterDef?"
col oname     format a20 head "ObjectName"
col gname     format a20 head "GroupName"
col object    format a35 trunc
col dblink    format a35 head "DBLink"
col message   format a25
col broken    format a6  head "Broken?"

prompt Replication schemas/ sites
select sname, masterdef, dblink
from   sys.dba_repschema;

prompt RepCat Log (after a while you should see no entries):
select request, status, message, errnum
from   sys.dba_repcatlog;

prompt Entries in the job queue
select job, last_date, last_sec, next_date, next_sec, broken, failures,
       what
from   sys.dba_jobs
where  schema_user = 'REPADMIN';

prompt Replication Status:
select sname, master, status
from   sys.dba_repcat;

-- Returns all conflict resolution methods
-- select * from all_repconflict;

-- Returns all resolution methods in use
-- select * from all_represolution;

prompt Objects registered for replication
select gname, type||' '||sname||'.'||oname object, status
from   sys.dba_repobject;

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

rem -----------------------------------------------------------------------
rem Filename:   reperrs.sql
rem Purpose:    Try to apply transaction in error and delete the remaining
rem             transactions that is still in error.
rem Date:       06-Feb-2006
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

connect repadmin/repadmin

spool apply_errors.sql
select 'exec dbms_defer_sys.execute_error(''' || deferred_tran_id ||''','''||
       destination || ''')'
from   deferror;
spool off
@apply_errors
commit;

spool delete_errors.sql
select 'exec dbms_defer_sys.delete_error(''' || deferred_tran_id ||''','''||
       destination || ''')'
from   deferror;
spool off
@delete_errors
commit;

-- Deletes all errors in one go...
-- execute dbms_defer_sys.delete_error(NULL,NULL);

-- Clear entries from the local RepCatLog (DBA_REPCATLOG)
-- exec dbms_repcat.purge_master_log;

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

rem -----------------------------------------------------------------------
rem Filename:   repdel.sql
rem Purpose:    Remove replication support from database
rem Date:       03-Oct-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

spool repdel

connect repadmin/repadmin

REM Stop replication
execute dbms_repcat.suspend_master_activity(gname=>'MYREPGRP');

REM Delete replication groups
-- execute dbms_repcat.drop_master_repobject('SCOTT', 'EMP', 'TABLE');
execute dbms_repcat.drop_master_repgroup('MYREPGRP');
execute dbms_repcat.remove_master_databases('MYREPGRP', 'TD2.world');

REM Remove private databse links to other master databases
drop database link TD2.world;

connect sys

REM Remove the REPADMIN user
execute dbms_defer_sys.unregister_propagator(username=>'REPADMIN');
execute dbms_repcat_admin.revoke_admin_any_schema(username=>'REPADMIN');
drop user repadmin cascade;

REM Drop public database links to other master databases
drop public database link TD2.world;

spool off

ocm5  Configure and manage distributed materialized views
https://shenh.tripod.com/Oracleblog/index.blog/1825024/ocm5-configure-and-manage-distributed-materialized-views/

1): dblink

http://www.globusz.com/ebooks/Oracle/00000021.htm#4

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061505

CREATE PUBLIC DATABASE LINK remote
USING 'remote';

CREATE DATABASE LINK local
CONNECT TO hr IDENTIFIED BY hr
USING 'local';

CREATE DATABASE LINK remote.us.oracle.com
CONNECT TO CURRENT_USER
USING 'remote';

2): mv log
SQL> create materialized view log on table EMP;

3): actual mv
SQL> create materialized view emp
refresh fast with primary key
start with sysdate
next sysdate + 1/(24*60)
as (select * from emp@dblink);

4): refresh

dbms_mview.refresh ( 'hr.emp', 'F' );

BEST:
http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html

streams:
http://www.oracle.com/technology/obe/obe9ir2/obe-intg/streams/streams.htm
http://www.oracle.com/technology/obe/obe9ir2/obe-intg/streams/streams.htm?_template=/ocom/print

http://tech.it168.com/a2008/0724/198/000000198400.shtml


http://www.oracle.com/technology/obe/obe10gdb/integrate/streams/streams.htm

Implementing streams: D17333GC10
Oracle Database 10g: Implement Streams (Database and Grids)
http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D17333GC10&p_org_id=1001&lang=US&source_call=

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D17333GC20&p_org_id=1001&lang=US&source_call=

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D50315GC10&p_org_id=1001&lang=US&source_call=

http://education.oracle.com/pls/web_prod-plq-dad/show_desc.redirect?dc=D18681&p_org_id=1001&lang=US&source_call=

Posted by shenh at 11:27 PM EST

Updated: Wednesday, 7 January 2009 12:58 AM EST

Share This Post

Permalink

4. Data Management
ocm4  Implement fine-grained auditing
https://shenh.tripod.com/Oracleblog/index.blog/1824988/ocm4-implement-finegrained-auditing/

http://www.databasejournal.com/features/oracle/article.php/3640201

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html

DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ',
audit_column => 'salary'
statement_types => 'insert,update,delete,select');
-- -----------------------------------
With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

Consider the following commands:

/* create audit event handler */
CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS
BEGIN
UTIL_ALERT_PAGER(schema1, table1, policy1); -- send an alert note to my pager
END;

/* add the policy */
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ',
audit_column => 'salary',
handler_schema => 'sec',
handler_module => 'log_id',
enable => TRUE);
-- -----------------------------------
package:
DBMS_FGA
DBMS_FGA.ADD_POLICY

http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#i1011302
DBA_FGA_AUDIT_TRAIL
sys.fga_log$
DBA_COMMON_AUDIT_TRAIL

http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1008289

http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#i1009205

DBMS_FGA.ADD_POLICY
(object_schema => 'scott',
object_name=>'emp',
policy_name => 'mypolicy1',
audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types=> 'INSERT, UPDATE',
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ALL_COLUMNS);

DBMS_FGA.DISABLE_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );

DBMS_FGA.DROP_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );

DBMS_FGA.ENABLE_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
enable BOOLEAN);
-- -------------------------------------------


traditioonal:
grant select on employees to sue, rich
grant update (department_name, location_id) on departments to scott;

revoke [priviledge|all] on object from
user|role|public [cascade constrainsts];

role_sys_privs : system priviledges granted to roles

role_tab_privs : Table priviledges granted to roles

user_role_privs : Roles accessible by the user

user_tab_privs_made : Object priviledges granted on the user's objects

user_tab_privs_recd : Object priviledges granted to the user

user_col_privs_made : on specific columns

user_col_privs_recd : on specific columns

user_sys_privs : System priviledges granted to the user

ocm4  Create a secure application role
https://shenh.tripod.com/Oracleblog/index.blog/1824989/ocm4-create-a-secure-application-role/

http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm

http://www.oracle.com/technology/deploy/security/database-security/secure-application-roles/index.html

CREATE ROLE admin_role IDENTIFIED USING hr.admin;

connect system/oracle
CREATE OR REPLACE procedure sec_roles
authid current_user
as
v_user varchar2(50);
v_manager_id number :=1;
begin
v_user := (sys_context ('userenv', 'session_user'));

select manager_id into v_manager_id from hr.employees where email=v_user;

if v_manager_id = 100 then
dbms_session.set_role('ots_role');
else
null;
end if;
exception
when no_data_found then
v_manager_id:=0;
end sec_roles;
/


The role can only be enabled by modules defined inside the PL/SQL package hr.admin.

When such a role is assigned to a user, it becomes a default role for that user, which is automatically enabled at login without resorting to the package. A user with a default role does not have to be authenticated in any way to use the role. For example, the password for the role is not requested or required.

To restrict the role solely to the use specified by the IDENTIFIED USING clause, you can take either of the following actions:

Immediately after granting such a role to a user, issue an ALTER USER statement with the clause DEFAULT ROLE ALL EXCEPT role, substituting the application role for role. Then role can only be used by applications executing the authorized package.

When assigning roles, use GRANT ALL EXCEPT role.

Roles that are enabled inside an invoker's right procedure remain in effect even after the procedure exits. Therefore, you can have a dedicated procedure that deals with enabling the role for the rest of the session to use.

http://download-east.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#sthref606

ocm4  Grant specific privileges for a Flashback Query
https://shenh.tripod.com/Oracleblog/index.blog/1824990/ocm4-grant-specific-privileges-for-a-flashback-query/

GRANT FLASHBACK ON SCOTT.BONUS_DROP TO SCOTT

grant FLASHBACK ANY TABLE to scott

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

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm

Grant flashback privileges to users, roles, or applications that need to use flashback features as follows:

For the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK to provide access to the features in this package.

For Flashback Query and Flashback Version Query, grant FLASHBACK and SELECT privileges on specific objects to be accessed during queries or grant the FLASHBACK ANY TABLE privilege to allow queries on all tables.

For Flashback Transaction Query, grant the SELECT ANY TRANSACTION privilege.

For Execution of undo SQL code, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables, as appropriate, to permit execution of undo SQL code retrieved by a Flashback Transaction Query.

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

http://orafaq.com/node/50

ocm4  Set parameters for retaining undo
https://shenh.tripod.com/Oracleblog/index.blog/1824991/ocm4-set-parameters-for-retaining-undo/

undo_retention

show parameter undo

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14196/storage003.htm

ocm4  Implement fine-grained access control
https://shenh.tripod.com/Oracleblog/index.blog/1824994/ocm4-implement-finegrained-access-control/

Fine-Grained Access Control (FGAC)
vpd(Virtual Private Database)
DBMS_RLS
CONTEXT:

Oracle8i Fine Grained Access Control - Working Examples 67977.1
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=67977.1

Oracle Row Level Security: Part 1
http://www.securityfocus.com/infocus/1743

Oracle Row Level Security: Part 2
http://www.securityfocus.com/infocus/1744

http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
http://www.oracle-base.com/articles/10g/DatabaseSecurityEnhancements10g.php#vpd
http://www.oracle-base.com/articles/9i/OracleLabelSecurity9i.php
http://www.oracle-base.com/articles/9i/SecurityEnhancements9i.php


http://www.proligence.com/nyoug_fgac.pdf

http://www.psoug.org/reference/dbms_rls.html

begin
dbms_rls.add_policy
(
object_schema => 'HOSPITAL',
object_name => 'PATIENTS',
policy_name => 'PATIENT_VIEW_POLICY',
policy_function => 'GET_DOCTOR_ID',
function_schema => 'HOSPITAL',
statement_types => 'SELECT, INSERT, UPDATE, DELETE',
update_check => true,
enable => true
);
end;

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

create or replace function get_doctor_id
(
p_schema_name in varchar2,
p_table_name in varchar2
)
return varchar2
is
l_doctor_id number;
begin
select doctor_id
into l_doctor_id
from doctors
where doctor_name = USER;
return 'doctor_id = '||l_doctor_id;
end;

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

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

The user’s original query

SELECT * FROM PATIENTS

was rewritten to

SELECT * FROM
(SELECT * FROM PATIENTS)
WHERE DOCTOR_ID = 1 
 

ocm4  Create and manage contexts
https://shenh.tripod.com/Oracleblog/index.blog/1824995/ocm4-create-and-manage-contexts/

How to Determine Active Context (DBMS_SESSION.LIST_CONTEXT)
Doc ID: Note:69573.1

https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=69573.1&blackframe=1


http://www.databasejournal.com/features/oracle/article.php/3644956

http://www.orafusion.com/art_fgac.htm

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


DROP CONTEXT vpd_context;
CREATE CONTEXT vpd_context USING ap.app_security_context;

-- CREATE OR REPLACE PACKAGE ap.app_security_context
PROCEDURE setuserinfo;

DBMS_SESSION.SET_CONTEXT('vpd_context', 'cc_limit', cclimit);
DBMS_SESSION.SET_CONTEXT('vpd_context', 'crd_memo', crdmemo);

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

CREATE OR REPLACE TRIGGER ap.on_logon
AFTER LOGON
ON DATABASE

ocm4  Use SQL*Loader
https://shenh.tripod.com/Oracleblog/index.blog/1824996/ocm4-use-sqlloader/

Utilities
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#i1005437

SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc,
DISCARDMAX=5

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

#!/bin/sh
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac

-- -----------------------------
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)

example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,

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

Loading Data in Stream Record Format

load data
infile 'example.dat' "str '|\n'"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))

example.dat:
hello,world,|
james,bond,|

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

1 -- This is a sample control file
2 LOAD DATA
3 INFILE 'sample.dat'
4 BADFILE 'sample.bad'
5 DISCARDFILE 'sample.dsc'
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = '.'
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)


Utilities
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_concepts.htm#i1005437

SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc,
DISCARDMAX=5

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

#!/bin/sh
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac

-- -----------------------------
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)

example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,

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

Loading Data in Stream Record Format

load data
infile 'example.dat' "str '|\n'"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))

example.dat:
hello,world,|
james,bond,|

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

1 -- This is a sample control file
2 LOAD DATA
3 INFILE 'sample.dat'
4 BADFILE 'sample.bad'
5 DISCARDFILE 'sample.dsc'
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = '.'
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)

ocm4  Implement transportable tablespaces between homogeneous and heterogeneous systems (using different methods of moving files)
https://shenh.tripod.com/Oracleblog/index.blog/1824997/ocm4-implement-transportable-tablespaces/

begin
sys.dbms_tts.transport_set_check ( ts_list => 'TS1,TS2' );
end;
/

select * from sys.transport_set_violations;

For Source DB:
step 1): read only tablespace (mandatory step)
alter tablespace ts1 read only;
alter tablespace ts2 read only;

step 2):
exp '/ as sysdba'
transport_tablespace=y
tablespaces=$tbs_list
file=tts_exp.dmp
log=tts_exp.log

step 3):
alter tablespace xxxx read write;

For destination DB:
step 1):
destination offline and drop the tablespace if exists

step 2):
Import = imp file=tt.dmp log=tt.log tablespaces=test transportable_tablespace=y datafiles=(.......,
only avail in expdb/impdb combination:
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.

........)

imp \'/ as sysdba \' transport_tablespace=y tablespaces=ts1,ts2 file=tts.dmp parfile=imp.par log=tts_log

imp tts.par
datafiles=\
/u01/oradata/orcl/orcl_ts1_01.dbf,\
/u02/oradata/orcl/orcl_ts2_01.dbf

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

expdp system/manager DUMPFILE=tts.dmp DIRECTORY=trans_dir TRANSPORT_TABLESPACES=tts TRANSPORT_FULL_CHECK = TRUE

impdp system/manager parfile=c:\tts\trans.par

 -- create the following datapump parameter file: save it as trans.par
DUMPFILE=tts.dmp
DIRECTORY=trans_dir
REMAP_SCHEMA=uwclass:scott
TRANSPORT_DATAFILES='c:\temp\tts.dbf'

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

SQL> select name, platform_id,platform_name   
2 from   v$database;

NAME       PLATFORM_ID       PLATFORM_NAME
-------    -----------       -----------------------
GRID                 2       Solaris[tm] OE (64-

SELECT * FROM v$transportable_platform ORDER BY 1;

SQL> select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
          1 Solaris[tm] OE (32-bit)             Big
          2 Solaris[tm] OE (64-bit)             Big
          3 HP-UX (64-bit)                      Big
          4 HP-UX IA (64-bit)                   Big
          5 HP Tru64 UNIX                       Little
          6 AIX-Based Systems (64-bit)          Big
          7 Microsoft Windows IA (32-bit)       Little
          8 Microsoft Windows IA (64-bit)       Little
          9 IBM zSeries Based Linux             Big
         10 Linux IA (32-bit)                   Little
         11 Linux IA (64-bit)                   Little
         12 Microsoft Windows 64-bit for AMD    Little
         13 Linux 64-bit for AMD                Little
         15 HP Open VMS                         Little
         16 Apple Mac OS                        Big

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

RMAN>CONVERT TABLESPACE lmt_xact
  TO PLATFORM='Solaris[tm] OE (32-bit)'
  FORMAT='/u03/oracle/oradata/rptrepos';

RMAN> convert tablespace users
2> to platform 'HP-UX (64-bit)'
3>  format='/home/oracle/rman_bkups/%N_%f';

RMAN> convert tablespace users, maints
2> to platform 'HP-UX (64-bit)'
3> format='/home/oracle/rman_bkups/%N_%f'
4> parallelism = 5;

RMAN> convert tablespace users
2> to platform 'HP-UX (64-bit)'
3> db_file_name_convert '/usr/oradata/dw10/dw10','/home/oracle/rman_bkups'
4> ;

RMAN> convert
2> datafile '/usr/oradata/dw10/dw10/users01.dbf'
3> format '/home/oracle/rman_bkups/%N_%f'
4> ;

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

http://www.oracle.com/technology/pub/articles/10gdba/week16_10gdba.html

http://www.databasejournal.com/features/oracle/article.php/3600931

http://www.dbazine.com/olc/olc-articles/ault5

http://www.oracle.com/technology/deploy/availability/htdocs/xtts.htm

ocm4  Choose the appropriate partition method (range, hash, list, and composite)
https://shenh.tripod.com/Oracleblog/index.blog/1824999/ocm4-choose-the-appropriate-partition-method/

How to Implement Partitioning in Oracle Versions 8 and 8i
Doc ID: Note:105317.1

Admin Guide: chapter 17
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#ADMIN017


10g Concepts: chapter 18
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#CNCPT212

1):
Range Partitioning Example
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);

2):
List Partitioning Example
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

3):
Hash Partitioning Example
CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4
STORE IN (ts1, ts2, ts3, ts4);

4):
Composite Partitioning Range-Hash Example
CREATE TABLE sales_composite
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY'))
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY'))
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY'))
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
STORE IN (ts2, ts4, ts6, ts8),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
(SUBPARTITION p3_s1 TABLESPACE ts4,
SUBPARTITION p3_s2 TABLESPACE ts5));

-- range-list
CREATE TABLE sample_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
TABLESPACE tbs_1
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
TABLESPACE tbs_2
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
TABLESPACE tbs_3
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
TABLESPACE tbs_4
);

-- template
CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
SUBPARTITION TEMPLATE
(SUBPARTITION a TABLESPACE ts1,
SUBPARTITION b TABLESPACE ts2,
SUBPARTITION c TABLESPACE ts3,
SUBPARTITION d TABLESPACE ts4
)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

ocm4  Choose the appropriate partition key
https://shenh.tripod.com/Oracleblog/index.blog/1825000/ocm4-choose-the-appropriate-partition-key/

1):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#i449863

Partition Key
Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of one or more columns that determines the partition for each row. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partition key. A partition key:

Consists of an ordered list of 1 to 16 columns

Cannot contain a LEVEL, ROWID, or MLSLABEL pseudocolumn or a column of type ROWID

Can contain columns that are NULLable


Top Partitioned Tables Issues
Doc ID: Note:165599.1 

ocm4  Choose appropriate indexing methods (local, global, prefixed, and nonprefixed)
https://shenh.tripod.com/Oracleblog/index.blog/1825002/ocm4-choose-appropriate-indexing-methods/

admin guide: chapter 17
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1106387

Top Partitioned Index Issues
Doc ID: Note:165309.1

1):
Creating a Range-Partitioned Global Index
CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm2_ix VALUES LESS THAN (3)
PARTITION pm3_ix VALUES LESS THAN (4)
PARTITION pm4_ix VALUES LESS THAN (5)
PARTITION pm5_ix VALUES LESS THAN (6)
PARTITION pm6_ix VALUES LESS THAN (7)
PARTITION pm7_ix VALUES LESS THAN (8)
PARTITION pm8_ix VALUES LESS THAN (9)
PARTITION pm9_ix VALUES LESS THAN (10)
PARTITION pm10_ix VALUES LESS THAN (11)
PARTITION pm11_ix VALUES LESS THAN (12)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);

2): local index: index columns is the same as partition key columns

CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

CREATE INDEX emp_ix ON emp(deptno)
LOCAL STORE IN (ts7, ts8, ts9);


3):

ocm4  Perform partition maintenance operations
https://shenh.tripod.com/Oracleblog/index.blog/1825004/ocm4-perform-partition-maintenance-operations/

1): query
The following query displays the subpartition names and tablespaces:

SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE'
ORDER BY TABLESPACE_NAME;

2): modify
ADD PARTITION
COALESCE PARTITION, COALESCE SUBPARTITION
DROP PARTITION
EXCHANGE PARTITION, EXCHANGE SUBPARTITION
MERGE PARTITIONS, MERGE SUBPARTITIONS
MODIFY DEFAULT ATTRIBUTES
MODIFY PARTITION,MODIFY SUBPARTITION
MOVE PARTITION
RENAME PARTITION
SPLIT PARTITION
TRUNCATE PARTITION

admin guide: 17 Managing Partitioned Tables and Indexes
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#i1007318

ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;

ALTER TABLE sales ADD PARTITION q1_2000
VALUES LESS THAN (2000, 04, 01) COMPRESS
SUBPARTITIONS 8 STORE IN tbs5;

ALTER TABLE diving MODIFY PARTITION locations_us
ADD SUBPARTITION us_locs5 TABLESPACE us1;

ALTER TABLE quarterly_regional_sales
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);

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

ALTER INDEX q1_sales_by_region_locix
MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;

ALTER INDEX q1_sales_by_region_locix
REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;

ALTER INDEX hgidx ADD PARTITION p5;

ALTER TABLE ouu1
COALESCE PARTITION;

ALTER TABLE diving MODIFY PARTITION us_locations
COALESCE SUBPARTITION;

ALTER INDEX hgidx COALESCE PARTITION;

1):
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;

2):
DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales DROP PARTITION dec98;

3):
ALTER TABLE sales DROP PARTITION dec98
UPDATE INDEXES;

-- -----------------------------------
1):
ALTER TABLE sales
DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales DROP PARTITTION dec98;
ALTER TABLE sales
ENABLE CONSTRAINT dname_sales1;

2):
DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales DROP PARTITION dec94;

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

ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;

-- ------------------------------------
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX

ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
WITH VALIDATION;

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

ALTER TABLE sales EXCHANGE SUBPARTITION q3_1999_s1
WITH TABLE q3_1999 INCLUDING INDEXES;

-- ----------------------------------------
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;

ALTER TABLE four_seasons MODIFY PARTITION
quarter_two REBUILD UNUSABLE LOCAL INDEXES;


ALTER TABLE q1_sales_by_region
MERGE PARTITIONS q1_northcentral, q1_southcentral
INTO PARTITION q1_central
STORAGE(MAXEXTENTS 20);

ALTER TABLE all_seasons
MERGE PARTITIONS quarter_1, quarter_2 INTO PARTITION quarter_2
SUBPARTITIONS 8;

ALTER TABLE quarterly_regional_sales
MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest
INTO SUBPARTITION q1_1999_west
TABLESPACE ts4;
-- -------------------------------------------
ALTER TABLE emp
MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;

ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;

ALTER TABLE dept MODIFY PARTITION p1
REBUILD UNUSABLE LOCAL INDEXES;

ALTER TABLE sales_by_region
MODIFY PARTITION region_south
ADD VALUES ('OK', 'KS');

ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
ADD VALUES ('KS');

ALTER TABLE sales_by_region
MODIFY PARTITION region_south
DROP VALUES ('OK', 'KS');

ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
DROP VALUES ('KS');

-- --------------------------------------
template:
ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE
(SUBPARTITION e TABLESPACE ts1,
SUBPARTITION f TABLESPACE ts2,
SUBPARTITION g TABLESPACE ts3,
SUBPARTITION h TABLESPACE ts4
);

You can drop a subpartition template by specifying an empty list:

ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE ( );

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

ALTER TABLE parts MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING COMPRESS;

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

ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types
TABLESPACE tbs23 PARALLEL (DEGREE 2);

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

ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;

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

ALTER TABLE sales_by_region
SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD')
INTO
( PARTITION region_east_1
TABLESPACE tbs2,
PARTITION region_east_2
STORAGE (NEXT 2M PCTINCREASE 25))
PARALLEL 5;

ALTER TABLE sales_by_region
SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID')
INTO
( PARTITION region_wildwest,
PARTITION region_unknown);

ALTER TABLE all_seasons SPLIT PARTITION quarter_1
AT (TO_DATE('16-dec-1997','dd-mon-yyyy'))
INTO (PARTITION q1_1997_1 SUBPARTITIONS 4 STORE IN (ts1,ts3),
PARTITION q1_1997­_2);

ALTER TABLE quarterly_regional_sales SPLIT PARTITION q1_1999
AT (to_date('15-Feb-1999','dd-mon-yyyy'))
INTO ( PARTITION q1_1999_jan_feb
TABLESPACE ts1,
PARTITION q1_1999_feb_mar
STORAGE (NEXT 2M PCTINCREASE 25) TABLESPACE ts2)
PARALLEL 5;

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

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

ocm4  Maintain indexes on a partitioned table
https://shenh.tripod.com/Oracleblog/index.blog/1825005/ocm4-maintain-indexes-on-a-partitioned-table/

 SKIP_UNUSABLE_INDEXES init.ora parameter
UPDATE INDEXES sub clauses

1): By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

ALTER TABLE DROP PARTITION P1 UPDATE INDEXES;

How to Backup Partition of Range Partitioned Table with Local Indexes
Doc ID: Note:412264.

ocm4  Create and manage LOB segments
https://shenh.tripod.com/Oracleblog/index.blog/1825007/ocm4-create-and-manage-lob-segments/

http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10796/adlob_ta.htm

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_3001.htm

http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml

CREATE TABLE test_lobtable (
id NUMBER
, xml_file CLOB
, image BLOB
, log_file BFILE
)
LOB (xml_file, image)
STORE AS (
TABLESPACE lob_data
CHUNK 4096
CACHE
STORAGE (MINEXTENTS 2)
INDEX (
TABLESPACE lob_index
STORAGE (MAXEXTENTS UNLIMITED)
)
)
/

ALTER TABLE test_lobtable
ADD (image2 BLOB)
LOB (image2) STORE AS image2_lob_seg (
TABLESPACE lob_data
CHUNK 4096
PCTVERSION 5
ENABLE STORAGE IN ROW
INDEX image2_lob_idx (
TABLESPACE lob_index
)
)
/

ALTER TABLE test_lobtable
MODIFY LOB (image) (
STORAGE (NEXT 1M)
CACHE
)
/

ALTER TABLE test_lobtable
MOVE TABLESPACE users2
LOB (xml_file) STORE AS (
TABLESPACE lob_data2
)
/

SELECT
table_name "Table"
, column_name "Column"
, segment_name "Segment"
, index_name "Index"
FROM user_lobs
WHERE table_name = 'TEST_LOBTABLE';


SELECT
segment_name "Segment"
, segment_type "Type"
, tablespace_name "Tablespace"
FROM user_segments
ORDER BY segment_name DESC;

Segment TS Type Tablespace
------------------------- ------------ ------------------------------
XML_FILE_LOB_SEG LOBSEGMENT LOB_DATA
XML_FILE_LOB_IDX LOBINDEX LOB_DATA
TEST_LOBTABLE TABLE USERS
IMAGE_LOB_SEG LOBSEGMENT LOB_DATA2
IMAGE_LOB_IDX LOBINDEX LOB_DATA2
IMAGE2_LOB_SEG LOBSEGMENT LOB_DATA
IMAGE2_LOB_IDX LOBINDEX LOB_DATA

ocm4  Apply parallelism appropriately
https://shenh.tripod.com/Oracleblog/index.blog/1825009/ocm4-apply-parallelism-appropriately/ 

1): data warehouse guide: Parallelization Rules for SQL Statements
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#i1007537

2): data warehouse guide: query and monitor
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#i1008510

ocm4  Tune memory for parallel operations
https://shenh.tripod.com/Oracleblog/index.blog/1825010/ocm4-tune-memory-for-parallel-operations/

On systems where parallel execution will never be used, PARALLEL_MAX_SERVERS can be set to zero.

On large systems with abundant SGA memory, PARALLEL_EXECUTION_MESSAGE_SIZE can be increased to improve throughput.

data warehouse guide
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#sthref2210

ocm4  Set parallel parameters
https://shenh.tripod.com/Oracleblog/index.blog/1825011/ocm4-set-parallel-parameters/

1):
show parameter parallel

eg: PARALLEL_MAX_SERVERS


2):
ALTER TABLE orders PARALLEL 4;
ALTER INDEX iorders PARALLEL 4;
SELECT /*+ PARALLEL(orders, 4) */ COUNT(*) FROM orders;


ALTER SESSION DISABLE PARALLEL DML|DDL|QUERY
ALTER SESSION ENABLE PARALLEL DML|DDL|QUERY

eg:
ALTER SESSION DISABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;

ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;

Posted by shenh at 10:58 PM EST

Updated: Tuesday, 6 January 2009 11:23 PM EST

Share This Post

Permalink

ocm3  Create a recovery catalog database
https://shenh.tripod.com/Oracleblog/index.blog/1824950/ocm3create-a-recovery-catalog-database/

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb001.htm

CREATE USER rman IDENTIFIED BY cat
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE tools
QUOTA UNLIMITED ON tools;

sql>GRANT RECOVERY_CATALOG_OWNER TO rman;


rman
RMAN> CONNECT CATALOG rman/cat@catdb

Run the CREATE CATALOG command to create the catalog. The creation of the catalog can take several minutes. If the catalog tablespace is this user's default tablespace, then you can run this command:

CREATE CATALOG;

You can specify the tablespace name for the catalog in the CREATE CATALOG command. For example:

CREATE CATALOG TABLESPACE cat_ts;

You can check the results by using SQL*Plus to query the recovery catalog to see which tables were created:

SQL> SELECT TABLE_NAME FROM USER_TABLES;

ocm3  Configure Recovery Manager
https://shenh.tripod.com/Oracleblog/index.blog/1824951/ocm3configure-recovery-manager/

http://www.globusz.com/ebooks/Oracle/00000018.htm#3

register, unregister a target database

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmcatdb002.htm

% rman TARGET / CATALOG rman/cat@catdb

If the target database is not mounted, then mount or open it:

RMAN> STARTUP MOUNT;

Register the target database in the connected recovery catalog:

RMAN> REGISTER DATABASE;

RMAN> REPORT SCHEMA;

Cataloging Older Files in the Recovery Catalog


If you have datafile copies, backup pieces or archive logs on disk, you can catalog them in the recovery catalog using the CATALOG command. When using a recovery catalog, cataloging older backups that have aged out of the control file lets RMAN use the older backups during restore operations. For example:

RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';
RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf',
'/disk1/arch_logs/archive1_732.dbf';
RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';
You can also catalog multiple backup files in a directory at once, using the CATALOG START WITH command, as shown in this example:

RMAN> CATALOG START WITH '/disk1/backups/';

RMAN lists the files to be added to the RMAN repository and prompts for confirmation before adding the backups.

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

unregister:

LIST BACKUP SUMMARY
LIST COPY SUMMARY

DELETE BACKUP DEVICE TYPE sbt;
DELETE BACKUP DEVICE TYPE DISK;
DELETE COPY;

RMAN will list the backups that it intends to delete and prompt for confirmation before deleting them.

Run the UNREGISTER DATABASE command. For example:

UNREGISTER DATABASE;

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

The script removes all records with status DELETED from the recovery catalog.
@?/rdbms/admin/prgrmanc.sql

-- ---------------------------------------------
RESYNC CATALOG :
populate controlfile information about the database to rman catalog

CONTROL_FILE_RECORD_KEEP_TIME


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

instance recovery: FAST_START_MTTR_TARGET
1): ALTER SYSTEM SET FAST_START_MTTR_TARGET=30;

2): The maximum value for FAST_START_MTTR_TARGET is 3600 seconds (one hour).

3):
SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR
FROM V$INSTANCE_RECOVERY;

-- -----------------------------------
sbtest:
$ORACLE_HOME/lib/libobk.so on UNIX

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt
PARMS='SBT_LIBRARY=/vendor/lib/some_mm_lib.so',
'ENV=(NSR_SERVER=tape_svr,NSR_CLIENT=oracleclnt,NSR_GROUP=oracle_tapes)';
}

% sbttest some_file.f -trace sbtio.log
% sbttest tbs_33.f -dbname prod

ocm3  Use Recovery Manager to perform database backups
https://shenh.tripod.com/Oracleblog/index.blog/1824952/ocm3use-recovery-manager-to-perform-database-backups/

rman target=/
{
allocate channel c1 ;
allocate channel c2 ;
allocate channel c3 ;
backup incremental level=0 database;
}

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

LIST SCRIPT NAMES;
LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES;

CREATE SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

RUN { EXECUTE SCRIPT full_backup; }

PRINT SCRIPT full_backup;
PRINT SCRIPT full_backup TO FILE 'my_script_file.txt';

REPLACE SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}

DELETE SCRIPT 'full_backup'; 

ocm3  Use Recover Manager to perform complete database restore and recovery operations
https://shenh.tripod.com/Oracleblog/index.blog/1824953/ocm3use-recover-manager-to-perform-complete-database-restore-and-recovery-operations/

Best Test Case:
http://www.oracle.com.cn/viewthread.php?tid=50419

== ======================================

1): restore a bad control file

rman nocatalog target "sys/sunsdl"
RMAN> configure controlfile autobackup on;
RMAN> configure controlfile autobackup format for device type disk to 'c:\temp\%F';
RMAN> backup database;

text file:
SQL> alter database backup controlfile to trace;

binary file:
SQL> alter database backup controlfile to 'c:\temp\controlfile.bak';

RMAN> copy CURRENT CONTROLFILE TO 'c:\cf.cpy';

recover:
SQL> shutdown abort;
SQL> startup nomount

 CREATE CONTROLFILE REUSE DATABASE "DF" NORESETLOGS  ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 3
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 'C:\ORACLE\ORADATA\PDF\REDO01.LOG'  SIZE 100M,
10    GROUP 2 'C:\ORACLE\ORADATA\PDF\REDO02.LOG'  SIZE 100M,
11    GROUP 3 'C:\ORACLE\ORADATA\PDF\REDO03.LOG'  SIZE 100M
12  -- STANDBY LOGFILE
13  DATAFILE
14    'C:\ORACLE\ORADATA\PDF\SYSTEM01.DBF',
15    'C:\ORACLE\ORADATA\PDF\UNDOTBS01.DBF',
16    'C:\ORACLE\ORADATA\PDF\INDX01.DBF',
17    'C:\ORACLE\ORADATA\PDF\TOOLS01.DBF',
18    'C:\ORACLE\ORADATA\PDF\USERS01.DBF'
19  CHARACTER SET ZHS16GBK
20  ;

SQL> alter database open;
SQL> startup
SQL> recover database ;  --because of shutdown abort
SQL> alter database open;

SQL> startup mount
SQL> recover database using backup controlfile;
SQL> recover database using backup controlfile until cancel;
SQL> alter database open resetlogs;

select CHECKPOINT_CHANGE# from v$database;

SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          5 NO  CURRENT
         2          3 YES ACTIVE 
         3          4 YES ACTIVE

SQL> select resetlogs_change# - 1,CONTROLFILE_CHANGE# from v$database;

RESETLOGS_CHANGE#-1 CONTROLFILE_CHANGE#
------------------- -------------------
              68658               69410

== =================================================================

RMAN> restore controlfile from autobackup;
RMAN> set dbid=3171861129  -- if startup nomount is run by rman, don't need this
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/temp/%F';

alter database clear logfile group 1;
alter database clear logfile group 2;

SQL> alter database open noresetlogs;
SQL> alter database open resetlogs;

SQL> archive log list;
SQL> alter system switch logfile;

SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          2 YES ACTIVE
         2          1 YES INACTIVE
         3          3 NO  CURRENT
-- ----------------------------------------------------------
-- ----------------------------------------------------------
-- ----------------------------------------------------------

RMAN>  backup format 'c:\noarch_%s%p1.bak' database;
RMAN> copy current controlfile to 'c:\currcont.ctl';

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

RMAN> run {
?uro;€?uro;€?uro;€ allocate channel test type disk;
?uro;€?uro;€?uro;€ restore controlfile to 'd:\control.ctl';
       release channel test;
?uro;€?uro;€?uro;€}

RMAN> run {
        set until time='2005-02-17 15:01:20';
        restore database;
        recover database;
        alter database open resetlogs;
        }

RMAN> register database;
     RMAN> list incarnation of database;
     RMAN> reset database;
     RMAN> list incarnation of database;
    
SQL> alter database clear unarchived logfile 'D:\ORACLE\ORADATA\ORACLE\REDO03.LOG';                                                                                                                                           
SQL> alter database clear unarchived logfile 'D:\ORACLE\ORADATA\ORACLE\REDO02.LOG';                                                                            
SQL> alter database clear unarchived logfile 'D:\ORACLE\ORADATA\ORACLE\REDO01.LOG';

RMAN> run {
       allocate channel test type disk;
       SET ARCHIVELOG DESTINATION TO 'D:\oracle\oradata\archive';
       RESTORE ARCHIVELOG ALL;
       release channel test;
     }

STARTUP MOUNT
     RMAN> run {
     2> restore datafile 1;
     3> recover datafile 1;
     4> alter database open;
     5> }
    
RMAN> run {
     2> restore tablespace system;
     3> recover tablespace system;
     4> alter database open;
     5> }

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

select US#,NAME,ts#,STATUS$,FLAGS from undo$;

*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$)
_offline_rollback_segments=true

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

ocm3  Set Flashback Database parameters
https://shenh.tripod.com/Oracleblog/index.blog/1824954/ocm3set-flashback-database-parameters/

http://www.globusz.com/ebooks/Oracle/00000018.htm

1. flashback logs
2. recyclebin
3. undo tablespace and undo retention

DB_RECOVERY_FILE_DEST=/u01/...
DB_RECOVERY_FILE_DEST_SIZE=20G
DB_FLASHBACK_RETENTION_TARGET=1440

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=$ORACLE_BASE/flash_recovery_area/mylcl/
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
SQL>archive log list #-- Ensure that the database is in archive log mode
SQL> ALTER DATABASE FLASHBACK ON;
DISABLE FLASH BACK
SQL> ALTER DATABASE FLASHBACK OFF;

-- ------------------------------------------------
startup MOUNT EXCLUSIVE ;
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
SELECT FLASHBACK_ON FROM V$DATABASE;

ALTER TABLESPACE USERS FLASHBACK OFF;  

ocm3  Monitor Flashback Database logs and statistics
https://shenh.tripod.com/Oracleblog/index.blog/1824955/ocm3monitor-flashback-database-logs-and-statistics/

V$FLASHBACK_DATABASE_STAT
V$FLASHBACK_DATABASE_LOG

select logon_user, operation, table_name, table_owner from flashback_transaction_query where hextoraw(xid) = '04001E0015020000';

http://kevin.allenpress.com/docs/oracle10g/backup.102/b14192/rpfbdb003.htm

http://www.globusz.com/ebooks/Oracle/00000019.htm

ocm3  Perform a Flashback Database operation
https://shenh.tripod.com/Oracleblog/index.blog/1824956/ocm3perform-a-flashback-database-operation/

init.ora
db_recovery_file_dest
db_recovery_file_dest_size

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

sql> startup mount exclusive;
sql> alter database flashback on;

-- ------------------------------------
1. shudown the database;
2. startup the database in mount state;
3. sql> flashback database to
timestamp to_timestamp('2004-12-16 16:10:10', 'YYYY-MM-DD HH24:MI:SS');
3a): sql>flashback database to scn 1329643;
4. open the database -- open resetlogs;

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


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm

http://orafaq.com/node/50

http://www.globusz.com/ebooks/Oracle/00000019.htm

V$DATABASE
V$FLASHBACK_ DATABASE_LOG
V$FLASHBACK_ DATABASE_STAT

http://www.oracle.com/technology/obe/obe10gdb/ha/flashback/flashback.htm

http://www.oracle.com/technology/oramag/oracle/flashback/index.html
http://www.oracle.com/technology/oramag/oracle/06-nov/o66recovery.html
http://www.oracle.com/technology/oramag/oracle/05-may/o35ocp.html
http://www.oracle.com/technology/oramag/oracle/05-mar/o25ocp.html
http://www.oracle.com/technology/oramag/oracle/04-may/o34tech_avail.html

http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html


http://www.oracle-base.com/articles/10g/Flashback10g.php

ocm3  Configure a flash recovery area
https://shenh.tripod.com/Oracleblog/index.blog/1824957/ocm3configure-a-flash-recovery-area/

http://www.oracleadvice.com/10g/10g_RMAN.htm

db_recovery_file_dest_size # Set size of flash recovery area
db_recovery_file_dest # Set location of flash recovery area. Turns on OMF feature.

Contents
Datafile: copies Created by the new ‘BACKUP AS COPY’ RMAN command

Controlfile auto-backups Including SPFILEs

Archived redo logs: Oracle automatically housekeeps old files

Flashback logs: Changed block copies used to reconstruct datafiles

RMAN files / backupsets: Created by RMAN

*Online redo logs: Oracle can generate names of multiplexed copies
log_archive_dest_n=db_recovery_file_dest

*Current Controlfile: Multiplexed copy


The log_archive_dest and log_archive_duplex_dest parameters are not compatible with a flash recovery area. You must use log_archive_dest_n instead.

log_archive_dest_10 is implicitly set to the db_recovery_file_dest.

OMF: you should use different locations for
• db_create_file_dest
• db_create_online_log_dest_n
• db_recovery_file_dest

control file location:
control_files: init.ora create one copy of each named file (non-OMF)
db_create_online_log_dest_n: Place one controlfile copy in each of db_create_online_log_dest_n
db_create_file_dest & db_recovery_file_dest: one controlfile copy each
db_recovery_file_dest: OMF controlfile in the flash recovery area only
None non-OMF controlfile in a platform-dependent place


online redo logs location:
1. Place one member in each of db_create_online_log_dest_n up to MAXLOGMEMBERS
2. Place one member in each of db_create_file_dest & db_recovery_file_dest
3. Create one member in db_recovery_file_dest as a permanent file
4. Create non-OMF online redo logs in a platform-dependent place

Posted by shenh at 10:02 PM EST

Updated: Tuesday, 6 January 2009 10:16 PM EST

Share This Post

Permalink

http://static7.userland.com/oracle/gems/alejandroVargas/GridControl10gR2Install.pdf

http://download.oracle.com/docs/cd/B12037_01/em.101/b12013/firewalls.htm


port info:
$ORACLE_HOME/sysman/setupinfo.txt
ORACLE_HOME/sysman/config/emoms.properties
ORACLE_HOME/install/portlist.ini

# Start everything
/u01/app/oracle/product/10.2.0/db10g/bin/dbstart
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl startall
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl start agent

# Stop everything
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl stop agent
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
/u01/app/oracle/product/10.2.0/db10g/bin/dbshut

http://www.iselfschooling.com/oracle10g/Grid.htm




http://oracle.iselfschooling.com:7777/em

http://njds0036:7777/em

http://gcds0040.private.linksynergy.com:4889/em

sysman sys password



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

http://www.itpub.net/224872.html

http://www.google.com/search?hl=en&q=site%3Awww.oracle.com+obe+gridcontrol

http://www.oracle.com/technology/obe/obe10gemgc_10202/install/install.htm

http://www.oracle.com/technology/obe/obe10gemgc_10203/patching/patching.htm

http://www.oracle.com/technology/obe/obe10gem/index.html
http://www.oracle.com/technology/obe/obe10gem/indexR1.html
http://www.oracle.com/technology/obe/obe10gemgc_10202/index.html
http://www.oracle.com/technology/obe/obe10gemgc_10202/patching/patching.htm
http://www.oracle.com/technology/obe/obe10gemgc_10203/index.html

http://www.oracle.com/technology/obe/obe10gemgc_10203/patchwizard/patchwizard.htm

http://www.oracle.com/technology/obe/obe10gem/grid/ctrlmng/ctrlmng.htm

http://www.oracle.com/technology/obe/obe10gem/install/index.html
http://www.oracle.com/technology/obe/obe10gem/install/gridinst/gridinst.htm
http://www.oracle.com/technology/obe/obe10gem/install/agentinst/agentinst.htm
http://www.oracle.com/technology/obe/obe10gem/install/mngadmin/mngadmin.htm
http://www.oracle.com/technology/obe/obe10gem/install/addwebapp/addwebapp.htm

http://www.oracle.com/technology/obe/obe10gemgc_10202/patchwizard/patchwizard.htm

http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/enterprise_manager_configuration/enterprise_configuration_and_policies.htm

http://www.oracle.com/technology/obe/obe10gemgc_10202/host/host.htm

http://www.oracle.com/technology/obe/obe10gemgc_10202/slm/slm.htm https://shenh.tripod.com/Oracleblog/index.blog/1824923/ocm2-configure-the-enterprise-manager-repository/

ocm2  Configure the Enterprise Manager repository
http://www.oracle-base.com/articles/10g/Oracle10gR2GridControlInstallationOnRedHatAndCentOS.php 

http://users.telenet.be/oraguy.be/Papers/configure%20the%20database%20control%20repository.pdf

-- -----------------------------------
Manually configure the Oracle 10g EM dbconsole
emca -deconfig dbcontrol db -repos drop

-- drop the sysman schema by following command
emca -deconfig dbcontrol db -repos drop

-- create it again
emca -deconfig dbcontrol db -repos create.

emca -repos create
emca -config dbcontrol db

http://10.130.132.94/temp/phpbb2/viewtopic.php?t=108

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

sysman

emctl emctl status dbconsole emctl status agent
emca
repmanager

$ORACLE_HOME\sysman\admin\emdrep\bin\RepManager -connect UTF8 -action drop

%ORACLE_HOME%\install\portlist.ini

http://www.oracle.com/technology/deploy/availability/pdf/EM_HA_Arch_10gR1.pdf
http://www.oracle.com/technology/deploy/availability/pdf/MAA_WP_10gR2_EnterpriseManagerBestPractices.pdf


Software download db2 : hp-ux ibm-aix
http://www.itpub.net/634237.html

ocm2  Create Enterprise Manager Grid Control users
https://shenh.tripod.com/Oracleblog/index.blog/1824924/ocm2-create-enterprise-manager-grid-control-users/

grid control home --> setup --> administrators --> create

Creating Roles and Administrators
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/creating_roles_administrators/creating_administrators_and_roles.htm



http://www.oracle.com/technology/obe/obe10gEMR2/quick_start/index.html




Preferred Credentials (user password etc )
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/preferred_credentials/preferred_credentials.htm

System Monitoring ( lots of stuff: user-define, baseline )
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/system_monitoring/system_monitoring.htm

Creating Groups ( operation applying to all targets in a group)
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/groups/groups.htm


System and Services ( define a system and a service, and use Root Cause Analysis.)
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/system_services/system_services.htm

Enterprise Configuration Management (hardware,software change information )
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/enterprise_manager_configuration/enterprise_configuration_and_policies.htm

Provisioning (patching,clone db)
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/provisioning/provisioning.htm

Information Publisher (create, schedule, and publish reports)
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/information_publishers/information_publishers.htm


Management Plug-ins (third party software)
management plug-in is a target type provided by the user or a third party to extend Enterprise Manager's set of predefined target types.
http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/extensibility/extensibility.htm 

ocm2  Use Enterprise Manager to modify a database configuration
https://shenh.tripod.com/Oracleblog/index.blog/1824925/ocm2-use-enterprise-manager-to-modify-a-database-configuration/

ocm2  Configure Enterprise Manager to modify a database availability
https://shenh.tripod.com/Oracleblog/index.blog/1824927/ocm2-configure-enterprise-manager-to-modify-a-database-availability/

select a database over databases or target --> adminitration

ocm2  Create and manage jobs
https://shenh.tripod.com/Oracleblog/index.blog/1824929/ocm2-create-and-manage-jobs/

Creating Jobs

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_job.htm#BABHCBFD

http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/jobs/creating_jobs.htm

grid control home --> jobs (tab)

1):
init.ora
job_queue_processes = 10

2):
dba_jobs

3):
dbms_job.*


dba_jobs_running

BEGIN
DBMS_JOB.CHANGE(14144, null, null, 'sysdate+3');
COMMIT;
END;

note: must commit;

http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_job.htm#996728

http://www.psoug.org/reference/dbms_job.html

http://www.dba-oracle.com/tips_oracle_dbms_job.htm


DBMS_SCHEDULER

ocm2  Create and monitor alerts
https://shenh.tripod.com/Oracleblog/index.blog/1824927/ocm2-configure-enterprise-manager-to-modify-a-database-availability/

all targets (or other ) --> manage metrics --> edit thresholds

http://www.globusz.com/ebooks/Oracle/00000014.htm

DBMS_SERVER_ALERT.*

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_salt.htm#sthref6921

1):
DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000',
1, 2, 'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'dev.oracle.com');

a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call.

The observation period is set to 1 minute. This period specifies the number of minutes that the condition must deviate from the threshold value before the alert is issued.

The number of consecutive occurrences is set to 2. This number specifies how many times the metric value must violate the threshold values before the alert is generated.

The name of the instance is set to inst1.

The constant DBMS_ALERT.OBJECT_TYPE_SERVICE specifies the object type on which the threshold is set. In this example, the service name is dev.oracle.com.

Note: If you don’t want Oracle to send any metric-based alerts, simply set the warning value and the critical value to NULL.


2):
DBMS_SERVER_ALERT.GET_THRESHOLD(
metrics_id IN NUMBER,
warning_operator OUT NUMBER,
warning_value OUT VARCHAR2,
critical_operator OUT NUMBER,
critical_value OUT VARCHAR2,
observation_period OUT NUMBER,
consecutive_occurrences OUT NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,
object_name IN VARCHAR2)




-- ------------------------------------------------
DBA_ALERT_HISTORY
dba_outstanding_alerts
dba_thresholds

v_$active_session_history
dba_advisor_journal
dba_advisor_objects
dba_advisor_parameters
dba_advisor_rationale
dba_advisor_recommendations
dba_advisor_sqlw_journal
dba_advisor_tasks
dba_advisor_usage
dba_hist_snapshot
dba_alert_history
dba_outstanding_alerts
dba_thresholds


http://download-east.oracle.com/docs/cd/B14099_19/manage.1012/b16242/udm2.htm 

ocm2  Create notifications
https://shenh.tripod.com/Oracleblog/index.blog/1824931/ocm2-create-notifications/

Notifications ( executing operating system commands (including scripts) and PL/SQL procedures )
By using SNMP traps, the Enterprise Manager notification system also enables you to invoke the functionality of SNMP-enabled third-party applications such as HP OpenView. Some administrators may want to send a notification to third-party applications when a certain metric has exceeded a threshold.

Notifications are defined via notification methods and notification rules.

http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/notification/notification.htm


http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm#sthref1645

DBMS_CHANGE_NOTIFICATION.*

step 1):
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO HR;
GRANT CHANGE NOTIFICATION TO HR;

ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4; 

ocm2  Implement Grid Control and Database Control
https://shenh.tripod.com/Oracleblog/index.blog/1824933/ocm2-implement-grid-control-and-database-control/

 -- ----------------------------------------------------------
Manually configure the Oracle 10g EM dbconsole

emca -deconfig dbcontrol db -repos drop

-- drop the sysman schema by following command
emca -deconfig dbcontrol db -repos drop

-- create it again
emca -deconfig dbcontrol db -repos create.


execute dbms_workload_repository.modify_snapshot_settings(interval => 0);

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

create:
http://www.akadia.com/services/ora_dbconsole.html

drop and recreate:
http://forums.oracle.com/forums/thread.jspa?threadID=478330&tstart=60

Now start the Oracle EM dbconsole Build Script
($ORACLE_HOME/bin/emca for Linux and
$ORACLE_HOME\Bin\emca.bat for Windows).

$ emca -repos create
$ emca -config dbcontrol db

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

[oracle@gcds0022 ~]$ cd $ORACLE_HOME/bin
[oracle@gcds0022 bin]$ emctl status dbconsole
TZ set to GMT
OC4J Configuration issue. /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBC

onsole_gcds0022.private.linksynergy.com_ORCL not found.
[oracle@gcds0022 bin]$ emca -repos create

STARTED EMCA at Jun 28, 2007 10:43:28 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jun 28, 2007 10:48:30 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgt

oollogs/emca/ORCL/emca_2007-06-28_10-43-28-AM.log.
Jun 28, 2007 10:48:30 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jun 28, 2007 10:51:07 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 28, 2007 10:51:07 AM
[oracle@gcds0022 bin]$ emca -config dbcontrol db

STARTED EMCA at Jun 28, 2007 10:51:33 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): hshen@linkshare.com
Outgoing Mail (SMTP) server for notifications (optional): mail.linkshare.com
Jun 28, 2007 10:54:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 28, 2007 10:54:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://gcds0022.private.linksynergy.com:1158/em

<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 28, 2007 10:54:25 AM

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

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.2.0/db_1

Database hostname ................ gcds0022.private.linksynergy.com
Listener port number ................ 1521
Database SID ................ ORCL
Email address for notifications ............... hshen@linkshare.com
Outgoing Mail (SMTP) server for notifications ............... mail.linkshare.com

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jun 28, 2007 10:52:38 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/ORCL/

emca_2007-06-28_10-51-33-AM.log.
Jun 28, 2007 10:52:45 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...

== ===================================================

alter system set job_queue_processes=10 scope=both;

emca -config dbcontrol db

== ===================================================

/u01/app/oracle/product/10.2.0/db_1/bin/emctl start dbconsole

$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole

== ====================================================

RC scripts: auto start db console:

http://www.akadia.com/download/documents/dbconsole.txt


http://lvds0025.private.linksynergy.com:1158/em

== ===================================================

sysman: dbconsole repository

== ==================================================

This operation is being logged at
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/ORCL/emca_2007-03-19_04-30-57-PM.log

/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_lvds0023.private.linksynergy.com_ORCL

isqlplusctl status

isqlplusctl stop

emctl status dbconsole

emctl stop dbconsole

show parameter job_queue_processes

alter system set job_queue_processes=0 scope=both;

alter system set job_queue_processes=10 scope=both;


disable crontab jobs


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


http://lvds0025.private.linksynergy.com:1158/em/console/aboutApplication

/u01/app/oracle/product/10.2.0/cfgtoollogs/emca/ORC1/emca_repos_drop_2007-03-19_03-47-29-PM.log

[19-03-2007 15:47:30] Dropping the repository..
[19-03-2007 15:47:30] Quiescing DB ...
[19-03-2007 15:47:30] File - /tmp/JuvsldGrz6/kd1RrI7SlL.sql.
[19-03-2007 16:11:30] Return code = 245.
[19-03-2007 16:11:31] Failed.
[19-03-2007 16:11:31] Checking for Repos User ...
[19-03-2007 16:11:31] File - /tmp/K3aPcaoazi/Jaolu9kcLv.sql.
[19-03-2007 16:11:39] Return code = 0.
[19-03-2007 16:11:39] Exists.
[19-03-2007 16:11:39] Repos User exists..
[19-03-2007 16:11:39] Clearing EM Contexts ...
[19-03-2007 16:11:39] Clearing contexts. File - /tmp/T74p04p7L2/hnhVJkOy05.sql.

[19-03-2007 16:13:58] Return code = 0.
[19-03-2007 16:13:58] Done.
[19-03-2007 16:13:58] Unquiescing DB ...
[19-03-2007 16:13:58] Temp File - /tmp/jgBIRtOqMx/wN1CZj0YlT.sql.
[19-03-2007 16:13:58] Return code = 0.
[19-03-2007 16:13:58] Done.
[19-03-2007 16:13:58] Dropped Repository Successfully.


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

drop and recreate the dbcontrol repository.
emca -deconfig dbcontrol db -repos drop

Delete any remaining subdirectories in the ORACLE_HOME with the old
hostname).

emca -config dbcontrol db -repos create

== =================================================


begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(527040,30); end;

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

ALTER SYSTEM SET pga_aggregate_target = 629145600 COMMENT='1200M'
ALTER SYSTEM SET workarea_size_policy = "AUTO"

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

create index LINK.MERCHANT_SITE("MID","BID_STATUS");

isqlplusctl start| stop

isqlplusctl start

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

ocm2  Choose the appropriate tablespace type for the intended use
https://shenh.tripod.com/Oracleblog/index.blog/1824934/ocm2-choose-the-appropriate-tablespace-type-for-the-intended-use/

dedicated tablespace for user sysman and user mgmt_views, locally managed tablespace, automatic segment management.

ocm2  Create Scheduler jobs
https://shenh.tripod.com/Oracleblog/index.blog/1824935/ocm2-create-scheduler-jobs/

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHHBGGI

http://www.oracle.com/technology/obe/obe10gEMR2/Quick_Start/jobs/creating_jobs.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/schedadmin.htm

http://www.globusz.com/ebooks/Oracle/00000017.htm

-- ----------------------------------------------------------
--Verify enabled status:
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

select * from dba_jobs;

select * from DBA_SCHEDULER_JOBS;

select * from DBA_SCHEDULER_JOBS where job_name = 'GATHER_STATS_JOB';

--disable
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

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

ocm2  Create schedules
https://shenh.tripod.com/Oracleblog/index.blog/1824936/ocm2-create-schedules/

http://www.globusz.com/ebooks/Oracle/00000017.htm

Creating a Schedule

DBMS_SCHEDULER.CREATE_SCHEDULE(

SCHEDULE_NAME => 'TEST_SCHEDULE',

START_DATE => SYSTIMESTAMP,

END_DATE => SYSTIMESTAMP + 30,

REPEAT_INTERVAL => 'FREQ=HOURLY;INTERVAL= 12',

COMMENTS => 'EVERY 12 HOURS')

Note the following about creating a Schedule:

When you create a schedule, Oracle provides access to PUBLIC. Thus, all users can use your schedule, without any explicit grant of privileges to do so.
You specify the start and end times using the TIMESTAMP WITH TIME ZONE datatype. The Scheduler also supports all NLS_TIMESTAMP_TZ_FORMAT settings.

You must use a calendering expression to create the repeat interval.

DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'TEST_JOB02',
PROGRAM_NAME => 'TEST_PROGRAM',
SCHEDULE_NAME => 'TEST_SCHEDULE')

Altering a Schedule

You can alter the attributes (except SCHEDULE_NAME) of a schedule by using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package.

Dropping a Schedule

DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => 'TEST_SCHEDULE');

When you drop a schedule by using the FORCE=TRUE attribute, you’ll drop the schedule, even if there are jobs and windows that use the schedule. The Scheduler first disables the dependent jobs/windows before dropping the schedule itself. 

ocm2  Assign jobs to windows
https://shenh.tripod.com/Oracleblog/index.blog/1824938/ocm2-assign-jobs-to-windows/

Working with Scheduler Windows

Windows enable the automatic changing of resource plans based on a schedule.

Creating a Window

Windows are always created in the SYS schema.
To create a window, you must have the MANAGE SCHEDULER system privilege.
A window is automatically enabled upon its creation.
DBMS_SCHEDULER.CREATE_WINDOW (

WINDOW_NAME => 'TEST_WINDOW',

START_DATE => '01-JAN-05 12:00:00AM',

REPEAT_INTERVAL => 'FREQ=DAILY',

RESOURCE_PLAN => 'TEST_RESOURCEPLAN',

DURATION => INTERVAL '60' MINUTE,

END_DATE => '31-DEC-05 12:00:00AM',

WINDOW_PRIORITY => 'HIGH',

COMMENTS => 'Test Window')

START_DATE Time when the Window will open.
REPEAT_INTERVAL The next time the window will open again.
RESOURCE_PLAN Tells us that while this window is open, resource allocation to all the jobs that run in this window will be guided by the resource plan directives in the resource plan TEST_RESOURCEPLAN.
DURATION Window will remain open for a period of 60 minutes, after which it will close.
END_DATE Window will open for the last time on December 31, 2005, after which it will be disabled and closed.
WINDOW_PRIORITY Possible values are: LOW, HIGH.
When two Windows overlap, the high-priority window will open and the lower-priority window does not open.


You can create a window using a saved schedule:

DBMS_SCHEDULER.CREATE_WINDOW (

WINDOW_NAME => 'TEST_WINDOW',

SCHEDULE_NAME => 'TEST_SCHEDULE',

RESOURCE_PLAN => 'TEST_RESOURCEPLAN',

DURATION => interval '160' minute,

COMMENTS => 'Test Window')

Opening a Window

A window will automatically open at a time specified by its START_TIME attribute.
Only one window can be open at any given time.
A window can be manually opened:
DBMS_SCHEDULER.OPEN_WINDOW (

WINDOW_NAME =>'BACKUP_WINDOW',

DURATION => '0 12:00:00')

When you specify the duration, you can specify days, hours, minutes, seconds, in that order.

You can open an already open window. If you do this, the duration of the window will last a time period equal to its duration attribute.
Closing a Window

DBMS_SCHEDULER.CLOSE_WINDOW('BACKUP_WINDOW')

A running job may close upon the closing of its window, if you create a job with the attribute STOP_ON_WINDOW_CLOSE set to TRUE.

Disabling a Window

You can only disable a window if no job uses that window or if the window is not open.
If the window is open, you can disable it by using the DISABLE program with the FORCE=TRUE attribute.
DBMS_SCHEDULER.DISABLE (NAME => 'BACKUP_WINDOW')

Dropping a Window

You can drop a window by using the DROP_WINDOW procedure.
If a job associated with a window is running, a DROP_WINDOW procedure will continue to run through to completion and is disabled after it completes.
If you set the STOP_ON_WINDOW_CLOSE attribute to TRUE, however, the job will immediately stop when you drop an associated window.
 

ocm2  Create programs
https://shenh.tripod.com/Oracleblog/index.blog/1824940/ocm2-create-programs/

http://www.globusz.com/ebooks/Oracle/00000017.htm

Creating a Program

DBMS_SCHEDULER.CREATE_PROGRAM(
PROGRAM_NAME => 'TEST_PROGRAM',
PROGRAM_ACTION => 'SCOTT.UPDATE_SCHEMA_STATS',
PROGRAM_TYPE => 'STORED_PROCEDURE',
ENABLED => TRUE)

Note: If you want to create the program in a different user’s schema, you must qualify the program name with the schema name.

ocm2  Create job classes
https://shenh.tripod.com/Oracleblog/index.blog/1824941/ocm2-create-job-classes/

http://www.globusz.com/ebooks/Oracle/00000017.htm

Creating a Job Class

Using job classes helps you prioritize jobs by allocating resources differently among the various jobs.
All job classes are created in the SYS schema. To create a job class you need MANAGE SCHEDULER privilege.
For users to create jobs that belong to a job class, the job owner must have EXECUTE privileges on the job class.
There is a default job class, DEFAULT_JOB_CLASS, to which all jobs will belong if you don’t explicitly assign them to a job class.
DBMS_SCHEDULER.CREATE_JOB_CLASS (

JOB_CLASS_NAME => 'ADMIN_JOBS',

RESOURCE_CONSUMER_GROUP => 'ADMIN_GROUP',

LOGGING_LEVEL => DBMS_SCHEDULER.LOGGING_OFF

LOGGING_HISTORY => 30,

COMMENTS => 'Admin related jobs.')

LOGGING_
LEVEL
This attribute specifies how much information is logged. The three possible options are:
DBMS_SCHEDULER.LOGGING_OFF
DBMS_SCHEDULER.LOGGING_RUNS
DBMS_SCHEDULER.LOGGING_FULL:
In addition to recording every run of a job, the Scheduler will log every time a job is created, enabled, disabled, altered, and so on.

Note: As a DBA, you can set logging at the job class level in order to audit Scheduler jobs. In this case, an individual user can only increase the amount of logging the individual job level.

LOGGING_HISTORY Specifies the number of days (default is 30) that the database will retain the logs before purging them.
Oracle will automatically create a daily job called the PURGE_LOG, which cleans the log entries.


Manually Purging a Job Class Log

By default, once a day, the Scheduler will purge all window logs and job logs that are older than 30 days.

DBMS_SCHEDULER.PURGE_LOG(LOG_HISTORY=7,

WHICH_LOG =>'JOB_LOG')

LOG_HISTORY This specifies how much history (in days) to keep. The valid range is 0-999. If set to 0, no history is kept.
WHICH_LOG This specifies which type of log. Valid values are: JOB_LOG, WINDOW_LOG, AND JOB_AND_WINDOW_LOG.

You can purge log of a specific job:

DBMS_SCHEDULER.PURGE_LOG (

LOG_HISTORY => 1,

JOB_NAME => 'TEST_JOB1')

You can modify the retention period (the default is 30days) of the logs for a job class:

DBMS_SCHEDULER.SET_ATTRIBUTE(

'TEST_JOB_CLASS', 'log_history', '7')

In order to clear all window and job logs:

DBMS_SCHEDULER.PURGE_LOG()

Altering a Job Class

DBMS_SCHEDULER.SET_ATTRIBUTE(

NAME => 'ADMIN_JOBS',

ATTRIBUTE => 'START_DATE',

VALUE => '01-JAN-2005 9:00:00 PM US/Pacific')

You can change the START_DATE, END_DATE, and other logging-related attributes as well.

Dropping a Job Class

DBMS_SCHEDULER.DROP_JOB_CLASS('TEST_CLASS')

If you want to drop a job class with jobs in it, you must specify the FORCE=TRUE option in your DROP_JOB_CLASS procedure. When you do this, the jobs in the dropped job class are disabled and moved to the default job class in your database. If the job is already running when you drop its job class, the job will run to completion anyway. 

ocm2  Install the Enterprise Manager Grid Control infrastructure
https://shenh.tripod.com/Oracleblog/index.blog/1824942/ocm2-install-the-enterprise-manager-grid-control-infrastructure/

ocm2  Deploy Enterprise Manager Grid Control agents
https://shenh.tripod.com/Oracleblog/index.blog/1824944/ocm2-deploy-enterprise-manager-grid-control-agents/

http://download.oracle.com/docs/cd/B19306_01/readmes.102/b28832/toc.htm

http://<OMS_host>:<OMS_port>/agent_download/agent_install_readme.html

http://mgmthost27.acme.com:4889/agent_download/10.2.0.2.0/<platform>

OMShost=stadv26.us.oracle.com
httpPort=4889

<OMS_HOME>/sysman/agent_download/10.2.0.1.0/<platform>/agentDownload.linux

./agentDownload.<platform> -b /scratch/agentInstall
./agentDownload.<platform> -b /scratch/agentInstall -c "host1,host2,host3" -n myCRS -l

<Agent_Home>/bin/emctl secure agent <password>

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

 1): harder, need unlock on the server   
./emctl unsecure agent

-- OMS Upload URL - http://alalpboo136.choicepoint.net:4889/em/upload/ is locked or unavailable.
-- Unsecuring Agent...  Failed.

2): easier
./emctl secure agent
./emctl upload agent

./emctl stop agent
./emctl start agent

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

ocm2  Configure Grid Control for business requirements
https://shenh.tripod.com/Oracleblog/index.blog/1824945/ocm2-configure-grid-control-for-business-requirements/

Posted by shenh at 9:31 PM EST

Updated: Tuesday, 6 January 2009 9:58 PM EST

Share This Post

Permalink

1. Server Configuration
ocm1  Create the database
https://shenh.tripod.com/Oracleblog/index.blog/1824896/ocm1-create-the-database/

1): set environment variables
dbca&


10g R2 manual create db withtout omf

control_files=('/u01/app/oradata/orcl/control01.ctl',
'/u01/app/oradata/orcl/control02.ctl',
'/u01/app/oradata/orcl/control03.ctl')
db_name=orcl
db_block_size=8192
undo_management=auto
shared_pool_size=100000000

create database orcl
logfile
group 1 ('/u01/app/oradata/orcl/redo01.log') size 10m,
group 2 ('/u01/app/oradata/orcl/redo02.log') size 10m,
group 3 ('/u01/app/oradata/orcl/redo03.log') size 10m
datafile '/u01/app/oradata/orcl/system01.dbf' size 100m
autoextend on next 1m maxsize unlimited
extent management local
sysaux datafile '/u01/app/oradata/orcl/sysaux.dbf' size 100m
autoextend on next 1m maxsize unlimited
extent management local
undo tablespace undo
datafile '/u01/app/oradata/orcl/undo01.dbf' size 100m
default temporary tablespace temp
tempfile '/u01/app/oradata/orcl/temp.dbf' size 100m;

@?/rdbms/admin/catalog
@?/rdbms/admin/catproc

2): use and create templates for dbca

Cloning Databases Using DBCA
The "Template Management" section of the Database Configuration Assistant (DBCA) can be used to clone databases. The following method creates a clone of and existing database including both the structure and the data:

Start the Database Configuration Assistant (DBCA).
On the "Welcome" screen click the "Next" button.
On the "Operations" screen select the "Manage Templates" option and click the "Next" button.
On the "Template Management" screen select the "Create a database template" option and select the "From and existing database (structure as well as data)" sub-option then click the "Next" button.
On the "Source database" screen select the relevant database instance and click the "Next" button.
On the "Template properties" screen enter a suitable name and description for the template, confirm the location for the template files and click the "Next" button.
On the "Location of database related files" screen choose either to maintain the file locations or to convert to OFA structure (recommended) and click the "Finish" button.
On the "Confirmation" screen click the "OK" button.
Wait while the Database Configuration Assistant progress screen gathers information about the source database, backs up the database and creates the template.
By default the template files are located in the "$ORACLE_HOME/assistants/dbca/templates" directory.

http://www.oracle-base.com/articles/10g/ServerConfiguration10g.php

3):
create database .

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

ocm1  Determine and set sizing parameters for database structures
https://shenh.tripod.com/Oracleblog/index.blog/1824898/ocm1-determine-and-set-sizing-parameters-for-database-structures/

1): alter database backup controlfile to trace

alter database backup controlfile to trace as 'c:\\a.trc'

to see initial db setting


CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE

DATAFILE
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\MGMT.DBF',
'C:\ORACLE10GDB\PRODUCT\10.2.0\ORADATA\ORCL\MGMT_ECM_DEPOT1.DBF'
CHARACTER SET WE8MSWIN1252
;

2):
Structures are Tablespaces,Tables.

Tablespaces
1. All tablespaces can be locally managed [ UNDO/TEMP/Datafile ]
2. Extent management [ AUTOALLOCATE / UNIFORM ]
AUTOALLOCATE is the default . 

ocm1  Create and manage temporary, permanent, and undo tablespaces
https://shenh.tripod.com/Oracleblog/index.blog/1824899/ocm1create-and-manage-temporary-permanent-and-undo-tablespaces/

drop tablespace xxx;

drop tablespace xxx including contents;

drop tablespace xxx including contents and datafiles;

drop tablespace xxx cascade constraints;

alter tablespace xxx add datafile 'xxx' size 200m autoextend on next 10m maxsize 1000M;

alter database datafile 'xxx' resize 500m;

alter tablespace test01 rename to test02;

alter tablespace xxx read only;
alter tablespace xxx read write;

alter tablespace xxx online;
alter tablespace xxx offline;


alter database default temorary tablespace temp;


alter database default tablespace users;

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

OMF: Oracle Managed File

DB_CREATE_FILE_DEST

-- ------------------------------------------
PERMANENT
CREATE TABLESPACE hold_data datafile '/u02/oradata/mylcl/hold_data.dbf'
size 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
TEMPORARY
Create TEMPORARY TABLESPACE TEMP_DATA tempfile '/u02/oradata/mylcl/temp_data.dbf'
size 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
UNDO
Create UNDO TABLESPACE UNDOTBS1 datafile '/u02/oradata/mylcl/undotbs1.dbf'
size 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

alter tablespace USER_DATA ADD DATAFILE '/u06/oradata/ORC1/user_data27.dbf' size 8000M;

1): dba_tablespaces

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_7003.htm

CREATE BIGFILE TABLESPACE bigtbs_01
DATAFILE 'bigtbs_f1.dat'
SIZE 20M AUTOEXTEND ON;

CREATE UNDO TABLESPACE undots1
DATAFILE 'undotbs_1a.f'
SIZE 10M AUTOEXTEND ON
RETENTION GUARANTEE;

CREATE UNDO TABLESPACE undots1
DATAFILE 'undotbs_1a.f'
SIZE 10M AUTOEXTEND ON
RETENTION NOGUARANTEE;

CREATE TEMPORARY TABLESPACE temp_demo
TEMPFILE 'temp01.dbf' SIZE 5M AUTOEXTEND ON;

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TEMPORARY TABLESPACE tbs_05;

Adding a Temporary Tablespace to a Tablespace Group: Example

CREATE TEMPORARY TABLESPACE tbs_temp_01
TEMPFILE 'temp02.dbf' SIZE 5M AUTOEXTEND ON
TABLESPACE GROUP tbs_grp_01;


CREATE TABLESPACE tbs_01
DATAFILE 'tbs_f2.dat' SIZE 40M
ONLINE;

CREATE TABLESPACE tbs_03
DATAFILE 'tbs_f03.dbf' SIZE 20M
LOGGING;

CREATE TABLESPACE tbs_02
DATAFILE 'diskb:tbs_f5.dat' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 100M;

CREATE TABLESPACE tbs_04 DATAFILE 'file_1.f' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

CREATE TABLESPACE auto_seg_ts DATAFILE 'file_2.f' SIZE 1M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Creating Oracle-managed Files: Examples

The following example sets the default location for datafile creation and creates a tablespace with a datafile in the default location. The datafile is 100M and is autoextensible with an unlimited maximum size:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '$ORACLE_HOME/rdbms/dbs';

CREATE TABLESPACE omf_ts1;
The following example creates a tablespace with an Oracle-managed datafile of 100M that is not autoextensible:

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;

ocm1  Stripe data files across multiple physical devices and locations
https://shenh.tripod.com/Oracleblog/index.blog/1824900/ocm1stripe-data-files-across-multiple-physical-devices-and-locations/

1): alter database backup controlfile to trace;

2): select name from v$datafile
select member from v$logfile
select name from v$controlfile
select name from v$tmpfile

3): startup mount;
alter database rename file ... to ...

-- -------------------------------------------
Goal of striping data is simple:-
To avoid I/O bottlenecks during parallel processing, all tablespaces
accessed by parallel operations should be striped.
Many current OS's support disk striping hence DBA's need not stripe data
across the disk.
In the event that you have to stripe data across Disks then
you should distribute the load evenly across the Disks.
More information
Oreilly
Let us say we have to stripe database files and we have 2 disk controllers
CONTROLLER 1 CONTROLLER 2
/u02 /u03
/u04 /u05
/u06 /u07
/u08 /u09
Create a tablespace which will look as one logical device
from oracle perspective and spread it across the disks.
CREATE TABLESPACE MYDATA
DATAFILE '/u02/oradata/mylcl/mydata.dbf' size 2M ,
DATAFILE '/u03/oradata/mylcl/mydata.dbf' size 2M ,
DATAFILE '/u04/oradata/mylcl/mydata.dbf' size 2M ,
DATAFILE '/u05/oradata/mylcl/mydata.dbf' size 2M
extent management local SEGMENT SPACE MANAGEMENT AUTOALLOCATE;

ocm1  Configure the database environment to support optimal data access performance
https://shenh.tripod.com/Oracleblog/index.blog/1824901/ocm1configure-the-database-environment-to-support-optimal-data-access-performance/

1): memory
2): disk

ocm1  Create and manage database configuration files
https://shenh.tripod.com/Oracleblog/index.blog/1824902/ocm1create-and-manage-database-configuration-files/

1): pfile and spfile
$ORACLE_HOME/dbs/init_SID.ora

2): create pfile from spfile;
create spfile from pfile;

3): alter system set ...=.. scope=[both|memory|spfile] sid='*'
show parameter ... 

ocm1  Create and manage bigfile tablespaces
https://shenh.tripod.com/Oracleblog/index.blog/1824904/ocm1create-and-manage-bigfile-tablespaces/

http://www.databasejournal.com/features/oracle/article.php/3646226

select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

CREATE DATABASE mydb
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
SET DEFAULT BIGFILE TABLESPACE
UNDO TABLESPACE undotbs
DEFAULT TEMPORARY TABLESPACE temp;

http://www.adp-gmbh.ch/ora/concepts/tablespaces.html

1. Bigfile is a big tablespace with single datafile.
2. Supported only for locally managed databases.

What to do if you have to increase space on a big file tablespace:-
1. RESIZE
alter tablespace bigtbs resize 120g;
2. AUTOEXTEND
alter tablespace bigtbs autoextend on next 20G;
-- ----------------------------------------------------------------

1):
create bigfile tablespace xxx

create smallfile tablespace xxx

2): default

ocm1  Create and manage multiple network configuration files
https://shenh.tripod.com/Oracleblog/index.blog/1824905/ocm1create-and-manage-multiple-network-configuration-files/