RMAN Database Restore from ASM to File System

RMAN Database Restore from ASM (NON-RAC) TO File System (NON-RAC)

0. Environment

On Source Server

1. Backup database
2. Create pfile from spfile
3. Push backup files/pfile to target server

On Target Server

4. Edit pfile
5. Create Required Folders
6. Add the entry in oratab
7. Startup Nomount
8. Restore controlfile
9. Mount database
10. Catalog backup pieces
11. Restore/recover database
12. Change dbname/DBID uisng NID
13. Modify DB_NAME in init file and start the database
14. Add TNS entry
15. Verify


0. Environment

Source: DB: prpt,  Diskgroup: DATA    Node: RAC1, DB Version: 11.2.0.3
Target: DB: TEST,  File System: /u01  Node: RAC2, DB Version: 11.2.0.3

On Source SERVER
==================

Step 1: Backup database w148p – source

[oracle@rac1 ~]$ cat open0.rcv
run {
allocate channel t1 type disk;
backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s';
release channel t1;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
backup archivelog all format '/u01/share/backup/arch_%d_%u_%s';
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u01/share/backup/Control_%d_%u_%s';
release channel c1;
}
exit
[oracle@rac1 ~]$


[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [prpt] ?
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 17:11:07 2016

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

connected to target database: PRPT (DBID=3870074951)

RMAN> @open0.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> backup incremental level 0 database format '/u01/share/backup/database_%d_%u_%s';
4> release channel t1;
5> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=36 device type=DISK

Starting backup at 06-OCT-16
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA1/prpt/datafile/system.258.924538565
input datafile file number=00002 name=+DATA1/prpt/datafile/sysaux.257.924538565
input datafile file number=00005 name=+DATA1/prpt/datafile/example.256.924538565
input datafile file number=00003 name=+DATA1/prpt/datafile/undotbs1.260.924538565
input datafile file number=00004 name=+DATA1/prpt/datafile/users.263.924538565
channel t1: starting piece 1 at 06-OCT-16
channel t1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/database_PRPT_0drhmou2_13 tag=TAG20161006T171130 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:25
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel t1: starting piece 1 at 06-OCT-16
channel t1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/database_PRPT_0erhmour_14 tag=TAG20161006T171130 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-16

released channel: t1

RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format '/u01/share/backup/arch_%d_%u_%s';
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=36 device type=DISK

Starting backup at 06-OCT-16
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=924541640
input archived log thread=1 sequence=2 RECID=2 STAMP=924541917
input archived log thread=1 sequence=3 RECID=3 STAMP=924541917
channel a1: starting piece 1 at 06-OCT-16
channel a1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/arch_PRPT_0frhmouu_15 tag=TAG20161006T171157 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-16

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format '/u01/share/backup/Control_%d_%u_%s';
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=36 device type=DISK

Starting backup at 06-OCT-16
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 06-OCT-16
channel c1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/Control_PRPT_0grhmouv_16 tag=TAG20161006T171159 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 06-OCT-16

released channel: c1

RMAN> exit

Recovery Manager complete.
[oracle@rac1 ~]$


Step 2: Create pfile from spfile

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRPT      READ WRITE

SQL> create pfile='/home/oracle/initTEST.ora' from spfile;

File created.

SQL>


Step 3: Push backup files/pfile to target server

[oracle@rac1 ~]$ cd /u01/share/backup/
[oracle@rac1 backup]$ scp * oracle@rac2:/u01/share/backup/prpt
arch_PRPT_0frhmouu_15            100% 2002KB   2.0MB/s   00:00
Control_PRPT_0grhmouv_16         100% 9568KB   9.3MB/s   00:00
database_PRPT_0drhmou2_13        100% 1082MB  40.1MB/s   00:27
database_PRPT_0erhmour_14        100% 9600KB   9.4MB/s   00:00
[oracle@rac1 backup]$

[oracle@rac1 ~]$ scp initTEST.ora oracle@rac2:/home/oracle
initTEST.ora     100%  877     0.9KB/s   00:00
[oracle@rac1 ~]$

On Target Server
=================

Step 4: Edit pfile initTEST.ora

[oracle@rac2 ~]$ cat initTEST.ora
TEST.__db_cache_size=419430400
TEST.__java_pool_size=16777216
TEST.__large_pool_size=16777216
TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TEST.__pga_aggregate_target=520093696
TEST.__sga_target=754974720
TEST.__shared_io_pool_size=0
TEST.__shared_pool_size=251658240
TEST.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prpt' <---
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$


Step 5: Create Required Folders

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/TEST/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/oradata/TEST


Step 6: Add the entry in oratab

[oracle@rac2 ~]$ cat /etc/oratab | grep -i TEST
TEST:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@rac2 ~]$


Step 7: Startup nomount

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [cat] ? TEST
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 17:43:53 2016

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

Connected to an idle instance.

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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initTEST.ora';

File created.

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


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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      
/u01/app/oracle/product/11.2.0/db_1/dbs/spfileTEST.ora
SQL>

[oracle@rac2 ~]$ ps -ef | grep pmon
oracle    3687     1  0 13:39 ?        00:00:01 asm_pmon_+ASM
oracle    3874     1  0 17:45 ?        00:00:00 ora_pmon_TEST
oracle    4006 32731  0 17:46 pts/1    00:00:00 grep pmon
[oracle@rac2 ~]$


Step 8: Restore controlfile

[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 17:46:55 2016

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

connected to target database: PRPT (not mounted)

RMAN> restore controlfile from '/u01/share/backup/prpt/Control_PRPT_0grhmouv_16';

Starting restore at 06-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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/TEST/control01.ctl
output file name=/u01/app/oracle/oradata/TEST/control02.ctl
Finished restore at 06-OCT-16

RMAN>


Step 9: Mount database

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN>

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PRPT      MOUNTED

SQL>


Step 10: Catalog the backup pieces

RMAN> catalog start with '/u01/share/backup/prpt/';

searching for all files that match the pattern /u01/share/backup/prpt/

List of Files Unknown to the Database
=====================================
File Name: /u01/share/backup/prpt/database_PRPT_0drhmou2_13
File Name: /u01/share/backup/prpt/arch_PRPT_0frhmouu_15
File Name: /u01/share/backup/prpt/database_PRPT_0erhmour_14
File Name: /u01/share/backup/prpt/Control_PRPT_0grhmouv_16

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

List of Cataloged Files
=======================
File Name: /u01/share/backup/prpt/database_PRPT_0drhmou2_13
File Name: /u01/share/backup/prpt/arch_PRPT_0frhmouu_15
File Name: /u01/share/backup/prpt/database_PRPT_0erhmour_14
File Name: /u01/share/backup/prpt/Control_PRPT_0grhmouv_16

RMAN>


Step 11: Restore database

RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size
------- ----------
3       1.95M

  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       1176811    06-OCT-16 1199528    06-OCT-16
..
  1    3       1200733    06-OCT-16 1200748    06-OCT-16 <-- 3
  
cat rman_recovery.rcv
  
run {
# allocate a channel to the tape device
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
ALLOCATE CHANNEL d2 DEVICE TYPE disk;
# rename the datafiles and online redo logs
SET NEWNAME FOR DATABASE   TO  '/u01/app/oracle/oradata/TEST/%b'; <-- 11g fewture
SET NEWNAME FOR tempfile  1 TO  '/u01/app/oracle/oradata/TEST/%b';
SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_3.264.924539539''
to ''/u01/app/oracle/oradata/TEST/group_3.264.924539539''" ;
SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_2.261.924539539''
to ''/u01/app/oracle/oradata/TEST/group_2.261.924539539''" ;
SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_1.262.924539537''
to ''/u01/app/oracle/oradata/TEST/group_1.262.924539537''" ;
# Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SEQUENCE 4; <--- 3 + 1
# restore the database and switch the datafile names
restore database;
switch datafile all;
switch tempfile all;
# recover the database
RECOVER DATABASE;
}
  

RMAN> @rman_recovery.rcv

RMAN> run {
2> # allocate a channel to the tape device
3> ALLOCATE CHANNEL d1 DEVICE TYPE disk;
4> ALLOCATE CHANNEL d2 DEVICE TYPE disk;
5> # rename the datafiles and online redo logs
6> SET NEWNAME FOR DATABASE   TO  '/u01/app/oracle/oradata/TEST/%b';
7> SET NEWNAME FOR tempfile  1 TO  '/u01/app/oracle/oradata/TEST/%b';
8> SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_3.264.924539539''
9> to ''/u01/app/oracle/oradata/TEST/group_3.264.924539539''" ;
10> SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_2.261.924539539''
11> to ''/u01/app/oracle/oradata/TEST/group_2.261.924539539''" ;
12> SQL "ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_1.262.924539537''
13> to ''/u01/app/oracle/oradata/TEST/group_1.262.924539537''" ;
14> # Do a SET UNTIL to prevent recovery of the online logs
15> SET UNTIL SEQUENCE 4;
16> # restore the database and switch the datafile names
17> restore database;
18> switch datafile all;
19> switch tempfile all;
20> # recover the database
21> RECOVER DATABASE;
22> }
allocated channel: d1
channel d1: SID=26 device type=DISK

allocated channel: d2
channel d2: SID=24 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

sql statement: ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_3.264.924539539''to ''/u01/app/oracle/oradata/TEST/group_3.264.924539539''

sql statement: ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_2.261.924539539''to ''/u01/app/oracle/oradata/TEST/group_2.261.924539539''

sql statement: ALTER DATABASE RENAME FILE ''+DATA1/prpt/onlinelog/group_1.262.924539537''to ''/u01/app/oracle/oradata/TEST/group_1.262.924539537''

executing command: SET until clause

Starting restore at 06-OCT-16

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/system.258.924538565
channel d1: restoring datafile 00002 to /u01/app/oracle/oradata/TEST/sysaux.257.924538565
channel d1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/undotbs1.260.924538565
channel d1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/users.263.924538565
channel d1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/example.256.924538565
channel d1: reading from backup piece /u01/share/backup/database_PRPT_0drhmou2_13
channel d1: errors found reading piece handle=/u01/share/backup/database_PRPT_0drhmou2_13
channel d1: failover to piece handle=/u01/share/backup/prpt/database_PRPT_0drhmou2_13 tag=TAG20161006T171130
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:01:25
Finished restore at 06-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=924545382 file name=/u01/app/oracle/oradata/TEST/system.258.924538565
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=924545382 file name=/u01/app/oracle/oradata/TEST/sysaux.257.924538565
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=924545382 file name=/u01/app/oracle/oradata/TEST/undotbs1.260.924538565
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=924545382 file name=/u01/app/oracle/oradata/TEST/users.263.924538565
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=924545382 file name=/u01/app/oracle/oradata/TEST/example.256.924538565

renamed tempfile 1 to /u01/app/oracle/oradata/TEST/temp.291.924538685 in control file

Starting recover at 06-OCT-16

starting media recovery

channel d1: starting archived log restore to default destination
channel d1: restoring archived log
archived log thread=1 sequence=2
channel d1: restoring archived log
archived log thread=1 sequence=3
channel d1: reading from backup piece /u01/share/backup/arch_PRPT_0frhmouu_15
channel d1: errors found reading piece handle=/u01/share/backup/arch_PRPT_0frhmouu_15
channel d1: failover to piece handle=/u01/share/backup/prpt/arch_PRPT_0frhmouu_15 tag=TAG20161006T171157
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/prpt/archivelog/2016_10_06/thread_1_seq_2.450.924545383 thread=1 sequence=2
archived log file name=+FRA/prpt/archivelog/2016_10_06/thread_1_seq_3.451.924545383 thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-16
released channel: d1
released channel: d2

RMAN> **end-of-file**

RMAN> sql 'alter database open';

sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 10/06/2016 18:10:27
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> sql 'alter database open RESETLOGS';

sql statement: alter database open RESETLOGS

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/group_3.264.924539539
/u01/app/oracle/oradata/TEST/group_2.261.924539539
/u01/app/oracle/oradata/TEST/group_1.262.924539537

SQL>


SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                       DBID
--------- -------------------- ------------------------------ ----------
PRPT      READ WRITE           prpt                           3870074951

SQL>


Step 12: Change Database name and DB ID

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
Database mounted.
SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [TEST] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@rac2 ~]$ nid target=sys dbname=TEST

DBNEWID: Release 11.2.0.3.0 - Production on Thu Oct 6 18:16:38 2016

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

Password:
Connected to database PRPT (DBID=3870074951)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/TEST/control01.ctl
    /u01/app/oracle/oradata/TEST/control02.ctl

Change database ID and database name PRPT to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3870074951 to 2222709898
Changing database name from PRPT to TEST
    Control File /u01/app/oracle/oradata/TEST/control01.ctl - modified
    Control File /u01/app/oracle/oradata/TEST/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/TEST/system.258.92453856 - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/TEST/sysaux.257.92453856 - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/TEST/undotbs1.260.92453856 - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/TEST/users.263.92453856 - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/TEST/example.256.92453856 - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/TEST/temp.291.92453868 - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/TEST/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/TEST/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2222709898.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

[oracle@rac2 ~]$

Please note database will go down automatically


Step 13: Change DB_NAME and start the database

[oracle@rac2 ~]$ cat initTEST.ora
TEST.__db_cache_size=419430400
TEST.__java_pool_size=16777216
TEST.__large_pool_size=16777216
TEST.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TEST.__pga_aggregate_target=520093696
TEST.__sga_target=754974720
TEST.__shared_io_pool_size=0
TEST.__shared_pool_size=251658240
TEST.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/TEST/control01.ctl','/u01/app/oracle/oradata/TEST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='TEST' <---
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@rac2 ~]$

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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initTEST.ora';

File created.

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


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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                       DBID
--------- -------------------- ------------------------------ ----------
TEST      READ WRITE           TEST                           2222709898

SQL> SELECT NAME FROM V$TEMPFILE;

NAME
-------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/temp.291.924538685

SQL>


Step 14: Add TNS entry

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

[oracle@rac2 admin]$ tnsping TEST

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-OCT-2016 18:21:42

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (60 msec)
[oracle@rac2 admin]$


Step 15: Verify

[oracle@rac2 ~]$ ps -ef | grep tns
root         9     2  0 13:36 ?        00:00:00 [netns]
oracle    3421     1  0 13:38 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    5018  3554  0 18:22 pts/2    00:00:00 grep tns
[oracle@rac2 ~]$ lsnrctl status LISTENER | grep -i TEST
Service "TEST" has 1 instance(s).
  Instance "TEST", status READY, has 1 handler(s) for this service...
Service "TESTXDB" has 1 instance(s).
  Instance "TEST", status READY, has 1 handler(s) for this service...
[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus scott/tiger@TEST

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 18:22:52 2016

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


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

SQL>

Please create password file in $ORACLE_HOME/dbs/ for this database.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.