RMAN Database Restore ASM – ASM

RMAN Database Restore from ASM (NON-RAC) TO ASM (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: w148p, Diskgroup: DATA   Node: RAC2
Target: DB: prpt,  Diskgroup: DATA1  Node" RAC1

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@rac2 ~]$ . oraenv
ORACLE_SID = [w148p] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$
[oracle@rac2 ~]$ vi open0.rcv
[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 15:00:56 2016

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

connected to target database: W148P (DBID=3175693255)

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=23 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=+DATA/w148p/datafile/system.260.906753423
input datafile file number=00002 name=+DATA/w148p/datafile/sysaux.259.906753425
input datafile file number=00005 name=+DATA/w148p/datafile/example.278.923245569
input datafile file number=00003 name=+DATA/w148p/datafile/undotbs1.258.906753425
input datafile file number=00004 name=+DATA/w148p/datafile/users.256.906753425
channel t1: starting piece 1 at 06-OCT-16
channel t1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/database_W148P_08rhmh9g_8 tag=TAG20161006T150104 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:35
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_W148P_09rhmhaj_9 tag=TAG20161006T150104 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=23 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=2 RECID=1 STAMP=917627829
input archived log thread=1 sequence=3 RECID=2 STAMP=917640006
input archived log thread=1 sequence=4 RECID=3 STAMP=917697258
input archived log thread=1 sequence=5 RECID=4 STAMP=917732523
input archived log thread=1 sequence=6 RECID=5 STAMP=923244820
input archived log thread=1 sequence=7 RECID=6 STAMP=923245696
input archived log thread=1 sequence=8 RECID=7 STAMP=923263242
input archived log thread=1 sequence=9 RECID=8 STAMP=924518116
input archived log thread=1 sequence=10 RECID=9 STAMP=924534102
input archived log thread=1 sequence=11 RECID=10 STAMP=924534103
channel a1: starting piece 1 at 06-OCT-16
channel a1: finished piece 1 at 06-OCT-16
piece handle=/u01/share/backup/arch_W148P_0arhmhan_10 tag=TAG20161006T150143 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:07
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=23 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_W148P_0brhmhau_11 tag=TAG20161006T150150 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@rac2 ~]$


Step 2: Create pfile from spfile

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W148P     READ WRITE

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

File created.

SQL>


Step 3: Push backup files/pfile to target server

[oracle@rac2 ~]$ cd /u01/share/backup/
[oracle@rac2 backup]$ scp * oracle@rac1:/u01/share/backup/
arch_W148P_0arhmhan_10        100%  182MB  45.6MB/s   00:04
Control_W148P_0brhmhau_11     100% 9568KB   9.3MB/s   00:00
database_W148P_08rhmh9g_8     100% 1060MB  20.0MB/s   00:53
database_W148P_09rhmhaj_9     100% 9600KB   4.7MB/s   00:02
[oracle@rac2 backup]$         

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

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

Step 4: Edit pfile initprpt.ora

[oracle@rac1 ~]$ cat initprpt.ora
prpt.__db_cache_size=419430400
prpt.__java_pool_size=16777216
prpt.__large_pool_size=16777216
prpt.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prpt.__pga_aggregate_target=520093696
prpt.__sga_target=754974720
prpt.__shared_io_pool_size=0
prpt.__shared_pool_size=251658240
prpt.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prpt/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA1','+FRA'
*.db_block_size=8192
#*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='w148p' <---- we will change db name later
*.DB_UNIQUE_NAME='prpt' <---
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prptXDB)'
*.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@rac1 ~]$


Step 5: Create Required Folders

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/prpt/adump
[oracle@rac1 ~]$


Step 6: Add the entry in oratab

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


Step 7: Startup nomount

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [orcl] ? prpt
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 15:29:45 2016

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initprpt.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> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      w148p
SQL> show parameter db_unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      prpt
SQL>

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle    3547     1  0 15:31 ?        00:00:00 ora_pmon_prpt <---
oracle    3821 24693  0 15:35 pts/1    00:00:00 grep pmon
oracle    3859     1  0 13:38 ?        00:00:00 asm_pmon_+ASM
[oracle@rac1 ~]$


Step 8: Restore controlfile

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 6 15:41:57 2016

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

connected to target database: W148P (not mounted)

RMAN> restore controlfile from '/u01/share/backup/Control_W148P_0brhmhau_11';

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=+DATA1/prpt/controlfile/current.259.924536529
output file name=+FRA/prpt/controlfile/current.443.924536529
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, db_unique_name from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME
--------- -------------------- ------------------------------
W148P     MOUNTED              prpt

SQL>


Step 10: Catalog the backup pieces

RMAN> CATALOG BACKUPPIECE '/u01/share/backup/arch_W148P_0arhmhan_10';

cataloged backup piece
backup piece handle=/u01/share/backup/arch_W148P_0arhmhan_10 RECID=15 STAMP=924537573

RMAN> CATALOG BACKUPPIECE '/u01/share/backup/Control_W148P_0brhmhau_11';

cataloged backup piece
backup piece handle=/u01/share/backup/Control_W148P_0brhmhau_11 RECID=16 STAMP=924537578

RMAN> CATALOG BACKUPPIECE '/u01/share/backup/database_W148P_08rhmh9g_8';

cataloged backup piece
backup piece handle=/u01/share/backup/database_W148P_08rhmh9g_8 RECID=17 STAMP=924537583

RMAN> CATALOG BACKUPPIECE '/u01/share/backup/database_W148P_09rhmhaj_9';

cataloged backup piece
backup piece handle=/u01/share/backup/database_W148P_09rhmhaj_9 RECID=18 STAMP=924537587

RMAN>


Step 11: Restore database

RMAN> list backup of archivelog all;


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14      182.39M    DISK        00:00:04     06-OCT-16
        BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: TAG20161006T150143
        Piece Name: /u01/share/backup/arch_W148P_0arhmhan_10

  List of Archived Logs in backup set 14
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       998841     17-MAR-16 1027575    19-JUL-16
..
..
  1    11      1176060    06-OCT-16 1176075    06-OCT-16 <-- 11

RMAN>

/* select file# from v$datafile;*/
/* SELECT 'SQL "ALTER DATABASE RENAME FILE '''''|| MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;' FROM V$LOGFILE; */

[oracle@rac1 ~]$ 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 datafile 1 to '+DATA1';
set newname for datafile 2 to '+DATA1';
set newname for datafile 3 to '+DATA1';
set newname for datafile 4 to '+DATA1';
set newname for datafile 5 to '+DATA1';
SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_3.282.906753483''
to ''+DATA1''" ;
SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_2.281.906753481''
to ''+DATA1''" ;
SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_1.279.906753479''
to ''+DATA1''" ;
# Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SEQUENCE 12; <--- 11+1
# restore the database and switch the datafile names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
# recover the database
 RECOVER DATABASE;
}

RMAN> @rman_recovery.rcv

RMAN> run
2> {
3> # allocate a channel to the tape device
4>  ALLOCATE CHANNEL d1 DEVICE TYPE disk;
5>  ALLOCATE CHANNEL d2 DEVICE TYPE disk;
6> # rename the datafiles and online redo logs
7> set newname for datafile 1 to '+DATA1';
8> set newname for datafile 2 to '+DATA1';
9> set newname for datafile 3 to '+DATA1';
10> set newname for datafile 4 to '+DATA1';
11> set newname for datafile 5 to '+DATA1';
12> SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_3.282.906753483''
13> to ''+DATA1''" ;
14> SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_2.281.906753481''
15> to ''+DATA1''" ;
16> SQL "ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_1.279.906753479''
17> to ''+DATA1''" ;
18> # Do a SET UNTIL to prevent recovery of the online logs
19> SET UNTIL SEQUENCE 12;
20> # restore the database and switch the datafile names
21> RESTORE DATABASE;
22> SWITCH DATAFILE ALL;
23> # recover the database
24>  RECOVER DATABASE;
25> }
allocated channel: d1
channel d1: SID=21 device type=DISK

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

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/w148p/onlinelog/group_3.282.906753483''to ''+DATA1''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_2.281.906753481''to ''+DATA1''

sql statement: ALTER DATABASE RENAME FILE ''+DATA/w148p/onlinelog/group_1.279.906753479''to ''+DATA1''

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 +DATA1
channel d1: restoring datafile 00002 to +DATA1
channel d1: restoring datafile 00003 to +DATA1
channel d1: restoring datafile 00004 to +DATA1
channel d1: restoring datafile 00005 to +DATA1
channel d1: reading from backup piece /u01/share/backup/database_W148P_08rhmh9g_8
channel d1: piece handle=/u01/share/backup/database_W148P_08rhmh9g_8 tag=TAG20161006T150104
channel d1: restored backup piece 1
channel d1: restore complete, elapsed time: 00:00:35
Finished restore at 06-OCT-16

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=924538599 file name=+DATA1/prpt/datafile/system.258.924538565
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=924538599 file name=+DATA1/prpt/datafile/sysaux.257.924538565
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=924538599 file name=+DATA1/prpt/datafile/undotbs1.260.924538565
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=924538599 file name=+DATA1/prpt/datafile/users.263.924538565
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=924538599 file name=+DATA1/prpt/datafile/example.256.924538565

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=10
channel d1: restoring archived log
archived log thread=1 sequence=11
channel d1: reading from backup piece /u01/share/backup/arch_W148P_0arhmhan_10
channel d1: piece handle=/u01/share/backup/arch_W148P_0arhmhan_10 tag=TAG20161006T150143
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_10.444.924538601 thread=1 sequence=10
archived log file name=+FRA/prpt/archivelog/2016_10_06/thread_1_seq_11.445.924538601 thread=1 sequence=11
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-16
released channel: d1
released channel: d2

RMAN>
RMAN> **end-of-file**

RMAN>

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 16:17:46
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@rac1 ~]$ 

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA1/prpt/onlinelog/group_3.264.924538683
+DATA1/prpt/onlinelog/group_2.261.924538681
+DATA1/prpt/onlinelog/group_1.262.924538681

SQL>

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

NAME      OPEN_MODE            DB_UNIQUE_NAME        DBID
--------- -------------------- --------------- ----------
W148P     READ WRITE           prpt            3175693255
                                               
SQL>                                           


Step 12: Change Database name and DB ID

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

add this entry to pfile *.control_files='+DATA1/prpt/controlfile/current.259.924536529','+FRA/prpt/controlfile/current.443.924536529'

SQL> startup nomount pfile='/home/oracle/initprpt.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> alter database mount;

Database altered.

SQL>

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

[oracle@rac1 ~]$ nid target=sys dbname=prpt  <----

DBNEWID: Release 11.2.0.3.0 - Production on Thu Oct 6 16:26:44 2016

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

Password:
Connected to database W148P (DBID=3175693255)

Connected to server version 11.2.0

Control Files in database:
    +DATA1/prpt/controlfile/current.259.924536529
    +FRA/prpt/controlfile/current.443.924536529

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

Proceeding with operation
Changing database ID from 3175693255 to 3870074951
Changing database name from W148P to PRPT
    Control File +DATA1/prpt/controlfile/current.259.924536529 - modified
    Control File +FRA/prpt/controlfile/current.443.924536529 - modified
    Datafile +DATA1/prpt/datafile/system.258.92453856 - dbid changed, wrote new name
    Datafile +DATA1/prpt/datafile/sysaux.257.92453856 - dbid changed, wrote new name
    Datafile +DATA1/prpt/datafile/undotbs1.260.92453856 - dbid changed, wrote new name
    Datafile +DATA1/prpt/datafile/users.263.92453856 - dbid changed, wrote new name
    Datafile +DATA1/prpt/datafile/example.256.92453856 - dbid changed, wrote new name
    Datafile +DATA/prpt/tempfile/temp.291.92453868 - dbid changed, wrote new name
    Control File +DATA1/prpt/controlfile/current.259.924536529 - dbid changed, wrote new name
    Control File +FRA/prpt/controlfile/current.443.924536529 - dbid changed, wrote new name
    Instance shut down

Database name changed to PRPT.
Modify parameter file and generate a new password file before restarting.
Database ID for database PRPT changed to 3870074951.
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@rac1 ~]$

[oracle@rac1 ~]$ ps -ef | grep pmon
oracle    3859     1  0 13:38 ?        00:00:01 asm_pmon_+ASM
oracle    5753 24693  0 16:27 pts/1    00:00:00 grep pmon
[oracle@rac1 ~]$

Please note database will go down automatically


Step 13: Modify DB_NAME in init file and start the database

[oracle@rac1 ~]$ cat initprpt.ora
prpt.__db_cache_size=419430400
prpt.__java_pool_size=16777216
prpt.__large_pool_size=16777216
prpt.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prpt.__pga_aggregate_target=520093696
prpt.__sga_target=754974720
prpt.__shared_io_pool_size=0
prpt.__shared_pool_size=251658240
prpt.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/admin/prpt/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA1/prpt/controlfile/current.259.924536529','+FRA/prpt/controlfile/current.443.924536529'
*.db_block_size=8192
#*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='prpt' <---
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prptXDB)'
*.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@rac1 ~]$

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

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

File created.

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


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

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             754974960 bytes
Database Buffers          503316480 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
--------- -------------------- ------------------------------ ----------
PRPT      READ WRITE           prpt                           3870074951

SQL>

SQL> SELECT NAME FROM V$TEMPFILE;

NAME
-------------------------------------------------------------------------
+DATA/prpt/tempfile/temp.291.924538685

SQL>


Step 14: Add TNS entry

PRPT =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prpt)
    )
  )

[oracle@rac1 admin]$ tnsping PRPT

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 06-OCT-2016 16:35:54

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

Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prpt)))
OK (100 msec)
[oracle@rac1 admin]$


Step 15: Verify

[oracle@rac1 ~]$ lsnrctl status LISTENER | grep -i prpt
Service "prpt" has 1 instance(s).
Service "prptXDB" has 1 instance(s).
[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus scott/tiger@prpt

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 6 16:37:50 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.