ORA-28040: No matching authentication protocol

When trying to connect to 12c database with client or database which is lesser than 11g , it rejects the connection with below error.


An unknown error has occurred. The provided message is "Could not get JDBC Connection; nested exception is java.sql.SQLException: ORA-28040: No matching authentication protocol ".

To fix such error , we just need to add the following the parameter in sqlnet.ora file in $ORACLE_HOME/network/admin and restart the listener , that's it , all good :)

SQLNET.ALLOWED_LOGON_VERSION=8

ORA-28031 error

Note Id "What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded? (Doc ID 778785.1)" explains this well

#1. One must check first the number of roles granted to a user or role. It is useful to know that, with the sole exception of IDENTIFIED GLOBALLY roles, as documented, the roles created by a user are automatically granted to that user.
Check the number of roles that have been granted to all users and roles, using the following query:

This query should indicate the number of roles that have been granted to all users, including roles that have been granted via roles.

select "Grantee", count(*) "Role Number" from

(

select distinct connect_by_root grantee "Grantee", granted_role

from dba_role_privs

connect by prior granted_role=grantee

)

group by "Grantee"

order by "Grantee","Role Number"

/ 

Equally helpful could be the following query, returning the roles that have been granted to a specific user:

select distinct connect_by_root grantee,granted_role

from dba_role_privs

connect by prior granted_role=grantee

start with grantee='';

Keeping in mind, as seen at:
MAX_ENABLED_ROLES
documentation page, that the number of enabled roles for a user cannot be larger than 148, if any of the above users has more than 148 roles enabled, some policy should be envisaged so that the roles are not be enabled all at once.

Of course, the easiest thing that can be done is to reset the number of default roles for the user, as indicated at the ALTER USER documentation page:

alter user default roles ;

The list of roles can be: an explicit list of roles, all roles or all roles except .

Sometimes, it happens that sessions running as SYS (this is mostly common in job sessions) fail with the ORA-28031 and the user SYS has indeed more than 148 roles. However, normal sessions don't have any problems connecting and running commands in the database.
The SYS user does not require all the roles that have been defined in the database. Simply set DBA as the only default role for sys:

alter user SYS default roles DBA;

and this should eliminate the error when running statistics taking jobs or advisories.

The most difficult scenario to address situation is when the application enables or disables roles and, for some reasons, the number of jobs defined for a user surpasses 148. In this situation, the application must be checked and commands like:

set roles all;
or
execute dbms_session.set_role('ALL');

should be replaced with commands enabling only specific roles, which are, of course, less than 148.

TO FIND ORACLE HOME WITH FROM SQL PROMPT

1ST WAY

SQL> select file_spec from dba_libraries where library_name='ORDIMLIBS';

FILE_SPEC
------------------------------------------------------------
/u01/app/oracle/product/1110/dbhome/lib/libordim11.so



2ND WAY

SQL> host echo $ORACLE_HOME
/opt/oracle/product/10.2.0/db_1

how to tail alert log in adrci

==> adrci

ADRCI: Release 11.2.0.3.0 - Production on Tue Jun 24 20:28:32 2014

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

ADR base = "/opt/app/oracle"
adrci>  show homes
ADR Homes:
diag/asm/+asm/+ASM
diag/asmtool/user_oracle/host_2740023319_80
diag/diagtool/user_oracle/host_2740023319_11
diag/tnslsnr/server/listener
diag/rdbms/dbname/DBNAME
diag/clients/user_oracle/host_2740023319_11
adrci> set homes diag/rdbms/dbname/DBNAME
adrci> show alert -tail -f
2014-06-24 12:08:41.139000 +00:00

sql for sizes

1. Top 10 segments

SELECT * FROM
(
select 
    SEGMENT_NAME, 
    SEGMENT_TYPE, 
    BYTES/1024/1024/1024 GB, 
    TABLESPACE_NAME 
from 
    dba_segments
order by 3 desc  
) WHERE
ROWNUM >= 10;

How to install and execute latest Exacheck

Step1 : Download the latest exacheck from this node "Oracle Exadata Database Machine exachk or HealthCheck (Doc ID 1070954.1)"


Step 2: Create the directory on the server which you want to use as Exacheck directory and copy the downloaded file there. 
 i use naming convention like exachk_224, exchk_225_beta


Step 3: Unzip the bundle

==> unzip exachk_225_BETA_bundle.zip

Archive:  exachk_225_BETA_bundle.zip

  inflating: exachk.zip

  inflating: ExachkBestPracticeChecks_v225_BETA.pdf

  inflating: ExachkFeatureFixHistory_v225_BETA.pdf

  inflating: ExachkUserGuide_v225_BETA.pdf

  inflating: exachk_dbm_maa_public.html

  inflating: exachk_dbm_post-upgrade_public.html

  inflating: exachk_dbm_pre-upgrade_public.html

  inflating: exachk_diff_sample_report_public.html

  inflating: exachk_profile_sysadmin_sample_public.html

  inflating: md5sums.txt

Step 4:Unzip the exachk.zip

==> unzip exachk.zip
Archive:  exachk.zip
   creating: .cgrep/
  inflating: .cgrep/auto_upgrade_check.pl
  inflating: .cgrep/ggdiscovery.sh
  inflating: .cgrep/create_version.pl
  inflating: .cgrep/scgrepx86
  inflating: .cgrep/scgrep
  inflating: .cgrep/checkDiskFGMapping.sh
  inflating: .cgrep/exalogic_zfs_checks.aksh
  inflating: .cgrep/auto_upgrade.pl
  inflating: .cgrep/asrexacheck
  inflating: .cgrep/utlusts.sql
  inflating: .cgrep/utlu112i.sql
  inflating: .cgrep/oracle-upstarttmpl.conf
  inflating: .cgrep/lcgrep3
  inflating: .cgrep/init.tmpl
   creating: .cgrep/profiles/
  inflating: .cgrep/profiles/F6AFECA37F177C3FE04313C0E50A56BF.prf
  inflating: .cgrep/profiles/D49B218473787400E0431EC0E50A0BB9.prf
  inflating: .cgrep/profiles/DF65D0F7FB6F1014E04312C0E50A7808.prf
  inflating: .cgrep/profiles/D49C0AB26A6D45A8E0431EC0E50ADE06.prf
  inflating: .cgrep/profiles/D49C4F9F48735396E0431EC0E50A9A0B.prf
  inflating: .cgrep/profiles/DFE9C207A8F2428CE04313C0E50A6B0A.prf
  inflating: .cgrep/profiles/D49BDC2EC9E624AEE0431EC0E50A3E12.prf
  inflating: .cgrep/profiles/DF65D6117CB41054E04312C0E50A69D1.prf
  inflating: .cgrep/profiles/D8367AD6754763FEE04312C0E50A6FCB.prf
  inflating: .cgrep/profiles/DA94919CD0DE0913E04312C0E50A7996.prf
  inflating: .cgrep/profiles/D49C0FBF8FBF4B1AE0431EC0E50A0F24.prf
 extracting: .cgrep/profiles/F13E11974A282AB3E04312C0E50ABCBF.prf
  inflating: .cgrep/profiles/EF6C016813C51366E04313C0E50AE11F.prf
  inflating: .cgrep/profiles/F32F44CE0BCD662FE04312C0E50AB058.prf
  inflating: .cgrep/profiles/E2E972DDE1E14493E04312C0E50A1AB1.prf
  inflating: .cgrep/profiles/D49AD88F8EE75CD8E0431EC0E50A0BC3.prf
  inflating: .cgrep/profiles/E8DF76E07DD82E0DE04313C0E50AA55D.prf
  inflating: .cgrep/profiles/EA5EE324E7E05128E04313C0E50A4B2A.prf
  inflating: .cgrep/profiles/E1BF012E8F210839E04313C0E50A7B68.prf
  inflating: .cgrep/profiles/D462A6F7E9C340FDE0431EC0E50ABE12.prf
  inflating: .cgrep/pxhcdr.sql
  inflating: .cgrep/diff_collections.pl
  inflating: .cgrep/utluppkg.sql
  inflating: .cgrep/versions.dat
  inflating: .cgrep/profiles.dat
  inflating: .cgrep/rack_comparison.py
  inflating: .cgrep/raw_data_browser.pl
  inflating: .cgrep/lcgrep6
  inflating: .cgrep/check_reblance_free_space.sql
  inflating: .cgrep/psqlplus
  inflating: .cgrep/show_file_in_html.pl
  inflating: .cgrep/reset_crshome.pl
  inflating: .cgrep/CollectionManager_App.sql
  inflating: .cgrep/preupgrd.sql
  inflating: .cgrep/ogghc_11203.sql
  inflating: .cgrep/merge_collections.pl
  inflating: .cgrep/scnhealthcheck.sql
  inflating: .cgrep/ogghc_11204.sql
  inflating: .cgrep/lcgrep5
  inflating: .cgrep/ogghc_12101.sql
  inflating: exachk
  inflating: UserGuide.txt
  inflating: readme.txt
  inflating: CollectionManager_App.sql
  inflating: rules.dat

  inflating: collections.dat


Step 5: Execute the script ./exachk


==> ./exachk

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0/grid?[y/n][y]y


Checking ssh user equivalency settings on all nodes in cluster

It will check all the database running and and then ask for ROOT  password for compute node and cell node

once it finishes execution

It comes back with detailed HTML report and zipped files. 

how to install oracle apex

SQL> @apxldimg.sql /opt/app/apex

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

. Loading images directory: /opt/app/apex/apex/images

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.


Directory dropped.

timing for: Load Images
Elapsed: 00:00:31.67

VI , sed and Awk command for DBA

1.Remove lines with SQL> from a file

sed --in-place '/SQL> select/d' test.sql

for f in *.sql;
do
sed --in-place '/SQL>/d' "$f";
done

2.remove empty lines from a files

sed -i '/^\s*$/d' file 

Export Import Script Reference

expdp directory=EXPORTDP dumpfile=tables_periodcode_hkgrf3_exp.dmp logfile=tables_periodcode_dbas_exp.log tables=RRS.RA_ASI_DISTANCE,RRS.RA_ASTI_DETAIL,RRS.RA_AUDITSTORE,RRS.RA_BANDEDPACKITEM,RRS.RA_CONVERT_VOLUME,RRS.RA_INJCTSCDAT,RRS.RA_MPROJ_STATUS,RRS.RA_QC_PANEL,RRS.RA_SCHEDULE,RRS.RA_SG_MPIBD_STATUS,RRS.RA_SG_MP_STATUS,RRS.RA_SG_STORE_STATUS,RRS.RA_SHOP_TREND,RRS.RA_SQSTATUS,RRS.RA_STORE_ACV,RRS.RA_SUSTATUS,RRS.RA_SW_GENFACTOR_STATUS2 query=\"where periodcode in \(\'20101406\',\'20101407\',\'20101408\',\'20101409\',\'20101410\',\'20101411\',\'20101412\',\'20111401\',\'20111402\',\'20111403\',\'20111404\',\'20111405\',\'20111406\',\'20111407\',\'20111408\',\'20111409\',\'20111410\',\'20111411\',\'20111412\',\'20111601\',\'20111602\',\'20111603\',\'20111604\',\'20111605\',\'20111606\',\'20121401\',\'20121402\',\'20121403\',\'20121404\',\'20121405\',\'20121406\',\'20121407\',\'20121408\',\'20121409\',\'20121410\',\'20121411\',\'20121412\',\'20121606\',\'20131401\',\'20131402\',\'20131403\',\'20131404\',\'20131405\'\)\"

Export Partitions like (this works only in 11g)

expdp directory=EXPDP1 dumpfile=CA_RAW_2013.dmp logfile=CA_RAW_2013.log TABLES=RRS.CA_RAW:P2013%

select 'exec rrs.p_create_partition.create_partition('||replace (substr(partition_name,2,instr(partition_name,'_',-1)-2),'2011','2012')||','||substr(partition_name,instr(partition_name,'_',-1)+1)||','||''''||table_name||''''||');' from dba_tab_partitions where table_owner='RRS' and table_name='SC_RAW' and partition_name like '%20111412%' and partition_name like '%\_%' ESCAPE '\';

PLSQL command reference

To Make all Tablespace READ ONLY

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP')) loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" read only';
  end loop;
end;
/

Make all tablespace NOLOGGING

begin
  for s in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02','TEMP','TEMP_RRS') and LOGGING='LOGGING') loop
    execute immediate 'alter tablespace  "'||s.tablespace_name||'" NOLOGGING';
  end loop;
end;
/

To make all file autoextend on

begin
  for s in (select file_id from dba_data_files where autoextensible='NO') loop
    execute immediate 'alter database datafile '||s.file_id||' autoextend on';
  end loop;
end;
/

OS watcher Black box

OSWatcher (Includes: [Video]) (Doc ID 301137.1)





==> ./startOSWbb.sh 120 72

==> Setting the archive log directory to/opt/app/oswbb/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
NETSTAT found on your system.
TOP found on your system.

Testing for discovery of OS CPU COUNT
oswbb is looking for the CPU COUNT on your system
CPU COUNT will be used by oswbba to automatically look for cpu problems

CPU COUNT found on your system.
CPU COUNT = 32

Discovery completed.

Starting OSWatcher Black Box v7.1  on Tue Apr 29 09:44:48 EDT 2014
With SnapshotInterval = 120
With ArchiveInterval = 72

OSWatcher Black Box - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)
If you need further assistance or have comments or enhancement
requests you can email me Carl.Davis@Oracle.com

Data is stored in directory: /opt/app/oswbb/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Tue Apr 29 09:44:53 EDT 2014
oswbb heartbeat:Tue Apr 29 09:46:53 EDT 2014\




To collect OS Watcher files for a particular day use below command. 

# cd /opt/oracle.oswatcher/osw/archive
# find . -name '*13.03.15*' -print -exec zip /tmp/osw_`hostname`.zip {} \; 

{where 13- year 03- Month 15-day}

Real Application Command reference

Location of cluster alert log

$GRID_HOME/log/hostname



1. Stop crs on Node1 

crsctl stop crs 

if not stopping 

crsctl stop crs -f 

2. Remove the socket files on node1 

rm -rf /var/tmp/.oracle 
rm -rf /tmp/.oracle 


4. crsctl stat res -t -init 

5. crsctl stat res -t 

From 11.2.0.2 onwards, Cluster Health Monitor(CHM/OS) note 1328466.1 data can also be collected, i.e.:

# $GRID_HOME/bin/diagcollection.sh --chmos --incidenttime 04/24/201420:00:00 --incidentduration 05:00

This command will collect data from 2012-02-18 05:00 to 10:00 for 5 hours. incidenttime is specified as MM/DD/YYYY24HH:MM:SS, incidentduration is specified as HH:MM.

Upgrading OWB from 11.2.0.2 to 11.2.0.4

After I upgraded my database from 11.2.0.2 to 11.2.0.4 , I found that all the compoenents except Oracle Warehouse Builder was upgraded

Oracle Application Express : 4.2.4.00.08 : VALID
OWB : 11.2.0.2.0 : VALID
Oracle Enterprise Manager : 11.2.0.4.0 : VALID
OLAP Catalog : 11.2.0.4.0 : VALID
Spatial : 11.2.0.4.0 : VALID
Oracle Multimedia : 11.2.0.4.0 : VALID
Oracle XML Database : 11.2.0.4.0 : VALID
Oracle Text : 11.2.0.4.0 : VALID
Oracle Expression Filter : 11.2.0.4.0 : VALID
Oracle Rules Manager : 11.2.0.4.0 : VALID
Oracle Workspace Manager : 11.2.0.4.0 : VALID
Oracle Database Catalog Views : 11.2.0.4.0 : VALID
Oracle Database Packages and Types : 11.2.0.4.0 : VALID
JServer JAVA Virtual Machine : 11.2.0.4.0 : VALID
Oracle XDK : 11.2.0.4.0 : VALID
Oracle Database Java Packages : 11.2.0.4.0 : VALID
OLAP Analytic Workspace : 11.2.0.4.0 : VALID
Oracle OLAP API : 11.2.0.4.0 : VALID

I am sure that our application is not using OWB , but still I wanted have it upgraded to latest version .

All we did was :

@?/owb/UnifiedRepos/clean_owbsys.sql

@?/owb/UnifiedRepos/cat_owb.sql

and done

COMP_NAME||':'||VERSION||':'||STATUS

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

OWB : 11.2.0.4.0 : VALID

Automatic Stats Jobs

script to check stats job stats

for 11g
_________________________________________________________________________________________________________
set pages 999
set lines 180
col client_name for a35
col JOB_STATUS for a10
col JOB_START_TIME for a47
col JOB_DURATION for a15
select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history
where client_name='auto optimizer stats collection' order by job_start_time;



for 10g
_______________________________________________________________________________________________________________________________

set pages 999
set lines 300
col JOB_NAME for a25
col STATUS for a10
col ACTUAL_START_DATE for a40
col RUN_DURATION for a15
select JOB_NAME,STATUS,ACTUAL_START_DATE,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB' order by ACTUAL_START_DATE;

For 11g
Troubleshooting Scheduler Autotasks issues (Doc ID 1561498.1)

How to Force Table Statistics to be Gathered on Every Execution of Automatic Statistics and GATHER_DATABASE_STATS jobs (Doc ID 1490594.1)
exec dbms_stats.set_table_prefs('SCOTT','EMP','STALE_PERCENT',0);

BEGIN
dbms_auto_task_admin.enable();
END;

• How to enable auto stats collection?

  If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

  exec DBMS_AUTO_TASK_ADMIN.ENABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, 
   window_name => NULL);

• How to disable the auto stats collection?

  In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

  exec DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, 
   window_name => NULL);

• How can I check the status of the 'auto optimizer stats collection'?

  The status of the automatic statistics collection can be checked using:
  
  

  select client_name, JOB_SCHEDULER_STATUS 
   from DBA_AUTOTASK_CLIENT_JOB
   where client_name='auto optimizer stats collection';

  
  The possible Job status:
  
  • DISABLED
  • RETRY SCHEDULED
  • SCHEDULED
  • RUNNING
  • COMPLETED
  • BROKEN
  • FAILED
  • REMOTE
  • SUCCEEDED
  • CHAIN_STALLED

• How can I check whether or not the database has the 'auto optimizer stats collection' job enabled to run during the next maintenance window?

  SELECT CLIENT_NAME,
         STATUS
  FROM   DBA_AUTOTASK_CLIENT
  WHERE  CLIENT_NAME = 'auto optimizer stats collection';

• How can I see the history of the automatic stats job for each day?

  
  

  SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
   FROM dba_autotask_client_history
   WHERE client_name like '%stats%';
   
                                                    JOBS    JOBS     JOBS
   CLIENT_NAME                     WINDOW_NAME      CREATED STARTED  COMPLETED
   ------------------------------- ---------------- ------- -------- ----------
   auto optimizer stats collection THURSDAY_WINDOW        1        1          1
   auto optimizer stats collection SUNDAY_WINDOW          3        3          3
   auto optimizer stats collection MONDAY_WINDOW          1        1          1
   auto optimizer stats collection SATURDAY_WINDOW        2        2          2

• How to manually execute the Optimizer Statistics Auto Task?

  
  In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:
  
  

  SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

RMAN command reference

LIST COMMAND

RMAN> LIST ARCHIVELOG ALL;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATAFILE 1;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE 1, 2, 3;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST expired Backup of archivelog all summary;
RMAN> LIST Backup of tablespace Test summary;
RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1437;
RMAN> LIST CONTROLFILECOPY “/tmp/cntrlfile.copy”;
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST FAILURE;
RMAN> LIST FAILURE 641231 detail;
RMAN> LIST Backup of Controlfile;
RMAN> LIST Backup of Spfile;
RMAN> LIST Backup of Tablespace Test;
RMAN> LIST expired Backup;
RMAN> LIST expired Backup summary;
RMAN> LIST expired Backup of Archivelog all;
RMAN> LIST expired Backup of datafile 10;
RMAN> LIST recoverable backup;


COLD BACKUP


run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
backup database as compressed backupset format '/dumps/DCOE01PR/cold_before_upgrade/dcoe01pr_DB_b4_upgrade_%u_%D_%M';
Backup current controlfile format '/dumps/DCOE01PR/cold_before_upgrade/dcoe01pr_CF_b4_upgrade_%u_%D_%M'; 
release channel d1;
release channel d2;
release channel d3;
release channel d4;

}



spool log to rman_debug_restore01.log
spool trace to rman_debug_restore01.trc
set echo on
run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
allocate channel d5 device type disk;
allocate channel d6 device type disk;
allocate channel d7 device type disk;
allocate channel d8 device type disk;
allocate channel d9 device type disk;
allocate channel d10 device type disk;
allocate channel d11 device type disk;
allocate channel d12 device type disk;
allocate channel d13 device type disk;
allocate channel d14 device type disk;
allocate channel d15 device type disk;
allocate channel d16 device type disk;
debug all;
restore database check readonly;
debug off;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
release channel d9;
release channel d10;
release channel d11;
release channel d12;
release channel d13;
release channel d14;
release channel d15;
release channel d16;
}
spool trace off

spool log off

restore database check readonly;

How to give privilege to kill session without giving 'alter system' privilege

Step 1 : Create the procedure with sys user
create or replace procedure kill_session
(pn_sid number
,pn_serial number)
as
lv_user varchar2(30);
begin
select username into lv_user from v$session where sid = pn_sid and serial# = pn_serial;
if lv_user is not null and lv_user  in ('DBR') then
execute immediate 'alter system kill session '''||pn_sid||','||pn_serial||'''';
else
raise_application_error(-20000,'Attempt to kill protected system session has been blocked.');
end if;
end;
/

Procedure created.

you can modify the clause lv_user  in  accordingly to suit your need.
Above package allows user DBR to kill sessions for only DBR user.

Step 2: grant execute privilege to user
SQL> grant execute on sys.kill_session to dbr;

Grant succeeded.

Step 3: create a synonym the user
SQL> create synonym dbr.kill_session for sys.kill_session;

Synonym created.

Forgot to give "switch datafile in run " while doing restore

RMAN automates the procedure for restoring files. You do not need to go into the operating system, locate the backup or copy that you want to use, and manually copy files into the appropriate directories. When you issue a RESTORE command, RMAN directs a server session to restore the correct backups and copies to either:

• The default location, overwriting the files with the same name currently there
• A new location, which you can specify with the SET NEWNAME command

To restore a datafile, either mount the database or keep it open and take the datafile to be restored offline. When RMAN performs a restore, the RESTORE command creates the restored files as datafile copies and records them in the repository. The following table describes the behavior of the RESTORE, SET NEWNAME, and SWITCH commands.

Run SET NEWNAME?	RESTORE Behavior	Run SWITCH?
No	RMAN restores the files to their current path names and immediately removes the repository records for the datafile copies created during the restore.	N/A
Yes	RMAN restores the files to the path names specified by SET NEWNAME and does not remove the repository records for the datafile copies created during the restore.	If yes, then RMAN updates the datafile names in the control file to the names of the restored files; if no, then RMAN does not update the filenames in the control file and the restored files become datafile copies.

For example, if you restore datafile ?/oradata/trgt/tools01.dbf to its default location, then RMAN restores the file ?/oradata/trgt/tools01.dbf and overwrites any file that it finds with the same filename. If you run a SET NEWNAME command before you restore a file, then RMAN creates a datafile copy with the name that you specify. For example, assume that you run the following commands:

SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/tmp/tools01.dbf';
RESTORE DATAFILE '?/oradata/trgt/tools01.dbf';

In this case, RMAN creates a datafile copy of ?/oradata/trgt/tools01.dbf named /tmp/tools01.dbf and records it in the repository. To change the name for datafile?/oradata/trgt/tools01.dbf to /tmp/tools01.dbf in the control file, run a SWITCH command so that RMAN considers the restored file as the current database file. For example:

SWITCH DATAFILE '/tmp/tools01.dbf' TO DATAFILECOPY '?/oradata/trgt/tools01.dbf';