Error 3 while creating Physical Standby Using RMAN DUPLICATE RMAN-04014 RMAN-03015 RMAN-03002 Linux-x86_64 Error

bash-4.1$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 29 05:58:36 2015

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

connected to target database: MASTER (DBID=2934831228)

RMAN> CONNECT AUXILIARY sys/oracle@standby;

connected to auxiliary database: MASTER (not mounted)

RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate channel prmy5 type disk;
7> allocate auxiliary channel stby1 type disk;
8> duplicate target database for standby from active database
9> spfile
10> parameter_value_convert ‘master’,’standby’
11> set ‘db_unique_name’=’standby’
12> set control_files=’+FLASH/standby/controlfile/control.ctl’
13> set db_create_file_dest=’+DATA’
14> set db_create_online_log_dest_1=’+FLASH’
15> set db_create_online_log_dest_2=’+DATA’
16> set db_recovery_file_dest=’+FLASH’
17> set DB_RECOVERY_FILE_DEST_SIZE=’10G’
nofilenamecheck;
18> 19> }

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=149 device type=DISK

allocated channel: prmy2
channel prmy2: SID=19 device type=DISK

allocated channel: prmy3
channel prmy3: SID=151 device type=DISK

allocated channel: prmy4
channel prmy4: SID=17 device type=DISK

allocated channel: prmy5
channel prmy5: SID=136 device type=DISK

allocated channel: stby1
channel stby1: SID=11 device type=DISK

Starting Duplicate Db at 29-APR-15
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: stby1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 05:59:03
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

Issue: While working on physical stand by creation, I came across numerous issue and issue 2 “https://vjdba.wordpress.com/2015/04/29/error-2-while-creating-physical-standby-using-rman-duplicate-rman-04014-rman-03015-rman-=03002-linux-x86_64-error/” is one among them. While I progressed in some part, you will notice line like “sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora””. So this started the standby instance using the spfile, where as the primary requirement in to start the standby instance in nomount phase using a pfile with just one parameter name db_name.

Solution: to solve this, I brought down the standby instance which was running using the spfile and brought it back using the pfile.

Steps: All performed at standby machine:

-bash-4.1$ cd /u01/app/oracle/product/11.2.0/db1/dbs
-bash-4.1$ ls -ltr
total 20
-rw-r–r– 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r– 1 oracle oinstall 17 Apr 22 01:19 initstandby.ora
-rw-r—– 1 oracle oinstall 1536 Apr 29 05:55 orapwstandby
-rw-rw—- 1 oracle oinstall 1544 Apr 29 05:55 hc_standby.dat
-rw-r—– 1 oracle oinstall 3584 Apr 29 05:55 spfilestandby.ora
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 29 05:59:41 2015

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

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

SQL> select name , open_mode from v$database;
select name , open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> sho parameter spfile;

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/product/11.2.0
/db1/dbs/spfilestandby.ora
SQL> shu immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> !mv spfilestandby.ora spfilestandby.ora_bkp

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL> exit

 

Error 2 while creating Physical Standby Using RMAN DUPLICATE RMAN-04014 RMAN-03015 RMAN-03002 Linux-x86_64 Error

bash-4.1$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 29 05:54:19 2015

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

connected to target database: MASTER (DBID=2934831228)

RMAN> CONNECT AUXILIARY sys/oracle@standby

connected to auxiliary database: WHATEVER (not mounted)

RMAN> run {
2> allocate channel prmy1 type disk;
3> allocate channel prmy2 type disk;
4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate channel prmy5 type disk;
7> allocate auxiliary channel stby1 type disk;
8> duplicate target database for standby from active database
9> spfile
10> parameter_value_convert ‘master’,’standby’
set ‘db_unique_name’=’standby’
11> 12> set control_files=’+FLASH/standby/controlfile/control.ctl’
13> set db_create_file_dest=’+DATA’
14> set db_create_online_log_dest_1=’+FLASH’
15> set db_create_online_log_dest_2=’+DATA’
16> set db_recovery_file_dest=’+FLASH’
17> set DB_RECOVERY_FILE_DEST_SIZE=’10G’
18> nofilenamecheck;
19> }

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=149 device type=DISK

allocated channel: prmy2
channel prmy2: SID=19 device type=DISK

allocated channel: prmy3
channel prmy3: SID=151 device type=DISK

allocated channel: prmy4
channel prmy4: SID=10 device type=DISK

allocated channel: prmy5
channel prmy5: SID=147 device type=DISK

allocated channel: stby1
channel stby1: SID=96 device type=DISK

Starting Duplicate Db at 29-APR-15

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db1/dbs/orapwmaster’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db1/dbs/orapwstandby’ targetfile
‘/u01/app/oracle/product/11.2.0/db1/dbs/spfilemaster.ora’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora””;
}
executing Memory Script

Starting backup at 29-APR-15
Finished backup at 29-APR-15

sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora”

contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/standby/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=standbyXDB)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_1 =
”LOCATION=/u02/archive/standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”standby” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”+FLASH/standby/controlfile/control.ctl” comment=
”” scope=spfile”;
sql clone “alter system set db_create_file_dest =
”+DATA” comment=
”” scope=spfile”;
sql clone “alter system set db_create_online_log_dest_1 =
”+FLASH” comment=
”” scope=spfile”;
sql clone “alter system set db_create_online_log_dest_2 =
”+DATA” comment=
”” scope=spfile”;
sql clone “alter system set db_recovery_file_dest =
”+FLASH” comment=
”” scope=spfile”;
sql clone “alter system set DB_RECOVERY_FILE_DEST_SIZE =
10G comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/standby/adump” comment= ”” scope=spfile

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=standbyXDB)” comment= ”” scope=spfile

sql statement: alter system set log_archive_dest_1 = ”LOCATION=/u02/archive/standby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=master” comment= ”” scope=spfile

sql statement: alter system set db_unique_name = ”standby” comment= ”” scope=spfile

sql statement: alter system set control_files = ”+FLASH/standby/controlfile/control.ctl” comment= ”” scope=spfile

sql statement: alter system set db_create_file_dest = ”+DATA” comment= ”” scope=spfile

sql statement: alter system set db_create_online_log_dest_1 = ”+FLASH” comment= ”” scope=spfile

sql statement: alter system set db_create_online_log_dest_2 = ”+DATA” comment= ”” scope=spfile

sql statement: alter system set db_recovery_file_dest = ”+FLASH” comment= ”” scope=spfile

sql statement: alter system set DB_RECOVERY_FILE_DEST_SIZE = 10G comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 05:55:19
RMAN-03015: error occurred in stored script Memory Script
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925

Issue: as part of physical standby configuration, I did not create audit file directory in standby machine and thus this failure.

Solution:

@Primary database named “master”

SQL> sho parameter audit_file_dest

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/app/oracle/admin/master/adump

@Standby database machine name “standby”

-bash-4.1$ mkdir -p /u01/app/oracle/admin/standby/adump

It worked.

 

Error 1 while creating Physical Standby Using RMAN DUPLICATE ORA-17629 ORA-17627 ORA-17629

bash-4.1$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Apr 29 05:38:56 2015

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

connected to target database: MASTER (DBID=2934831228)

RMAN> CONNECT AUXILIARY sys/oracle@standby

connected to auxiliary database: WHATEVER (not mounted)

RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
2> 3> 4> allocate channel prmy3 type disk;
5> allocate channel prmy4 type disk;
6> allocate channel prmy5 type disk;
7> allocate auxiliary channel stby1 type disk;
8> duplicate target database for standby from active database
9> spfile
10> parameter_value_convert ‘master’,’standby’
11> set ‘db_unique_name’=’standby’
12> set control_files=’+FLASH/standby/controlfile/control.ctl’
13> set db_create_file_dest=’+DATA’
14> set db_create_online_log_dest_1=’+FLASH’
15> set db_create_online_log_dest_2=’+DATA’
16> set db_recovery_file_dest=’+FLASH’
17> set DB_RECOVERY_FILE_DEST_SIZE=’10G’
18> nofilenamecheck;
19> }

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=136 device type=DISK

allocated channel: prmy2
channel prmy2: SID=19 device type=DISK

allocated channel: prmy3
channel prmy3: SID=151 device type=DISK

allocated channel: prmy4
channel prmy4: SID=17 device type=DISK

allocated channel: prmy5
channel prmy5: SID=149 device type=DISK

allocated channel: stby1
channel stby1: SID=87 device type=DISK

Starting Duplicate Db at 29-APR-15

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db1/dbs/orapwmaster’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db1/dbs/orapwstandby’ targetfile
‘/u01/app/oracle/product/11.2.0/db1/dbs/spfilemaster.ora’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0/db1/dbs/spfilestandby.ora””;
}
executing Memory Script

Starting backup at 29-APR-15
RMAN-03009: failure of backup command on prmy1 channel at 04/29/2015 05:42:32
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server
continuing other job steps, job failed will not be re-run
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: stby1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/29/2015 05:42:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy2 channel at 04/29/2015 05:42:32
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Primary db name: primary

standby db name: standby:

Solution: Copied primary password file “orapwmaster” from primary rdbms_home/dbs to standby rdbms_home/dbs and renamed it to orapwstandby and it worked!

 

MOVE ALL ORACLE DATAFILES TO NEWLY CREATE ASM DISKGROUPS

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
MASTER READ WRITE

SQL> alter system set db_create_file_dest=’+DATA’ scope=spfile;

System altered.

/* This parameter define the default location for data files,
control_files etc, if no location for these files is specified
at the time of their creation. +DATA is the diskgroup in ASM
*/

SQL> sho parameter db_create_file

NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string

SQL> alter system set db_create_online_log_dest_1=’+FLASH’ scope=spfile;

System altered.

/*
This parameter defines the default location number 1 for online logs.
*/

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE DISABLE BLOCK CHANGE TRACKING
*
ERROR at line 1:
ORA-19759: block change tracking is not enabled

/* If you are using block change tracking, then disable it. In my case I am not using it. */

SQL> sho parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string /u02/oradata/master/control01.
ctl, /u02/oradata/master/control02.ctl

SQL> alter system reset control_files scope=spfile;

System altered.

/*
Remove the control_files parameter from spfile.
So next time we restore the control file it will automatically go
to +DATA diskgroup since it is defined in db_create_file_dest,
and the new path will be automatically updated in spfile.
*/

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

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
-bash-4.1$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Apr 28 07:42:43 2015

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1603411968 bytes

Fixed Size 2213776 bytes
Variable Size 1224738928 bytes
Database Buffers 369098752 bytes
Redo Buffers 7360512 bytes

RMAN> restore controlfile from ‘/u02/oradata/master/control01.ctl’;

Starting restore at 28-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+FLASH/master/controlfile/current.256.878197401
Finished restore at 28-APR-15

/*
Control file restored to +DATA from its old location.
It will be restored to +DATA because we set db_create_file_dest to
+DATA.
*/

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format ‘+DATA’;

Starting backup at 28-APR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/master/system01.dbf
output file name=+DATA/master/datafile/system.256.878197455 tag=TAG20150428T074413 RECID=1 STAMP=878197504
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/master/sysaux01.dbf
output file name=+DATA/master/datafile/sysaux.257.878197509 tag=TAG20150428T074413 RECID=2 STAMP=878197554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/master/undotbs01.dbf
output file name=+DATA/master/datafile/undotbs1.258.878197565 tag=TAG20150428T074413 RECID=3 STAMP=878197598
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/master/controlfile/backup.259.878197599 tag=TAG20150428T074413 RECID=4 STAMP=878197601
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/master/users01.dbf
output file name=+DATA/master/datafile/users.260.878197603 tag=TAG20150428T074413 RECID=5 STAMP=878197603
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-APR-15
channel ORA_DISK_1: finished piece 1 at 28-APR-15
piece handle=+DATA/master/backupset/2015_04_28/nnsnf0_tag20150428t074413_0.261.878197603 tag=TAG20150428T074413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-APR-15

/* make a copy of all datafiles on +DATA diskgroup. */

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DATA/master/datafile/system.256.878197455”
datafile 2 switched to datafile copy “+DATA/master/datafile/sysaux.257.878197509”
datafile 3 switched to datafile copy “+DATA/master/datafile/undotbs1.258.878197565”
datafile 4 switched to datafile copy “+DATA/master/datafile/users.260.878197603”

/*
Switch database to the datafile copies. After this switch the copies on +DATA
have become the database datafiles and the original files have become copies.
*/

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 28 07:47:26 2015

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

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

SQL> set lines 200 pages 30
SQL> col file_name format a50

SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_data_files;

FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 +DATA/master/datafile/system.256.878197455 .68359375 YES 31.9999847
2 +DATA/master/datafile/sysaux.257.878197509 .6640625 YES 31.9999847
3 +DATA/master/datafile/undotbs1.258.878197565 .571289063 YES 31.9999847
4 +DATA/master/datafile/users.260.878197603 .004882813 YES 31.9999847

SQL> col member for a50
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
1 ONLINE /u02/oradata/master/redo01.log NO
2 ONLINE /u02/oradata/master/redo02.log NO
3 ONLINE /u02/oradata/master/redo03.log NO

SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string +FLASH/master/controlfile/current.256.878197401

/*
Migrate tempfile to ASM storage
*/

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————–
/u02/oradata/master/temp01.dbf

SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;

FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 /u02/oradata/master/temp01.dbf .0625 YES 31.9999847

SQL> alter tablespace temp add tempfile size 20m;

Tablespace altered.

SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;

FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
1 /u02/oradata/master/temp01.dbf .0625 YES 31.9999847
2 +DATA/master/tempfile/temp.262.878197751 .01953125 NO 0

SQL> alter tablespace temp drop tempfile ‘/u02/oradata/master/temp01.dbf’;

Tablespace altered.

SQL> select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_temp_files;

FILE_ID FILE_NAME BYTES/1024/1024/1024 AUT MAXBYTES/1024/1024/1024
———- ————————————————– ——————– — ———————–
2 +DATA/master/tempfile/temp.262.878197751 .01953125 NO 0

/*
Migrate redolog files to ASM
*/

SQL> alter system set db_create_online_log_dest_1=’+FLASH’ scope=spfile;

System altered.

/*
This parameter defines the default location number 1 for online
logs i.e. if you don’t specify a location for the log file while
creating it it will be created on the location defined in this
parameter, which I have already run earlier.
*/

SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 CURRENT /u02/oradata/master/redo01.log
2 52428800 INACTIVE /u02/oradata/master/redo02.log
3 52428800 INACTIVE /u02/oradata/master/redo03.log

/*
Here in this case log group 2 and 3 are yet UNUSED, so its safe
to drop them and re-create them on ASM. This database is running
in NOARCHIVELOG mode so its okay to drop any group which is
check pointed or UNUSED.
IF you are on an ARCHIVELOG enabled database then make sure any
group you delete is archived i.e.
SELECT group# , archived from v$log; and archived should be YES.
*/

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 52428800;

Database altered.

/*
This magic works because of db_create_online_log_dest_1 parameter.
Log group 3 created on ASM. No need of mentioning the file name.
*/

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 52428800;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 ACTIVE /u02/oradata/master/redo01.log
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215

SQL> alter system checkpoint;

System altered.

SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 INACTIVE /u02/oradata/master/redo01.log
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 52428800;

Database altered.

SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

GROUP# BYTES STATUS MEMBER
———- ———- —————- ————————————————–
1 52428800 UNUSED +FLASH/master/onlinelog/group_1.259.878198279
2 52428800 CURRENT +FLASH/master/onlinelog/group_2.258.878198229
3 52428800 UNUSED +FLASH/master/onlinelog/group_3.257.878198215

 

Restore statistics and view statistics history

Hello All,

I was given a requirement to restore stats of a table named scpomgr.PROCESSDFU as during the batch run stats for this table was deleted.

First check stats that are available for table in history table:

SQL> select * from dba_TAB_STATS_HISTORY where TABLE_NAME=’PROCESSDFU’ order by 5;

 OWNER                          TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME              STATS_UPDATE_TIME

—————————— —————————— —————————— —————————— ——————————————————-

SCPOMGR                        PROCESSDFU                                                                                   24-MAR-14 06.46.58.215534 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   26-MAR-14 02.20.21.988521 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   26-MAR-14 02.20.28.753781 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   26-MAR-14 02.22.24.705528 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   26-MAR-14 02.22.30.211436 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   27-MAR-14 01.33.20.082902 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   27-MAR-14 02.53.54.478502 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   27-MAR-14 02.54.20.920565 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   31-MAR-14 01.35.34.259835 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   31-MAR-14 03.06.42.532219 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   03-APR-14 01.34.34.239765 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   05-APR-14 04.34.44.820826 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   05-APR-14 04.34.50.505479 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 12.33.53.956840 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.16.58.848276 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.17.17.880748 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.18.28.036483 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.18.36.226104 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.49.09.915276 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.50.36.336024 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.51.08.913710 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 02.54.49.447963 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   07-APR-14 03.09.02.222698 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   10-APR-14 12.33.59.703584 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   14-APR-14 08.05.56.219625 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   14-APR-14 08.06.01.897461 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   14-APR-14 12.19.01.910884 PM -05:00

SCPOMGR                        PROCESSDFU                                                                                   17-APR-14 12.32.36.076777 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.19.38.103485 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.19.43.961863 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.20.42.277090 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.20.47.446610 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.22.41.857548 AM -05:00

SCPOMGR                        PROCESSDFU                                                                                   21-APR-14 06.22.45.729694 AM -05:00

On querying the num_rows for scpomgr.PROCESSDFU we find the stats are missing:

 SQL> select NUM_ROWS from dba_tab_statistics where TABLE_NAME=’PROCESSDFU’ and owner=’SCPOMGR’;

NUM_ROWS

———-

Then, upon deciding on the date for which stats are to be restored, run below command to restore the stats:

SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′);

BEGIN DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′); END;

 *

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at “SYS.DBMS_STATS”, line 3668

ORA-06512: at “SYS.DBMS_STATS”, line 4501

ORA-06512: at “SYS.DBMS_STATS”, line 27802

ORA-06512: at line 1

 Oops, the stats are locked for this table, so first you need to unlock the stats of this table:

 SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS(ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’);

 PL/SQL procedure successfully completed.

Then restore stats for 05^th of April:

 SQL> exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>’SCPOMGR’,tabname=>’PROCESSDFU’,as_of_timestamp=>’05-APR-14 04.34.50.505479 PM -05:00′);

 PL/SQL procedure successfully completed.

 SQL> select NUM_ROWS from dba_tab_statistics where TABLE_NAME=’PROCESSDFU’ and owner=’SCPOMGR’;

 NUM_ROWS

———-

643920

Done!

 

Change purge schedule of change table

Recently I set up Asynchronous Distributed HotLog Mode change data capture(I will post blog for this set up later). After the set up we tested in the target that change data is getting captured. However, next day I was informed by app team that change data was no more to be seen. My bad that I did not read more about retention of data. Upon reading oracle documentation I found this:

“When change tables are imported, the job queue is checked for a Change Data Capture purge job. If no purge job is found, then one is submitted automatically (using the DBMS_CDC_PUBLISH.PURGE procedure). If a change table is imported, but no subscriptions are taken out before the purge job runs (24 hours later, by default), then all rows in the table will be purged.”

So I decided to change this window manually and this blog is about how I achieved this. Though its a small topic to write about, but I struggled to find exact note of how to make this change. So hope this will help others who find themselves situation similar to mine 🙂

First check all the scheduled jobs:

set pages 200
col job_name for a30
col PROGRAM_NAME for a30
col SCHEDULE_NAME for a30
col PROGRAM_OWNER for a20
col job_creator for a20
col owner for a17
select owner, job_name, job_creator, PROGRAM_NAME, SCHEDULE_NAME, STATE from DBA_SCHEDULER_JOBS;

OWNER JOB_NAME JOB_CREATOR PROGRAM_NAME SCHEDULE_NAME STATE
----------------- ------------------------------ -------------------- --------------------------
SYS CDC$A_CDC_DIST_HOT_CHANGE_ PUBL_CDC_TARGET APPLY_PROGRESS_PROG DISABLED
SYS SM$CLEAN_AUTO_SPLIT_MERGE SYS SCHEDULED
SYS RSE$CLEAN_RECOVERABLE_SCRIPT SYS SCHEDULED
SYS FGR$AUTOPURGE_JOB SYS DISABLED
SYS BSLN_MAINTAIN_STATS_JOB SYS BSLN_MAINTAIN_STATS_PROG BSLN_MAINTAIN_STATS_SCHED SCHEDULED
SYS XMLDB_NFS_CLEANUP_JOB SYS DISABLED
SYS DRA_REEVALUATE_OPEN_FAILURES SYS MAINTENANCE_WINDOW_GROUP SCHEDULED
SYS HM_CREATE_OFFLINE_DICTIONARY SYS MAINTENANCE_WINDOW_GROUP DISABLED
SYS ORA$AUTOTASK_CLEAN SYS ORA$AGE_AUTOTASK_DATA DAILY_PURGE_SCHEDULE SCHEDULED
SYS FILE_WATCHER SYS FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE DISABLED
SYS PURGE_LOG SYS PURGE_LOG_PROG DAILY_PURGE_SCHEDULE SCHEDULED
ORACLE_OCM MGMT_STATS_CONFIG_JOB SYS SCHEDULED
ORACLE_OCM MGMT_CONFIG_JOB SYS MAINTENANCE_WINDOW_GROUP SCHEDULED
PUBL_CDC_TARGET CDC$_DEFAULT_PURGE_JOB PUBL_CDC_TARGET SCHEDULED

Then I see job “CDC$_DEFAULT_PURGE_JOB” is running and owned by CDC publisher “PUBL_CDC_TARGET”

Check the current schedule of the CDC purge job:

SQL> select OWNER,JOB_NAME,START_DATE,REPEAT_INTERVAL, RUN_COUNT from dba_scheduler_jobs where owner='PUBL_CDC_TARGET';

OWNER JOB_NAME START_DATE REPEAT_INT RUN_COUNT
----------------- ---------------------------- ------------------------------------ ------------
PUBL_CDC_TARGET CDC$_DEFAULT_PURGE_JOB 18-APR-14 12.40.50.000000 AM -05:00 FREQ=DAILY; INTERVAL=1 1

–This shows that job runs daily every 24 hours, now login and job owner and change schedule:

SQL> conn PUBL_CDC_TARGET/*******
Connected.

SQL> BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"PUBL_CDC_TARGET"."CDC$_DEFAULT_PURGE_JOB"',
attribute => 'repeat_interval',
value => 'freq=weekly;byday=FRI;byhour=20;byminute=0; bysecond=0');
END;
/

PL/SQL procedure successfully completed.

SQL> select OWNER,JOB_NAME,START_DATE,REPEAT_INTERVAL, RUN_COUNT from dba_scheduler_jobs where owner='PUBL_CDC_TARGET';

OWNER JOB_NAME START_DATE REPEAT_INTERVAL RUN_COUNT
----------------- ------------------------------ ------------------------- ---------------------
PUBL_CDC_TARGET CDC$_DEFAULT_PURGE_JOB 18-APR-14 12.40.50.000000 freq=weekly;byday=FRI;byhour=20;byminute=0; byseco 1
 AM -05:00 nd=0

 

Oracle datafile missing ORA-01157, ORA-01110, ORA-27037

Problem:

ORA-01157, ORA-01110, ORA-27037

Problem definition:

I had an issue where one my associated deleted the datafile from few tablespaces accidentally. We did not have any RMAN backup configured as this was a QA DB. Also, the alert reported in the alert logfile showed the datafile name had garbage characters associated with the datafile name.

Snippets from the alert logfile are given below:

ALTER DATABASE OPEN
Thu Mar 13 09:15:41 2014
Errors in file /u01/app/oracle/admin/ROPQADB/bdump/ropqadb_dbw0_1704168.trc:
ORA-01157: cannot identify/lock data file 140 – see DBWR trace file
ORA-01110: data file 140: ‘/u04/oradata/ROPPDDB/scpodata_15.dbf^[[D^[[D^[[D^[[D^[[D6^[[C^[[C^[[C^[[C’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Thu Mar 13 09:15:41 2014
Errors in file /u01/app/oracle/admin/ROPQADB/bdump/ropqadb_dbw0_1704168.trc:
ORA-01157: cannot identify/lock data file 146 – see DBWR trace file
ORA-01110: data file 146: ‘/u04/oradata/ROPPDDB/scpoindex_17.dbf^[[D^[[D^[[D^[[D^[[D^[[3~8^[[C^[[C^[[C^[[C’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Thu Mar 13 09:15:41 2014
Errors in file /u01/app/oracle/admin/ROPQADB/bdump/ropqadb_dbw0_1704168.trc:
ORA-01157: cannot identify/lock data file 155 – see DBWR trace file
ORA-01110: data file 155: ‘/u03/oradata/ROPPDDB/tbs_arch_data_05.dbf^[[D^[[D^[[D^[[D^[[D^[[3~6^[[C^[[C^[[C^[[C’
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

Resolution:

1. Open the database in mount phase:

startup mount

2. Drop datafiles entries that were reported in the alert logfile.

alter database datafile ‘<DATAFILE_NAME>’ offline drop;

If you look at the abstract from the alert logfile, the junk characters in the datafile did not allow me to drop datafile with its absolute file name. Thus, I dropped datafile using its file id:

Failures:

Thu Mar 13 10:00:42 2014
alter database datafile ‘/u03/oradata/ROPPDDB/tbs_arch_data_05.dbf’ offline drop
ORA-1516 signalled during: alter database datafile ‘/u03/oradata/ROPPDDB/tbs_arch_data_05.dbf’ offline drop…
Thu Mar 13 10:03:08 2014
alter database datafile ‘/u03/oradata/ROPPDDB/tbs_arch_data_05.dbf’ offline drop
Thu Mar 13 10:03:08 2014
ORA-1516 signalled during: alter database datafile ‘/u03/oradata/ROPPDDB/tbs_arch_data_05.dbf’ offline drop…
Thu Mar 13 10:05:00 2014
alter database datafile ‘/u04/oradata/ROPPDDB/scpoindex_17.dbf^[[D^[[D^[[D^[[D^[[D^[[3~8^[[C^[[C^[[C^[[C’ offline drop
Thu Mar 13 10:05:00 2014
ORA-1516 signalled during: alter database datafile ‘/u04/oradata/ROPPDDB/scpoindex_17.dbf^[[D^[[D^[[D^[[D^[[D^[[3~8^[[C^[[C^[[C^[[C’ offline drop…

Success:

Thu Mar 13 10:05:46 2014
alter database datafile 155 offline drop
Thu Mar 13 10:05:46 2014
Completed: alter database datafile 155 offline drop
Thu Mar 13 10:06:59 2014
alter database datafile 140 offline drop
Thu Mar 13 10:06:59 2014
Completed: alter database datafile 140 offline drop
Thu Mar 13 10:07:07 2014
alter database datafile 146 offline drop
Completed: alter database datafile 146 offline drop

3. Open the database:

alter database open;

Alert logfile abstract:

Thu Mar 13 10:07:20 2014
alter database open
Thu Mar 13 10:07:21 2014
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes

……. some more logs……

Thu Mar 13 10:07:59 2014
Completed: alter database open

4. Take the list of datafiles querying DBA_TABLESPACES:

This is an important step as this will give you the number of datafiles and current size of each datafile. I again state that this is important because in next step we are going to drop tablespaces that have missing datafile which we dropped in step 3.

set lines 200
col file_name format a50
set pages 30

select file_id,file_name, bytes/1024/1024/1024, autoextensible, maxbytes/1024/1024/1024 from dba_data_files
where tablespace_name='<TABLESPACE_NAME>’
/

Do this for each tableaspace.

5. Drop tablespaces whose files are missing:

drop tablespace <tbsp_name> including contents and datafiles;

6. Recreate dropped tablespaces