Learning Oracle: February 2016

Monday, February 29, 2016

How To Check The Techstack Version

How To Check The Techstack Version

Verify the 10.1.3.X Oracle Home version and the required version is 10.1.3.4 or higher <Use Document 454811.1> (for 10.1.3.5 ) or <Document 728531.1> (for 10.1.3.4 ) to upgrade it

TO check the versions perform the below

cd $INST_TOP/ora/10.1.3
. ./$CONTEXT_NAME.env

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'

Note: Oracle Application Server 10g signifies the Base Install and Patch of Oracle Application Server 10g signifies the Patchset on top of it

Example:

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'

Oracle Application Server 10g 10.1.3.0.0
Patch of Oracle Application Server 10g 10.1.3.4.0

Verify the 10.1.2.X Oracle Home version and the required version is 10.1.2.3 <Use Document 437878.1 to upgrade it.>

TO check the versions perform the below

Log-out and Re-Login, before performing the following steps.

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'

Note: Oracle Application Server 10g signifies the Base Install Patch of Oracle Application Server 10g signifies the Patchset on top of it

Example:

$ORACLE_HOME/OPatch/opatch lsinventory -detail -invPtrLoc $ORACLE_HOME/oraInst.loc | egrep 'Oracle Application Server 10g|Patch of Oracle Application Server 10g'

Oracle Application Server 10g 10.1.2.0.2
Patch of Oracle Application Server 10g 10.1.2.3.0

How To Recreate the /appsutil/scripts/ directory

How To Recreate the /appsutil/scripts/ directory

The $ORACLE_HOME/appsutil directory or subdirectories of appsutil are missing.

How to recreate this directory with its subdirectories and contents?

How can I run AutoConfig on Database Tier without this directory, since adautocfg.sh exists in /appsutil/scripts/?

Solution:

To implement the solution, please update the RDBMS ORACLE_HOME file system with AutoConfig files from the AppsTier by performing the following steps exactly:

On the Application Tier (as the APPLMGR user):

Log in to the APPL_TOP environment and source the environment file

Create appsutil.zip file: "perl /bin/admkappsutil.pl"
(This will create appsutil.zip in $APPL_TOP/admin/out/appsutil.zip)

Copy or FTP the appsutil.zip file to the RDBMS $ORACLE_HOME

On the Database Tier (as the ORACLE user):

$ cd $ORACLE_HOME

$ unzip -o appsutil.zip

Generate the Database Context File:

Context File Creation on UNIX

$ cd $ORACLE_HOME

$ . _.env

$ cd $ORACLE_HOME/appsutil/bin

$ perl adbldxml.pl tier=db appsuser= appspasswd=

$ cd $ORACLE_HOME/appsutil/bin

$ adconfig.sh contextfile= appspass=

After running these steps, all files and directories for Autoconfig are now present, including "$ORACLE_HOME/appsutil/bin/adautocfg.sh" and the "$ORACLE_HOME/appsutil/scripts" directory.

Sunday, February 28, 2016

How to find the particular Data Dictionary Table or Dynamic Performance View (V$)

How to find the particular Data Dictionary Table or Dynamic Performance View (V$)

Even though we use some of the important Data Dictionary Tables and Dynamic Performance (V$) views regularly, sometimes we fall short to recall a particular view, i.e. It’s not easy to remember all the dictionary views.

What is the view to find the information about data files, tablespaces, etc.,?

What are the list of Data Dictionary and Dynamic Performance Views in so and so Oracle Version?

Well, I hope all of we know that there is a data dictionary view called “DICTIONARY”, using this DICTIONARY view, we can find the particular or list of pertinent views of any object in the database.

What is the DICTIONARY view?

The DICTIONARY view is a data dictionary view used to find the list of Data Dictionary and Dynamic Performance (V$) views, and it contains description of data dictionary tables and views. It has two columns, one is ‘TABLE_NAME’ – Name of the object and another column is ‘COMMENTS’ – Text comment on the object.

Discription:

SQL> desc DICTIONARY;

Name Null? Type
- -------- -------------------------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

Usage:

To find the list of data dictionary tables and dynamic performance views.

SELECT * FROM dict;

To find the particular data dictionary table or dynamic performance view.

SELECT * FROM dictionary WHERE table_name LIKE ‘%Search String%’;

i.e.

SELECT * FROM dictionary WHERE table_name LIKE ‘%TABLESP%’;

Example:

SQL> SELECT * FROM dictionary WHERE table_name='DICTIONARY';

TABLE_NAME
-------------
COMMENTS
-------------

DICTIONARY

Description of data dictionary tables and views

Note: While querying, either the complete word DICTIONARY or first four characters DICT, which is a synonym for DICTIONARY, can be used.

FNDLOAD

FNDLOAD

FNDLOAD is a concurrent program that can move Oracle Applications data between database and text file. FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database. Conversion between database format and text file format is specified by a configuration file.

Oracle currently supports the migration of the following types of data using FNDLOAD

- Printers / Print queues / Executables Printers / Print queues / Executables.
- Roles / Responsibilities / Forms Roles / Responsibilities / Forms.
- Menus / Users / Request Sets Menus / Users / Request Sets.
- Request Groups / Request Queues Request Groups / Request Queues.
- Work shifts / Programs / Libraries Work shifts / Programs / Libraries.
- Attachments / Help Files Attachments / Help Files.
- Mime Types Mime Types.
- Security Information.

.ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

Advantages when using FNDLOAD

Because downloaded data is stored in a text file, version administration is possible
No learning curve. this is relief for developer/dbas
Fully supported and recommended by Oracle
Capture the migrations in a file and use it during installations(log file).
Pin-point when something happened and where (database) easily
AOL data migration process is now simplified!

Disadvantages

Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
No validation against migrating database/instance sensitive data

The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]

The mode is either DOWNLOAD or UPLOAD.
The configfile is the file that Fndload needs to download on upload data.
T he data file is the output file, in which the downloaded data is written
The entity is the entity you want to download

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where

'0' is request id (request ID 0 is assigned to request ID's which are not submitted via Submit Concurrent Request Form.

'Y' indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

Example of download:

FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name

Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt – CUSTOM_MODE=FORCE undocumented parameter

Where is Config File Located

Configuration files with extension .lct
On linux – all the configuration files are in $FND_TOP/patch/115/import directory
On linux Oracle also places the original configuration files in $FND_TOP/admin/import directory
Data files with extension .ldt
The configfiles (.lct) are delivered and maintained by Oracle
It has entity definitions, parent-child relationships and user input parameters identified by :NAME
Downloading a parent automatically downloads all children – (Example) Concurrent Program download

Working example for these code objects :

Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name

Lookups

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=prod LOOKUP_TYPE=lookup name

Descriptive Flexfield with all of specific Contexts

$ FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_HEADERS'

Please note that PO is the Application Shortname against which descriptive flexfield against PO Headers is registered
PO_HEADERS is the name of Descriptive Flexfield against PO Headers
You can find the details using this script

SELECT application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name
FROM fnd_descriptive_flexs_vl
WHERE APPLICATION_TABLE_NAME like 'PO_HEAD%' ORDER BY APPLICATION_TABLE_NAME
/
To upload into another environment
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_HEADERS_DFF.ldt

Concurrent Programs

$ FNDLOAD apps/ O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUST_PROG_IMP.ldt PROGRAM APPLICATION_SHORT_NAME="XX_GS_IMP" CONCURRENT_PROGRAM_NAME="XX_CUST_PROG_IMP"

Please note that XX_GS_IMP will be your custom Application Shortname where concurrent program is registered
XX_CUST_PROG_IMP Will be the name of your request group
XX_CUST_PROG_IMP.ldt is the file where concurrent program definition will be extracted to upload to the target instance

$ FNDLOAD apps/ O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUST_PROG_IMP.ldt

Value Sets

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=value set name

Value Sets with values

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=value set name

Profile Options

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=profile option APPLICATION_SHORT_NAME=prod

Requset Group

$ FNDLOAD apps/ O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPGROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_REPGROUP_NAME" APPLICATION_SHORT_NAME="XX_MYGROUP"

Please note that XX_MYGROUP will be your Application Shortname where request group is registered
and XX_REPGROUP_NAME Will be the name of your request group

Next need to upload this Request Group info to the target instance after having transferred the ldt file

FNDLOAD apps/ O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_REPGROUP_NAME.ldt

Request Sets

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=prod REQUEST_SET_NAME=request set

Responsibilities

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=responsibility

Menus

FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=menu_name

Forms/Functions

FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

Responsibilities

$ FNDLOAD apps/ O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESPON_NAME.ldt FND_RESPONSIBILITY RESP_KEY="XX_RESPON_NAME"

please note that XX_RESPON_NAME is the responsibility key in source instance
Now need to upload this information to target instance after having transferred the ldt file

FNDLOAD apps/ O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_RESPON_NAME.ldt

Users

FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSWD.ldt FND_USER USER_NAME='USER_NAME'
When you look at the ldt file, you can find this kind info, and do not worry about password, it will be encrypted only.

BEGIN FND_USER "username"
OWNER = "ANONYMOUS"
LAST_UPDATE_DATE = "2010/01/07"
ENCRYPTED_USER_PASSWORD =
"ZG19D5C2DB16C4F5DD4D5D9A2F0CE3FEEB07BAF9CD0A591B64CA8B357C38EE7B17533B0C00D1DBA278D83E0A8994737B2C63"
SESSION_NUMBER = "3375"
START_DATE = "2007/01/01"
END_DATE = "*NULL*"
LAST_LOGON_DATE = "2010/04/30"
DESCRIPTION = "Name of the user"
PASSWORD_DATE = "2010/01/07"
PASSWORD_ACCESSES_LEFT = "*NULL*"
PASSWORD_LIFESPAN_ACCESSES = "*NULL*"
PASSWORD_LIFESPAN_DAYS = "*NULL*"
EMAIL_ADDRESS = "username@test.com"
FAX = "*NULL*"

To upload the FND_USER using FNDLOAD command use

FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FND_USER_PASSWD.ldt

when you upload the FND_USER details with FNDLOAD, need to be complete these actions priorily.

1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.

2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER

3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities,functions, menu, request group etc., prior to running FNDLOAD on users.

Top New Features in Oracle 11g

Top New Features in Oracle 11g

Database Replay Explore Database Replay, the new tool that captures SQL statements and lets you replay them at will.

Partitioning Learn about Referential, Internal, and Virtual Column partitioning; new sub-partitioning options; and more.

Transaction Management Get an introduction to Flashback Data Archive and explore Enterprise Manager's LogMiner interface.

Schema Management Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables.

SQL Plan Management Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it's used.

SQL Performance Analyzer Accurately assess the impact of rewriting of SQL statements and get suggested improvements.

SQL Access Advisor Get advice about optimal table design based on actual use of the table, not just data.

PL/SQL: Efficient Coding Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.

RMAN Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs.

Security Learn about Tablespace Encryption, case-sensitive passwords, data masking, and other features.

Automatic Storage Management Learn about new SYSASM role, variable extent sizes, and other ASM improvements.

Manageability Explore automatic memory management, multicolumn statistics, online patching, and more features.

Caching and Pooling Explore SQL Result Cache, PL/SQL Function Cache, and Database Resident Connection Pooling.

SQL Operations: Pivot and Unpivot Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to a relational table.

SecureFiles Explore next-generation LOBs: LOB encryption, compression, deduplication, and asynchronicity.

Resiliency Explore Automatic Health Monitor, Automatic Diagnostic Repository, and other new resiliency features.

Data Guard Query the physical standby database in real time without shutting down recovery, just for starters.

PL/SQL Performance Explore in-lining of code, "real" native compilation, PLS timer, use of simple integer, and more.

Data Warehousing and OLAP Get a tour of new features in these areas, including Cube Organized MVs.

Top New Features in Oracle 10g

Top New Features in Oracle 10g

Flashback Versions Query

Rollback Monitoring

Tablespace Management

Oracle Data Pump

Flashback Table

Automatic Workload Repository

SQL*Plus Rel 10.1

Automatic Storage Management

RMAN: offline recovery of incremental backups, previewing restore, recovering through resetlogs, file compression

Auditing: captures user activities at a very detailed level, which may obviate manual, trigger-based auditing

Wait Interface

Materialized Views

Enterprise Manager 10g

Virtual Private Database

Automatic Segment Management

Transportable Tablespaces

Automatic Shared Memory Management

ADDM and SQL Tuning Advisor

Scheduler

Saturday, February 27, 2016

Start / Stop Mobile Application Services in Oracle Apps R12

Start / Stop Mobile Application Services in Oracle Apps R12

Login as applmgr user :

Go to $ADMIN_SCRIPTS_HOME

Stop Mobile Application Services :

./mwactl.sh -login apps/apps_password stop_force
./mwactlwrpr.sh stop apps/apps_password

Start Mobile Application Services :

./mwactl.sh -login apps/apps_password start
./mwactlwrpr.sh start apps/apps_password

Check Mobile Services are up and Runing:

ps -ef | grep mwa

Find port Number of Mobile Services :

grep mwa $CONTEXT_FILE

Connect to Mobile Services :

telnet hostname.domainname portnumber(mobile application service port_number)

Startup & shutdown of Oracle Application DBA

Startup & shutdown of Oracle Application DBA

As you are aware by now there is Database Tier ( Database & DB Listener ) and Application Tier ( WebServer, Forms, Reports, Concurrent Manager, Discoverer, Apps Listener, OnetoOne Fullfillment Server )

Order of StartUp of Services Should be
First DB Listener, Database & then Application Tier Services

Order of ShutDown of Services Should be
First Application Tier Services then Database & DB Listener

Database Startup/Shutdown Scripts

Depending on your AD Version these will be in:

ORACLE_HOME / appsutil/scripts /SID_hostname

addbctl.sh database startup shutdown script
addlnctl.sh database listener Script

Where

AD is for Application DBA
DB is for database
DLN is database listener
CTL is control

Application Tier Startup/Shutdown Scripts

Depending on your AD Version these will be in:

$INST_TOP/admin/scripts or $ADMIN_SCRIPTS_HOME

adalnctl.sh Apps Listener Control Script
adapcctl.sh Apache/Web Server Control Script
adcmctl.sh Concurrent Manager Control Script
addisctl.sh Discoverer Control Script
adfrmctl.sh Forms server Control Script
adrepctl.sh Report Server Control Script
adstpall.sh Stop All Middle/Application Tier
adstrtall.sh Start All Middle/Application Tier

Where AD & CTL you already know now :) isn't it

adl stand for Apps Listener
apc stand for Apache
cm Concurrent Manager
dis Discoverer
frm forms
rep report

Thursday, February 25, 2016

Difference between DDL, DML and DCL commands

Difference between DDL, DML and DCL commands

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database

ALTER - alters the structure of the database

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - deletes all records from a table, the space for the records remain

MERGE - UPSERT operation (insert or update)

CALL - call a PL/SQL or Java subprogram

EXPLAIN PLAN - explain access path to data

LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database

REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

Wednesday, February 24, 2016

Find E-Business Suite users daily logins at the forms

Find E-Business Suite users daily logins at the forms

To find the users logins at the forms only use the following query

SELECT apps.fnd_user.user_name,apps.fnd_logins.START_TIME, apps.fnd_logins.END_TIME, apps.fnd_logins.LOGIN_TYPE FROM apps.fnd_logins, apps.fnd_user WHERE apps.fnd_logins.user_id = apps.fnd_user.user_id
AND TO_CHAR(start_time, 'DD/MM/YYYY') = TO_CHAR(SYSDATE, 'DD/MM/YYYY')
AND login_type= 'FORM' --and apps.fnd_user.USER_NAME = 'SYSADMIN' ORDER BY start_time DESC;

Kill oracle idle session because of security reason

Kill oracle idle session because of security reason

Many times users open a session to the database, use it for some time, and then stay idle for hours, even days. They open the form, but they leave it idle for days, until they come back to the office and use the application.

Sometimes users in E-business suite login to E-Business Suite, do some work and just leave the forms open staying idle for a long time. If Oracle Apps client is not doing any activity for some time session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session( both forms & self service) after idle time value is reached to the one mentioned in configuration file.

From FND.G or 11.5.9 or with introduction of AppsLocalLogin.jsp to enter into application, profile option "ICX Session

Timeout" is used only to determine Forms Session Idle timeout value . This might be confusing as earlier this profile option used to control forms as well as self service application(with session.timeout) session.timeout is used to control Idle session timeout for Self Service Applications (Served by Jserv via JVM).

Killing idle sessions for sometime is good for:

a) Security reasons and

b) Releasing resources to the server, especially memory.

The best and most effective technique in "killing the idle sessions" is making the session SNIPED

How to make an idle session get SNIPED

You must set:

A.The initialization parameter resource_limit = TRUE in the init.ora

alter system set resource_limit=TRUE scope=both;

B.Idle_time in the user profile

then you setup idle sessions to become sniped after x minutes.

With the following example the user session becomes sniped after 8 hours of idle time.

alter profile DEFAULT set idle_time=480;

Finding the SNIPED sessions and killing them.

Use the following query to get the sniped idle sessions.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID UNIX_PROCESS, v$session.USERNAME, STATUS,
OSUSER, MACHINE, v$session.PROGRAM, MODULE,
'alter system kill session ' || '''' || SID || ', ' || v$session.serial# || '''' || ' immediate;' kill_sql FROM
v$session, v$process
WHERE v$session.paddr = v$process.addr AND
status = 'SNIPED' ORDER BY logon_time ASC;

Unsuccessful logins

Unsuccessful logins

If you have set up auditing in Oracle E-Business Suite then you can monitor the unsuccessful logins from the applsys.FND_UNSUCCESSFUL_LOGINS table.

For example, the unsuccessful logins for the running day.

SELECT B.USER_NAME, U.ATTEMPT_TIME FROM applsys.FND_UNSUCCESSFUL_LOGINS U, applsys.FND_USER B WHERE U.USER_ID = B.USER_ID
--AND B.USER_NAME LIKE NVL(:USER_NAME, '%') AND TRUNC(U.ATTEMPT_TIME) > TRUNC(SYSDATE-1) ORDER BY U.ATTEMPT_TIME;

Create a super user like DBA but with no administration power

Create a super user like DBA but with no administration power

Some time you have the requirement to create a user to the database that has access to all v$session, v$lock,dba_free_space and other dictionary views to monitor performance issues, sql tuning, and other DBA operations.

But you don't want to have real power to change things like a DBA can do.

The quick and easy word around is to grant the select_catalog_role role to the new user.

create user dba_monitor identified by dba_monitor;
grant connect, select_catalog_role to dba_monitor;

Now with the new user you can use tools like TOAD or Grid Control to monitor the database as a DBA but with no real power to change things

An example of 1TB OLTP Unix database

An example of 1TB OLTP Unix database

To understand better and have a solid example of a system, imagine this middle range server.

16 Cores PARISC or Itanium 2 (8 CPUs dual core)
Operating System : HP-UX 11i v2
48 GB of RAM
EMC SAN storage attached
The brand name (HP) is not import, can be something equivalent from Sun or IBM.

IF this machine is going to host a 1TByte OLTP database with about 2500 users (dedicated connections) then you can setup for example like this:

Memmory parameters
db_block_size=8192
db_cache_size=4GB
shared_pool_size=4GB
large_pool_size=128MB
shared_pool_reserved_size=80MB
java_pool_size=128MB
log_buffer=10MB
sga_max_size=16GB
pga_aggregate_target=4GB

Tip:From Oracle9i and afterwards you will need 20GB for the oracle processes (Every process takes at an average 8MB at unix).

At Oracle8i you will need 5GB for the oracle processes (Every process takes at an average 2MB at unix).

Parallel processing

You must take advantage of the 16Cpus, so parallel processing must be setup. A good setup is for minimum 16/4=4 and for

maximum 16*4=64.
parallel_min_servers=4
parallel_max_servers=64
parallel_min_percent=0
recovery_parallelism=0
fast_start_parallel_rollback=LOW
parallel_automatic_tuning=FALSE
parallel_execution_message_size=16384

Other init parameters

db_writer_processes=4 (A good setup is 16/4=4)
db_file_multiblock_read_count=8
timed_statistics=TRUE
open_cursors=10000
log_checkpoint_interval=0
log_checkpoint_timeout=1800
log_archive_max_processes=3

Filesystems

Oracle software: 1 filesystem(30GB is enough)
For the datafiles: 10 filesystems (100GB each) is good enough. Don't use for instance less than 5, it won't be good for Disk I/O performance
For redologs: 5 filesystems, very small (10GB each) is good enough
For controlfiles: 3 filesystems, very small (use the same for redologs)
For archives: 1 filesystem (100GB) setting Rman backup every 6 hours with delete input.
For trace files, exports, scripts, etc: 1 filesystem (200GB)

Tip: Create TEMP tablepace and UNDO tablespace with 10 datafiles each, one in every filesystem.

Change the user password back to previous without knowing it

Change the user password back to previous without knowing it

As a DBA you may need to log in as another user, for example to test an application after doing some workarounds to solve a problem. But you don't know the password and you don't want to wait. You can change the password, as dba, but how to change it back to previous?

The right way to proceed is:

note down the current (old) password, as found in the table dba_users
modify the password with the command ALTER USER IDENTIFIED BY
connect using the new password
do what you wanted to do
reset the password with the clause IDENTIFIED BY VALUES

All the steps by example

For our example we will use the HELPDESK user with password oracle123

CREATE USER helpdesk IDENTIFIED BY oracle123;

GRANT CREATE SESSION TO helpdesk;

As a DBA you don't know the real password for user HELPDESK, but you can find the encoded password in the view dba_users

SELECT username, PASSWORD, 'alter user ' || username || ' identified by values ' || '''' || PASSWORD || '''' ||';' alter_user_sql FROM dba_users WHERE username = 'HELPDESK';

so the encoded value is 414AD71995BE5241, modify the password to your own

ALTER USER helpdesk IDENTIFIED BY helpdesk;

Connect as helpdesk with password helpdesk. Do your work, if you finish, change back the password

ALTER USER HELPDESK IDENTIFIED BY VALUES '414AD71995BE5241';

When did a user change his/her password?

When did a user change his/her password?

The table sys.user$ contains the field ptime, which keeps the time when the password was changed the last time over. Do not

confound it with ctime, which is the "creation time", nor with ltime, which is the time the account has been locked (if any).

SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED" FROM USER$ WHERE ptime IS NOT NULL ORDER BY ptime DESC;

Cluster Management (Starting and stopping clusterware and resources)

Cluster Management (Starting and stopping clusterware and resources)

Check status of Cluster Ready Services on Specific Node

[oracle@racnode1 ~]$ ./crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

Check status of running services on all nodes

[oracle@racnode1 ~]$ ./srvctl status nodeapps

VIP racnode1-vip is enabled
VIP racnode1-vip is running on node: racnode1
VIP racnode2-vip is enabled
VIP racnode2-vip is running on node: racnode2
VIP racnode3-vip is enabled
VIP racnode3-vip is running on node: racnode3
VIP racnode4-vip is enabled
VIP racnode4-vip is running on node: racnode4
Network is enabled
Network is running on node: racnode1
Network is running on node: racnode2
Network is running on node: racnode3
Network is running on node: racnode4
GSD is disabled
GSD is not running on node: racnode1
GSD is not running on node: racnode2
GSD is not running on node: racnode3
GSD is not running on node: racnode4
ONS is enabled
ONS daemon is running on node: racnode1
ONS daemon is running on node: racnode2
ONS daemon is running on node: racnode3
ONS daemon is running on node: racnode4
eONS is enabled
eONS daemon is running on node: racnode1
eONS daemon is running on node: racnode2
eONS daemon is running on node: racnode3
eONS daemon is running on node: racnode4

Check status of complete clusterware stack on all nodes:

[oracle@racnode1 ~]$ ./crsctl status resource -t

--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMDATA1.dg
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
ora.LISTENER.lsnr
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
ora.asm
ONLINE ONLINE racnode1 Started
ONLINE ONLINE racnode2 Started
ONLINE ONLINE racnode3 Started
ONLINE ONLINE racnode4 Started
ora.eons
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
ora.gsd
OFFLINE OFFLINE racnode1
OFFLINE OFFLINE racnode2
OFFLINE OFFLINE racnode3
OFFLINE OFFLINE racnode4
ora.net1.network
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
ora.ons
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
ora.registry.acfs
ONLINE ONLINE racnode1
ONLINE ONLINE racnode2
ONLINE ONLINE racnode3
ONLINE ONLINE racnode4
--------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE racnode2
ora.oc4j
1 OFFLINE OFFLINE
ora.racdb.db
1 OFFLINE OFFLINE
2 OFFLINE OFFLINE
3 OFFLINE OFFLINE
4 OFFLINE OFFLINE
ora.racnode1.vip
1 ONLINE ONLINE racnode1
ora.racnode2.vip
1 ONLINE ONLINE racnode2
ora.racnode3.vip
1 ONLINE ONLINE racnode3
ora.racnode4.vip
1 ONLINE ONLINE racnode4
ora.scan1.vip
1 ONLINE ONLINE racnode2
[oracle@racnode1 ~]$

Check status of servers in clusterware

[oracle@racnode1 ~]$ ./crsctl status server -f
NAME=racnode1
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

NAME=racnode2
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

NAME=racnode3
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

NAME=racnode4
STATE=ONLINE
ACTIVE_POOLS=Generic ora.racdb
STATE_DETAILS=

Stop CRS on all nodes in clusterware

[root@racnode1 bin]# ./crsctl stop cluster -all

CRS-2673: Attempting to stop 'ora.crsd' on 'racnode2'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode3'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode4'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode3'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'racnode3'
CRS-2673: Attempting to stop 'ora.ASMDATA1.dg' on 'racnode3'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode3'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode4'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode4'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'racnode4'
CRS-2673: Attempting to stop 'ora.ASMDATA1.dg' on 'racnode4'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ASMDATA1.dg' on 'racnode1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode3' succeeded
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode2'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'racnode2'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'racnode2'
CRS-2673: Attempting to stop 'ora.racnode3.vip' on 'racnode3'
CRS-2677: Stop of 'ora.racnode3.vip' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.ASMDATA1.dg' on 'racnode2'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.racnode1.vip' on 'racnode1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.racnode1.vip' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.racnode4.vip' on 'racnode4'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.racnode2.vip' on 'racnode2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'racnode2'
CRS-2677: Stop of 'ora.racnode4.vip' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.racnode2.vip' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.ASMDATA1.dg' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode3'
CRS-2677: Stop of 'ora.asm' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'racnode3'
CRS-2673: Attempting to stop 'ora.ons' on 'racnode3'
CRS-2677: Stop of 'ora.ASMDATA1.dg' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode4'
CRS-2677: Stop of 'ora.asm' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'racnode4'
CRS-2673: Attempting to stop 'ora.eons' on 'racnode4'
CRS-2677: Stop of 'ora.ons' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode3'
CRS-2677: Stop of 'ora.net1.network' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.ons' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode4'
CRS-2677: Stop of 'ora.net1.network' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.ASMDATA1.dg' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode2'
CRS-2677: Stop of 'ora.ASMDATA1.dg' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.asm' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ons' on 'racnode1'
CRS-2677: Stop of 'ora.ons' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode1'
CRS-2677: Stop of 'ora.net1.network' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.eons' on 'racnode3' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode3' has completed
CRS-2677: Stop of 'ora.eons' on 'racnode4' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode4' has completed
CRS-2677: Stop of 'ora.crsd' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'racnode3'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode3'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode3'
CRS-2673: Attempting to stop 'ora.asm' on 'racnode3'
CRS-2677: Stop of 'ora.cssdmonitor' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.crsd' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'racnode4'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode4'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode4'
CRS-2673: Attempting to stop 'ora.asm' on 'racnode4'
CRS-2677: Stop of 'ora.cssdmonitor' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.evmd' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.eons' on 'racnode1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode1' has completed
CRS-2673: Attempting to stop 'ora.eons' on 'racnode2'
CRS-2673: Attempting to stop 'ora.ons' on 'racnode2'
CRS-2677: Stop of 'ora.evmd' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.ons' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode2'
CRS-2677: Stop of 'ora.net1.network' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.crsd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.eons' on 'racnode2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode2' has completed
CRS-2677: Stop of 'ora.asm' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode3'
CRS-2677: Stop of 'ora.asm' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode4'
CRS-2677: Stop of 'ora.crsd' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'racnode2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode2'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode2'
CRS-2673: Attempting to stop 'ora.asm' on 'racnode2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.cssd' on 'racnode3' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'racnode3'
CRS-2677: Stop of 'ora.cssd' on 'racnode4' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'racnode4'
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1'
CRS-2677: Stop of 'ora.evmd' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'racnode1'
CRS-2677: Stop of 'ora.diskmon' on 'racnode3' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'racnode4' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.asm' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode2'
CRS-2677: Stop of 'ora.cssd' on 'racnode2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'racnode2'
CRS-2677: Stop of 'ora.diskmon' on 'racnode2' succeeded
[root@racnode1 bin]#

Stop crs on specific node (Run this command as root user)

[root@racnode1 bin]# ./crsctl stop has

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.crsd' on 'racnode1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'racnode1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'racnode1'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ASMDATA1.dg' on 'racnode1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.racnode1.vip' on 'racnode1'
CRS-2677: Stop of 'ora.racnode1.vip' on 'racnode1' succeeded
CRS-2672: Attempting to start 'ora.racnode1.vip' on 'racnode2'
CRS-2677: Stop of 'ora.registry.acfs' on 'racnode1' succeeded
CRS-2676: Start of 'ora.racnode1.vip' on 'racnode2' succeeded
CRS-2677: Stop of 'ora.ASMDATA1.dg' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ons' on 'racnode1'
CRS-2677: Stop of 'ora.ons' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'racnode1'
CRS-2677: Stop of 'ora.net1.network' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.eons' on 'racnode1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'racnode1' has completed
CRS-2677: Stop of 'ora.crsd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'racnode1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.evmd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.asm' on 'racnode1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'racnode1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.asm' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'racnode1'
CRS-2677: Stop of 'ora.drivers.acfs' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.cssd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'racnode1'
CRS-2673: Attempting to stop 'ora.diskmon' on 'racnode1'
CRS-2677: Stop of 'ora.gpnpd' on 'racnode1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'racnode1'
CRS-2677: Stop of 'ora.gipcd' on 'racnode1' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'racnode1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'racnode1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@racnode1 bin]#

Start crs on specific node (Run this command as root user)

[root@racnode1 bin]# ./crsctl start has

CRS-4123: Oracle High Availability Services has been started.

Check status of a specific instance (INSTANCE NAME IS CASE SENSITIVE)

[oracle@racnode1 ~]$ srvctl status instance -d racdb -i racdb1
Instance racdb1 is running on node racnode1

Check status of all instances in a Cluster (INSTANCE NAME IS CASE SENSITIVE)

[oracle@racnode1 ~]$ ./srvctl status instance -d racdb -i racdb1,racdb2,racdb3,racdb4

Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
Instance racdb3 is running on node racnode3
Instance racdb4 is running on node racnode4

Stop specific Instance in a cluster (INSTANCE NAME IS CASE SENSITIVE)

[oracle@racnode1 ~]$ ./srvctl stop instance -d racdb -i racdb4

Now check status of instance

[oracle@racnode1 ~]$ ./srvctl status instance -d racdb -i racdb1,racdb2,racdb3,racdb4
Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
Instance racdb3 is running on node racnode3
Instance racdb4 is not running on node racnode4
[oracle@racnode1 ~]$

Start specific Instance in a cluster

[oracle@racnode1 ~]$ ./srvctl start instance -d racdb -i racdb4

Now check status of instance

[oracle@racnode1 ~]$ ./srvctl status instance -d racdb -i racdb1,racdb2,racdb3,racdb4

Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
Instance racdb3 is running on node racnode3
Instance racdb4 is running on node racnode4
[oracle@racnode1 ~]$

Check the status of a cluster database

[oracle@racnode1 ~]$./srvctl status database -d racdb

Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
Instance racdb3 is running on node racnode3
Instance racdb4 is running on node racnode4

Stop a cluster database

[oracle@racnode1 ~]$ ./srvctl stop database -d racdb

Check status of database

[oracle@racnode1 ~]$ srvctl status database -d racdb

Instance racdb1 is not running on node racnode1
Instance racdb2 is not running on node racnode2
Instance racdb3 is not running on node racnode3
Instance racdb4 is not running on node racnode4

Start a cluster database

[oracle@racnode1 ~]$ ./srvctl stop database -d racdb

Check status of database

[oracle@racnode1 ~]$ ./srvctl status database -d racdb
Instance racdb1 is running on node racnode1
Instance racdb2 is running on node racnode2
Instance racdb3 is running on node racnode3
Instance racdb4 is running on node racnode4

Changes in 11gr2 regarding votedisk

Changes in 11gr2 regarding votedisk

There are few noticeable changes made in 11gR2 specific to voting disks.

As we are aware that Voting disk and OCR can now be stored in ASM. But we also know in previous versions, CSS need voting disk to start.

So how grid starts, when voting disk is in ASM diskgroup?

We have created ASM diskgroup DATA and my voting and OCR resides in it.

SQL> select name, type, total_mb, usable_file_mb
from v$asm_diskgroup;

NAME TYPE TOTAL_MB USABLE_FILE_MB
------ ------ ---------- --------------
DATA EXTERN 3072 2672

SQL> set line 200
SQL> column path format a30
SQL> select name, path, header_status from v$asm_disk;

NAME PATH HEADER_STATU
------ ------- ------------
DATA_0002 /dev/sdg MEMBER
DATA_0000 /dev/sdi MEMBER
DATA_0001 /dev/sdh MEMBER

crsctl query css votedisk

## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 4f2e374b254e4f57bf85ac5db31a91fb (/dev/sdi) [DATA]
Located 1 voting disk(s).
Few more things -

1. Voting disks are stored on individual disks Vs OCR which are stored as files in the ASM diskgroup. So voting disks will not span multiple disks i.e. they are confined in single disk.

2. At the start of ohasd, all disks as part asm_diskstring are scanned. ohasd know about asm_diskstring from GPnP profile.

This functionality allows CSS start before ASM.

3. How ohasd will know which disk contains voting disk?

Following is the ASM disk header metadata (dumped using kfed). ohasd use the markers between vfstart & vfend. If the

markers between vfstart & vfend are 0 then disk does *NOT* contain voting disk

grid@db1:~> kfed read ‘/dev/sdi’ | grep vf
kfdhdb.vfstart: 128 ; 0x0ec: 0×00000080
kfdhdb.vfend: 160 ; 0x0f0: 0x000000a0

grid@db1:~> kfed read ‘/dev/sdg’ | grep vf
kfdhdb.vfstart: 0 ; 0x0ec: 0×00000000
kfdhdb.vfend: 0 ; 0x0f0: 0×00000000

grid@db1:~> kfed read ‘/dev/sdh’ | grep vf
kfdhdb.vfstart: 0 ; 0x0ec: 0×00000000
kfdhdb.vfend: 0 ; 0x0f0: 0×00000000

So from above output, disk ‘/dev/sdi’ contains voting disk.

4. Number of voting disks, is decided based on the diskgroup redundancy.

5. If we want to store a voting disk in a new diskgroup (apart from one, which already holds a voting disk) we need to create a QUORAM failure group.

From Oracle docs – A quorum failure group is a special type of failure group and disks in these failure groups do not contain user data and are not considered when determining redundancy requirements.

6. Manual backup of the voting disk is not required anymore. Required data is backed up

Stopping all cluster and database services and processes on a single node in RAC

Stopping all cluster and database services and processes on a single node in RAC

1.stop instance

srvctl stop instance -i TESTRAC1 -d testrac

2.Stop ASM

srvctl stop asm -n or-01 -f

3.Stop Nodeapps

srvctl stop nodeapps -n or-01 -f

4.Stop CRS

crsctl stop crs

How Oracle Cluster Starts

How Oracle Cluster Starts

When considering how Oracle Clusterware is able to store vital files (OCR and Voting Files) in an ASM Diskgroup, and while ASM is itself dependent on Oracle Clusterware.

The following is a brief overview of the logic used for starting Oracle Clusterware with ASM when clusterware files; OCR and Voting Files, are stored in an ASM Diskgroup. Note, that there are configuration dependent variations to the following logic, but what is described is the most common case.

1.When a node of an Oracle Clusterware cluster restarts, OHASD is started by platform-specific means. OHASD is the root for bringing up Oracle Clusterware. OHASD has access to the OLR (Oracle Local Registry) stored on the local file system. OLR provides needed data to complete OHASD initialization

2.OHASD brings up GPNPD and CSSD. CSSD has access to the GPNP Profile stored on the local file system. This profile contains the following vital bootstrap data;

a.ASM Diskgroup Discovery String
b.ASM SPFILE location (Diskgroup name)
c.Name of the ASM Diskgroup containing the Voting Files

3.The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

4.OHASD starts an ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE, assuming it is stored in a Diskgroup.

5.With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.

6.OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

7.Clusterware completes initialization and brings up other services under its control.

OLR holds metadata for the local node and in conjunction with GPnP profile OHASD has all the information required initially to join the node to the cluster and OHASD manages OLR. so both OLR and GPnP profile is essential and required.

In RAC, exactly from gpnp profile cssd gets all required information to get hold of maximum number of voting disk to start cssd .

While doing to as asm_diskstring parameter ,it searches all the disk for voting file signature .
When it completes the search ,writes how many voting file it found and start cssd . those information you can see on any RAC node using below command from OS user which is holding GI .

$ gpnptool get

You can see asm_disktring value on above output .

If your system is using default path as per OS, then it will be blank.

In Case of Non-RAC, there is no such use of gpnp.

Only OLR is being used to initialize ohasd.

ASMCMD>DSGET

To fetch the ASM diskstring. The ASMCMD will work in both RAC and NON-RAC environment.

Dropping a RAC database in Windows

Dropping a RAC database in Windows

Stop database from cluster utility

C:\Users\xyz>set oracle_home=D:\app\11.2.0\grid

C:\Users\xyz>srvctl stop database -d FINOLD -o IMMEDIATE

Connect Any of Cluster Instance and change cluster database parameter to FALSE

C:\Users\xyz>SET ORACLE_SID=FINOLD1

C:\Users\xyz>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 23 11:53:42 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 5144301568 bytes
Fixed Size 2264128 bytes
Variable Size 1006633920 bytes
Database Buffers 4127195136 bytes
Redo Buffers 8208384 bytes
Database mounted.

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shutdown abort;

ORACLE instance shut down.

Mount database in exclusive mode

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 5144301568 bytes
Fixed Size 2264128 bytes
Variable Size 1006633920 bytes
Database Buffers 4127195136 bytes
Redo Buffers 8208384 bytes
Database mounted.

SQL> select logins,parallel from v$instance;

LOGINS PAR
---------- ---

RESTRICTED NO
---------------

Drop Database

When you issue this command, this will drop the database including datafiles, control files, redo log files & archive log files.

SQL> drop database;

Database dropped.

Drop database including backups

To drop the database including the backup, we can go for the below option

RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

Remove database entry from cluster

C:\Users\xyz>srvctl stop instance -i FINOLD1 -d FINOLD

C:\Users\xyz>srvctl stop instance -i FINOLD2 -d FINOLD

PRCC-1017 : FINOLD was already stopped on OR-12

C:\Users\xyz>srvctl remove instance -i FINOLD1 -d FINOLD

Remove instance from the database FINOLD? (y/[n]) y

C:\Users\xyz>srvctl remove instance -i FINOLD2 -d FINOLD

Remove instance from the database FINOLD? (y/[n]) y

C:\Users\xyz>srvctl remove database -d FINOLD

Flashback on RAC

Flashback on RAC

Check status

select FLASHBACK_ON, LOG_MODE from v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
NO NOARCHIVELOG

Create restore point

CREATE RESTORE POINT before_app_upg GUARANTEE FLASHBACK DATABASE;

Created.

Check restore point

select NAME,SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM GV$RESTORE_POINT;

check logfiles under flash_recovery_area

show parameter db_recovery

[oracle@orac1 ~]$ export PATH=/u01/app/11.2.0/grid/bin:$PATH;
[oracle@orac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@orac1 ~]$ export ORACLE_SID=+ASM1
[oracle@orac1 ~]$ asmcmd

ASMCMD> cd DATA/RAC/FLASHBACK
ASMCMD> ls
log_1.317.772113565
log_2.318.772113845
ASMCMD> ls -l
Type Redund Striped Time Sys Name
FLASHBACK UNPROT COARSE JAN 09 12:00:00 Y log_1.317.772113565
FLASHBACK UNPROT COARSE JAN 09 12:00:00 Y log_2.318.772113845
ASMCMD>

Test Flashback

srvctl stop database -d RAC

[oracle@orac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 13:03:00 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 369101212 bytes
Database Buffers 41943040 bytes
Redo Buffers 6103040 bytes
Database mounted.

SQL> flashback database to restore point before_app_upg;

alter database open resetlogs;

Drop Restore Point

drop restore point before_app_upg;

Shutdown and start both instances

shutdown immediate;

srvctl start database -d RAC

OCR and voting disk management in 11GR2 RAC

OCR and voting disk management in 11GR2 RAC

OCR: It created at the time of Grid Installation. It’s store information to manage Oracle cluster-ware and it’s component such as RAC database, listener, VIP,Scan IP & Services.

Minimum 1 and maximum 5 copy of OCR is possible.

Voting Disk: It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster. If any node is not passing heat-beat across other note or voting disk, then that node will be evicted by Voting disk.

Minimum 1 and maximum 15 copy of voting disk is possible.

New Facts:

-We can store OCR And Voting disk on ASM or certified cluster file system.

-We can dynamically add or replace voting disk & OCR.

-Backup of Voting disk using “dd” command not supported.

-Voting disk and OCR can be keep in same disk-group or different disk-group

-Voting disk and OCR automatic backup kept together in a single file.

-Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week

-You must have root or sudo privilege account to manage it.

To find current location of Voting disk:

[oracle@rsingle ~]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group
– —– —————– ——— ———
1. ONLINE 6a60a2c3510c4fbfbff62dcdc279b247 (ORCL:DATA1) [DATA]

Relocate or multiplexing Voting disk to another disk-group (With normal redundancy)

[root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl replace votedisk +CRS
Successful addition of voting disk afb77b2693a24f1ebfe876784103e82a.
Successful deletion of voting disk 6a60a2c3510c4fbfbff62dcdc279b247.
Successfully replaced voting disk group with +CRS.
CRS-4266: Voting file(s) successfully replaced

New location of Voting disk:

root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
– —– —————– ——— ———
1. ONLINE afb77b2693a24f1ebfe876784103e82a (ORCL:DATA2) [CRS]
2. ONLINE 3e2542c5b1154ffdbfc8b6dea7dce390 (ORCL:DATA3) [CRS]
3. ONLINE 8e0f3c5921cc4f93bf223de1465d83cc (ORCL:DATA4) [CRS]
Located 3 voting disk(s).

You can use these commands to add, delete,query and replace votedisks

crsctl add css votedisk
crsctl delete css votedisk
crsctl query css votedisk

crsctl replace css votedisk

OCR (Oracle Cluster Registry)

To find location of Current OCR:

[oracle@rsingle ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2964
Available space (kbytes) : 259156
ID : 1390115973
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

Create mirror copy of OCR online

oracle@rsingle ~]$ sudo ocrconfig -add +CRS

Password:

Check location of OCR after mirror copy creation:

root@rsingle ~]# /u01/app/11.2.0/grid/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2964
Available space (kbytes) : 259156
ID : 1390115973
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File Name : +CRS
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Another file to find location of OCR:

root@rsingle ~]# cat /etc/oracle/ocr.loc

#Device/file getting replaced by device +CRS
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+CRS

SP2-1503: Unable to initialize Oracle call interface

SP2-1503: Unable to initialize Oracle call interface

Solution:

After upgrade from Oracle 10g, or Oracle 11gR1 to Oracle 11g R2 you may get at the

$>sqlplus "/ as sysdba"

SP2-1503: Unable to initialize Oracle call interface

SP2-0152: ORACLE may not be functioning properly

This problem is related to the $ORA_TZFILE environment variable in Oracle 11GR2 which is no longer needed.

The workaround is to unset the variable and then call sqlplus again

$unset ORA_TZFILE

$sqlplus "/ as sysdba"

sql>

If you use Oracle E-Business Suite 11i or R12 then you have to remove the environment variable ORA_TZFILE also from the
database environment file.

At the end of $ORACLE_DATABASE_HOME/SID_host.env there is an entry

######################
# Timezone Specification file
ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
export ORA_TZFILE
######################

you must remove it. It shows a file(timezlrg.dat) that no longer exists!

You must also remove the ORA_TZFILE and from $ORACLE_DATABASE_HOME/appsutil/template/ad8idbux.env

Tuesday, February 23, 2016

Enabling and disabling Archivelog mode

Enabling and disabling Archivelog mode

Solution:

First check in what mode you are.

Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE;

From NOARCHIVELOG -> AUTOMATIC ARCHIVELOG MODE

To turn on automatic archivelog mode for Oracle9i set the parameters.

alter system set log_archive_start=TRUE scope=spfile;
alter system set log_archive_max_processes=3 scope=spfile;
alter system set log_archive_min_succeed_dest=1 scope=spfile;
alter system set log_archive_trace=0 scope=spfile;
alter system set log_archive_format='arch%t_EDB1_%s.ARC' scope=spfile;
alter system set log_archive_dest_1='location=/oracle10/ARCHIVES/EDB1' scope=spfile;

You have to restart the database

sqlplus "/ as sysdba"

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

alter system switch logfile;

Check if the archive log file is created at the log_archive_dest_1

Depending on the version of Oracle some small differences might exist. For example at Oracle10g you don't have to set:

log_archive_start=TRUE and the log_archive_format must contain %s, %t and %r.

For RAC especially, because of the two instances lets say OTE1 and OTE2 the steps are a little bit different.

First you have to set the parameter cluster_database = FALSE

Then to startup mount only one instance (PTE1)

Then shutdown immediate this instance (PTE1)

Then change parameter cluster_database = TRUE

From AUTOMATIC ARCHIVELOG MODE -> NOARCHIVELOG

Remove all parameters from spfile that have to do with archiving, and restart the database. For example

ALTER SYSTEM RESET log_archive_format SCOPE=SPFILE SID='*';

You have to restart the database

sqlplus "/ as sysdba"

shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

Crosscheck archives (CHANGE ARCHIVELOG ALL VALIDATE)

Crosscheck archives (CHANGE ARCHIVELOG ALL VALIDATE)

Summary

Maybe for some reason you have lost some archives or delete them on purpose. The result is the RMAN backup of the archives to constantly fail.

For example you might get error messages from rman backup like this:

channel t1: starting piece 1 at 29-APR-04

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t1 channel at 04/29/2004 18:01:05

ORA-19505: failed to identify file "/oradmp/archive/PRODDB/PRODDB_1_161663.arc"

ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory

The workaround is to connect to the RMAN catalog and execute a validation of the archive logs.

The Recovery Manager CHANGE command removes references to a backup piece, (datafile or archivelog) from the controlfile and
recovery catalog when the file no longer exists on disk.

First connect to the RMAN Catalog. Login as oracle owner of the software at the unix and set the ORACLE_HOME.

rman target / catalog rman/*****@RMAN

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

target database Password:

connected to target database: PRODDB (DBID=4131138365)

connected to recovery catalog database

Then crosscheck the archives.

RMAN>CHANGE ARCHIVELOG ALL VALIDATE;

Tip: If you have a RAC system you need to validate archives from both nodes.

ORA-00257: Database freeze, restart archiving

ORA-00257: Database freeze, restart archiving

Summary

If you have enable archive log mode and run out of space at the archive log destination filesystem, then it is a matter of time to get the database freeze. You'll get the error:

ORA-00257 archiver error. Connect internal only, until freed.

The workaround is very simple. First option is to release some space by taking an rman backup with delete input for the archives, or change the archive log destination to a filesystem with enough free space.

alter system set log_archive_dest_1='location=/filesystem_with_free_space' scope=memory;

If you don't have time to wait for the backup move the archives to another destination, or the final solution if you don't have any free space at all, just delete them!

In case you delete archives, bear in mind that you have to take a full database backup afterwards. Don't wait for the next scheduled backup next week! Take it at the same day you delete the archives.

Now that you have free space at the archive log destination. Restart the archiver.

sqlplus "/ as sysdba" OR svrmgrl>CONNECT internal

ARCHIVE LOG STOP;

ARCHIVE LOG START;

ARCHIVE LOG ALL;

ALTER SYSTEM SWITCH LOGFILE;

One good way to avoid restarting archiver manually, is setting reopen at the archive log destination. For example reopen=60 will automatically in 60 seconds retry to start archiving (so you only have to get free space at the archive log destination)

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/genevamig/oradata/GNVTMG1/archives reopen=60';

Tip:If you move or delete archives, then the Rman will fail the next time because of the missing logs.

channel t1: starting piece 1 at 29-APR-04
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 04/29/2004 18:01:05
ORA-19505: failed to identify file "/oradmp/archive/PRODDB/PRODDB_1_161663.arc"
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Recovery Manager complete.

In that case you should tell Rman to check which archived logs are still available on disk. The workaround is: Crosscheck archives.

Oracle Golden Gate Questions and Answers

Oracle Golden Gate Questions and Answers

Golden Gate Questions and Answers

1) Is GoldenGate certified for Oracle E-business Suite Release 12. The metalink note 1112325.1t suggest that it can be used for EBS reporting . Is it a certified combination.

2) There is a feature called Syncfile in Goldengate. Can this be used for E-business suite file synching (binaries file synching to Disaster Recovery site) ? The below URL gives a hint about it.
www.oracle.com/goldengate/goldengate-faq.pdf

3) So many people use Data Guard, It seems GG can also do the same thing. It can read from the redologs and apply them. Shouldn’t we use GG instead of DataGuard. What are the pros and cons of both.

4) GG versus Oracle Advanced Replication?

5) Is the GG mainly a data replication solution and doesn’t offer any file synchronization ? What about SynFile feature.

6) Are we saying GG is mainly data replication solution and doesn’t offer any file synchronization ?

7) GG and DataGuard are licensed together .Can you shed some light on it.

8) GG can help upgrading the database from 9i to 11g . How is that possible ?

Oracle Answers.

Question

Is GoldenGate certified for Oracle E-business Suite Release 12. The metalink note 1112325.1t suggest that it can be used for EBS reporting . Is it a certified combination.

Answer:
The Knowledge document Doc ID 1112325.1 describes using Oracle GoldenGate with Oracle E-Business Suite Release 12.1.1 for operational reporting, in which selected tables are replicated from the primary Oracle E-Business Suite database to a secondary (duplicate) instance. This enables read-intensive, custom reports to be run without using resources on the primary Oracle E-Business Suite database. So as of now only selected tables are replicated from the primary Oracle E-Business Suite database to a secondary (duplicate) instance using Oracle GoldenGate for operational reporting only.

Please Note Important: It is highly recommended that organizations planning to implement Oracle GoldenGate to achieve Operational Reporting using Oracle E-Business as a source, have technical specialists with prior training and experience using Oracle GoldenGate. Organizations lacking sufficient skills within the team, should contact Oracle Consulting Services for assistance before attempting this type of solution.

Supported User Data Tables and limitations:
-----------------------------------------------------
a.Approximately 21000 Oracle E-Business Suite tables are supported by Oracle GoldenGate.
b.Tables without a primary key or unique index are fully supported by Oracle GoldenGate, but could add extra volume to redo log generation as all columns must be supplementally logged.
c.Fewer than twenty Oracle E-Business Suite tables are not supported by Oracle GoldenGate; this is because they contain data types ANYDATA and UROWID.

Current restrictions mean that Oracle GoldenGate may not always capture 100 percent of changed data created by Oracle E-Business Suite. The issues range from data types not supported by Oracle GoldenGate 10.4, through to data issues such as ANYDATA or UROWID columns that may require addition of business logic to transform the data into a usable form on the target system.

Please refer to the KM Doc ID 1112325.1 for more information.

Question

There is a feature called Syncfile in Goldengate. Can this be used for E-business suite file synching (binaries file synching to Disaster Recovery site) ?

Answer:

NO, Syncfile commands in OGG cannot be used with Oracle Database as it is only used with Nonstop Tandem Environments.
FYI, Syncfile utility is mainly used to duplicate entire files on a scheduled basis in NonStop systems.By default, Syncfile uses the NonStop FUP DUP utility to perform file duplication

Question

So many people user Data Guard, It seems GG can also do the same thing. It can read from the redologs and apply them. Should'nt we use GG instead of DataGuard. What are the pros and cons of both.

Answer:

Oracle Data Guard and Oracle Active Data Guard:
---------------------------------------------------------
Oracle Active Data Guard, which is built on the infrastructure of Oracle Data Guard, allows a physical standby database to be open read-only while changes are applied to it from the primary database. This enables read-only applications to utilize the physical standby with minimal latency between the data on the standby database and that on the primary database, even while processing very high transaction volumes at the primary database.

An Oracle Active Data Guard standby database is used for automatic repair of data corruptions detected by the primary database, transparent to the application. In the event of an unplanned outage on the primary database, high availability is maintained by quickly failing over to the standby database. An Active Data Guard standby database can also be used to off-load fast incremental backups from the primary database given that it is a block-for-block physical replica of the primary.

Oracle Active Data Guard enables read-only reporting on the physical standby with minimal latency between reporting and production data. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database using fast incremental backups. Oracle Active Data Guard provides the additional benefit of high availability and disaster protection by quickly failing over to the standby database in the event of a planned or an unplanned outage at the production site. Active Data Guard only supports Oracle Database Enterprise Edition.

Oracle Data Guard provides disaster recovery (DR) and data availability for the Oracle Database, enabling very fast failover to a standby database in the event of a failure in the primary database. Oracle Active Data Guard extends the physical standby database to be open for read-only traffic, and also for fast incremental backups. This allows
customers to better leverage their existing DR investment and increase the performance of the production database by offloading query and backup processing to (up to 30) physical standby databases.

Oracle Active Data Guard also provides transparent data protection by automatically repairing corrupted data blocks on the primary database using the valid data blocks from the physical standby database, and vice versa.
Oracle Data Guard and Oracle Active Data Guard are exclusively for Oracle to Oracle replication, where the database schemas are identical and the computing platforms have the similar architecture. Because replication is at the physical block level, and no data or schema transformations take place, this is the fastest replication possible. Oracle Data Guard with the Oracle Active Data Guard option continues to be the strategicdisaster recovery and data availability solution for Oracle databases.

Oracle GoldenGate:
--------------------------
Oracle GoldenGate delivers an enterprise-wide solution for heterogeneous real time data access and delivery needs. Oracle GoldenGate works across different databases, platforms and schemas, and supports active-active configurations. By reading database logs, Oracle GoldenGate avoids any impact to the source database and ensures reliable, transactionally consistent delivery of database transactions across wide area or local networks. With its flexible, modular architecture, Oracle GoldenGate solves a wide range of high availability challenges, including active-active deployments, multi-master deployments, data distribution for infrastructure cost reduction and high availability, and zero downtime migrations and upgrades. Oracle GoldenGate is the strategic replication solution for Oracle Database and for heterogeneous databases, with proven success in a wide range of demanding industries and mission critical use cases.

Oracle GoldenGate is a log-based, real-time change data capture (CDC) software platform that moves high volumes of transactional data between databases with very low latency, and allows logical data replication. A typical environment includes a capture, pump and delivery process.You can replicate all or a portion of your data, and you can filter or transform the data within any of these processes not only for heterogeneous environments but also different database schemas.Oracle GoldenGate is also an excellent product for minimizing downtime during planned maintenance, including application and database upgrades, in addition to platform migrations.

With GoldenGate for Oracle, you can replicate DML and DDL operations.You can move data between similar or dissimilar supported Oracle versions, or you can move data between an Oracle database and a database of another type. GoldenGate supports the filtering, mapping, and transformation of data.

GoldenGate supports both Oracle Database Standard Edition and Oracle Database Enterprise Edition. For replication involving a heterogeneous mix of Oracle and non-Oracle databases, GoldenGate should be the obvious choice.

Summary:
----------------
1.If your business requirement is a simple, high-performance, drop-in solution for disaster recovery (DR), data protection and high availability (HA) for the entire Oracle database, Oracle Active Data Guard should be the recommended solution.

***Disaster Recovery (DR) / Data Protection / High Availability (HA) - Use Active Data Guard

a.It is a high-performance, simple, drop-in solution for HA and DR.
b.It maximizes DR investment by utilizing physical standbys for queries and backups.
c.It works for all apps and data types, including packaged apps that can’t be changed.
d.It is fully integrated with all other Oracle database HA capabilities – e.g. RAC, Flashback, Client Failover, Automatic Block Repair, Lost Write Protection, etc.
e.It provides basic capabilities to reduce planned downtime for technology refresh, select migrations, and upgrades of Oracle Database software.

2.If your business requirement is to set up a data distribution / data synchronization solution through this Oracle-Oracle replication configuration, or a more flexible multi-master HA solution, Oracle GoldenGate should be the recommended solution.

***flexible HA, active-active, schema & platform changes, HA/DR for non-Oracle databases - Use Oracle GoldenGate

a.It supports heterogeneous platforms & databases, transformations, subset replication, active-active deployments, etc.
b.It supports a wide variety of minimal and zero downtime planned maintenance activities.

Note: Active-active configurations usually require data conflicts to be avoided or at least managed by the application, so active-active architecture for real-time replication is more suitable for custom applications. With respect to planned migrations, GoldenGate’s certification on Oracle Siebel CRM supports an active-active configuration to provide zero downtime upgrades and migrations.

3.If your business requirement is Information Distribution & Consolidation, Application Upgrades & Changes

***Use GoldenGate – supports heterogeneous platforms & databases, transformations, subset replication, active-active deployments, hub & spoke, multi-master, bi-directional configurations, etc.

4.Dual Use of both Oracle GoldenGate and Oracle Active Data Guard in your production environment:
Oracle GoldenGate and Oracle Active Data Guard when used together offer a unique data protection and information distribution solution not offered by any other product. Given the fact that they are licensed together, Oracle recommends that both solutions are deployed together whenever appropriate.
For example, a centralized global manufacturing database may be protected using an Active Data Guard physical standby, set up with Data Guard Fast-Start Fail-over with synchronous redo transport, ensuring zero data loss and integrated fail-over of applications in the event of an outage at the primary data center.

At the same time, using Oracle GoldenGate, it is possible to set up bi-directional replication from this central database to smaller regional databases supporting local manufacturing operations. These may be non-Oracle databases, and they could also be configured in a hardware and OS platform that is different from that of the central database. Enabling such a fully active, globally distributed and highly available configuration is one of the unique value propositions of implementing Oracle GoldenGate together with Oracle Active Data Guard.Oracle GoldenGate can also help upgrade customers on older database versions such as Oracle9i to Oracle Database 11g without impacting their operations, and after the upgrade those customers can start benefiting from Active Data Guard’s advanced HA capabilities.

Question

GG versus Oracle Advanced Replication.

answer

Advanced replication supports various configurations of updateble-snapshot, multi-master and update anywhere replication whereas Oracle GoldenGate delivers an enterprise-wide solution for heterogeneous real time data access and delivery needs.

Oracle GoldenGate works across different databases, platforms and schemas, and supports active-active configurations. By reading database logs, Oracle GoldenGate avoids any impact to the source database and ensures reliable, transactionally consistent delivery of database transactions across wide area or local networks. With its flexible, modular architecture, Oracle GoldenGate solves a wide range of high availability challenges, including active-active deployments, multi-master deployments, data distribution for infrastructure cost reduction and high availability, and zero downtime migrations and upgrades. Oracle GoldenGate is the strategic replication solution for Oracle Database and for heterogeneous databases.

So ti is advisable to use Oracle GoldenGate as it supports replication to heterogeneous environments and avoids any impact to the source database.

Question

Are we saying GG is mainly data replication solution and doesn’t offer any file synchronization ? I can see GG has many products in it.

Answer:

OGG support file synchronization only in Nonstop systems but not in Oracle database environments.As it internally uses Nonstop tools like FUP DUP and TACL scripts to accomplish this function.
Currently file synchronization is not supported in open systems and Oracle database environments.

Question

GG and DataGuard are licensed together .Can you shed some light on it.

Answer:

The license for Oracle GoldenGate includes the ability to capture database transactions from Oracle Database and deliver database transactions to Oracle Database. It also includes a full use license for Oracle Active Data Guard, and a full use license for XStream. Note, the capture and deliver features of Oracle GoldenGate work with any edition of the Oracle Database, while Active Data Guard and XStream require Oracle Database Enterprise Edition.

Qustion

All the features mentioned above for GG have resemblance with Oracle Data Integrator as well . What is the difference ? Where we should pitch ODI and where to pitch GG ? Aren’t they very close to each other ?

Answer:

Oracle Data Integrator Enterprise Edition:
------------------------------------------
Oracle Data Integrator Enterprise Edition delivers high-performance movement and transformation of heterogeneous data in batches and mini-batches for use in data warehousing, business intelligence, application integration, and service oriented architecture use cases. Oracle Data Integrator Enterprise Edition utilizes heterogeneous databases to perform high speed complex data transformations and leverage existing resources. With its flexible, modular architecture and declarative design methodology, Oracle Data Integrator Enterprise Edition helps companies reduce the total cost of ownership for data integration. In addition, Oracle Data Integrator Enterprise Edition integrates with Oracle’s and third party data quality products.

Oracle Data Integrator Enterprise Edition remains the strategic data integration product from Oracle for ETL, ELT, batch integration, mini-batch integration, and bulk transformation. The changed data capture functionality included with Oracle Data Integrator Enterprise Edition is suitable for periodic, ongoing loads for a data warehouse.

Oracle GoldenGate:
------------------------------------------
As Oracle’s solution for enterprise-wide heterogeneous real time data access and delivery needs, Oracle GoldenGate complements Oracle Data Integrator Enterprise Edition to provide the real time data integration required for real time business intelligence. By reading transaction logs, Oracle GoldenGate imposes minimal impact on transactional systems and maintains transactional integrity across databases. Oracle
GoldenGate moves data with very low latencies in the most demanding operational and business intelligence environments. Oracle GoldenGate is Oracle’s strategic solution for real time data integration. It enables non-invasive operational reporting and other types of query offloading by maintaining a real-time replica that can be on a different database platform and lower cost hardware. In combination with Oracle Data Integrator Enterprise Edition for transformation, Oracle GoldenGate makes real time business intelligence possible, allowing companies to combine insight about the current state with historical context.

Recommended Data Integration Use Cases:
-----------------------------------------------------------
1. Typical Data Warehouse Loading & SOA Use Cases: Oracle Data Integrator Enterprise Edition For typical batch-oriented data warehousing, Oracle Data Integrator Enterprise Edition (ODI-EE) is the recommended solution. ODI-EE extracts, loads, and transforms data for initial loads and incremental batch updates with set-based execution of complex transformations. It can also work with comprehensive data quality products such as
Oracle Data Quality. ODI-EE is also the solution for bulk transformations in a SOA.

2. Data Synchronization, Query Offloading: Oracle GoldenGate Oracle GoldenGate is recommended for data synchronization use cases in which two systems are kept in sync bi-directionally and for use cases of moving database transactions from more expensive systems to less expensive, then running BI tools and other queries against the lower cost replica to reduce the load and cost on mission critical transactional systems.

3. Combining Oracle GoldenGate and Oracle Data Integrator Enterprise Edition It is a powerful combination to use Oracle GoldenGate and Oracle Data Integrator together for real time business intelligence. Oracle GoldenGate captures database transactions from OLTP sources and propagates them in real time to a permanent staging area in an enterprise data warehouse, Then, ODI picks up the changes from the permanent staging area and performs transformation within the database to load into the optimized data warehouse schema. By combining information about the current business
state with historical analysis and context, companies can drive informed business decisions throughout the day.

There is no other solution available in the market that offers real time data integration and transformation within heterogeneous databases to enable real time business intelligence without additional server investment.

Product Integration Plans:Oracle Data Integrator Enterprise Edition and Oracle GoldenGate will continue as separate products that offer additional advantages when used together. Product integration plans include, for example, allowing Oracle GoldenGate to move data from source to target in real time and directly populate the change data capture tables of Oracle Data Integrator so it can perform streamlined complex transformations into the
warehouse schema. This will further reduce the end-to-end latency for real time data warehousing.
With its next generation data integration platform, Oracle envisions even greater synergies between bulk data movement and transformation and real time data movement. Existing customers will be carried forward to the next generation data integration platform from any of Oracle’s existing data integration products, including Oracle GoldenGate.

Learning Oracle

Pages