Welcome to Oracle's Barking...!!!. I’m thrilled you’re here—because I have so much I want to share with you. I’m here to make a life out of what I love, and what I love is blogging. I am passionate about Oracle , and I truly believe that passion is relayed back to my readers through the innovative and exciting content that I post. Explore my site, and all that I have to offer; perhaps Oracle's Barking...!!! will ignite your own passions too.
Export: Release 11.2.0.3.0 - Production on Sat Apr 29 15:00:44 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31641: unable to create dump file "/mount-mig/TT0037598650/fullpserv_01.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 18
Solution Description: **************************
Mostly you will get this error on SunOS:
---------------------------------------------------- Start The Database And Run This : sqlplus / as sysdba
ALTER SYSTEM SET EVENTS '10298 trace name context forever, level 32';
ORA-31693: Table data object "VERTEX"."RATECITY" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/mnt/vertex/importdata/expdp_fulldb_vert/expdp_fulldb_vertppf_19082017_02.dmp" for read
ORA-19505: failed to identify file "/mnt/vertex/importdata/expdp_fulldb_vert/expdp_fulldb_vertppf_19082017_02.dmp"
ORA-27037: unable to obtain file status
Solution Description: **************************
Issue is due to the mount/file system was not being accessible from the second node.
Then use Cluster=N to force Data Pump to use only the instance where the job is started and ran again data pump job using below datapump parameter:
(ii) Archive log Should be enable & Flash Recovery Area Details. ******************************************************************************
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /FRA/prod
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0
(iii) File-System Details of Target Side. ************************************************
[oracle@bangaloe ~]$ df -h /disk02
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_target-lv_root
86G 22G 60G 27% /
[oracle@bangaloe ~]$ df -h /FRA
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_target-lv_root
86G 22G 60G 27% /
Step: 1 ============================================== Set environments and start rman for prod ( @Primary Site). --------------------------------------------------------------------------------
[oracle@source ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@source ~]$
[oracle@source ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:03:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3010714195)
Step: 2 ============================================================================ Now check old backup details, but we need new backup with autocontrolfile ( @Primary Site). ------------------------------------------------------------------------------------------------------------------------------------
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
Step: 3 =========================================================================== Check controlfile autobakcup details, It's should ON, if Not then enable it. ( @Primary Site). ----------------------------------------------------------------------------------------------------------------------------------
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name prod are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
Step: 4 =============================================== Take full databae backup with archivelog ( @Primary Site). ----------------------------------------------------------------------------------
Starting backup at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/disk01/prod/prod/system01.dbf
input datafile file number=00002 name=/disk01/prod/prod/sysaux01.dbf
input datafile file number=00005 name=/disk01/prod/prod/example01.dbf
input datafile file number=00003 name=/disk01/prod/prod/undotbs01.dbf
input datafile file number=00004 name=/disk01/prod/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-AUG-17
channel ORA_DISK_1: finished piece 1 at 05-AUG-17
piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp tag=TAG20170805T130803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 05-AUG-17
Starting backup at 05-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=45 STAMP=951224968
channel ORA_DISK_1: starting piece 1 at 05-AUG-17
channel ORA_DISK_1: finished piece 1 at 05-AUG-17
piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_annnn_TAG20170805T130928_drbxkk2x_.bkp tag=TAG20170805T130928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-AUG-17
Starting Control File and SPFILE Autobackup at 05-AUG-17
piece handle=/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-AUG-17
RMAN> exit
Step: 5 =========================================== Creare some directiories on target side ( @Target Site). ----------------------------------------------------------------------------
Step: 7 ============================================================================= On target server start database via rman and restore spfile from autobackup ( @Target Site). -------------------------------------------------------------------------------------------------------------------------------------
[oracle@target ~]$ vi /etc/oratab
[oracle@target ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@target dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:18:43 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount force;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initprod.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
RMAN> restore spfile from '/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp';
Starting restore at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-AUG-17
RMAN> exit
Step: 8 ========================================================================================== Come to target SQL*Plus promte and create pfile from spfile and do some modification acording to target server. ( @Target Site). --------------------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@target ~]$ . oraenv
ORACLE_SID = [oracle] ? prod
The Oracle base has been set to /u01/app/oracle
[oracle@target ~]$ cd $ORACLE_HOME/dbs
[oracle@target dbs]$ ls -lrth
-rw-r----- 1 oracle oinstall 3.5K Aug 5 13:28 spfileprod.ora
[oracle@target dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:30:41 2017
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> create pfile from spfile;
File created.
SQL> exit
[oracle@target dbs]$ ls -lrth
-rw-r----- 1 oracle oinstall 3.5K Aug 5 13:28 spfileprod.ora
-rw-r--r-- 1 oracle oinstall 1.4K Aug 5 13:30 initprod.ora
Step: 9 ====================================================================================== Remove spfile from dbs location, down previous stared instnace & start it again via pfile ( @Target Site). ------------------------------------------------------------------------------------------------------------------------------------------------------
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:37:09 2017
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> shut immediate
ORA-01507: database not mounted
[oracle@target dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 5 13:38:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
SQL> create spfile from pfile;
File created.
SQL> exit
Step: 10 ===================================================================================== Now start rman and restore controlfile from autobackup & put database in mount stage ( @Target Site). ------------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@target dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:38:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore controlfile from '/FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp';
Starting restore at 05-AUG-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Step: 11 ======================================================== Now tell your newly stared db to your backup location ( @Target Site). -------------------------------------------------------------------------------------------------
Starting implicit crosscheck copy at 05-AUG-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 05-AUG-17
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
searching for all files that match the pattern /FRA/prod/prod/
List of Files Unknown to the Database
=====================================
File Name: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /FRA/prod/prod/autobackup/2017_08_05/o1_mf_s_951224970_drbxklvm_.bkp
RMAN> run { 2> SET NEWNAME FOR DATAFILE 1 TO "/disk02/prod/prod/system01.dbf"; 3> SET NEWNAME FOR DATAFILE 2 TO "/disk02/prod/prod/sysaux01.dbf"; 4> SET NEWNAME FOR DATAFILE 3 TO "/disk02/prod/prod/undotbs01.dbf"; 5> SET NEWNAME FOR DATAFILE 4 TO "/disk02/prod/prod/users01.dbf"; 6> SET NEWNAME FOR DATAFILE 5 TO "/disk02/prod/prod/example01.dbf"; 7> restore database; 8> switch datafile all; 9> recover database; 10> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 05-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /disk02/prod/prod/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /disk02/prod/prod/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /disk02/prod/prod/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /disk02/prod/prod/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /disk02/prod/prod/example01.dbf
channel ORA_DISK_1: reading from backup piece /FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp
channel ORA_DISK_1: piece handle=/FRA/prod/prod/backupset/2017_08_05/o1_mf_nnndf_TAG20170805T130803_drbxgwpf_.bkp tag=TAG20170805T130803
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 05-AUG-17
****************************************** Note: the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.
**********************************************
Step: 13 =================================================================================== Now try to start your database with resetlogs and will get below error, then rename your redologs. ( @Target Site). ------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/disk01/prod/prod/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9
SQL> alter database rename file '/disk01/prod/prod/redo01.log' TO '/disk02/prod/prod/redo01.log';
Database altered.
SQL> alter database rename file '/disk01/prod/prod/redo02.log' TO '/disk02/prod/prod/redo02.log';
Database altered.
SQL> alter database rename file '/disk01/prod/prod/redo03.log' TO '/disk02/prod/prod/redo03.log';
Database altered.
Step: 14 ======================================================================================== Again try to start your database with resetlogs and will get below error, Clear your one unclear redologfile. ( @Target Site). --------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/disk02/prod/prod/redo01.log'
SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;
SQL> alter database clear logfile '/disk02/prod/prod/redo01.log';
Database altered.
Step: 14 =========================================================================== Again try to start your database with resetlogs and you are done here..!!. ( @Target Site). ---------------------------------------------------------------------------------------------------------------------------------
SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;
Setting up Log_Archive_Dest_1 (To store Local Archive log ) & Log_Archive_Dest_2 ( To Send local archive to remote server 'Standby' ) Parameter ( @Primary Site).
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/prod_sty/adump'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/FRA/prod_sty'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=prod_styXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''prod_sty'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/disk01'', ''/disk02'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/FRA'', ''/FRA'', ''/disk01'', ''/disk02'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/disk02/prod/prod/controlfile_01.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''prod_sty'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''prod'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(prod_sty,prod)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=prod NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prod'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
allocated channel: stby
channel stby: SID=18 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/disk02/prod/prod/controlfile_01.ctl';
Loging to Sqlplus and create standby log, First count online redo log on primary and create standby log on primary +1, means if primary have N online redo log then @ standby you have to create N+1
