జయకుమార్(JAYAKUMAR)

2018-07-13T16:58:00.002+05:30

cpu

2018-07-13T16:29:00.000+05:30

col statement format a30 wrap
col username format a10 trunc
select b.sql_text \"Statement \",a.buffer_gets \"BUffer Gets\",
a.executions \"Executions\",
a.buffer_gets/decode(a.executions,0,1,a.executions) \"Ratio\",
c.username from v$sqlarea a,
v$sqltext_with_newlines b,
dba_users c
where a.parsing_user_id = c.user_id
and a.address=b.address
and a.buffer_gets>1
order by a.buffer_gets desc,b.piece;

To find CPU Usage

2011-12-16T12:41:00.002+05:30

col statement format a30 wrap col username format a10 trunc select b.sql_text \"Statement \",a.buffer_gets \"BUffer Gets\", a.executions \"Executions\", a.buffer_gets/decode(a.executions,0,1,a.executions) \"Ratio\", c.username from v$sqlarea a, v$sqltext_with_newlines b, dba_users c where a.parsing_user_id = c.user_id and a.address=b.address and a.buffer_gets>1 order by a.buffer_gets desc,b.piece;

HSBC faq's

2011-12-16T10:26:00.000+05:30

1.tell me abt ur self
2.how many dba's r there in ur project
3.daily activities
4.backup stratergies
5.why more redo is generated when you say begin bkup
6.wht is diff between normalblock and modified block
7.installation procedure
8.about pt?wht u know
9.rman uses
10.db refresh hot/cold
11.user unable to access?everything is fine unable to ping?
12.how to import fast large data
13.datafile lost no bkup
14.tablespace mgmt and types of TS
15.i hav sunday bkup,monday schema refresh done on tuesday db crash wht happens.will i get data of tht schema
16.cron jobs
17.OS tunning
18.wht hapens when ts is in begin bkup
19.db size of ur project and little abt ur environment
20.bkup overwriting policy
21.adding redolog files,members, changing dest syntaxes
22.why u need to set environment variables while installation and where u set and in which user u set
23.10g features
24.asm
25.tkprof,stats pack
26.control file lost when db is running,wht hapens no bkup no trace.
can we create again db
27.one ts is in bkup mode and tht datafile is deleted by mistake no bkup wht hapens.wht abt tht data
28.dbverify utility?how many time u do tht
29.dblinks
30.redolog member corrupted wht u do.

R12: Format Payment Instructions: Error "Java Exception in Thread 'Main' java.lang.OutOfMemoryError: Java Heap Space"

2011-12-16T10:11:00.000+05:30

Solution

To implement the solution, please execute the following steps:
Please refer to NOTE 553413.1 and follow the steps to terminate the PPR on a test instance, then:

1. Increase the amount of memory available as follows:
a. Login to the instance as system administrator
b. Go to Concurrent -> Program -> Define
c. Search for the following concurrent program
Short Name: IBY_FD_PAYMENT_FORMAT
Application: Payments

d. In the 'Executable' group, there is a field named 'Options'.
Enter the value '-ms2048m -mx2048m' as the value for this field.

This will set the heap space min and max size to 2GB.
Bounce the concurrent managers.

Output Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space

2011-12-16T09:54:00.000+05:30

Solution:

1. Log into applications with the System Administrator responsibility.
2. Navigate to Concurrent -> Program -> Define
3. Query the XML Publisher Template Re-Generator program
4. Set the following value for the Executable Options : -Xmx1024m
5. Save changes.
6. Retest the program.

9i ARCHITECTURE FAQ

2010-09-27T22:18:00.005+05:30

1. What is an Oracle Server
Oracle server is a database management system which consists of an
oracle instance and an oracle database.

2. What is an Oracle Instance
Instance is a combination of SGA and background processes.

3. What is an Oracle database
Oracle database consists of database files which provides the physical
storage for database information .

4. What is SGA
System Global Area used to store database information that is shared by database
processes.

5. What are the mandatory background processes
SMON, PMON, DBWR, LGWR, CKPT.

6. What are the components of SGA
Shared pool, Database buffer cache, Redo log buffer, Large pool, Java pool.

7. What does shared pool contain
Shared pool contains most recently executed SQL statements
Most recently used data definitions.

8. What does database buffer cache contain
It contains copies of data blocks that have been retrieved from the datafiles.

9. What does redo log buffer contain
Redo log buffer records all changes made to the data for recovery purpose.

10.What is the function of SMON
SMON performs instance recovery by roll forwarding the committed as
Well as uncommitted transactions and rollbacks the uncommitted transactions.
Colease free space in datafiles.

11. What is the function of PMON
PMON cleans up after failed processes by rolling back the user’s current transactions,
Releases table or row locks, resources held by user processes, restarts the dead
Dispatchers.

12. What is the function of DBWR
DBWR writes dirty buffers (buffers which are modified but not yet written to
Datafiles.) from buffer cache to datafiles.

13. What is the function of LGWR
LGWR writes the data from redo log buffer to redo log files.

14. What is the purpose of checkpoint
Purpose of checkpoint is to make sure that all the buffers in the buffer cache that were
Modified prior to point in time have been written to the datafiles.

15. When does DBWR writes dirty buffers from buffer cache to datafiles
When a check point occurs
When dirty buffers reach thresh hold
When time out occurs
When a tablespace is taken offline
When a tablespace is taken to read only
When a tablespace is kept in begin backup mode
When a table is dropped or truncated
When the database is shutdown.

16. When does LGWR writes to redo log files
When redo log buffer is one third full
When a commit occurs
Before DBWR writes to datafiles
Every 3 seconds
When there is 1MB of redo in redo log buffer

17. What is a parameter file
Parameter file defines the characteristics of the instance

18. What are the types of parameter files
Pfile, spfile

19. What is the difference between pfile and spfile
Pfile is a text file , spfile is a binary file
Changes to entries in pfile take effect on the next startup
Changes to entries in spfile are persistent across startup and shutdown

20. What is the default location of pfile
$ORACLE_HOME/dbs

21. What is the use of control file?what information it contain?
Control file is used to maintain the integrity of the database.It contains information
Like database name, timestamp of database creation, tablespace names, location of
Datafiles and redo log files, checkpoint information, current redo log sequence
Number

22. What are the logical structures of the database
Tablespaces, segments, extente, blocks

23. What is a tablespace
Tablespace is a logical structure of the database that consists of one or more datafiles

24. What is a segment
Segment is a space allocated for a specific object within a tablespace

25. What is an extent
An extent is a set of contiguous oracle blocks

26. What is an oracle block
Block is a smallest unit of storage for read write operations.

27. What is the default standard block size in 9i
8k

28. What are the parameters for sizing the buffer cache,shared pool,redo log buffer
Db_cache_size, shared_pool_size, log_buffer

29. What is a password file
Password file authenticates privileged users to start up and shut down an oracle
Instance

30. What is an archived redo log file
Archived redo log files are offline copies of the redo log files that are necessary for
Recovery from media failures.

31. What is a session
A session is a specific connection of a user to an oracle server

32. What is PGA
Memory reserved for each user process connecting to an oracle database

33. What Is dedicated server
Server process handles the request of a single user process in dedicated server

34. What is shared server
In shared server, multiple server process handles the requests of several user
Processes

35. What is server process
Server process is a program which interacts directly with oracle server

36. What is user process
User process is a program which requestsinteraction with the oracle server

37. How can we control the size of SGA
SGA_MAX_SIZE parameter can control the size of sga.

10g installation document

2010-09-23T08:21:00.001+05:30

We should install Rpm’s
[root@rac21 ~]#rpm –ivh compat-gcc-c++-7.3-2.96.128.i386.rpm
Configuring Network
[root@rac21 ~]#vi /etc/hosts
192.168.0.1 orawave1.appsdba.com orawave1
:wq
[root@rac21 ~]#service network restart
NOTE: After that you have to check ip and hostname
[root@rac21 ~]#ifconfig --------to check ip address
[root@rac21 ~]#hostname –i
[root@rac21 ~]#hostname -d
[root@rac21 ~]#hostname
[root@rac21 ~]#ping 192.168.0.1

We have to added following lines to sysctl.conf
]#vi /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
:wq

[root@rac21 ~]#sysctl –p
(or)
[root@rac21 ~]#/sbin/sysctl –p

For Oracle 10g we should create one group and one user
NOTE: Group should be DBA or OINSTALL
[root@rac21 ~]#groupadd -g 400 dba
[root@rac21 ~]#useradd -u 500 -g dba -d /u01/oracle oracle

Then need to change Owner and permission by using chown and chmod
[root@rac21 ~]#chown –R oracle:dba /u01/oracle
[root@rac21 ~]#chmod –R 777 /u01/oracle

Enable GUI by running Xhost +[root@rac21 ~]#xhost +

[root@rac21 ~]#vi /etc/oraInst.loc

inventory_loc=/u01/oracle/10.2.0/oraInventory
:wq

Switch to user for setting environment
[root@rac21 ~]#su - oracle
[oracle@oracle~]$ls -a
[oracle@oracle~]$vi .bash_profile

export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/oracle/10.2.0
export PATH=$PATH:$ORACLE_HOME/bin:.
export LD_ASSUME_KERNEL=2.4.19
export ORACLE_SID=prod
:wq

[oracle@oracle~]$echo $ ORACLE_BASE
/u01/oracle
[oracle@oracle~]$ echo $ ORACLE_HOME
/u01/oracle/10.2.0
[oracle@oracle~]$ echo $ ORACLE_SID
prod
[oracle@oracle~]$ echo $ PATH
[oracle@oracle~]$cd /stage/10g/database
[oracle@oracle~]$ ./runInstaller

After ending the installation OUI will give one script that is root.sh
We should run as a root user
[root@rac21 ~]# sh /u01/oracle/10.2.0/root.sh

tablespace

2010-06-28T22:52:00.000+05:30

The primary types of tablespaces in an Oracle database are permanent, undo, and temporary.
Permanent tablespaces contain segments that persist beyond the duration of a session or a transaction.

Although the undo tablespace may have segments that are retained beyond the end of a session
or a transaction, it provides read consistency for select statements that access tables being modified as well as provides undo data for a number of the flashback features of the database. Primarily,however, undo segments store the previous values of columns being updated or deleted, or toprovide an indication that the row did not exist for an insert so that if a users session fails before the user issues a commit or a rollback, the updates, inserts, and deletes will be removed. Undo segments are never directly accessible by a user session, and undo tablespaces may only have undo segments.

As the name implies, temporary tablespaces contain transient data that exists only for the
duration of the session, such as space to complete a sort operation that will not fit in memory.
Bigfile tablespaces can be used for any of these three types of tablespaces, and they simplify
tablespace management by moving the maintenance point from the datafile to the tablespace.
Bigfile tablespaces consist of one and only one datafile. There are a couple of downsides to bigfile
tablespaces

SYSTEM Tablespace User segments should never reside in the SYSTEM tablespace, period.
As of Oracle 10g, you can specify a default permanent tablespace in addition to the ability to specify a default temporary tablespace in Oracle9i.

SYSAUX Tablespace Like the SYSTEM tablespace, the SYSAUX tablespace should not have any
user segments. The contents of the SYSAUX tablespace, broken down by application, can be
reviewed using EM Database Control. You can edit the SYSAUX tablespace by clicking the
Tablespaces link under the Server tab and clicking the SYSAUX link. Figure 3-1 shows a graphical representation of the space usage within SYSAUX.

Undo Multiple undo tablespaces can exist in a database, but only one undo tablespace can be active at any given time. Undo tablespaces are used for rolling back transactions, for providing read consistency for select statements that run concurrently with DML statements on the same table or set of tables, and for supporting a number of Oracle Flashback features, such as Flashback Query.

Temporary More than one temporary tablespace can be online and active in the database,
10g, multiple sessions by the same user would use the same temporary tablespa
one default temporary tablespace could be assigned to a user. To solve this pote
bottleneck, Oracle supports temporary tablespace groups. A temporary tablespa
synonym for a list of temporary tablespaces.
A temporary tablespace group must consist of at least one temporary tablesp
empty. Once a temporary tablespace group has no members, it no longer exists