Thursday, September 4, 2014

RMAN>Restore RAC Dataguard RMAN backup to a new server (Non-ASM) !

Need to remember (From Dataguard end rman backup ) :

 db_name = isldb
 db_unique_name = isldg

Always take the backup stopping the 'SYNC' command from Dataguard end .


Restore RAC Dataguard RMAN backup to a new server :

 - Take the RMAN full database backup .

[BACKUP SCRIPT ]

    ----
#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=isldg
TODAY=`date '+%Y%m%d_%H%M%S'`
LOG_FILE=/cloudfs/rmanback/isl/$TODAY/${ORACLE_SID}_FULL_${TODAY}.log
mkdir -p /cloudfs/rmanback/isl/$TODAY

 sh /home/oracle/stop_isl.sh

 echo "isldb Level 0 Backup started at `date` from node $HOSTNAME" >$LOG_FILE
rman <<! >>$LOG_FILE
connect target /
run{
allocate channel d1  device type disk format '/cloudfs/rmanback/isl/$TODAY/%t_%s_%r.dbf';
allocate channel d2  device type disk format '/cloudfs/rmanback/isl/$TODAY/%t_%s_%r.dbf';
backup CHECK LOGICAL AS COMPRESSED BACKUPSET incremental level 0 database;
backup format '/cloudfs/rmanback/isl/$TODAY/spfile.spbak'
  (spfile);
backup archivelog all;
backup format '/cloudfs/rmanback/isl/$TODAY/controlfile.ctlbk'
  (current controlfile);
release channel d1;
release channel d2;
}
!
sh /home/oracle/start_isl.sh
echo "isldb Level 0 Backup finished at `date` from node $HOSTNAME" >>$LOG_FILE
exit 0
-----

 ---
[oracle@droda1 ~]$ cat start_isl.sh 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
export ORACLE_SID=isldg

 $ORACLE_HOME/bin/sqlplus / as sysdba <<!
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
EXIT;

 [oracle@droda1 ~]$ cat stop_isl.sh 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
PATH=$HOME/bin:/u01/app/oracle/product/11.2.0.3/dbhome_1/bin:.:/usr/local/bin:/bin:/usr/bin
export PATH
export ORACLE_SID=isldg

 $ORACLE_HOME/bin/sqlplus / as sysdba <<!
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
EXIT;
-----

[BACKUP SCRIPT ]

  - Transfer the backup with the logfile to remote server on the SAME LOCATION .



Step 1 : Create pfile for ISLDB database :

[oracle@PRIM ~]$ cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldg/control01.ctl','/u01/app/oracle/oradata/isldg/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE



Step 2:

   [oracle@PRIM dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 28 15:22:46 2014

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

Connected to an idle instance.

SQL> startup nomount pfile='initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
SQL>


Step 3 : Restore the Controlfile

[oracle@PRIM dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 28 15:30:33 2014

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

connected to target database: ISLDB (not mounted)

RMAN> restore controlfile from '/cloudfs/rmanback/isl/20140827_172834/controlfile.ctlbk';

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 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/product/11.2.0/db_1/dbs/cntrlisldb.dbf
Finished restore at 28-AUG-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>


Step 4 : Restore all the datafiles :

  1)

RMAN> run {
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldg/system1.dbf' ;
restore datafile 1 ;
}2> 3> 4>

executing command: SET NEWNAME

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK

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 00001 to /u01/app/oracle/oradata/isldg/system1.dbf
channel ORA_DISK_1: reading from backup piece /cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf
channel ORA_DISK_1: piece handle=/cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf tag=TAG20140827T172835
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 28-AUG-14

RMAN>

2)

run {
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldg/sysaux1.dbf' ;
restore datafile 2 ;
}


3)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldg/undotbs1.dbf' ;
restore datafile 3 ;
}


4)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldg/undotbs2.dbf' ;
restore datafile 4 ;
}


5)

run {
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldg/users1.dbf' ;
restore datafile 5 ;
}


6)

run {
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf';
restore datafile 6 ;
}


7)

run {
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf';
restore datafile 7;
}


8)

run {
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf';
restore datafile 8 ;
}


9)

run {
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf';
restore datafile 9 ;
}


10)

run {
set newname for datafile '+DATA/isldg/tempfile/temp.301.849513611'  to '/u01/app/oracle/oradata/isldg/temp1.dbf';
restore datafile 10 ;
}


Step 5 :

  Run the SWITCH command :

run
{
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldg/system1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldg/sysaux1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldg/undotbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldg/undotbs2.dbf' ;
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldg/users1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/isldb/temp1.dbf' ;
switch datafile all;
switch tempfile all;
}





OR ,

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

RUN
{
set newname for datafile 1 to '/u01/app/oracle/oradata/isldg/system1.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/isldg/sysaux1.dbf' ;
set newname for datafile 3 to  '/u01/app/oracle/oradata/isldg/undotbs1.dbf';
set newname for datafile 4 to  '/u01/app/oracle/oradata/isldg/undotbs2.dbf';
set newname for datafile 5 to  '/u01/app/oracle/oradata/isldg/users1.dbf' ;
set newname for datafile 6 to '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf' ;
set newname for datafile 7 to '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf' ;
set newname for datafile 8 to '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf' ;
set newname for datafile 9 to '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/isldg/temp1.dbf' ;
restore database;
switch datafile all;
}


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




Step 3 :  Recover the Database  [PROCEDURE : A]
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'

-------



Solution :

I manually put the archivelog to '/u01/app/oracle/product/11.2.0/db_1/dbs/thread_1_seq_1000.652.856710149'
directory and rename to is as 'arch1_1000_839681983.dbf' .

After that it checks , 'arch2_771_839681983.dbf' . I put it manually also



 ---
ERROR :
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 10219039198 generated at 08/27/2014 11:00:42 needed for
thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf
ORA-00280: change 10219039198 for thread 2 is in sequence #771


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'

.
.
.
And so on . And After some few logs , make cancel :
-----

SQL>  recover database until cancel using backup controlfile;
ORA-00279: change 10230768222 generated at 08/27/2014 19:01:58 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1002_839681983.dbf
ORA-00280: change 10230768222 for thread 1 is in sequence #1002


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
--


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+red



Step 4 : Open the Database


During open got the below error :

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 2 is being cleared, operation not allowed
ORA-00312: online log 2 thread 2: '+redo'


Solution :

 a) Backup the controlfile from the dataguard host machine  and recreate it like below :

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control.sql' resetlogs ;

Database altered.


 b) Edit the controlfile :

   [oracle@PRIM ~]$ cat control.sql
CREATE CONTROLFILE REUSE DATABASE "ISLDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/isldg/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/isldg/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/isldg/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/isldg/system1.dbf',
  '/u01/app/oracle/oradata/isldg/sysaux1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs2.dbf',
  '/u01/app/oracle/oradata/isldg/users1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf'
CHARACTER SET WE8ISO8859P1
;


SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


---
[oracle@PRIM ~]$ vi initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE
--


SQL> startup nomount pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
               4706304 bytes
SQL> @/home/oracle/control.sql

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED



SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled




Step 5 :

 Open the database with the "_no_recovery_through_resetlogs=TRUE"

 cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
_no_recovery_through_resetlogs=TRUE


SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.


Step 9 : Create TEMP File :

SQL>  create temporary tablespace TEMP1  tempfile '/u01/app/oracle/oradata/isldg/temp01.dbf' size 100M;

Tablespace created.

SQL> alter DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/isldg/temp01.dbf


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile='/home/oracle/initisldb.ora';

File created.


Step 10 : RESTART DATABASE :

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
Database opened.


Fianlly , Change the required Parameter Like : 'sga_target' ,'log_archive' etc ....


Step 3 :  Recover the Database  [PROCEDURE  B]

If archivelog is not avaiable then follow the below steps :

 1)

 SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldb/system1.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10276863887 generated at 08/31/2014 18:11:50 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1029_839681983.dbf
ORA-00280: change 10276863887 for thread 1 is in sequence #1029


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldb/system1.dbf'


ORA-01112: media recovery not started

Solution :

[oracle@PRIM ~]$ vi initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE
~



For logfile missing ,
Open the database using above pfile (_allow_resetlogs_corruption = true)  but again during open got the below error :

SQL> startup pfile='/home/oracle/initisldb.ora';
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled



[oracle@PRIM ~]$ vi initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
_allow_resetlogs_corruption = true
_no_recovery_through_resetlogs=TRUE


Now, Open the database using above pfile (_no_recovery_through_resetlogs=TRUE)  :


SQL> startup mount pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.


SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
isldb            OPEN



Finally , create spfile from the pfile=/home/oracle/initisldb.ora



Step 9 :Create Temp File :

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/isldb/temp1.dbf' size 100M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 SYSAUX                         YES NO  YES
         2 UNDOTBS1                       YES NO  YES
         4 UNDOTBS2                       YES NO  YES
         5 USERS                          YES NO  YES
         6 BU_SYSTEM_TBS                  YES NO  YES
         7 BU_HIS_LOG_TBS                 YES NO  YES
         8 BU_INDEX_TBS                   YES NO  YES
         9 BU_IMAGE_TBS                   YES NO  YES
         3 TEMP                           NO  NO  YES
        10 TEMP1                          NO  NO  YES

11 rows selected.

SQL>  drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.








========================

USING  'PRIM' End RMAN Backup :


Need to remember (From Primary HOST end rman backup ) :

 db_name = isldb
 db_unique_name = isldb


Restore RAC Dataguard RMAN backup to a new server :

 - Take the RMAN full database backup .

 - Transfer the backup with the logfile to remote server on the SAME LOCATION .



Step 1 : Create pfile for ISLDB database :

[oracle@PRIM ~]$ cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE



Step 2:

   [oracle@PRIM dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 28 15:22:46 2014

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

Connected to an idle instance.

SQL> startup nomount pfile='initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
SQL>


Step 3 : Restore the Controlfile

[oracle@PRIM dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 28 15:30:33 2014

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

connected to target database: ISLDB (not mounted)

RMAN> restore controlfile from '/cloudfs/rmanback/isl/20140827_172834/controlfile.ctlbk';

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 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/product/11.2.0/db_1/dbs/cntrlisldb.dbf
Finished restore at 28-AUG-14

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>


Step 4 : Restore all the datafiles :

  1)

RMAN> run {
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldg/system1.dbf' ;
restore datafile 1 ;
}2> 3> 4>

executing command: SET NEWNAME

Starting restore at 28-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK

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 00001 to /u01/app/oracle/oradata/isldg/system1.dbf
channel ORA_DISK_1: reading from backup piece /cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf
channel ORA_DISK_1: piece handle=/cloudfs/rmanback/isl/20140827_172834/856718916_67_%r.dbf tag=TAG20140827T172835
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 28-AUG-14

RMAN>

2)

run {
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldg/sysaux1.dbf' ;
restore datafile 2 ;
}


3)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldg/undotbs1.dbf' ;
restore datafile 3 ;
}


4)

run {
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldg/undotbs2.dbf' ;
restore datafile 4 ;
}


5)

run {
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldg/users1.dbf' ;
restore datafile 5 ;
}


6)

run {
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf';
restore datafile 6 ;
}


7)

run {
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf';
restore datafile 7;
}


8)

run {
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf';
restore datafile 8 ;
}


9)

run {
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf';
restore datafile 9 ;
}


10)

run {
set newname for datafile '+DATA/isldg/tempfile/temp.301.849513611'  to '/u01/app/oracle/oradata/isldg/temp1.dbf';
restore datafile 10 ;
}


Step 5 :

  Run the SWITCH command :

run
{
set newname for datafile '+DATA/isldg/datafile/system.298.849413815'  to  '/u01/app/oracle/oradata/isldg/system1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/sysaux.295.849413813'  to  '/u01/app/oracle/oradata/isldg/sysaux1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs1.296.849413813'  to  '/u01/app/oracle/oradata/isldg/undotbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/undotbs2.297.849413815'  to  '/u01/app/oracle/oradata/isldg/undotbs2.dbf' ;
set newname for datafile '+DATA/isldg/datafile/users.299.849413849'  to  '/u01/app/oracle/oradata/isldg/users1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_system_tbs.291.849413617'  to '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_his_log_tbs.292.849413617'  to '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_index_tbs.293.849413617'  to '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf' ;
set newname for datafile '+DATA/isldg/datafile/bu_image_tbs.294.849413619'  to '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/isldb/temp1.dbf' ;
switch datafile all;
switch tempfile all;
}



OR,

----------------------------------------
RUN
{
set newname for datafile 1 to '/u01/app/oracle/oradata/isldb/system1.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/isldb/sysaux1.dbf' ;
set newname for datafile 3 to  '/u01/app/oracle/oradata/isldb/undotbs1.dbf';
set newname for datafile 4 to  '/u01/app/oracle/oradata/isldb/undotbs2.dbf';
set newname for datafile 5 to  '/u01/app/oracle/oradata/isldb/users1.dbf' ;
set newname for datafile 6 to '/u01/app/oracle/oradata/isldb/bu_system_tbs1.dbf' ;
set newname for datafile 7 to '/u01/app/oracle/oradata/isldb/bu_his_log_tbs1.dbf' ;
set newname for datafile 8 to '/u01/app/oracle/oradata/isldb/bu_index_tbs1.dbf' ;
set newname for datafile 9 to '/u01/app/oracle/oradata/isldb/bu_image_tbs1.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/isldb/temp1.dbf' ;
restore database;
switch datafile all;
}


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

Step 6 :  Recover the Database :

Error :--------

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'

-------



Solution :

I manually put the archivelog to '/u01/app/oracle/product/11.2.0/db_1/dbs/thread_1_seq_1000.652.856710149'
directory and rename to is as 'arch1_1000_839681983.dbf' .

After that it checks , 'arch2_771_839681983.dbf' . I put it manually also



 ---
ERROR :
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 10219039198 generated at 08/27/2014 11:05:14 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1000_839681983.dbf
ORA-00280: change 10219039198 for thread 1 is in sequence #1000


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 10219039198 generated at 08/27/2014 11:00:42 needed for
thread 2
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf
ORA-00280: change 10219039198 for thread 2 is in sequence #771


ORA-00308: cannot open archived log
'/u01/app/oracle/product/11.2.0/db_1/dbs/arch2_771_839681983.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/isldg/system1.dbf'

.
.
.
And so on . And After some few logs , make cancel :
-----

SQL>  recover database until cancel using backup controlfile;
ORA-00279: change 10230768222 generated at 08/27/2014 19:01:58 needed for
thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_1002_839681983.dbf
ORA-00280: change 10230768222 for thread 1 is in sequence #1002


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
--


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+red



Step 7 : Open the Database


During open got the below error :

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 2 of thread 2 is being cleared, operation not allowed
ORA-00312: online log 2 thread 2: '+redo'


Sol :

 a) Backup the logfile and recreate it like below :

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/control.sql' resetlogs ;

Database altered.


 b) Edit the controlfile :

   [oracle@PRIM ~]$ cat control.sql
CREATE CONTROLFILE REUSE DATABASE "ISLDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/isldb/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/isldb/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/isldb/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/isldb/system1.dbf',
  '/u01/app/oracle/oradata/isldb/sysaux1.dbf',
  '/u01/app/oracle/oradata/isldb/undotbs1.dbf',
  '/u01/app/oracle/oradata/isldb/undotbs2.dbf',
  '/u01/app/oracle/oradata/isldb/users1.dbf',
  '/u01/app/oracle/oradata/isldb/bu_system_tbs1.dbf',
  '/u01/app/oracle/oradata/isldb/bu_his_log_tbs1.dbf',
  '/u01/app/oracle/oradata/isldb/bu_index_tbs1.dbf',
  '/u01/app/oracle/oradata/isldb/bu_image_tbs1.dbf'
CHARACTER SET WE8ISO8859P1
;


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


---
[oracle@PRIM ~]$ vi initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
#_no_recovery_through_resetlogs=TRUE
--


SQL> startup nomount pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
               4706304 bytes
SQL> @/home/oracle/control.sql

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED



SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38856: cannot mark instance UNNAMED_INSTANCE_2 (redo thread 2) as enabled




Step 8 :

 Open the database with the "_no_recovery_through_resetlogs=TRUE"

 cat initisldb.ora
isldb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.compatible='11.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/isldb/control01.ctl','/u01/app/oracle/oradata/isldb/control02.ctl'#Restore Controlfile
*.DB_NAME='isldb'
#_allow_resetlogs_corruption = true
_no_recovery_through_resetlogs=TRUE


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/initisldb.ora'
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.


Step 9 : Create TEMP File :

SQL>  create temporary tablespace TEMP1  tempfile '/u01/app/oracle/oradata/isldg/temp01.dbf' size 100M;

Tablespace created.

SQL> alter DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Database altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/isldg/temp01.dbf


SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile from pfile='/home/oracle/initisldb.ora';

File created.


Step 10 : RESTART DATABASE :

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes
Database mounted.
Database opened.


Fianlly , Change the required Parameter Like : 'sga_target' ,'log_archive' etc ....



                          ----controlfile example of pbldb------------

RUN
{
set newname for datafile 1 to '/u01/app/oracle/oradata/pbldb/system1.dbf' ;
set newname for datafile 2 to '/u01/app/oracle/oradata/pbldb/sysaux1.dbf' ;
set newname for datafile 3 to  '/u01/app/oracle/oradata/pbldb/undotbs1.dbf';
set newname for datafile 4 to  '/u01/app/oracle/oradata/pbldb/undotbs2.dbf';
set newname for datafile 5 to  '/u01/app/oracle/oradata/pbldb/users1.dbf' ;
set newname for datafile 6 to  '/u01/app/oracle/oradata/pbldb/users2.dbf' ;
set newname for datafile 25 to  '/u01/app/oracle/oradata/pbldb/users3.dbf' ;
set newname for datafile 26 to  '/u01/app/oracle/oradata/pbldb/users4.dbf' ;
set newname for datafile 7 to '/disk4/oradata/pbldb/bu_system_tbs1.dbf' ;
set newname for datafile 8 to '/disk4/oradata/pbldb/bu_system_tbs2.dbf' ;
set newname for datafile 9 to '/disk2/oradata/pbldb/bu_his_log_tbs1.dbf' ;
set newname for datafile 10 to '/disk2/oradata/pbldb/bu_his_log_tbs2.dbf' ;
set newname for datafile 11 to '/disk2/oradata/pbldb/bu_his_log_tbs3.dbf' ;
set newname for datafile 12 to '/disk2/oradata/pbldb/bu_his_log_tbs4.dbf' ;
set newname for datafile 13 to '/disk2/oradata/pbldb/bu_his_log_tbs5.dbf' ;
set newname for datafile 14 to '/disk2/oradata/pbldb/bu_his_log_tbs6.dbf' ;
set newname for datafile 15 to '/disk2/oradata/pbldb/bu_his_log_tbs7.dbf' ;
set newname for datafile 16 to '/disk2/oradata/pbldb/bu_his_log_tbs8.dbf' ;
set newname for datafile 17 to '/disk2/oradata/pbldb/bu_his_log_tbs9.dbf' ;
set newname for datafile 18 to '/disk2/oradata/pbldb/bu_his_log_tbs10.dbf' ;
set newname for datafile 19 to '/disk2/oradata/pbldb/bu_his_log_tbs11.dbf' ;
set newname for datafile 20 to '/disk4/oradata/pbldb/bu_index_tbs1.dbf' ;
set newname for datafile 21 to '/disk4/oradata/pbldb/bu_index_tbs2.dbf' ;
set newname for datafile 22 to '/disk4/oradata/pbldb/bu_index_tbs3.dbf' ;
set newname for datafile 23 to '/disk4/oradata/pbldb/bu_index_tbs4.dbf' ;
set newname for datafile 24 to '/u01/app/oracle/oradata/pbldb/bu_image_tbs5.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/pbldb/temp1.dbf' ;
restore database;
switch datafile all;
}





CREATE CONTROLFILE REUSE DATABASE "ISLDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/isldg/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/isldg/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/isldg/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/isldg/system1.dbf',
  '/u01/app/oracle/oradata/isldg/sysaux1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs1.dbf',
  '/u01/app/oracle/oradata/isldg/undotbs2.dbf',
  '/u01/app/oracle/oradata/isldg/users1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_system_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_his_log_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_index_tbs1.dbf',
  '/u01/app/oracle/oradata/isldg/bu_image_tbs1.dbf'
CHARACTER SET WE8ISO8859P1
;





CREATE CONTROLFILE REUSE DATABASE "PBLDB" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 4672
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/pbldb/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/pbldb/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/pbldb/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/pbldb/system1.dbf',
  '/u01/app/oracle/oradata/pbldb/sysaux1.dbf' ,
  '/u01/app/oracle/oradata/pbldb/undotbs1.dbf',
  '/u01/app/oracle/oradata/pbldb/undotbs2.dbf',
  '/u01/app/oracle/oradata/pbldb/users1.dbf' ,
  '/u01/app/oracle/oradata/pbldb/users2.dbf' ,
  '/u01/app/oracle/oradata/pbldb/users3.dbf' ,
  '/u01/app/oracle/oradata/pbldb/users4.dbf' ,
  '/disk4/oradata/pbldb/bu_system_tbs1.dbf' ,
  '/disk4/oradata/pbldb/bu_system_tbs2.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs1.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs2.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs3.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs4.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs5.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs6.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs7.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs8.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs9.dbf' ,
  '/disk2/oradata/pbldb/bu_his_log_tbs10.dbf',
  '/disk2/oradata/pbldb/bu_his_log_tbs11.dbf',
  '/disk4/oradata/pbldb/bu_index_tbs1.dbf',
  '/disk4/oradata/pbldb/bu_index_tbs2.dbf',
  '/disk4/oradata/pbldb/bu_index_tbs3.dbf',
  '/disk4/oradata/pbldb/bu_index_tbs4.dbf',
  '/u01/app/oracle/oradata/pbldb/bu_image_tbs5.dbf'
CHARACTER SET WE8ISO8859P1
;

-----------------------------END----------------------


Posted by at No comments:

Tuesday, September 2, 2014

Securing the Listener



Setting Listener Password :

step 1 :

  1.  Stop the listener.
  2.  Add the following parameter in the listener.ora: LOCAL_OS_AUTHENTICATION_listener_name = OFF
  3.  Restart the listener



Step 2 :

 set the password using lsnrctl :

   [oracle@pbltest ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-SEP-2014 16:29:32

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully
LSNRCTL>
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521)))
No changes to save for LISTENER.
The command completed successfully
LSNRCTL> exit


Step 3 :

 Check the below entry on the listener.ora file :

  LOCAL_OS_AUTHENTICATION_listener = OFF

#----ADDED BY TNSLSNR 02-SEP-2014 16:30:00---
PASSWORDS_LISTENER = ADD733DA61CD19A5


step 4 : Bounce the Listener .

[oracle@pbltest admin]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 02-SEP-2014 16:32:30

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521)))
TNS-01169: The listener has not recognized the password


Step 5 : How to access to LISTENER


 using 'set password' command .


Metalink Note : Password Not Required When Administering 10g or Newer Listeners Using Lsnrctl Utility (Doc ID 372717.1)
Posted by at No comments:
Subscribe to: Posts (Atom)