Restore the RMAN backup of a Oracle 12c RAC to a non ASM Single Instance database


We have a RMAN full backup Oracle 12c RAC ( 1 CDB +3 PDB )  and we want to restore it to a non ASM single instance database.

First take database full backup +(archive log, controlfile, spfile), then copy backups and parameter file of the RAC database to the destination server. Edit copied parameter file to create parameter file for the single instsance database like above:

TESTCDB.__db_cache_size=12569803776
TESTCDB.__java_pool_size=459524096
TESTCDB.__large_pool_size=1081701376
TESTCDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTCDB.__pga_aggregate_target=12038239232
TESTCDB.__sga_target=19131100672
TESTCDB.__shared_io_pool_size=266870912
TESTCDB.__shared_pool_size=5100223320
TESTCDB.__streams_pool_size=128435456
*.audit_file_dest='/u01/app/oracle/admin/TESTCDB/adump'
*.audit_trail='DB'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA/TESTCDB/ONLINELOG','/u01/app/oracle/oradata'
*.db_name='TESTCDB'
*.db_recovery_file_dest='/u01/app/oracle/fra'
*.db_recovery_file_dest_size=100G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTCDBXDB)'
*.enable_pluggable_database=true
*.log_archive_format='arc_%t_%s_%r.dbf'
*.memory_target=45G
*.open_cursors=300
*.processes=900
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.undo_tablespace='UNDOTBS1'

If we restore backup to the server which have a old restored copy of the database, then we must drop database and remove all data on Flash Recovery Area:

SQL> shut abort;
ORACLE instance shut down.

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.3891E+10 bytes
Database Buffers     2.4293E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@testdb4 bkp]$ cd /u01/app/oracle/fra/
[oracle@testdb4 fra]$ ll
total 4
drwxr-x---. 4 oracle oinstall 4096 Apr 20 19:11 TESTCDB
[oracle@testdb4 fra]$ rm -r *

 

Start the database with edited parameter file in nomount state:

SQL> startup nomount pfile=/u01/initTESTCDB.ora;
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
SQL>

Create directories that where we will restore datafiles and controlfile

[oracle@TESTdb4]$ mkdir -p /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED
[oracle@TESTdb4]$ cd /u01/app/oracle/oradata/TESTCDB
[oracle@TESTdb4]$ mkdir PDB1 PDB2 PDB3 CONTROLFILE

Connect to the RMAN and restore control file:

[oracle@testdb4 oradata]$ rman target /

RMAN> set decryption identified by 'yourpassword';
executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp';

Starting restore at 20-APR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTCDB/CONTROLFILE/control10.ctl
Finished restore at 20-APR-16

Then create spfile and pfile from our init.ora file , startup mount the database and catalog the copied  backups .:

RMAN> create spfile from pfile='/u01/initTESTCDB.ora';             
Statement processed
RMAN> create pfile from spfile;
Statement processed

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4.8318E+10 bytes
Fixed Size            5296928 bytes
Variable Size         2.6307E+10 bytes
Database Buffers     2.1877E+10 bytes
Redo Buffers          128917504 bytes
Database mounted.
SQL>

SQL>
[oracle@TEST4 fra]$ rman target /
RMAN>  catalog start with '/home/oracle/backup/rman/bkp2';


Starting implicit crosscheck backup at 20-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=948 device type=DISK
Crosschecked 24 objects
Finished implicit crosscheck backup at 20-APR-16

Starting implicit crosscheck copy at 20-APR-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-APR-16

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /home/oracle/backup/rman/bkp2

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)?
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/rman/bkp2/testcdb_contolfile_23r3h7rc_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_20r3h7q8_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_arch_22r3h7r4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1sr3h7gg_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_spfile_24r3h7rh_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1tr3h7ma_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1vr3h7p4_1_1.bkp
File Name: /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
File Name: /home/oracle/backup/rman/bkp2/testcdb_database_1ur3h7o1_1_1.bkp
RMAN>

You can generate RMAN restore scripts using queries below:

--For Daatafiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_datafile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$datafile;

--For Tempfiles
SELECT    'set newname for datafile '
       || file#
       || ' to ''/your_new_tempfile_location/'
       || SUBSTR (name, 17, LENGTH (name) - 16)
       || ''';'
  FROM v$tempfile;

--For Redologs
SELECT    'sql "alter database rename file '''''
       || MEMBER
       || ''''' to '
       || '''''/u01/app/oracle/oradata/logfile_'
       || ROWNUM
       || ''''' ";'
  FROM v$logfile

Set new name for all datafiles,tempfiles and logfile and then restore them

RMAN> run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697';
set newname for datafile 3 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663';
set newname for datafile 4 to '/u01/app/oracle/2> 3> oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743';
set newname for datafile 5 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01';
set newname for datafile 6 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743'4> 5> 6> 7> ;
set newname for datafile 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01';
set newname for datafile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187';
set newname for datafile 22 to '/u01/app/oracle/o8> 9> 10> radata/PDB1/system01.dbf';
set newname for datafile 23 to '/u01/app/oracle/oradata/PDB1/sysaux01.dbf';
set newname for datafile 24 to '/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf';
set newname for datafile 25 to '/u01/app/oracle/oradata/PDB1/tbs_inz11> 12> 13> 02.dbf';
set newname for datafile 26 to '/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf';
set newname for datafile 27 to '/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf';
set newname for datafile 34 to '/u01/app/oracle/oradata/PDB2/system01.dbf';
set newname for14> 15> 16> 17>  datafile 35 to '/u01/app/oracle/oradata/PDB2/sysaux01.dbf';
set newname for datafile 36 to '/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf';
set newname for datafile 37 to '/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf';
set newname for datafile 38 to '/u01/18> 19> 20> app/oracle/oradata/PDB2/tbs_PDB202.dbf';
set newname for datafile 39 to '/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf';
set newname for datafile 40 to '/u01/app/oracle/oradata/PDB3/system01.dbf';
set newname for datafile 41 to '/u01/app/oracle/oradata/PDB3/sy21> 22> 23> saux01.dbf';
set newname for datafile 42 to '/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf';
set newname for datafile 43 to '/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf';
set newname for datafile 44 to '/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf';
set n24> 25> 26> 27> ewname for datafile 45 to '/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf';
set newname for datafile 46 to '/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf';
set newname for datafile 47 to '/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf';
set newname for dataf28> 29> 30> ile 48 to '/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf';
set newname for datafile 49 to '/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf';
set newname for datafile 50 to '/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf';
set newname for tempfile 3 to '/u01/app/oracle/or31> 32> 33> adata/PDB3/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 4 to '/u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf';
set newname for tempfile 6 to '/u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf';
set newname for temp34> 35> 36> file 7 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01';
set newname for tempfile 8 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01';
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to '37> 38> '/u01/app/oracle/oradata/logfile_1'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''39> 40> /u01/app/oracle/oradata/logfile_3'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''41> 42> /u01/app/oracle/oradata/logfile_5'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/43> 44> u01/app/oracle/oradata/logfile_7'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u45> 46> 01/app/oracle/oradata/logfile_9'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/47> 48> u01/app/oracle/oradata/logfile_11'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to '49> 50> '/u01/app/oracle/oradata/logfile_13'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to51> 52>  ''/u01/app/oracle/oradata/logfile_15'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' 53> 54> to ''/u01/app/oracle/oradata/logfile_17'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.90427839355> 56> '' to ''/u01/app/oracle/oradata/logfile_19'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.9042757> 58> 8393'' to ''/u01/app/oracle/oradata/logfile_21'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.959> 60> 04278393'' to ''/u01/app/oracle/oradata/logfile_23'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1261> 62> 55.904278395'' to ''/u01/app/oracle/oradata/logfile_25'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26'' ";
sql "alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_163> 64> 4.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27'' ";
sql "alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28'' ";
restore database;
switch datafile all;
switch tempfile all;
65> 66> 67> 68> 69> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_2.284.903908803'' to ''/u01/app/oracle/oradata/logfile_1''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_2.258.903908803'' to ''/u01/app/oracle/oradata/logfile_2''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_1.283.903908803'' to ''/u01/app/oracle/oradata/logfile_3''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_1.259.903908803'' to ''/u01/app/oracle/oradata/logfile_4''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_5.1266.904278355'' to ''/u01/app/oracle/oradata/logfile_5''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_3.286.903909317'' to ''/u01/app/oracle/oradata/logfile_6''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_3.257.903909317'' to ''/u01/app/oracle/oradata/logfile_7''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_4.300.903909317'' to ''/u01/app/oracle/oradata/logfile_8''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_4.256.903909317'' to ''/u01/app/oracle/oradata/logfile_9''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_5.265.904278355'' to ''/u01/app/oracle/oradata/logfile_10''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_6.1264.904278355'' to ''/u01/app/oracle/oradata/logfile_11''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_6.264.904278355'' to ''/u01/app/oracle/oradata/logfile_12''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_7.1263.904278357'' to ''/u01/app/oracle/oradata/logfile_13''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_7.263.904278357'' to ''/u01/app/oracle/oradata/logfile_14''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_8.1262.904278357'' to ''/u01/app/oracle/oradata/logfile_15''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_8.262.904278357'' to ''/u01/app/oracle/oradata/logfile_16''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_9.1261.904278357'' to ''/u01/app/oracle/oradata/logfile_17''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_9.261.904278357'' to ''/u01/app/oracle/oradata/logfile_18''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_10.1260.904278393'' to ''/u01/app/oracle/oradata/logfile_19''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_10.267.904278393'' to ''/u01/app/oracle/oradata/logfile_20''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_11.1258.904278393'' to ''/u01/app/oracle/oradata/logfile_21''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_11.266.904278393'' to ''/u01/app/oracle/oradata/logfile_22''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_12.1259.904278393'' to ''/u01/app/oracle/oradata/logfile_23''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_12.268.904278393'' to ''/u01/app/oracle/oradata/logfile_24''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_13.1255.904278395'' to ''/u01/app/oracle/oradata/logfile_25''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_13.269.904278395'' to ''/u01/app/oracle/oradata/logfile_26''

sql statement: alter database rename file ''+DATA/TESTCDB/ONLINELOG/group_14.1257.904278395'' to ''/u01/app/oracle/oradata/logfile_27''

sql statement: alter database rename file ''+MULTIPLEX/TESTCDB/ONLINELOG/group_14.270.904278395'' to ''/u01/app/oracle/oradata/logfile_28''

Starting restore at 20-APR-16
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00040 to /u01/app/oracle/oradata/PDB3/system01.dbf
channel ORA_DISK_1: restoring datafile 00041 to /u01/app/oracle/oradata/PDB3/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00042 to /u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
channel ORA_DISK_1: restoring datafile 00045 to /u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
channel ORA_DISK_1: restoring datafile 00046 to /u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
channel ORA_DISK_1: restoring datafile 00047 to /u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
channel ORA_DISK_1: restoring datafile 00048 to /u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
channel ORA_DISK_1: restoring datafile 00049 to /u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
channel ORA_DISK_1: restoring datafile 00050 to /u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1sr3h7gg_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00034 to /u01/app/oracle/oradata/PDB2/system01.dbf
channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/PDB2/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00036 to /u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
channel ORA_DISK_1: restoring datafile 00037 to /u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
channel ORA_DISK_1: restoring datafile 00039 to /u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1tr3h7ma_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00022 to /u01/app/oracle/oradata/PDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00023 to /u01/app/oracle/oradata/PDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00024 to /u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
channel ORA_DISK_1: restoring datafile 00025 to /u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
channel ORA_DISK_1: restoring datafile 00026 to /u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
channel ORA_DISK_1: restoring datafile 00027 to /u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_database_1vr3h7p4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 20-APR-16

datafile 1 switched to datafile copy
input datafile copy RECID=33 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/system.299.903908697
datafile 3 switched to datafile copy
input datafile copy RECID=34 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/sysaux.285.903908663
datafile 4 switched to datafile copy
input datafile copy RECID=35 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs1.281.903908743
datafile 5 switched to datafile copy
input datafile copy RECID=36 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_system01
datafile 6 switched to datafile copy
input datafile copy RECID=37 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/users.282.903908743
datafile 7 switched to datafile copy
input datafile copy RECID=38 STAMP=909693179 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_sysaux01
datafile 8 switched to datafile copy
input datafile copy RECID=39 STAMP=909693180 file name=/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/undotbs2.293.903909187
datafile 22 switched to datafile copy
input datafile copy RECID=40 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/system01.dbf
datafile 23 switched to datafile copy
input datafile copy RECID=41 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/sysaux01.dbf
datafile 24 switched to datafile copy
input datafile copy RECID=42 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/PDB1_users01.dbf
datafile 25 switched to datafile copy
input datafile copy RECID=43 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz02.dbf
datafile 26 switched to datafile copy
input datafile copy RECID=44 STAMP=909693180 file name=/u01/app/oracle/oradata/PDB1/tbs_inz03.dbf
datafile 27 switched to datafile copy
input datafile copy RECID=45 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB1/tbs_inz01.dbf
datafile 34 switched to datafile copy
input datafile copy RECID=46 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/system01.dbf
datafile 35 switched to datafile copy
input datafile copy RECID=47 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/sysaux01.dbf
datafile 36 switched to datafile copy
input datafile copy RECID=48 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/PDB2_users01.dbf
datafile 37 switched to datafile copy
input datafile copy RECID=49 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB203.dbf
datafile 38 switched to datafile copy
input datafile copy RECID=50 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB202.dbf
datafile 39 switched to datafile copy
input datafile copy RECID=51 STAMP=909693181 file name=/u01/app/oracle/oradata/PDB2/tbs_PDB201.dbf
datafile 40 switched to datafile copy
input datafile copy RECID=52 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/system01.dbf
datafile 41 switched to datafile copy
input datafile copy RECID=53 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/sysaux01.dbf
datafile 42 switched to datafile copy
input datafile copy RECID=54 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/PDB3_users01.dbf
datafile 43 switched to datafile copy
input datafile copy RECID=55 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive05.dbf
datafile 44 switched to datafile copy
input datafile copy RECID=56 STAMP=909693182 file name=/u01/app/oracle/oradata/PDB3/tbs_archive04.dbf
datafile 45 switched to datafile copy
input datafile copy RECID=57 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive03.dbf
datafile 46 switched to datafile copy
input datafile copy RECID=58 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive02.dbf
datafile 47 switched to datafile copy
input datafile copy RECID=59 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_archive01.dbf
datafile 48 switched to datafile copy
input datafile copy RECID=60 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB303.dbf
datafile 49 switched to datafile copy
input datafile copy RECID=61 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB302.dbf
datafile 50 switched to datafile copy
input datafile copy RECID=62 STAMP=909693183 file name=/u01/app/oracle/oradata/PDB3/tbs_PDB301.dbf

renamed tempfile 3 to /u01/app/oracle/oradata/PDB3/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 4 to /u01/app/oracle/oradata/PDB1/temp012016-02-06_03-13-05-pm.dbf in control file
renamed tempfile 6 to /u01/app/oracle/oradata/PDB2/temp022016-02-20_03-13-05-pm.dbf in control file
renamed tempfile 7 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/cdb_temp01 in control file
renamed tempfile 8 to /u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/PDBSEED/seed_temp01 in control file

Recover database

RMAN> recover database;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10272
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11124
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11125
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10273
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11126
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=11127
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10274
channel ORA_DISK_1: reaTESTg from backup piece /home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/rman/bkp2/TESTcdb_arch_22r3h7r4_1_1.bkp tag=LOCAL_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc thread=1 sequence=10272
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc thread=2 sequence=11124
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11124_ckhd9twc_.arc RECID=59793 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc thread=2 sequence=11125
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10272_ckhd9vrm_.arc RECID=59794 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc thread=1 sequence=10273
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11125_ckhd9ttm_.arc RECID=59792 STAMP=909693691
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc thread=2 sequence=11126
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11126_ckhd9w8h_.arc RECID=59798 STAMP=909693693
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc thread=2 sequence=11127
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10273_ckhd9vwm_.arc RECID=59797 STAMP=909693692
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc thread=1 sequence=10274
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_1_10274_ckhd9wbq_.arc RECID=59796 STAMP=909693692
archived log file name=/home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931 thread=1 sequence=10275
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/TESTCDB/archivelog/2016_04_20/o1_mf_2_11127_ckhd9w9m_.arc RECID=59795 STAMP=909693692
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:42:18
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Copy archivelogs that recovery needs and recover database

[oracle@testdb1 2016_04_20]$ scp thread_2_seq_11128.989.909680709  192.168.1.109:/home/oracle/backup/rman/bkp2/
oracle@192.168.1.109's password:
thread_2_seq_11128.989.909680709

RMAN> recover database
2> ;

Starting recover at 20-APR-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10275 is already on disk as file /home/oracle/backup/rman/bkp2/thread_1_seq_10275.986.909680931
unable to find archived log
archived log thread=2 sequence=11128
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2016 20:45:52
RMAN-06054: media recovery requesting unknown archived log for thread 2 with sequence 11128 and starting SCN of 842116000

Open database with resetlogs

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> show pdbs;

CON_ID 	CON_NAME OPEN MODE	RESTRICTED
------ --------- --------- ---------- 	
2 		PDB$SEED  READ ONLY	 NO
3 		PDB1      MOUNTED
4 		PDB2 	  MOUNTED
5 		PDB3      MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

You can also use set new name only for database if you dont want place all files to different directories like in source:

RMAN> run
{
set newname for database to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
set newname for tempfile 1 to '/u01/app/oracle/oradata/TESTCDB/TESTCDB/DATAFILE/%U';
restore database;
switch datafile all;
switch tempfile all;
}

Useful Oracle RAC Commands


Shutdown and Start sequence of Oracle RAC 

 

STOP ORACLE RAC (11g)
1. emctl stop dbconsole
2. srvctl stop listener -n racnode1
3. srvctl stop database -d RACDB
4. srvctl stop asm -n racnode1 -f
5. srvctl stop asm -n racnode2 -f
6. srvctl stop nodeapps -n racnode1 -f
7. crsctl stop crs

START ORACLE RAC (11g)
1. crsctl start crs
2. crsctl start res ora.crsd -init
3. srvctl start nodeapps -n racnode1
4. srvctl start nodeapps -n racnode2
5. srvctl start asm -n racnode1
6. srvctl start asm -n racnode2
7. srvctl start database -d RACDB
8. srvctl start listener -n racnode1
9. emctl start dbconsole

 

To start and stop oracle clusterware (run as the superuser) :

[root@node1 ~]# crsctl stop crs

[root@node1 ~]# crsctl start crs

 

To start and stop oracle cluster resources running on all nodes :

[root@node1 ~]#  crsctl stop cluster -all

[root@node1 ~]#  crsctl start cluster -all

 

To check the current status of a cluster :

[oracle@node1~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

 

To check the current status of CRS :

[oracle@node1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

 

To display the status cluster resources :

[oracle@node1 ~]$ crsctl stat res -t

 

To check version of  Oracle Clusterware :

[oracle@node1 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [node1] is [11.2.0.4.0]
[oracle@node1 ~]$ 
[oracle@node1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]
[oracle@node1 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]

 

To check current status of OHASD (Oracle High Availability Services) daemon :

[oracle@node1 ~]$ crsctl check has
CRS-4638: Oracle High Availability Services is onli

 

Forcefully deleting resource :

[oracle@node1 ~]$ crsctl delete resource testresource -f

 

Enabling and disabling CRS daemons (run as the superuser) :

[root@node1 ~]# crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@node1 ~]# 
[root@node1 ~]# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.

 

To check the status of Oracle CRS :

[oracle@node1 ~]$ olsnodes
node1
node2

 

To print node name with node number :

[oracle@node1 ~]$ olsnodes -n
node1	1
node2	2

 

To print private interconnect address for the local node :

[oracle@node1 ~]$ olsnodes -l -p
node1	192.168.1.101

 

To print virtual IP address with node name :

[oracle@node1 ~]$ olsnodes -i
node1	node1-vip
node2	node2-vip
[oracle@node1 ~]$ olsnodes -i node1
node1	node1-vip

 

To print information for the local node :

[oracle@node1 ~]$ olsnodes -l
node1
pl

 

To print node status (active or inactive) :

[oracle@node1 ~]$ olsnodes -s
node1	Active
node2	Active
[oracle@node1 ~]$ olsnodes -l -s
node1	Active

 

To print node type (pinned or unpinned) :

[oracle@node1 ~]$ olsnodes -t
node1	Unpinned
node2	Unpinned
[oracle@node1 ~]$ olsnodes -l -t
node1	Unpinned

 

To print clusterware name :

[oracle@node1 ~]$ olsnodes -c
rac-scan

 

To display global public and global cluster_interconnect :

[oracle@node1 ~]$ oifcfg getif
eth0  192.168.100.0  global  public
eth1  192.168.1.0  global  cluster_interconnect

 

To display the database registered in the repository :

[oracle@gpp4 ~]$ srvctl config database
TESTRACDB

 

To display the configuration details of the database :

[oracle@TEST4 ~]$ srvctl config database -d TESTRACDB
Database unique name: TESTRACDB
Database name: TESTRACDB
Oracle home: /home/oracle/product/11.2.0/db_home1
Oracle user: oracle
Spfile: +DATA/TESTRACDB/spfileTESTRACDB.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TESTRACDB
Database instances: TESTRACDB1,TESTRACDB2
Disk Groups: DATA,ARCH
Mount point paths: 
Services: SRV_TESTRACDB
Type: RAC
Database is administrator managed

 

To change  policy of database from automatic to manual :

[oracle@TEST4 ~]$ srvctl modify database -d TESTRACDB -y MANUAL

 

To change  the startup option of database from open to mount :

[oracle@TEST4 ~]$ srvctl modify database -d TESTDB -s mount

 

To start RAC listener :

[oracle@TEST4 ~]$ srvctl start listener

 

To display the status of the database :

[oracle@TEST4 ~]$ srvctl status database -d TESTRACDB
Instance TESTRACDB1 is running on node TEST4
Instance TESTRACDB2 is running on node TEST5

 

To display the status services running in the database :

[oracle@TEST4 ~]$ srvctl status service -d TESTRACDB
Service SRV_TESTRACDB is running on instance(s) TESTRACDB1,TESTRACDB2

 

To check nodeapps running on a node :

[oracle@TEST4 ~]$ srvctl status nodeapps
VIP TEST4-vip is enabled
VIP TEST4-vip is running on node: TEST4
VIP TEST5-vip is enabled
VIP TEST5-vip is running on node: TEST5
Network is enabled
Network is running on node: TEST4
Network is running on node: TEST5
GSD is enabled
GSD is not running on node: TEST4
GSD is not running on node: TEST5
ONS is enabled
ONS daemon is running on node: TEST4
ONS daemon is running on node: TEST5
 

 
[oracle@TEST4 ~]$  srvctl status nodeapps -n TEST4
VIP TEST4-vip is enabled
VIP TEST4-vip is running on node: TEST4
Network is enabled
Network is running on node: TEST4
GSD is enabled
GSD is not running on node: TEST4
ONS is enabled
ONS daemon is running on node: TEST4

 

To start or stop all instances associated with a database. This command also starts services and listeners on each node :

[oracle@TEST4 ~]$ srvctl start database -d TESTRACDB

 

To shut down instances and services (listeners not stopped):

[oracle@TEST4 ~]$ srvctl stop database -d TESTRACDB

 

You can use -o option to specify startup/shutdown options.
To shutdown immediate database – srvctl stop database -d TESTRACDB -o immediate
To startup force all instances – srvctl start database -d TESTRACDB -o force
To perform normal shutdown – srvctl stop database -d TESTRACDB -i instance racnode1

To start or stop the ASM instance on racnode01 cluster node :

[oracle@TEST4 ~]$ srvctl start asm -n racnode1
[oracle@TEST4 ~]$ srvctl stop asm -n racnode1

 

To display current configuration of the SCAN VIP’s :

[oracle@test4 ~]$ srvctl config scan
SCAN name: vmtestdb.exo.local, Network: 1/192.168.5.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /vmtestdb.exo.local/192.168.5.100
SCAN VIP name: scan2, IP: /vmtestdb.exo.local/192.168.5.101
SCAN VIP name: scan3, IP: /vmtestdb.exo.local/192.168.5.102

 

Refreshing  SCAN VIP’s with new IP addresses from DNS :

[oracle@test4 ~]$ srvctl modify scan -n your-scan-name.example.com

 

To stop or start SCAN listener and the  SCAN VIP resources :

[oracle@test4 ~]$ srvctl stop scan_listener 
[oracle@test4 ~]$ srvctl start scan_listener 
[oracle@test4 ~]$ srvctl stop scan
[oracle@test4 ~]$ srvctl start scan

 

To display the status of SCAN VIP’s and SCAN listeners :

[oracle@test4 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node test4
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node test5
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node test5
 
 
[oracle@test4 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node test4
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node test5
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node test5

 

To add/remove/modify SCAN :

[oracle@test4 ~]$ srvctl add scan -n your-scan
[oracle@test4 ~]$ srvctl remove scan
[oracle@test4 ~]$ srvctl modify scan -n new-scan

 

To add/remove SCAN listener :

[oracle@test4 ~]$ srvctl add scan_listener
[oracle@test4 ~]$ srvctl remove scan_listener

 

To modify SCAN listener port :

srvctl modify scan_listener -p <port_number>
srvctl modify scan_listener -p <port_number>  (reflect changes to the current SCAN listener only)

To start the ASM instnace in mount state :

ASMCMD> startup --mount

 

To shut down ASM instance immediately(database instance must be shut down before the ASM instance is shut down) :

ASMCMD> shutdown --immediate

 

Use lsop command on ASMCMD to list ASM operations :

ASMCMD > lsop

 

To perform quick health check of OCR :

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

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

	 Cluster registry integrity check succeeded

	 Logical corruption check bypassed due to non-privileged user

 

To dump content of OCR file into an xml :

[oracle@test4 ~]$ ocrdump testdump.xml -xml

 

To add or relocate the OCR mirror file to the specified location :

[oracle@test4 ~]$ ocrconfig -replace ocrmirror ‘+TESTDG’
[oracle@test4 ~]$ ocrconfig -replace +CURRENTOCRDG -replacement +NEWOCRDG

 

To relocate existing OCR file :

[oracle@test4 ~]$ ocrconfig  -replce ocr ‘+TESTDG’

 

To add mirrod disk group for OCR :

[oracle@test4 ~]$ ocrconfig -add +TESTDG

 

To remove OCR mirror :

ocrconfig -delete +TESTDG

 

To remove the OCR or the OCR mirror :

[oracle@test4 ~]$ ocrconfig -replace ocr

[oracle@test4 ~]$ ocrconfig replace ocrmirror

 

To list ocrbackup list :

[oracle@test4 ~]$ ocrconfig -showbackup

test5     2016/04/16 17:30:29     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup00.ocr

test5     2016/04/16 13:30:29     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup01.ocr

test5     2016/04/16 09:30:28     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/backup02.ocr

test5     2016/04/15 13:30:26     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/day.ocr

test5     2016/04/08 09:30:03     /home/oracle/app/11.2.0/grid/cdata/vmtestdb/week.ocr

 

Performs OCR backup manually :

[root@testdb1 ~]# ocrconfig -manualbackup

testdb1     2016/04/16 17:31:42     /votedisk/backup_20160416_173142.ocr     0  

 

Changes OCR autobackup directory

[root@testdb1 ~]# ocrconfig -backuploc /backups/ocr

 

To verify the integrity of all the cluster nodes:

[oracle@node1]$ cluvfy comp ocr -n all -verbose
Verifying OCR integrity 
Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations

Checking daemon liveness...

Check: Liveness for "CRS daemon"
  Node Name                             Running?                
  ------------------------------------  ------------------------
  node2                                yes                     
  node1                                yes                     
Result: Liveness check passed for "CRS daemon"

Checking OCR config file "/etc/oracle/ocr.loc"...
OCR config file "/etc/oracle/ocr.loc" check successful

Disk group for ocr location "+DATA/testdb-scan/OCRFILE/registry.255.903592771" is available on all the nodes
Disk group for ocr location "+CRS/testdb-scan/OCRFILE/registry.255.903735431" is available on all the nodes
Disk group for ocr location "+MULTIPLEX/testdb-scan/OCRFILE/registry.255.903735561" is available on all the nodes

Checking OCR backup location "/bkpdisk"
OCR backup location "/bkpdisk" check passed
Checking OCR dump functionality
OCR dump check passed

NOTE: 
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.
OCR integrity check passed
Verification of OCR integrity was successful. 

.

Enable Cluster Time Synchronization Service on Oracle 11gr2 RAC


1) If Synchronization Service works with NTP   then CTSS must be in Observer mode. Lets check it:

[oracle@node1 ~]$grid_env
[oracle@node1 ~]$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[oracle@node2 ~]$ grid_env
[oracle@node2 ~]$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

This means RAC works with NTP.

2) Now lets disable NTP

[oracle@node1 ~]$ su -
Password:
[root@node1 ~]# grid_env
[root@node1 ~]# crsctl stop crs
<<<output trimmed>>>
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@node2 ~]# crsctl stop crs
<<<output trimmed>>>
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@node1 ~]$ /sbin/service ntpd stop
[root@node1 ~]# chkconfig ntpd off
[oracle@node2 ~]$ /sbin/service ntpd stop
[root@node2 ~]# chkconfig ntpd off
[root@node1 ~]# mv /etc/ntp.conf /tmp/
[root@node2 ~]# mv /etc/ntp.conf /tmp/

3. Start the cluster on all nodes

[root@node1 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
[root@node2 ~]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

4. Now let’s check ctss .  And we saw that ctss starts in active mode :

[oracle@node1 ~]$ crsctl check ctss
CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0
[root@node2 ~]# crsctl check ctss
CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0

Oracle Database Vault (part2)


Oracle database Vault Access Control Components

  • REALM  is a basically group of database schemas, objects or rolls that need to be secured in the database. For example: You may have table called EMP which contains employee salaries in schema called HR and you don’t want all the users to access this table specifically by SYS user which have an administrative access. If you want to prevent SYS user from accessing this particular table in the HR schema, then you can protect this table by configuring realm. So that this table will be protected from the administrators access.
  • COMMAND RULE  can be created to control execution of SQL statements. For example : You don’t want to create a table in HR schema, but user has system privilege create any table that means user can create table in any of the schemas of the database. As you want to restrict this user from running create table statement you can configure command rule and restrict the execution of create table statement. The SQL statements can be DDL’s or DML’s and you can restrict the execution all of this statements.
  • FACTOR- there are certain situations in which you will have to prevent access based on the user location, IP address or a particular user. In that scenario you configure variable called factor and this name attribute will recognize the components such as user locations, database IP addresses or session user and secures the area of database which you want to prevent from being accessed by the administrative users
  • RULE SET is a collection of one or more rules. You can associate this rule with realm authorization, command rules, factor assignments and also secure application roles
  • SECURE APPLICATION ROLE can be enabled based on the evaluation of the Oracle Database Vault rule set, the rule set evaluates to true or false depending on the evaluation of the rule which is associated with the rule set

.

Changes  after database vault installation:

Changes in initialization and Password Parameter settings:

  • AUDIT_SYS_OPERATIONS value changes from FALSE to TRUE – This means all operations performed by sys user will be audited
  • OS_ROLES paraeter values changes to FALSE- by default this is not configured, after you instal database vault this will be set to FALSE . This will disable operating system granting and revoking roles and privileges to users
  • RECYCLEBIN parameters values changes from ON to OFF- is this parameter Turned ON, the dropped objects in the database will be moved to recyclebean.By default recycle ben is turned on. When we enable database vault this parameter is turned off
  • REMOTE_LOGIN_PASSWORDFILE-this parameter is set to EXCLUSIVE by default, after installation od Database Vault value of this parameter is set to EXCLUSIVE again.
  • SQL92_SECURITY parameters value changes from FALSE to TRUE – if a user is granted update and delete privileges on a table, select privilege must enable that user from updating and deleting the table. SQL92_SECURITY enforces this functionality when update and delete privileges are granted to a user.
    .
    .New Database Roles
  • DV_OWNER-Oracle Database Vault Owner
  • DV_ACCTMGR-Oracle Database Vault Account Manager
    .
    .Changes to Database Auditing
  • After installing Database Vault $AUD table moved to SYSTEM schema from SYS schema
  • Modified audit settings- yo can also see certain change in the audit settings. dv will configure certain audit settings in the database. this again depends on the setting of audit_trail initialization parameter, if it set to none audit settings not configured, if it set db os os then audit setings can configured in the database vault in management.
    .
    .Privileges prevented for existing users:
  • ALTER PROFILE
  • ALTER USER
  • CREATE PROFILE
  • CREATE USER
  • DROP PROFILE
  • DROP USER
    .
    Basically this privileges are prevented from execution for sys and sysdba users . in database vault and management this privileges are granted to the special user who has DV_ACCTMGR privilege and this is will long be able perform any of the create user, drop user or any alter user operations in a database vault and management.
    .

      Privileges revoked from existing users and roles

  • DBA
  • PUBLIC
  • IMP_FULL_DATABASE
  • EXECUTE_CATALOG_ROLE
  • SCHEDULER_ADMIN
    .

Oracle Database Vault Schemas

When you install Database Vault two schemas (DVSYS and DVF)  are created during configuration.

  • DVSYS
    DVSYS schema basically contains Oracle database vault database objects in which Oracle Database Vault configuration information is stored
  • DVF
    There is a function called DBMS_MACSEC_FUNCTION package, this contains functions which can retrieve factor identitis. This package is owned by DVF schema.
    .

Oracle Database Vault Roles

  • DV_OWNER
    During the database vault installation and configuration when we were confiring it using DBCA we got the option to specify DV owner and DV account manager. We had provided DV account owner name as DVOWNER. This DVOWNER has a dv_owner role , this rule basically manages oracle database vault rules and its configuration
  • DV_ADMIN
    This role controls database vault PL/SQL packages
  • DV_ACCTMGR
    This role granted to Oracle Database Vault account manager account. This basically creates and manages database accounts and profiles. In the database vault and management sys user will not be able to perform any database account relatively activity such as creating user, altering user  to change the password, drop a user, create profile to maintain database users. So such a scenario dv_acctmgr will perform all this operations. A user who has DV_ACCTMGR rule will be performing all this operations in the database vault and management.
  • DV_SECANALYST
    User with this role can run reports in the Database Vault  administrative console
  • DV_PATCH_ADMIN
    This role granting to a user which performing patching the database vault and management. When applying patches in the database vault and management in earliest version database vault had to be disabled, this new role was introduced to avoid this step. Whenever yo needs to apply the patch in a database vault and management temporarily this rule is granted to the user. After the application of the patch this rule will be revoked from the user.

DBMS_MACADM Package
This package basically contains the procedures and the functions to create and configure the different components of the database vault such as realms, command rules, factors, rule sets and secure application roles. This package can be executed by the users who a granted dv_owner or dv_admin roles. Configuration of realms, command rules, factors also can be done using database vault administrative console. Below I will show you how to create and configure realms, command rules, factors and rule sets using database vault administrative console, I am also providing an example how it can be done using dbms_macadm package:

Creating a Realm

DBMS_MACADM.CREATE_REALM(
realm_name IN VARCHAR2,
description IN VARCHAR2,
enabled IN VARCHAR2,
audit_options IN NUMBER);

Creating a Comman rule

DBMS_MACADM.CREATE_COMMAND_RULE(
command IN VARCHAR2,
rule_set_name IN VARCHAR2,
object_owner IN VARCHAR2,
object_name IN NUMBER
enabled IN VARCHAR2);

Let’s test DV configuration and management:

SQL> conn dvacctmgr/<<your password >>
Connected.
SQL> create user demo identified by demo quota unlimited on users;
User created.
SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> 
SQL> grant create session, select any table to demo;

Grant succeeded.
SQL> grant create session, select any table to demo;
Grant succeeded.
SQL> conn demo/demo
Connected.

SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

As you can see user DEMO can fetch the records from EMPLOYEES table in HR schema. Let me configure REALM to restrict access to this particular table.
When you access the DV administration console you give the hostname or the IP address, the port number and slash DVA:

https://database_ip_adress: port_number/dva

Type your URL in your browser and press ok:

bp9

This is the login page database DV administration console.
Here we specify user name for the database vault owner dvowner and specify the password, hostname   or ip address of the my database server, and default port of oracle 1521. You can specify SID or service name in my example I am specifying SID name  which is DB11G and click login.

bp10

You can see different tabs on the console – Administration, Database Vault Reports, General Security Reports and Monitor. To Configure Realm click on  Administration tab and select Realms:

bp11

There are certain default realms which are configured during database vault installation. These are 4 realms which are default realms offered by oracle database vault.

  • Database Vault Account Management realm defines realm for users who perform account management activities in the database
  • Oracle Data Dictionary this defines the realm for catalog users
  • Oracle Database Vault this defines realm for oracle database vault users such as DVF and DVSYS schemas
  • Oracle Enterpise Manager this is basically for the enterprise manager users such as SYSMAN and DBSNMP who wants to access oracle database information

Now let’s create new realm for securing EMPLOYEES table in the HR schema, click create. In the create realm page enter the name of the realm, the description of the realm, status  and different auditing options( Audit on failure – will generate an audit record when there is a realm failure) and click OK to create realm.

bp12

Now you can see HR_realm in this list:

bp12

To protect the objects of the HR schema, select HR_realm and click edit .

bp14

In this realm   under the Realm Secured Objects you can see there are no objects which are protected. Click create and specify object owner, object type and object name which need to be secured.  As I am securing the object of HR schema,  i am specifying object owner as HR, object type as Table and object name as %-this will protect all the tables in the HR schema and click OK.

bp15

Now all tables under HR are protected. Let’s go to database and test how the securing objects of HR_realm has affected access of user demo to this tables .Earlier when the realm was not created demo user was able to access to table EMPLOYEES of HR schema.

Connect as DEMO user and again perform select statement :

SQL> select * from HR.EMPLOYEES;

ERROR at line 1:
ORA-01031: insufficient privileges

And you will get error “insufficient privileges” this means HR.EMPLOYEES table is protected by the REALM. This is how the REALM authorization or REALM protection  works. Lets go back database vault administration console.

bp16

We saw that some of object are protected here, but users not authorized to access the objects of this realm. To authorize users to access objects of this realm select HR_realm , click  edit , go to section Realm Authorizations and click the create button. In the create Realm Authorization page you can see Grantee, Authorization type and Authorization Rule Set. I am specifying Grantee as Demo user , authorization type is participant-that means this user will be able to access the objects of this realm, if  I specify the Authorization Options as OWNER I would have same functionality as Participant along with that it will have  additional privilege to grantee access to the objects authorized by this realm and  Authorization Rule Set – not specifying any rule set here:

bp17

Now let’s see how a Command rule functions in the DV and management.

Connect as sysdba, grantee create any table privilege to demo user.
Then create table SCOTT.TBL_TEST with user demo.

[oracle@orcl ~]$ sqlplus / as sysdba
SQL> grant create any table to demo ;
Grant succeeded.
SQL> conn demo/demo
Connected.
SQL> create table SCOTT.TBL_TEST(NAME VARCHAR2(10),MARKS NUMBER);
Table created.

Now let’s go to Database Vault console and click on Command rules.

bp18

As we had default realms we also have certain default command rules offered by oracle database vault . This command rules is to restrict execution of following commands in the database. To create new command rule, click create button. In the create command rule page, you can select the command which you want to restrict. Here I want to restrict creating any table in SCOTT schema by user demo:

bp19

And click OK to create command rule. Here you can see that  there is new command rule created.

bp20

Now let’s go back to database and test how create table statement works now:

SQL> conn demo/demo
Connected.
SQL> create table SCOTT.TEST as select * from ALL_OBJECTS;
create table SCOTT.TEST as select * from ALL_OBJECTS
                                         *
ERROR at line 1:
ORA-47400: Command Rule violation for CREATE TABLE on SCOTT.TEST

Here I try to create table under SCOTT, but I get  command rule violation for CREATE TABLE on SCOTT

Let’s look to small example how we can restrict access from a certain programs or modules.
Lets create Factor click on Factors and select create:

bp21

In the create new factor page fill specified name, description and Factor type.
Here we will create factor to find name the application from which database is going to connect and where we are going to block access from SQL*PLUS.

bp22

Now I will configure rule set:
bp23

Click create and  specify name, description for rule set , select status enabled and Evaluation Options All True – which means all the conditions which you specified should be evaluated to true, auditing options disabled. Then click OK.

bp24

After creating rule set edit it and click on create button to create Rules Associated to The Rule Set. Here we specified Rule Expression as DVF.F$MODULE=’SQL*PLUS.EXE’ and DVF.F$SESSION_USER IN(‘SCOTT’). DVF.F$MODULE here means that we created factor called MODULE, ‘SQL*PLUS.EXE’ here means any connections coming from sqlplus, DVF.F$SESSION_USER IN(‘SCOTT’)- means session user as SCOTT. All this means that if the user is SCOTT and SCOTT is connecting to the database using SQLPLUS it should be allowed to connect and none of the other users should be allowed to connect. Click ok and create rule.

bp25

Now I will create command rule to restrict connection to the database:

bp26

Now let’s go back to my database and test how this command rule works:

SQL> conn scott
Enter password: <your password>
Connected.
SQL> conn demo
Enter password: <your password>
ERROR:
ORA-47400: Command Rule violation for CONNECT on LOGON
Warning: You are no longer connected to ORACLE.
SQL>

During the connect with demo user we get ERROR Command Rule violation. This means that any user other than scott are not allowed to connect to the database using SQLPLUS.

ORACLE DATABASE VAULT REPORTS

There are two categories of DV reports- Oracle database Vault reports and General Security reports

  • Database Vault Reports are basically to give the information about the different components of the database vault such as Realms, their authorization violations, command rule violations, factors, rule sets and security application rules.
  • General Security Reports are the general reports such as user authorization, privileges, rules, object and system privileges and also it gives special reports for the security vulnerability issues

To see this reports login to the Database Vault Administration console and click on the Database Vault Reports tab:

bp27

Click on Realm Audit Report and Run Report, now you can see report which shows you different kind of realm violations:

bp28

Now let’s look command rule audit reports, here we can see command and returned error codes:

bp29

Let’s see General Security Reports:

bp30

Similarly you can select and run different reports.

Oracle Database Vault (part1)


1593501

Database Vault restricts access to the specific areas of the database from different users in the database as well as the administrative users. It also helps us to protect the database against insider threats. Insider threats, meaning the protecting database from the access users who have sysdba and sysoper privileges. Using database vault we can also enforce separation of duties where we can allocate different accounts for database account management and other database activities.

Database vault installation

Installation of the database vault includes two steps, enabling the database vault at the binary level and the registering database vault using dbca:
Enabling the database vault at the binary level
First of all, stop database console, listener and shutdown database. Database vault is dependent on oracle label security, so beforeinstallation of database vault you must enable label security.

Execute following  command :
For enabling label security component Chopt enable lbac
For enabling  database vault components Chopt enable dv

SQL> SHUTDOWN IMMEDIATE
SQL> EXIT
[oracle@orcl ~]$ emctl stop dbconsole
[oracle@orcl ~]$ lsnrctl stop
[oracle@orcl ~]$ chopt enable lbac 
Writing to /u01/app/oracle/product/11.2/db_1/install/enable_lbac.log...
%s_unixOSDMakePath% -f /u01/app/oracle/product/11.2/db_1/rdbms/lib/ins_rdbms.mk lbac_on
%s_unixOSDMakePath% -f /u01/app/oracle/product/11.2/db_1/rdbms/lib/ins_rdbms.mk ioracle

[oracle@orcl ~]$ chopt enable dv
Writing to /u01/app/oracle/product/11.2/db_1/install/enable_dv.log...
%s_unixOSDMakePath% -f /u01/app/oracle/product/11.2/db_1/rdbms/lib/ins_rdbms.mk dv_on
%s_unixOSDMakePath% -f /u01/app/oracle/product/11.2/db_1/rdbms/lib/ins_rdbms.mk ioracle

Run the following commands. The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.

[oracle@orcl Datafiles]$ cd $ORACLE_HOME/rdbms/lib
[oracle@orcl lib]$ make -f ins_rdbms.mk dv_on lbac_on
/usr/bin/ar d /u01/app/oracle/product/11.2/db_1/rdbms/lib/libknlopt.a kzvndv.o
/usr/bin/ar cr /u01/app/oracle/product/11.2/db_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/11.2/db_1/rdbms/lib/kzvidv.o 
/usr/bin/ar d /u01/app/oracle/product/11.2/db_1/rdbms/lib/libknlopt.a kzlnlbac.o
/usr/bin/ar cr /u01/app/oracle/product/11.2/db_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/11.2/db_1/rdbms/lib/kzlilbac.o 
[oracle@orcl lib]$ cd $ORACLE_HOME/bin
[oracle@orcl bin]$ relink all
writing relink log to: /u01/app/oracle/product/11.2/db_1/install/relink.log

Start the database, database control console process, and listener. Then run DBCA command to register Database Vault

[oracle@orcl ~]$ emctl start dbconsole
[oracle@orcl ~]$ lsnrctl start
SQL> startup;
ORACLE instance started.
Total System Global Area  534462464 bytes
Fixed Size		    2254952 bytes
Variable Size		  213911448 bytes
Database Buffers	  314572800 bytes
Redo Buffers		    3723264 bytes
Database mounted.
Database opened.

[oracle@orcl ~]$ dbca

Select configure database option to configure database vault

bp1

Select the database which will be configured for database vault

bp2

Select label security component and dv components click next

bp3

Also we can select this options when installing oracle database :

bp4

In the next screen you must specify the username and password for database vault owner and account manager.

bp5

And click next, select dedicated server mode , click ok and then finsh database vault configuration.

bp6

bp7

bp8

You can check if Oracle Database Vault is enabled or disabled by querying the V$OPTION data dictionary view. If Oracle Database Vault is enabled, the query returns TRUE. Otherwise, it returns FALSE.

Remember that the PARAMETER column value is case sensitive. For example:

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER                                                                   VALUE
---------------------------------------------------------------- ---------------
Oracle Database Vault                                                      TRUE

In the next blog post i will write about Oracle database Vault Access Control Components, changes happened after Database Vault installation and management of Database Vault.

Using Password Files


Password files allow you to set passwords that are stored outside the database and that are used for authenticating administrators. These passwords are stored in an external file that is encrypted by Oracle. Password files can be even used if the database is down-so. You can use this passwords even if database is down .

To create a password file use the ORAPWD utility:

[oracle@orcl ~]$ orapwd file=./test_pwd entries=100 ignorecase=n
Enter password for SYS: 
[oracle@orcl ~]$ 

After creating the password file, you must set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to an appropriate value (  NONE, EXCLUSIVE and SHARED ).
NONE-causing Oracle to behave as though a password file does not exist. EXCLUSIVE means that the password file is being used only by your database and that you can modify it from within the database . SHARED-allows you to use a single password file for multiple databases, but none of them can update the password file. If you need to update the password file then you need to switch this parameter to EXCLUSIVE in one of the databases, change the password file and then change it back to be used as SHARED.

SQL> show parameter remote_login_passwordfile;

NAME				        TYPE	   VALUE
--------------------------  ----------- ---------------
remote_login_passwordfile	  string	   EXCLUSIVE

Now try connect to oracle as sysdba from other machine

C:UsersValeh>sqlplus sys@db11g as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 5 00:17:21 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:

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

SQL>

OS Authentication


OS authentication is used to  authenticate user  at the OS level to connect  to the database. For OS authentication OS username must be mapped to the Oracle user name. The initialization parameter OS_AUTHENT_PREFIX controls that mapping. This value is concatenated with the OS username and used as the database user. Default value for OS_AUTHENT_PREFIX parameter ops$. If the default value is not changed, then database user of the OS user valeh will be ops$valeh.If this value is set to null then you will log onto the database as user valeh.

SQL> show parameter os_authent_prefix;

NAME				  TYPE	 VALUE
-------------------- ---------- -------------
os_authent_prefix     string	 ops$
SQL> 

To allow access from OS users, you must enable remote OS authentication, create a user and grant privileges :

--Fisrt create OS user for testing

[root@orcl ~]# useradd valeh
[root@orcl ~]# passwd valeh
Changing password for user valeh.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

SQL> show parameter remote_os_authent;
NAME 		      	TYPE 		VALUE
------------------ ----------- ----------------
remote_os_authent     boolean	FALSE

SQL> alter system set remote_os_authent=true scope=spfile;
SQL> shutdown
SQL> startup
 
SQL> show parameter remote_os_authent;
NAME			     TYPE	      VALUE
------------------  ---------- --------------
remote_os_authent    boolean     TRUE

SQL> create user ops$valeh identified externally;

User created.
SQL> grant create session to ops$valeh;

Grant succeeded.
 

Log in to the new created user, set oracle environment variables and run sqlplus, you don’t need to specify a username or a password to logon to Oracle:

[root@orcl ~]# su - valeh
[valeh@orcl ~]$ sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 2 19:56:37 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "OPS$VALEH"