Oracle References

1) Oracle Penetration Tools :  http://www.petefinnigan.com/tools.htm

2) Oracle Journal : http://www.databasejournal.com/article.php/2237601

Regarding 'sysdate' !

Sysdate with Time interval :

SELECT to_char(entry_date -8/24, 'DD-MON-YYYY HH12:MI:SSam'), OLD_BIN_ID old_bin, NEW_BIN_ID NEW_BIN, ISBN ASIN, QUANTITY 
FROM BINEDIT_ENTRIES
WHERE ENTRY_DATE BETWEEN trunc(SYSDATE) -1 +4/24 AND trunc(SYSDATE) -1 +16/24
--where entry_date BETWEEN trunc(sysdate)-1 '00:00:00' AND trunc(sysdate)-1 '00:00:00.000'
AND substr(old_bin_id,1,2) = 'SC'
AND substr(new_bin_id,1,2) = 'vt'
GROUP BY ENTRY_DATE, OLD_BIN_ID, NEW_BIN_ID, ISBN, Quantity
ORDER BY QUANTITY DESC;

Example :

select a.br_code,b.branch_nm,a.err_dt,a.err_code,a.err_msg from error_log a , BRANCH_HOME_BANK b where a.err_dt --like 'sysdate-1'
BETWEEN trunc(SYSDATE) -2 +20/24 AND trunc(SYSDATE) -2 +24/24
--BETWEEN TO_DATE('12/02/2014 20:00:00', 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('12/02/2014 23:59:00', 'MM/DD/YYYY HH24:MI:SS')
AND ERR_CODE like '%-20000%'  and br_code not in ('0100','0116','0117','0500')
order by a.err_dt desc  

Here , 20/24 means 8 PM

Sysdate : Todays date

sysdate -1 : 1 Day before 

select br_code,err_dt,err_code,err_msg from error_log  where err_dt --like 'sysdate-1'
BETWEEN trunc (SYSDATE) +20/24 AND trunc(SYSDATE)  +24/24
--BETWEEN TO_DATE('12/02/2014 20:00:00', 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('12/02/2014 23:59:00', 'MM/DD/YYYY HH24:MI:SS')
AND ERR_CODE like '%-20000%'  and br_code not in ('0100','0116','0117','0500')
order by err_dt;

ASM opeartions !

--1) we have 3 asm diskgroup : DATA,RECO ,REDO **GRP1 is DATA , GRP2 is REDO , GRP3 is RECO

select group_number,name,state from v$asm_diskgroup;

--2) To see the total disk group size / free size
select name, group_number, state, to_char(total_mb,'99,999,999') "TOTAL_MB",
to_char(free_mb,'99,999,999') "FREE_MB" from v$asm_diskgroup;

--3) How many disks in a diskgroup :

select * from v$asm_disk order by group_number --DATA has 20 disk , REDO has 4 disks , RECO has 20 disks

select group_number,name,path,DISK_NUMBER,OS_mb,total_mb,free_mb,state  from v$asm_disk order by group_number;


--4) To see any error on ASM :
select group_number, operation, state, error_code from v$asm_operation;

--5) To see FRA
select * from v$recovery_file_dest;

select * from v$flash_recovery_area_usage;


--6) To resize ASM DISK


 ALTER DISKGROUP 'DISKGROUP_NAME'  RESIZE DISK 'Name of DISK' SIZE 200G; //disk name got from v$asm_disk

For example ,

 SQL> ALTER DISKGROUP DATA  RESIZE DISK HDD_E0_S09_376018964P1 SIZE 200G;

--ALTER DISKGROUP DATA resize all;


####ASM Add Disk


--ALTER DISKGROUP DATA add disk '/dev/oracleasm/disks/DISK5' name DISK5;

select name,path from v$asm_disk;


DROP : ALTER DISKGROUP DATA drop disk '/dev/oracleasm/disks/DISK5'

UNDROP : ALTER DISKGROUP DATA undrop disks;

RESIZE : ALTER DISKGROUP DATA resize DISK%5 FILE_DISKB1;

MRP on Oracle Dataguard !

1) How we can understand MRP is running or not ?

ps -aef | grep mpr

After running the below command ,
'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;'

we can check the status using below command :
select thread#,sequence#,process,status from v$managed_standby;

It shows the extra processes called 'MRP' .


2) Installing Standby Database Data Guard Managed Recovery

Creating archive destination LOG_ARCHIVE_DEST_2: 'stdby1'
ARC1: Complete FAL archive (thread 1 sequence 224 destination stdby1)

At this point, there will be archives logs on the standby site, but they will not be applied to the standby database until the primary database is put in managed recovery mode. This can be achieved by issuing the following statement:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

There are few other options for the RECOVER MANAGED Oracle instance statement, but at this information will be limited to the options that are relevant for putting the database in the managed recovery mode its creation.

The managed recovery can be started as a foreground or background process. The“disconnect from session” option allows the background process to do the managed recovery. It will start a MRP (managed recovery process) on the standby site, which is responsible for applying the archived redo logs onto the standby database. If this keyword is omitted, it will be necessary to keep a session open for the recovery.

This does not start MRP (managed recovery process) on the standby site. In almost all scenarios, the DISCONNECT FROM SESSION option will be used to have a background process take care of recovery. This process was introduced in Oracle9i in an effort to ease the administration of the standby database.

When the MRP process starts to apply log files, it finds the log files fromstandby_archive_dest directory and created the log file name using thelog_archive_format parameter. If it detects a missing log file from this directory, it will send a request to the primary database to transfer the file again. The name of the primary database is found from the fal_server parameter, and it sends the fal_client parameter as the destination service where the files will be resent.

The following extract from the alert log file of the Oracle instance shows the Managed Recovery Process requested the archived log files to be sent to the resolve gap:

Completed: alter database recover managed Oracle instance di
Tue Sep 23 10:35:46 2003
Fetching gap sequence for thread 1, gap sequence 231-231
Trying FAL server: primary1
Media Recovery Log /oracle/appsdb/arch/stdby.1_231.dbf
Media Recovery Log /oracle/appsdb/arch/stdby.1_232.dbf

he general administration tasks include controlling various modes of recovery, starting up, and shutting down the standby database.

Controlling the Managed Recovery Process

To start the database in managed recovery mode; mount it first, and then start the managed recovery. This is a three-step process involving:

•  Starting up the database using:

STARTUP NOMOUNT;

•  Mounting the Oracle instance using:

ALTER DATABASE MOUNT STANDBY DATABASE;

•  Starting the recovery process using:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

Controlling the Managed Recovery Process
These statements should be executed by a user having SYSDBA privileges.

The RECOVER MANAGED Oracle instance statement has several options for controlling the recovery process. Either a foreground or a background recovery process can be started. The above-mentioned statement will initiate a foreground recovery process, and a session open will need to remain open. If recovery is started in a foreground session, use the TIMEOUT clause to stop recovery after the time specified by TIMEOUT.

For example:

ALTER DATABASE RECOVER MANAGED Oracle instance TIMEOUT 5;

This clause will stop the recovery process, if a new archive log does not arrive within five minutes.

A better option is to run the recovery process in background. The DISCONNECT FROM SESSION clause of the RECOVER MANAGED Oracle instance starts recovery in a background session. A complete statement to start recovery in background is:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION;

If the database is DML intensive, a multiple parallel recovery process can be started on the standby site to spread the load during the log apply operation.  The parallel clause is a request to start five parallel processes.

Oracle may decide to choose to start a different number of parallel processes depending on the resources available on the host machine. The following statement will start five parallel recovery processes to apply archived redo logs on the standby site:

ALTER DATABASE RECOVER MANAGED Oracle instance DISCONNECT FROM SESSION PARALLEL 5;

To stop the managed recovery, the Cancel command can be issued on the standby database. The complete statement is:

ALTER DATABASE RECOVER MANAGED Oracle instance CANCEL;

This statement will stop the MRP. There are several other options for the RECOVER MANAGED Oracle instance statement, which can be found in the Oracle documentation.

In ODA, archive not applying to DR

1) Alert log :

ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION...
Wed Nov 26 09:47:38 2014
FAL[client]: Failed to request gap sequence
GAP - thread 2 sequence 2422-2422
DBID 1036100936 branch 851819650
FAL[client]: All defined FAL servers have been attempted.

------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that's sufficiently large enough to maintain adequate log switch information to resolve
archivelog gaps.

solution :

The message about the CONTROLFILE_RECORD_KEEP_TIME parameter is often a red herring. The real culprit is:

GAP - thread 1 sequence 3126-3224

You are missing some log files. Find the log files from thread 1 for those sequences and manually transmit them to your standby database. Then register each one with the following:

ALTER DATABASE REGISTER LOGFILE '/dir/filename';

Once you have registered all logfiles with the standby, life should resume as normal.

HTH,
Brian

source : https://community.oracle.com/thread/2235823?tstart=0urce 

2) Error on Alert log :

Tue Nov 25 16:40:16 2014

WARN: ARC2: Terminating pid 5775 hung on an I/O operation

krsv_proc_kill: Killing 1 processes (Process by index)

ARC2: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_2

Tue Nov 25 16:50:12 2014

source : LGWR: Error 16198 due to hung I/O operation (Doc ID 1512424.1)

Cause :

This is an I/O problem. LGWR could not write to log_archive_dest_1 as the I/O operation is hung.

As can be seen from the alert.log, this is a hung I/O:

LGWR: Error 16198 due to hung I/O operation to LOG_ARCHIVE_DEST_1
LGWR: Detected ARCH process failure

From the RDA we could see that /rman3 is NFS mounted from the host gpttrtnlxmgmt

Disks Mounts:
gpttrtnlxmgmt:/rman3 4297753856    123008 4251116224   1% /rman3

From the operating system messages log we can see that there was an error connecting to this host:

RPC: error 5 connecting to server gpttrtnlxtest
nfs_statfs: statfs error = 5
...
RPC: error 512 connecting to server gpttrtnlxmgmt

Solution :

    

The NFS mount point is hung and cannot be used as a log archive destination.
As archivelogs are important to the database, the database will hang if there are any issues with the archivelog destination.

So ensure that the NFS mount point is working properly - refer the issue to your system administrator.
Otherwise use a different location for the archivelog destination where there are no known I/O issues.

--
2) Error on Alert log :

Tue Nov 25 21:20:27 2014

WARN: ARC2: Terminating pid 3096 hung on an I/O operation

krsv_proc_kill: Killing 1 processes (Process by index)

ARC2: Detected ARCH process failure

ARC2: STARTING ARCH PROCESSES

Solution :

krsv_proc_kill: Killing processes (Process by index) message in the alert log

Some times we'll get the following warning messages in the alert log of the database even continuously.

Alert log message:
==============
WARN: ARC2: Terminating ARCH (pid 10111) hung on a disk operation
Mon May 05 22:13:32 2014
krsv_proc_kill: Killing 266287972353 processes (Process by index)
Mon May 05 22:20:32 2014
ARC2: Detected ARCH process failure
ARC2: STARTING ARCH PROCESSES
Mon May 05 22:20:32 2014
ARC1 started with pid=21, OS id=12104
ARC1: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
Mon May 05 22:20:41 2014
Deleted Oracle managed file /data/oracle/flash_recovery_area/ORCL/archivelog/2014_05_05/o1_mf_1_29497_9phnw343_.arc          

Do not panic when you see the above message. As long as there are no other side-effects this error can be ignored. It occurs when disk IO & CPU load are high in the server. Normally we are getting this warning messages when ever backup runs since disk io will be very high during the backup.

During high disk IO & cpu load, db is also trying to archiving the file on disk, But unable to succeed the process due to above said reasons. So it is deleting the incomplete archive log from the disk automatically. 

source : http://oradba11g.blogspot.com/2014/05/krsvprockill-killing-processes-process.html



Note : In My environment , I got the above errors in the alert log file during slow connectivity between DC and DR .

Make SPFILE to an ASM Environment

Objective :


During Startup of the database and for memory parameter misconfigured I got the below error :

[oracle@ol5-112-rac2 ~]$ srvctl  start database -d isldb
PRCR-1079 : Failed to start resource ora.isldb.db
ORA-27102: out of memory
CRS-2674: Start of 'ora.isldb.db' on 'ol5-112-rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.isldb.db' on that would satisfy its placement policy
ORA-27102: out of memory
CRS-2674: Start of 'ora.isldb.db' on 'ol5-112-rac2' failed

Solution :

>  Delete all the default 'spfile'  from default  '/dbs'  location .

  

  1) Remove the present 'spfile'  from the ASM location .

 ASMCMD> pwd
+DATA/ISLDB/PARAMETERFILE

ASMCMD> ls
spfile.339.864311263
spfile.641.859222679
spfile.657.864311045
spfile.681.864305249

ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> ls
ASMCMD-08002: entry 'PARAMETERFILE' does not exist in directory '+DATA/ISLDB/'

2) 

Now, create spfile on ASM location :

SQL> startup pfile=initISL.ora

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                  2213896 bytes

Variable Size            1040189432 bytes

Database Buffers          603979776 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

SQL> create spfile='+DATA' from pfile='/home/oracle/initISL.ora';

File created.

ASMCMD> pwd

+DATA/ISLDB/PARAMETERFILE

ASMCMD> ls

spfile.339.864311475

ASMCMD> 

3) Start the database but got error again  :

  SQL> startup

ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/isldb/spfileisldb.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/isldb/spfileisldb.ora

ORA-15056: additional error message

ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/isldb/spfileisldb.ora

ORA-17503: ksfdopn:2 Failed to open file +DATA/isldb/spfileisldb.ora

ORA-15173: entry 'spfileisldb.ora' does not exist in directory 'isldb'

ORA-06512: at line 4

That means .. it searches the spfile to another location rather than '+DATA/ISLDB/PARAMETERFILE' location .

Then run the below command :

 ASMCMD>  mkalias +DATA/ISLDB/PARAMETERFILE/spfile.339.864311831  +DATA/isldb/spfileisldb.ora

Thats ok ...

Now startup the database .

For the Second database . simply restart the database .. it got the 'default ASM spfile '  automatically .

 site

  

Oracle Database Proxy Authenticated Connections

How proxy authenication works

source

Introduced with Oracle Database 10g, the basic premise of proxy authentication is that a user with the required permission can connect to the Oracle database using their own credentials, but proxy into another user in the database.  To put it more plainly: connect as USER_A but using the password of USER_B !

The proxy permission is granted through the “CONNECT THROUGH” privilege.  Interestingly, it is granted through an ALTER USER command as really it’s an “authorization” and property of the user and not truly a privilege like the traditional privileges we’re used to:

SQL> connect / as sysdba
Connected.
SQL> alter user USER_A grant connect through USER_B;

User altered.

SQL>

Now USER_B who may not know the password for USER_A can connect as USER_A by specifying the proxy account in square brackets in the connection string:

SQL> connect USER_B[USER_A]/passw0rd
Connected.
SQL> show user
USER is "USER_A"
SQL>

The password specified was the one for USER_B, not USER_A.  Hence the credentials for USER_B were used but the end result is that the session is connected as USER_A!

Specifically when a proxy authenticated connection is made the USERENV namespace parameters are updated as follows:

• The “SESSION_USER” becomes USER_A
• The “SESSION_SCHEMA” also becomes USER_A
• The “PROXY_USER” remains USER_B who initiated the connection and who’s credentials were used

Since the syscontext(‘USERENV’,’PROXY_USER’) remains unchanged, the connection is properly audited and information on who made the initial connection can still recorded in audit records.  However for all other purposes, USER_B has effectively connected to the database as USER_A without having to know USER_A’s password.

So back to the original question, a possible approach to their problem would be to create a second USER_B that has permission to proxy into their application user account APP_USER.  Then they could gradually roll out the credential change to use the new USER_B and proxy into APP_USER to all of their app servers.  Once all app servers have been updated it would then be safe to change the password on the base application account APP_USER.

A similar feature is the ability to change the current session’s schema. For example as USER_B issuing:

alter session set current_schema = USER_A;

This is a very quick and simple approach, but isn’t quite the same. Doing this only changes the “CURRENT_SCHEMA” which is the currently active default schema. Hence any queries issued without specifying the schema name will default to “CURRENT_SCHEMA”. But there are many cases when actually connecting to another user is required. For example, if the DBA needs to drop and re-create a database link then the “current_schema” approach will not suffice.  But the proxy authenticated connection alternative will work perfectly.

Another case where the “current_schema” approach may be an issue is if the application is user aware.  What I mean by this is that possibly the application has some logic such as “if user = USER_A then do suff“.  If you connect as USER_B and simply changes the current schema then the boolean logic of this condition will evaluate to FALSE.  However if you use a proxy authenticated connection user USER_A, the condition will evaluate to TRUE.

Previously if the DBA needed to connect to the database as a specific user (maybe to re-create a DB link for example) they might employ the old trick of temporarily changing the user’s password, quickly connecting, and then quickly changing it back using the extracted/saved password hash.  However there are numerous serious problems with this approach:

1. The schema may be locked
2. The password may be controlled by a PROFILE that may also need to be adjusted.
3. Account intrusion detection tools may detect the connection.
4. The connection may not be properly audited via Oracle or external auditing tools.
5. The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!

Hence that approach should never be used. The proxy authenticated connection alternative doesn’t have any of those issues and is perfectly safe.

A simple example

Putting it all together into a small example to show how the userenv properties are affected:

SQL> alter user USER_A grant connect through USER_B;

User altered.

SQL> connect USER_B[USER_A]/passw0rd
Connected.
SQL> alter session set current_schema = SCOTT;

Session altered.

SQL> select sys_context('USERENV','SESSION_USER') as session_user,
  2  sys_context('USERENV','SESSION_SCHEMA') as session_schema,
  3  sys_context('USERENV','CURRENT_SCHEMA') as current_schema,
  4  sys_context('USERENV','PROXY_USER') as proxy_id,
  5  user
  6  from dual;

SESSION_USER   SESSION_SCHEMA CURRENT_SCHEMA PROXY_ID       USER
-------------- -------------- -------------- -------------- ------------
USER_A         SCOTT          SCOTT          USER_B         USER_A

SQL>

As can be seen above, for all intensive purposes the connection has been made to USER_A but using the credentials of USER_B.  USER_A’s password did not need to be known nor was the USER_A account affected or adjusted in any way.

FAQs

What if USER_A’s password is locked or expired?

The answer is that the connection will still report the same error as it would have if a direct connection to USER_A was made:

SQL> connect / as sysdba
Connected.
SQL> alter user USER_A account lock;

User altered.

SQL> connect USER_B[USER_A]/passw0rd
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
SQL>

Can this be used with other tools such as Data Pump?

(Importing as the actual user instead of a DBA user was necessary with Oracle 10g under specific circumstances such as importing JOBs and REFRESH GROUPS).  The answer is yes it works with Data Pump and other similar tools:

$ impdp dumpfile=temp.dmp nologfile=y include=JOB

Import: Release 11.2.0.4.0 - Production on Wed Oct 15 19:10:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: USER_B[USER_A]/passw0rd

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "USER_A"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER_A"."SYS_IMPORT_FULL_01":  USER_B[USER_A]/******** dumpfile=temp.dmp nologfile=y include=JOB
Processing object type SCHEMA_EXPORT/JOB
Job "USER_A"."SYS_IMPORT_FULL_01" successfully completed at Wed Oct 15 19:10:22 2014 elapsed 0 00:00:01

$

Notice that the Data Pump master table is created in the USER_A schema even though we connected using the USER_B credentials.

Is proxy authentication supported by JDBC/JDBC thin driver?

Yes, it works through almost any OCI connection including JDBC connections.

What about Oracle Wallets?

The answer again is yes, they can support it too! See below for an example using an Oracle Wallet:

$ mkstore -wrl "/u01/app/oracle/wallet" -createCredential ORCL USER_A passw0rd
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1
$ mkstore -wrl "/u01/app/oracle/wallet" -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: ORCL USER_A
$

$ sqlplus /@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:04 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "USER_A"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus [app_user]/@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 15 13:45:14 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "APP_USER"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

Reporting and Revoking

Finally, how can we report on what proxy authentication authorizations we’ve granted as we need to clean them up (revoke them)?  Or perhaps we just need to report on or audit what’s out there?  Fortunately, it’s as simple as querying a catalog view to see what’s been set and we can remove/revoke through another simple ALTER USER command:

SQL> select * from PROXY_USERS;

PROXY        CLIENT       AUT FLAGS
------------ ------------ --- -----------------------------------
USER_B       USER_A       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

SQL> alter user USER_A revoke connect through USER_B;

User altered.

SQL> select * from PROXY_USERS;

no rows selected

SQL>

Conclusion

Since the introduction of the GRANT ANY OBJECT privilege with Oracle9i, the number of times that the DBA needs to actually connect as other users has been reduced.  However, there still are some distinct situations such as those mentioned in the examples above when the connection as another user may be absolutely necessary.

Thanks to the proxy authenticated connection capabilities introduced with Oracle Database 10g, connecting as another user when you don’t know the other account’s password has become a breeze.  And even if you do know the password, connecting through proxy authentication can still add value with the additional audit information.

Have any other situations where connecting as another user is absolutely necessary? Share them in the comments section below.

References

http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authentication.htm
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm