Database Administrator's blog: May 2016

In this article we are showing how to restore oracle11gr2 RMAN backup to different server:

[oracle@bkuptest ~]$ echo $ORACLE_SID
remitdb
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon May 16 11:14:06 2016

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

connected to target database (not started)

RMAN>

RMAN>

--==Startup database in nomount mode==
RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/soft/app/oracle/product/11.2.0.4.0/dbhome_1/dbs/initremitdb.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2260088 bytes
Variable Size 285213576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5517312 bytes

--==Restore spfile from RMAN backup==
RMAN> restore spfile from '/soft/spf_1_29228_150520161256.bkp';

Starting restore at 16-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=429 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /soft/spf_1_29228_150520161256.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-MAY-16

RMAN>

--===Create PFILE from SPFILE==
oracle@bkuptest ~]$
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 11:34:59 2016

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

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

SQL>
SQL> create pfile from spfile;

File created.

SQL>

--===PFILE parameter changes as require===
*.audit_file_dest='/soft/app/oracle/admin/bkuptest/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/bkuptest/control01.ctl','/fra/bkuptest/control02.ctl'
*.db_block_size=8192
*.db_domain='nblbd.com'
*.db_name='remitdb'
*.db_recovery_file_dest='/fra'
*.db_recovery_file_dest_size=193273528320
*.diagnostic_dest='/soft/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=remitdbXDB)'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.memory_max_target=7516192768
*.memory_target=7516192768
*.open_cursors=300
*.processes=5000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

--===Create SPFILE from PFILE==
SQL> create spfile from pfile;

File created.

--==Shutdown Database ===
RMAN> shutdown immediate

Oracle instance shut down

--===Startup database in no mount===
RMAN> startup nomount pfile='/soft/app/oracle/product/11.2.0.4.0/dbhome_1/dbs/initremitdb.ora';

connected to target database (not started)
Oracle instance started

Total System Global Area 7482626048 bytes

Fixed Size 2267792 bytes
Variable Size 5804918128 bytes
Database Buffers 1660944384 bytes
Redo Buffers 14495744 bytes

--===Set DBID ===
RMAN> set dbid=4077401740

executing command: SET DBID

--===Restore Control file from BACKUP===
RMAN> restore controlfile from '/soft/ctl_1_29227_150520161256.bkp';

Starting restore at 16-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5644 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
output file name=/data/bkuptest/control01.ctl
output file name=/fra/bkuptest/control02.ctl
Finished restore at 16-MAY-16

--===Database mount===
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

--==To let RMAN know about the backup in /soft, we use the CATALOG command===
RMAN>
RMAN> catalog start with '/soft';

Starting implicit crosscheck backup at 16-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1885 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=5650 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=6 device type=DISK
Crosschecked 3 objects
Crosschecked 154 objects
Finished implicit crosscheck backup at 16-MAY-16

Starting implicit crosscheck copy at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Crosschecked 2 objects
Finished implicit crosscheck copy at 16-MAY-16

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

searching for all files that match the pattern /soft
no files found to be unknown to the database

RMAN>

--==Restore database== RMAN> restore database;

Starting restore at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

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 00002 to /u01/app/oracle/oradata/remitdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/remitdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/remitdb/qpaydbindex01.dbf
channel ORA_DISK_1: reading from backup piece /soft/db_1_29225_150520161256.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/remitdb/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /u01/app/oracle/oradata/remitdb/users01.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u01/app/oracle/oradata/remitdb/nblqp2fa01.dbf
channel ORA_DISK_2: reading from backup piece /soft/db_1_29224_150520161256.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00005 to /u01/app/oracle/oradata/remitdb/qpaydb01.dbf
channel ORA_DISK_3: restoring datafile 00008 to /u01/app/oracle/oradata/remitdbuser2fa.DBF
channel ORA_DISK_3: reading from backup piece /soft/db_1_29223_150520161256.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /soft/db_1_29225_150520161256.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/remitdb/undotbs01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

channel ORA_DISK_2: ORA-19870: error while restoring backup piece /soft/db_1_29224_150520161256.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/remitdb/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

channel ORA_DISK_3: ORA-19870: error while restoring backup piece /soft/db_1_29223_150520161256.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/remitdb/qpaydb01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

failover to previous backup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/16/2016 13:06:46
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>

--====RESTORE and RECOVER the database==
RMAN> run{
SET NEWNAME FOR DATAFILE 1 TO '/data/bkuptest/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/data/bkuptest/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/data/bkuptest/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/data/bkuptest/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/data/bkuptest/qpaydb01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/data/bkuptest/qpaydbindex01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '/data/bkuptest/nblqp2fa01.dbf';
SET NEWNAME FOR DATAFILE 8 TO '/data/bkuptest/remitdbuser2fa.DBF';
restore database;
switch datafile all;
recover database;}

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

Starting restore at 16-MAY-16
Starting implicit crosscheck backup at 16-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1885 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=5650 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=6 device type=DISK
Crosschecked 3 objects
Crosschecked 154 objects
Finished implicit crosscheck backup at 16-MAY-16

Starting implicit crosscheck copy at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Crosschecked 2 objects
Finished implicit crosscheck copy at 16-MAY-16

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

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

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 00002 to /data/bkuptest/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /data/bkuptest/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /data/bkuptest/qpaydbindex01.dbf
channel ORA_DISK_1: reading from backup piece /soft/db_1_29225_150520161256.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /data/bkuptest/system01.dbf
channel ORA_DISK_2: restoring datafile 00004 to /data/bkuptest/users01.dbf
channel ORA_DISK_2: restoring datafile 00007 to /data/bkuptest/nblqp2fa01.dbf
channel ORA_DISK_2: reading from backup piece /soft/db_1_29224_150520161256.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00005 to /data/bkuptest/qpaydb01.dbf
channel ORA_DISK_3: restoring datafile 00008 to /data/bkuptest/remitdbuser2fa.DBF
channel ORA_DISK_3: reading from backup piece /soft/db_1_29223_150520161256.bkp
channel ORA_DISK_1: piece handle=/soft/db_1_29225_150520161256.bkp tag=DB_150520161256
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:05
channel ORA_DISK_2: piece handle=/soft/db_1_29224_150520161256.bkp tag=DB_150520161256
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:10:25
channel ORA_DISK_3: piece handle=/soft/db_1_29223_150520161256.bkp tag=DB_150520161256
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:14:36
Finished restore at 16-MAY-16

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=912002223 file name=/data/bkuptest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=912002224 file name=/data/bkuptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=912002225 file name=/data/bkuptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=912002226 file name=/data/bkuptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=912002227 file name=/data/bkuptest/qpaydb01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=912002228 file name=/data/bkuptest/qpaydbindex01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=912002229 file name=/data/bkuptest/nblqp2fa01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=912002230 file name=/data/bkuptest/remitdbuser2fa.DBF

Starting recover at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

Oracle Error:
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: '/data/bkuptest/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 13:57:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 961622 and starting SCN of 649440238 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961621 and starting SCN of 649432402 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961620 and starting SCN of 649423457 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961619 and starting SCN of 649413447 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961618 and starting SCN of 649405456 found to restore

RMAN>

Copy above specified Archivelog (sequence 961618,961619,961620,961621,961622) to following new server directory /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/ then recover database
=================================================================
RMAN> RECOVER DATABASE;

Starting recover at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

archived log for thread 1 with sequence 961619 is already on disk as file /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc
archived log for thread 1 with sequence 961620 is already on disk as file /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc
archived log for thread 1 with sequence 961621 is already on disk as file /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc
archived log for thread 1 with sequence 961622 is already on disk as file /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc thread=1 sequence=961619
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc thread=1 sequence=961620
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc thread=1 sequence=961621
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc thread=1 sequence=961622
unable to find archived log
archived log thread=1 sequence=961623
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 15:32:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 961623 and starting SCN of 649445097

Note: this error can be ignore as RMAN has applied all the available archivelog files and is now trying to apply a non-existent archive log file.

--====Change and Rename Redo Logs location=====
alter database rename file '/u01/app/oracle/oradata/remitdb/redo01a.log' to '/data/bkuptest/redo01a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo01b.log' to '/data/bkuptest/redo01b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo02a.log' to '/data/bkuptest/redo02a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo02b.log' to '/data/bkuptest/redo02b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo03a.log' to '/data/bkuptest/redo03a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo03b.log' to '/data/bkuptest/redo03b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo04a.log' to '/data/bkuptest/redo04a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo04b.log' to '/data/bkuptest/redo04b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo05a.log' to '/data/bkuptest/redo05a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/redo05b.log' to '/data/bkuptest/redo05b.log' ;

SQL>
SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo01a.log' to '/data/bkuptest/redo01a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo01b.log' to '/data/bkuptest/redo01b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo02a.log' to '/data/bkuptest/redo02a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo02b.log' to '/data/bkuptest/redo02b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo03a.log' to '/data/bkuptest/redo03a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo03b.log' to '/data/bkuptest/redo03b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo04a.log' to '/data/bkuptest/redo04a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo04b.log' to '/data/bkuptest/redo04b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo05a.log' to '/data/bkuptest/redo05a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/redo05b.log' to '/data/bkuptest/redo05b.log' ;

Database altered.

SQL>

alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo01a.log' to '/data/bkuptest/standby_redo01a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo01b.log' to '/data/bkuptest/standby_redo01b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo02a.log' to '/data/bkuptest/standby_redo02a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo02b.log' to '/data/bkuptest/standby_redo02b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo03a.log' to '/data/bkuptest/standby_redo03a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo03b.log' to '/data/bkuptest/standby_redo03b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo04a.log' to '/data/bkuptest/standby_redo04a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo04b.log' to '/data/bkuptest/standby_redo04b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo05a.log' to '/data/bkuptest/standby_redo05a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo05b.log' to '/data/bkuptest/standby_redo05b.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo06a.log' to '/data/bkuptest/standby_redo06a.log' ;
alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo06b.log' to '/data/bkuptest/standby_redo06b.log' ;

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo01a.log' to '/data/bkuptest/standby_redo01a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo01b.log' to '/data/bkuptest/standby_redo01b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo02a.log' to '/data/bkuptest/standby_redo02a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo02b.log' to '/data/bkuptest/standby_redo02b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo03a.log' to '/data/bkuptest/standby_redo03a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo03b.log' to '/data/bkuptest/standby_redo03b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo04a.log' to '/data/bkuptest/standby_redo04a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo04b.log' to '/data/bkuptest/standby_redo04b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo05a.log' to '/data/bkuptest/standby_redo05a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo05b.log' to '/data/bkuptest/standby_redo05b.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo06a.log' to '/data/bkuptest/standby_redo06a.log' ;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/remitdb/standby_redo06b.log' to '/data/bkuptest/standby_redo06b.log' ;

Database altered.

SQL>

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03a.log'
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03b.log'

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CLEARING
2 CLEARING
5 CLEARING
4 CLEARING
3 CLEARING_CURRENT

SQL>
SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 UNUSED
2 UNUSED
5 UNUSED
4 UNUSED
3 CURRENT

SQL>

--====

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3689
Session ID: 5644 Serial number: 5

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 16:52:00 2016

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 5804918128 bytes
Database Buffers 1660944384 bytes
Redo Buffers 14495744 bytes
Database mounted.
SQL> select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;

OPEN_MODE DATABASE_ROLE GUARD_S SWITCHOVER_STATUS
-------------------- ---------------- ------- --------------------
MOUNTED PRIMARY NONE NOT ALLOWED

SQL> alter database set standby database to maximize performance;

Database altered.

SQL>
SQL> shutdown immediate
ORA-01109: database not open

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

Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 5804918128 bytes
Database Buffers 1660944384 bytes
Redo Buffers 14495744 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 3887
Session ID: 5644 Serial number: 5

SQL>

ORA-39700: database must be opened with upgrade option

After restore database another server appear this error, because on older server database version was 11.2.0.1.0 and on new server database version is 11.2.0.4.0, therefore we need:
1) open with upgrade option
2) upgrade database – it means run script $ORACLE_HOME/rdbms/admin/catupgrd.sql
3) shutdown clearly and startup
4) run script $ORACLE_HOME/rdbms/admin/utlrp.sql – for recompile all invalid PL/SQL packages

sqlplus / as sysdba
SQL> startup upgrade;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
SQL> shutdown immediate;
SQL> startup;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 6023021936 bytes
Database Buffers 1442840576 bytes
Redo Buffers 14495744 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
.................
................running....
...............
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 17:58:53 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 7482626048 bytes
Fixed Size 2267792 bytes
Variable Size 6023021936 bytes
Database Buffers 1442840576 bytes
Redo Buffers 14495744 bytes
Database mounted.
Database opened.
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-05-16 17:59:33

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
...............
................
.....................
Function dropped.

Warning: XDB now invalid, could not find xdbconfig
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110:
data file 201: '/u01/app/oracle/oradata/remitdb/temp01.dbf'
ORA-01403: no data
found

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exit
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 18:00:09 2016

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

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

SQL>
SQL>
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>
SQL>

Database Administrator's blog

Pages

Wednesday, May 25, 2016

Restore Oracle11gr2 RMAN Backup to Another Server

Tuesday, May 24, 2016

ORA-31634: job already exists,ORA-31664: unable to construct unique job name when defaulted (DATAPUMP fails)