Some good question and answer from 10g OCP Certification Exam Guide part 1

PART1 

Question  1. Which of the following commands will start the iSQL*Plus server process?

(Choose the best answer.)

A. emctl start isqlplus

B. isqlplusctl start

C. isqlplus /nolog

D. lsnrctl start isqlplus

2. You issued the URL http://127.0.0.1:5500/em and received an error. What

could be the problem? (Choose three answers.)

A. You have not started the Database Listener

B. You have not started the dbconsole.

C. The dbconsole is running on a different port.

D. You are not logged onto the database server node.

E. You have not started the Grid Control agent.

F. You have not started the database.

3. Which files must be synchronized for a database to open? (Choose the best

answer.)

A. Datafiles, online redo log files, and the controlfile

B. The parameter file and the password file

C. All the multiplexed controlfile copies

D. None. SMON will synchronize all files by instance recovery after opening

the database.

4. During the transition from NOMOUNT to MOUNT mode, which file or files

are required? (Choose the best answer.)

A. Parameter file

B. Controlfiles

C. Online redo logs

D. Datafiles

E. All of the above

5. You shut down your instance with SHUTDOWN IMMEDIATE. What will

happen on the next startup? (Choose the best answer.)

A. SMON will perform automatic instance recovery.

B. You must perform manual instance recovery.

C. PMON will roll back uncommitted transactions.

D. The database will open without recovery.

6. You have created two databases on your computer and want to use Database

Control to manage them. Which of the following statements are correct?

(Choose two answers.)

A. You cannot use Database Control, because it can manage only one

database per computer.

B. You must use Grid Control, as you have multiple databases on the

computer.

C. You must start one OC4J process and contact it on different ports for each

database.

D. You must start one OC4J instance per database.

E. You must set the ORACLE_SID variable appropriately before starting an

OC4J instance.

7. You issue the command SHUTDOWN, and it seems to hang. What could be

the reason? (Choose the best answer.)

A. You are not connected as SYSDBA or SYSOPER.

B. There are other sessions logged on.

C. You have not connected with operating system or password file

authentication.

D. There are active transactions in the database; when they complete, the

SHUTDOWN will proceed.

8. What action should you take after terminating the instance with SHUTDOWN

ABORT? (Choose the best answer.)

A. Back up the database immediately.

B. Open the database and perform database recovery.

C. Open the database and perform instance recovery.

D. None, but some transactions may be lost.

E. None. Recovery will be automatic.

9. Using Database Control, you stop the Database Listener. Which of the

following statements is true? (Choose the best answer.)

A. Database Control will no longer be able to manage the database.

B. Existing sessions will be terminated.

C. No new sessions can be established.

D. You must restart the listener with the lsnrctl utility.

10. Database Control is a multitier web application. Which tier is responsible for

window management? (Choose the best answer.)

A. The dbconsole middle tier

B. The procedures within the database tier

C. The client browser

D. The OC4J application runtime environment

Answers

1. B. The emctl command is used to start the dbconsole, lsnrctl is used to start

database listeners, and isqlplus from a command line won’t do anything.

2. B, C, and D. Any of these could be a reason. A, E, and F are related to other

processes.

3. A. These are the three file types that make up a database: datafiles, online

redo log files, and the controlfile.

4. B. Remember which files are read at each stage: the parameter file at NOMOUNT,

the online redo log files and the datafiles at OPEN.

5. D. This holds true because IMMEDIATE is a clean shutdown.

6. D and E. D is correct because one OC4J can only support one Database Control

process to manage one database, and E is correct because without this the emctl

utility will not know which OC4J to start.

7. B. This answer is correct because the default shutdown mode is NORMAL,

which will wait for all sessions to log off.

8. E. It is vital to remember this!After a crash, or an abort, recovery is automatic

and unstoppable. No data is ever lost, and you need take no action at all.

9. C. Without a listener there is no way to launch the server process needed for

a session.

10. C. Your local window management is done by your local browser. The other

answers refer to processes that reside on the application server tier (A and D),

or within the database (B).

PART2


Questions
1. Which line of code will cause the following SQL statement to fail? (Choose
the best answer.)
1 CREATE BIGFILE TABLESPACE OCP10gDATA
2 DATAFILE '/oracle/ocp10gdata/ocp10gdata02.dbf'
3 EXTENT MANAGEMENT LOCAL
4 FREELISTS 5
5 NOLOGGING;
A. 1
B. 2
C. 3
D. 4
E. 5
F. The statement will succeed

2. You have mounted the database but did not open it. Which views do you
need to query if you need the locations of all datafiles and the names of
the tablespaces they belong to? (Choose all correct answers.)
A. V$DATAFILE

B. DBA_DATA_FILES
C. V$TABLESPACE
D. DBA_TABLESPACES
E. V$TEMPFILE
F. DBA_TEMP_FILES
G. V$UNDOFILE

3. You attempt to create a tablespace but receive an error that the datafile for the
tablespace cannot be created. The size of the datafile you wanted to create is
3GB, and you specified the SMALLFILE option for the tablespace. You verify
that the operating system directory where the file will reside is owned by the
same user as Oracle and the user has full read/write permissions. You are logged

in to the database as the user SYSTEM, and there is plenty of disk space on the

hard drive. What is the likely cause of the error? (Choose the best answer.)

A. You cannot create a file larger than 2GB in an Oracle database when

specifying SMALLFILE.

B. The operating system cannot create a file larger than 2GB.

C. You must specify the WITH OVERWRITE option for the datafile

specification.

D. You must specify the REUSE option for the datafile specification.

E. You must specify the AUTOEXEND option for the datafile specification.

4. You want to be able to re-create a tablespace quickly in case of failure but do

not have the SQL code to perform the operation. What is the best way to

determine which SQL statement will properly re-create the tablespace with

all options correctly set? (Choose the best answer.)

A. Use the Generate DDL option of iSQL*Plus.

B. Use the Generate DDL option of Enterprise Manager.

C. Use the Create Like option of iSQL*Plus.

D. Use the Create Like option of Enterprise Manager.

E. Query the CODE column of the V$TABLESPACE view.

F. Query the TEXT column of the DBA_TABLESPACES view.

5. Which line of code will cause the following SQL statement to fail? (Choose

the best answer.)

1 CREATE BIGFILE TABLESPACE OCP10gDATA

2 DATAFILE '/oracle/ocp10gdata/ocp10gdata02.dbf'

3 EXTENT MANAGEMENT DICTIONARY

4 FREELISTS 5

5 NOLOGGING;

A. 1

B. 2

C. 3

D. 4

E. 5

F. The statement will succeed

6. You determine that a datafile belonging to your ARCHIVE2002 tablespace

is too large. You want to reduce the size of the datafile so that disk space is

not wasted. This tablespace will not have any data added to it. When you

use Enterprise Manager to reduce the size of the datafile belonging to the

tablespace, you receive an error. What is the most likely cause? (Choose

the best answer.)

A. You cannot reduce the size of datafiles in Oracle.

B. You cannot reduce the size of datafiles using Enterprise Manager.

C. You do not have sufficient permissions to reduce the size of the file.

D. The file does not exist.

E. The file contains data beyond the size you want to reduce the file to.

7. You issue the following command to drop a tablespace and receive an error

indicating that the tablespace cannot be dropped. What is the likely cause?

(Choose the best answer.)

DROP TABLESPACE SYSAUX INCLUDING CONTENTS CASCADE CONSTRAINTS;

A. System tablespaces cannot be dropped.

B. You do not have permissions to drop the SYSAUX tablespace.

C. Objects in other tablespaces depend on objects in the tablespace being

dropped.

D. You cannot drop objects in the tablespace that you did not create.

E. The command should succeed.

8. You want to change extent management on your DATA09 tablespace from local

to dictionary to match the other tablespaces in the DATA01–DATA08 range.

Which method can be used to make this change? (Choose the best answer.)

A. DBMS_SPACE_ADMIN.TABLESPACE_DICTIONARY_MANAGED

B. DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_DICITONARY

C. Enterprise Manager

D. ALTER TABLESPACE DATA09 EXTENT MANAGEMENT DICTIONARY

E. You cannot convert a locally managed tablespace to dictionary

management

9. What permissions are required to create a tablespace? (Choose all correct

answers.)

A. CREATE TABLESPACE

B. MANAGE DATABASE

C. DBA

D. SYSDBA

E. SYSOPER

10. What types of segments can tablespaces in Oracle Database 10g store?

(Choose all correct answers.)

A. Tables

B. Sort segments

C. Redo segments

D. Undo segments

E. DBA segments

F. Clusters

Answers

1. D. When specifying local extent management in the creation of a tablespace,

you cannot specify a dictionary-managed segment storage parameter as well.

FREELISTS can be specified only when extent management is dictionary,

so that part of the CREATE TABLESPACE statement would cause the entire

statement to fail.

2. A, C, and E. Because the database is not yet in an OPEN state, any of the

DBA_ data dictionary views are not yet accessible; they can be queries only

when the database is in an open state. The V$TABLESPACE view will provide

you a list of tablespaces. The V$DATAFILE and V$TEMPFILE views can be

joined to the V$TABLESPACE view using the TS# column to retrieve a list

of all tablespaces and their datafiles. There is no V$UNDOFILE view.

3. D. The most likely reason that you are receiving an error on the creation of

the datafile for the tablespace is that a file with that name already exists. To

correct this, you must specify the REUSE option on the datafile specification

to have Oracle overwrite the existing file (or delete the file manually from the

hard disk). Neither Oracle nor the operating system will prevent a file of 3GB

being created for a SMALLFILE tablespace.

4. B. The Generate DDL option of Enterprise Manager will generate the SQL

code to re-create the selected tablespace. You can then cut and paste this into

a SQL script for later execution. There is no Generate or Create Like option

in iSQL*Plus, and the Create Like option of Enterprise Manager will display

a new page to enter the name of a tablespace with the same parameters as

the one selected. There is no CODE column in the V$TABLESPACE or TEXT

column in DBA_TABLESPACES.

5. C. In order to create a BIGFILE tablespace, you must specify local extent

management and automatic segment space management. You cannot create

a dictionary-managed BIGFILE tablespace.

6. E. Oracle allows you to reduce the size of datafiles, and this can be

accomplished from the command line or using Enterprise Manager. If

you are able to change the size of the file in Enterprise Manager, you have

the necessary permissions to perform the action. The most likely reason

you are unable to perform the action is that more data exists in the datafile

than the size you want to reduce it to. Specify a larger value and try again.

7. A. SYSAUX is a system tablespace and cannot be dropped. The same holds

true for the SYSTEM tablespace.

8. E. Once a tablespace is made locally managed, it is not possible to convert

it to a dictionary-managed tablespace. There is no command or Enterprise

Manager option to perform the change.

9. A and C. You must be granted either the CREATE TABLESPACE system

permission or the DBA role to create a tablespace. There is no MANAGE

DATABASE permission. The SYSOPER and SYSDBA roles provide permissions

for managing the instance and database but do not specifically grant the

holder the permission to create a tablespace.

10. A, B, D, and F. Tablespaces in Oracle can store tables, clusters (both are types

of permanent segments, which also include indexes, partitions, and others),

undo segments, and sort segments (a type of temporary segment). Redo

segments and DBA segments do not exist. Redo is stored in the redo log files.



PART3

Questions


1. You have created a new user called George. You have assigned George the
RESTRICTED_ACCESS user profile. The profile settings have all limits
configured as DEFAULT except for a concurrent sessions limit whose value is
set to 1. George asks you to help with a problem, and when you arrive at his
desk, you notice that he has several SQL*Plus sessions open, all of which are
connected to the instance with his user account. You suspect that your profile
limits are not being enforced. Which of the following should you investigate
to determine the cause of the problem? (Choose two correct answers.)
A. DBA_USER_LIMITS table
B. RESOURCE_LIMIT initialization parameter
C. DBA_PROFILE_LIMITS data dictionary view
D. DBA_PROFILES data dictionary view
E. V$LIMITS dynamic performance view


2. Which line of code will cause the following SQL statement to fail? (Choose
the best answer.)
1 CREATE USER Sam
2 IDENTIFIED EXTERNALLY BY $amP@ssw0rd
3 DEFAULT TABLESPACE Users
4 TEMPORARY TABLESPACE User_Temp
5 QUOTA 2048MB ON APPS_DATA
A. 1
B. 2
C. 3
D. 4
E. 5
F. The statement will succeed

3. You have been asked to provide additional information to your manager on
how system privileges can be assigned and behave. Which of the following
statements about system privileges are true? (Choose all correct answers.)
A. System privileges can be granted to others if you use the WITH ADMIN
OPTION.
B. System privileges can be granted to others if you use the WITH GRANT
OPTION.
C. Only the DBA can grant system privileges, since the DBA owns the database.
D. System privileges can be granted only by the owner of the database.

E. When revoked for a user, system privileges will also be revoked for any
user to whom that revokee granted them.
F. When revoked for a user, system privileges will not be revoked for any user
to whom that revokee granted them.

4. How would a user change his active profile? (Choose the best answer.)
A. ALTER USER SET PROFILE=NewProfile
B. ALTER SYSTEM SET PROFILE=NewProfile
C. ALTER SESSION SET PROFILE=NewProfile
D.
ALTER DATABASE SET PROFILE=NewProfile
E. A user cannot change his active profile

5. If you create a profile and specify limits for only some of the profile settings,
what value will be automatically assigned to any resource limit you do not

include in your CREATE PROFILE statement? (Choose the best answer.)

A. DEFAULT

B. 0

C. UNLIMITED

D. UNKNOWN

E. You must specify a value for all profile limits

6. If you do not specify a TEMPORARY TABLESPACE when creating a new user

account, what will be the value of this parameter when the user is created?

(Choose the best answer.)

A. SYSTEM

B. TEMP

C. NULL

D. Database default temporary tablespace

E. You must specify a value for TEMPORARY TABLESPACE

7. Which of the following commands can a new user called Anthony issue after

successfully connecting to the instance and establishing a user session? (Choose

all correct answers.)

A. ALTER USER Anthony PAS

B. ALTER USER Anthony QUOTA 2M ON SYSTEM;

C. ALTER USER Anthony ACCOUNT LOCK;

D. ALTER USER Anthony TEMPORARY TABLESPACE TEMP;

E. ALTER USER Anthony IDENTIFIED BY NEWPASS;

8. While passing by Benjamin’s desk, you notice that he is using SQL*Plus to

query data in your TempOrders table. You did not grant Benjamin privileges

to issue SELECT statements against your TempOrders table. Why is Benjamin

able to query your table? (Choose all correct answers.)

A. A user to whom you granted the SELECT privilege on the table also granted

it to Benjamin.

B. Benjamin is a DBA and can query any table in the database.

C. You granted Benjamin the UPDATE privilege on the TempOrders table,

which automatically grants the SELECT privilege.

D. Benjamin has been granted the SELECT ANY TABLE privilege by the DBA.

E. Benjamin has been granted the SELECT privilege on your TempOrders

table by a user to whom you granted the SELECT privilege WITH ADMIN

OPTION.

9. Which of the following statements will fail when granting privileges to the

role QueryRole? (Choose the best answer.)

A. GRANT CONNECT TO QueryRole;

B. GRANT CONNECT TO QueryRole WITH ADMIN OPTION;

C. GRANT SELECT ON Orders TO QueryRole;

D. GRANT SELECT ON Orders TO QueryRole WITH GRANT OPTION;

E. GRANT DBA TO QueryRole WITH ADMIN OPTION;

10. When creating a user using Enterprise Manager instead of the CREATE USER

statement, which additional privileges are granted to the user? (Choose the

best answer.)

A. SELECT_CATALOG_ROLE

B. SYSDBA

C. CONNECT

D. RESOURCE

E. DBA

Answers

1. B and D. You can query the DBA_PROFILES data dictionary view to determine

the settings of all profiles in the database, including the one George is supposed

to have assigned to him. The RESOURCE_LIMIT initialization parameter must

be set to TRUE for the concurrent sessions limit to be enforced, so you need

to verify this setting in the running instance and in the SPFILE. DBA_USER_

LIMITS, DBA_PROFILE_LIMITS, and V$LIMITS views do not exist.

2. B. When you create a user, you can specify at most one authentication

method. This SQL statement specified both EXTERNALLY and password

(BY $amP@ssw0rd) authentication at the same time, which would generate

an error when the command was executed.

3. A and F. If you have been granted a system privilege WITH ADMIN OPTION,

you can also grant the same privilege to others and specify the WITH ADMIN

OPTION. The WITH GRANT OPTION applies to object privileges and not

system privileges. If system privileges are revoked for a user to whom they

were granted WITH ADMIN OPTION, they will not be revoked for other

users to whom the revoke granted them; there are no cascading revokes for

system privileges. The DBA is a role and is not the only one that can grant

system privileges, and neither is the user SYS (the database owner), in that

anyone holding the DBA role can grant them, since the DBA role has system

privileges granted to it WITH ADMIN OPTION.

4. E. A user cannot himself change his active profile. Only the DBA can modify

a user’s profile or the profile limits.

5. A. If you do not specify a value for a limit in your profile, it will automatically

be assigned the value of DEFAULT. This means that Oracle will enforce whatever

value exists for the limit in the DEFAULT profile.

6. D. The currently defined database default temporary tablespace will be assigned

to the user if you do not specify a TEMPORARY TABLESPACE for the user. If

no database default temporary tablespace is defined, the user will be assigned

SYSTEM as the temporary tablespace.

7. E. Anthony, like any new user who has been configured with password

authentication, has the ability to change his password at any time. No other

changes to the user account are allowed to be performed by the user himself.

8. B and D. Benjamin can query your TempOrders table only if he is a DBA, who

automatically has the SELECT ANY TABLE privilege, or if Benjamin has been

granted the SELECT ANY TABLE privilege by the DBA. Another user to whom

you only granted the SELECT privilege could not grant it to Benjamin unless

you specified the WITH GRANT OPTION. Granting the UPDATE privilege on

the table does not automatically grant the SELECT privilege.

9. D. It is not possible to grant object privileges to roles WITH GRANT OPTION,

so this statement will fail. All other statements will succeed, assuming you

have the privileges required to perform the operation.

10. C. Using Enterprise Manager to create a user will automatically grant the user

the CONNECT role, which includes the CREATE SESSION privilege, so the

user is able to connect to the instance right away. This role also includes the

privileges to create tables, views, synonyms, sequences, and other database

objects, which may or may not be desired for all users.

What is fixed size and variable size in the show sga output

When we SELECT * from V$SGA (or show sga), we get the values of Fixed Size, Variable Size, Database Buffers and Redo Buffers. For e.g,

SQL>SELECT * FROM v$sga;

NAME                               VALUE
----------------------------- ----------
Fixed Size                       2070096
Variable Size                  234883504
Database Buffers               687865856
Redo Buffers                     6316032

While the "Database Buffers" is a product of db_cache_size and db_block_size or  the "Redo Buffer" is the value of log_buffer in INIT.ora, What is this fixed and variable size? How do wecompute Fixed Size and the Variable Size of a SGA? What are they indicative of? Here is theanswer for that. 

Fixed SGA
The fixed SGA is a component of the SGA that varies in size from platform to platform and
release to release.  It is “compiled” into the database.  The fixed SGA contains a set of
variables that point to the other components of the SGA and variables that contain the
values of various parameters like db_block_size and so on (constants basically for the currently mounted database).The size of the fixed SGA  have  no control and it is generally very small.  Think of this area as a “bootstrap” section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters The fixed size component is out of your control. It varies from release to release and platform to platform. Thus It contains a collection of objects that point to other areas inside the rest of the SGA -- think of it as the "road map" to the rest of the SGA.

Variable portion
The variable portion is called variable because its size (measured in bytes) can be changed.For consideration of the variable size, it is useful to look at v$sgastat 

SQL>compute sum of bytes on pool
SQL>break on pool skip 1
SQL>set pages 200
SQL>set pause on
SQL>select pool, name, bytes  from v$sgastat  order by pool, name;
POOL         NAME                               BYTES
------------ ----------------------------- ----------
java pool    free memory                      4194304
************                               ----------
sum                                           4194304

large pool   PX msg pool                      1076000
             free memory                      3118304
************                               ----------
sum                                           4194304

shared pool  1:kngisga                          32088
             AQ Propagation Scheduling          16000
             ASH buffers                     16252928
             ASM file                           19200
             ASM map operations                  3616
             ASM rollback operations             2592
             AW SGA                                40
             AWR Table Info (KEW layer            872
             Active Session History -            1528
             ArchLog Dest Array                  7656
             Array of cached attr                 400
             Auto tune undo info                   96
             BRANCH TABLE SEGMENTED AR         194016
             Background process state              48
             Banner Storage                      2048
             CCursor                         13817392
             CGS system incarn array            24864
             Checkpoint queue                  821248
             Cleanup state objects                384
             Client ID trace setting h            536
             Client ID trace settings            3872
             Closed Thread SCN Bitvec            8448
             Cursor Stats                     1152280
             DBWR event stats array               192
             DISPATCHER CONFIG ENTRY               88
             DISPATCHER CONFIG TABLE             2048
             DISPATCHERS INFO                    2400
             DML lock                          195872
             ENQUEUE STATS                      11928
             File Space Usage                   11336
             FileIdentificatonBlock            449664
             FileOpenBlock                    1600704
             Global Context Heap descr            400
             HTTP fixed headers                    72
             Heap0: KGL                       4569328
             JSX SGA                              112
             KCB Table Scan                        40
             KCB Table Scan Bitmap                248
             KCB Table Scan Buffer            3981120
             KCB buffer wait statistic           3352
             KCB incremental ckpt entr            512
             KCB private handles                 1800
             KCB undo bitvec                     4096
             KCB where statistics arra          25888
             KCK type array                       936
             KESTB existence bitvec             16384
             KESTB existence bitvec se            128
             
                     ..........................

             session idle latches                1280
             sessions                          932968
             set_descriptor_array               43776
             sga dev dict                          56
             sga listelement                     2048
             sga node map                          16
             sim cache nbufs                      640
             sim cache sizes                      640
             sim kghx free lists                    8
             sim lru segments                    5120
             sim segment hits                    5120
             sim segment num bufs                2560
             sim state object                      40
             sim trace buf                       5144
             sim trace buf context                200
             sim_knlasg                          1280
             simulator hash buckets            263168
             simulator hash latch               20480
             simulator latch/bucket st          11264
             slave class sga anchor                88
             sort segment handle                 3872
             spfile callback table: ks            448
             spfile cleanup structure           16760
             sql area                        57846312
             sql area:KOKA                      44528
             sql area:PLSQL                   1687768
             stat hash values                    1456
             state objects                       5680
             sys event stats                   198688
             sys event stats for Other         198688
             system default language h            568
             table definiti                     27264
             temp lob duration state o           3296
             temporary foreign ref               2848
             temporary table lock                3744
             threshold ale                      12192
             time manager context                  40
             trace buf hdr xtend                35088
             trace buffer                      655360
             trace buffer header array          35088
             trace events array                 68000
             trace_knlasg                         504
             transaction                       417296
             trigger condition node                72
             trigger defini                     33936
             trigger inform                      4224
             trigger source                     30624
             txncallback                        54208
             type object de                    789536
             where to latch num map              7304
             work area tab                     270144
             writes stopped lock conte             24
             writes stopped lock state             24
             x$ksmfs table                         24
             x$rule_set                         17608
             xscalc                              4000
             xslongops                           2592
             xsoqmehift                          2848
             xsoqojhift                          4128
             xsoqophift                          2848
             xsoqsehift                          3104
             xssinfo                             5952
************                               ----------
sum                                         226497208

             buffer_cache                   687865856
             fixed_sga                        2070096
             log_buffer                       6316032
************                               ----------
sum                                         696251984
621 rows selected.
SQL>

So, in general -- the fixed size is computed for you when Oracle is compiled.  You can do nothing to affect it.  The variable size is MOST affected by java_pool_size + large_pool_size + shared_pool_size but other parameters will contribute to it (eg: every control_file will consume 256 bytes of variable size memory.  If you have 4 control files, the SGA will have 1024 bytes set aside for them) in a small way.

Different recover scenario during incomplete database recovery

If you need to recover your database to a point in time by scn, sequence or time, you can use the following query to see the relation between time-scn-sequence, after restoring your database from a proper backup.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YY HH24:MI:SS';

SQL> select NAME, SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE# from v$archived_log where SEQUENCE# > 100;

NAME                                              SEQUENCE# FIRST_TIME        FIRST_CHANGE# NEXT_TIME         NEXT_CHANGE#
------------------------------------------------ ---------- ----------------- ------------- ----------------- ------------
D:\DATA\ARCHIVE\ARC00101_0800466637.001                 101 20-01-13 20:30:06       3982521 21-01-13 04:30:27      4011361
D:\DATA\ARCHIVE\ARC00102_0800466637.001                 102 21-01-13 04:30:27       4011361 21-01-13 19:47:21      4057937
D:\DATA\ARCHIVE\ARC00103_0800466637.001                 103 21-01-13 19:47:21       4057937 22-01-13 00:45:42      4080184
D:\DATA\ARCHIVE\ARC00104_0800466637.001                 104 22-01-13 00:45:42       4080184 22-01-13 09:00:30      4108498
D:\DATA\ARCHIVE\ARC00105_0800466637.001                 105 22-01-13 09:00:30       4108498 22-01-13 22:00:05      4150127
D:\DATA\ARCHIVE\ARC00106_0800466637.001                 106 22-01-13 22:00:05       4150127 23-01-13 03:40:58      4172819
D:\DATA\ARCHIVE\ARC00107_0800466637.001                 107 23-01-13 03:40:58       4172819 23-01-13 15:18:23      4211524
D:\DATA\ARCHIVE\ARC00108_0800466637.001                 108 23-01-13 15:18:23       4211524 23-01-13 15:22:48      4211829

8 rows selected.

You can modify the where clause depending on your needs. 

SEQUENCE# gives the sequence number of the archive log. FIRST_CHANGE# and NEXT_CHANGE# specify the first and last System Change Number (SCN); FIRST_TIME and NEXT_TIME specify the starting and ending time of that archivelog. Regarding to these information you can decide any of the following recover operations:

RMAN> recover database until sequence 107;
RMAN> recover database until SCN 4172819;
RMAN> recover database until time '23-01-13 03:40:58'

Or if you want to manually control recover process with specifying archive logs one by one, you can use "until cancel" clause in SQL. This recovery process continues until you cancel. If your archive logs are not on their default path you can specify the full path of the archive logs in this recovery process.

SQL > recover database until cancel;

How to start/stop Clusterware service in Oracle 10gr2 RAC

Oracle Clusterware services in a Windows RAC environment should be allowed to start automatically, ideally, with the help of the Oracle Process Manager Service (OPMD). If it is not installed already then you can do the following on the concerned node's command prompt - opmd.exe -install .

Oracle Process Manager Daemon (OPMD)—OPMD is registered with the Windows Service Control Manager (WSCM) and the startup of all Oracle Clusterware services is dependent on OPMD. On system startup, and after the default time period of 60 seconds has elapsed, OPMD automatically starts all of the registered Oracle Clusterware services. This startup delay enables other services to start that are outside of the scope of Oracle control, such as storage access, anti-virus, or firewall services. You can set OPMD to start manually. However, this will delay the startup of the rest of the affected Oracle Clusterware components.The opmd.exe allows the creation of a new service called the ‘Oracle Process Manager’. The Oracle Process Manager will control the startup of the clustering services to prevent the 'OracleCSService' from starting up before the rest of the OS services have started.

You can check the oracle background process in windows by,
set line 100

col program format a20

col description format a32

select A.SID, A.STATUS, A.PROGRAM, B.NAME, B.Description

from v$session A, v$bgprocess B

where A.PADDR=B.PADDR

and A.type='BACKGROUND';

Following is an outline of how to start Oracle Clusterware services manually if it should become necessary to do so.

1- Try starting crs with "crsctl start crs" command from $CRS_HOME\bin directory 
OR
2.  Start the following services from Windows 'Services' or by using the Windows command 'net start at the command prompt:
            a.  If OPMD is present, then start the service:  OracleProcessManagerService.  In addititon to OPMD, start the  OracleClusterVolumeService if you are using Oracle Cluster File System (OCFS) in your environment.

             b. If OPMD is not present then start the following services in the order provided:
                    OracleObjectService
                    OracleClusterVolumeService (if using OCFS)
                    OracleCSService
                    OracleEVMService
                    OracleCRService

To stop these services, the same instructions can be followed in reverse.

The reverse command for 'crsctl start crs' is 'crsctl stop crs.'  

However, it is advised that when stopping CRS the resources managed by the OCR are safely brought down before stopping the underlying clusterware services.  

Note that issuing 'crsctl stop crs' without first issuing 'srvctl stop nodeapps -n ' command will cause the ons.exe to continue running and so the ons.exe must be stopped from task manager before clusterware services are considered completely down. 

An example for Incomplete recovery through rman

An incomplete recovery is a type of recovery in which only a part of redo log , archive log data are applied to a restored backup in order to bring it to a specified time, sequence number or SCN prior to the failure. You will lose all works done after that specified time.You must run the database in ARCHIVELOG mode in order to perform an incomplete recovery.

The incomplete recovery consists of four steps:

1. Mount the database.
2. Restore all the datafiles.
3. Recover the database until the desired point.
4. Open the database with a resetlogs.

You can use the incomplete recovery with:

1- User managed backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL CANCEL recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL CHANGE Recovery (before the one specified SCN or system change number).

2- RMAN backups: in this case you have three options: UNTIL TIME Recovery (before a specific time), UNTIL SEQUENCE recovery (before the one specified corrupted, missed archive log or redo log file), UNTIL SCN Recovery (before the one specified SCN or system change number).

Practical example:-

First be sure that the database is in ARCHIVELOG mode. Then we will create a table called test_incomplete_rec to test the incomplete recovery.

C:\Users\Admin>set ORACLE_SID=craze

C:\Users\Admin>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 23 15:13:40 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select archiver from v$instance;

ARCHIVE

-------

STARTED

SQL> set time on

15:13:55 SQL> create table scott.test_incomplete_Reco as select * from dba_Tables;

Table created.

15:14:03 SQL>

Now take a full rman database backup ,

C:\Users\Admin>set ORACLE_SID=craze

C:\Users\Admin>rman target sys/sys

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 23 15:14:26 2013

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

connected to target database: CRAZE (DBID=781012619)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\DATABASE\SNCFCRAZE.ORA'; # default

Note:- Here rman having the default configuration , i changed nothing and the backup set will goes to $ORACLE_HOME/database folder.

RMAN>  backup database;

Starting backup at 23-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=D:\DATA\CRAZE\SYSTEM01.DBF

input datafile fno=00003 name=D:\DATA\CRAZE\SYSAUX01.DBF

input datafile fno=00002 name=D:\DATA\CRAZE\UNDOTBS01.DBF

input datafile fno=00005 name=D:\DATA\CRAZE\EXAMPLE01.DBF

input datafile fno=00004 name=D:\DATA\CRAZE\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 23-JAN-13

channel ORA_DISK_1: finished piece 1 at 23-JAN-13

piece handle=D:\ORACLE\DATABASE\01O055C8_1_1 tag=TAG20130123T151520 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 23-JAN-13

channel ORA_DISK_1: finished piece 1 at 23-JAN-13

piece handle=D:\ORACLE\DATABASE\02O055D2_1_1 tag=TAG20130123T151520 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 23-JAN-13

RMAN>

Now drop the table , 

15:17:01 SQL> drop table scott.test_incomplete_Reco;

Table dropped.

Note:- The table got dropped at 15:17:01 

15:17:15 SQL> select * from scott.test_incomplete_Reco;

select * from scott.test_incomplete_Reco

 *ERROR at line 1:

ORA-00942: table or view does not exist

15:17:22 SQL>

Now perform an incomplete recovery through rman ,

C:\Users\Admin>rman target sys/sys

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 23 15:21:13 2013

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

connected to target database: CRAZE (DBID=781012619)

RMAN> run

2> {

3> shutdown immediate;

4> startup mount;

5> sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ";

6> set until time '2013-01-23 15:16:00';

7> restore database;

8> recover database;

9> alter database open resetlogs;

10> }

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     931135488 bytes

Fixed Size                     2070096 bytes

Variable Size                234883504 bytes

Database Buffers             687865856 bytes

Redo Buffers                   6316032 bytes

sql statement: alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''

executing command: SET until clause

Starting restore at 23-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\DATA\CRAZE\SYSTEM01.DBF

restoring datafile 00002 to D:\DATA\CRAZE\UNDOTBS01.DBF

restoring datafile 00003 to D:\DATA\CRAZE\SYSAUX01.DBF

restoring datafile 00004 to D:\DATA\CRAZE\USERS01.DBF

restoring datafile 00005 to D:\DATA\CRAZE\EXAMPLE01.DBF

channel ORA_DISK_1: reading from backup piece D:\ORACLE\DATABASE\01O055C8_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\ORACLE\DATABASE\01O055C8_1_1 tag=TAG20130123T151520

channel ORA_DISK_1: restore complete, elapsed time: 00:00:26

Finished restore at 23-JAN-13

Starting recover at 23-JAN-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:05

Finished recover at 23-JAN-13

database opened

RMAN>

Database has been opened in resetlogs and we can check the existence of the table, 

15:24:58 SQL> select count(1) from scott.test_incomplete_Reco;

 COUNT(1)

----------

      1594

15:25:31 SQL>

what is granules in sga ?

With the dynamic SGA infrastructure, the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance.With dynamic SGA, the unit of allocation is called a granule. Granules are introduced in oracle 9.0.1 . Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and deallocate space in units of granules . Oracle tracks SGA memory use in integral numbers of granules, by SGA component. For example,

If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA. We can also retrieve information from v$sgainfo from oracle 10.1 onwards

SQL>select COMPONENT,GRANULE_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                      GRANULE_SIZE
------------------------------ ------------
shared pool                        16777216
large pool                         16777216
java pool                          16777216
streams pool                       16777216
DEFAULT buffer cache               16777216
KEEP buffer cache                  16777216
RECYCLE buffer cache               16777216
DEFAULT 2K buffer cache            16777216
DEFAULT 4K buffer cache            16777216
DEFAULT 8K buffer cache            16777216
DEFAULT 16K buffer cache           16777216
DEFAULT 32K buffer cache           16777216
ASM Buffer Cache                   16777216

13 rows selected .

Here granule_size is taken as 16MB.

Adding Granules to Components

A database administrator grows a component's SGA use with ALTER SYSTEM statements to modify the initialization parameter values. Oracle takes the new size, rounds it up to the nearest multiple of 16MB, and adds or takes away granules to meet the target size. Oracle must have enough free granules to satisfy the request. If the current amount of SGA memory is less than SGA_MAX_SIZE, then Oracle can allocate more granules until the SGA size reaches SGA_MAX_SIZE.

The minimum SGA size is three granules, based on:

1. One Granule for Fixed SGA (includes redo buffers)
2. One Granule for Buffer Cache
3. One Granule for the Shared Pool
4. 
   

Following pools can be dynamically resized , 

Stream pools can not dynamically resized  in 10.1