Recently while adding a datafile to system tablespace, one of our dba's made a mistake by inserting a white space in between "quote" and "plus" sign in ASM aware environment. Since it was a production we had to restore this wrongly added datafile to the ASM location as soon as possible.
We reproduced this issue in one of our test environment.
Steps to reproduce the issue:
[oracle@dbstnd ~]$ ps -ef | grep pmon
grid 18985 1 0 00:08 ? 00:00:00 asm_pmon_+ASM
oracle 28511 1 0 01:41 ? 00:00:00 ora_pmon_TEST
oracle 28983 28923 0 01:53 pts/5 00:00:00 grep --color=auto pmon
[oracle@dbstnd ~]$ . oraenv
ORACLE_SID = [TEST] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 01:53:51 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
TEST OPEN
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DATA/test/system01.dbf
+DATA/test/sysaux01.dbf
+DATA/test/undotbs01.dbf
+DATA/test/users01.dbf
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbstnd ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 13 01:56:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2215202024)
RMAN> backup database plus archivelog;
Starting backup at 13-JUL-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
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=2 RECID=1 STAMP=917056597
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/annnf0_tag20160713t015637_0.268.917056599 tag=TAG20160713T015637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
Starting backup at 13-JUL-16
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=+DATA/test/system01.dbf
input datafile file number=00002 name=+DATA/test/sysaux01.dbf
input datafile file number=00003 name=+DATA/test/undotbs01.dbf
input datafile file number=00004 name=+DATA/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/nnndf0_tag20160713t015639_0.269.917056599 tag=TAG20160713T015639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/ncsnf0_tag20160713t015639_0.270.917056625 tag=TAG20160713T015639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
Starting backup at 13-JUL-16
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=3 RECID=2 STAMP=917056626
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/annnf0_tag20160713t015706_0.272.917056627 tag=TAG20160713T015706 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
RMAN> restore database preview;
Starting restore at 13-JUL-16
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1014.30M DISK 00:00:16 13-JUL-16
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160713T015639
Piece Name: +DATA/test/backupset/2016_07_13/nnndf0_tag20160713t015639_0.269.917056599
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 931310 13-JUL-16 +DATA/test/system01.dbf
2 Full 931310 13-JUL-16 +DATA/test/sysaux01.dbf
3 Full 931310 13-JUL-16 +DATA/test/undotbs01.dbf
4 Full 931310 13-JUL-16 +DATA/test/users01.dbf
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 3 A 13-JUL-16
Name: +DATA/test/archivelog/2016_07_13/thread_1_seq_3.271.917056627
Media recovery start SCN is 931310
Recovery must be done beyond SCN 931310 to clear datafile fuzziness
Finished restore at 13-JUL-16
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 02:00:02 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
Here we added a datafile with white space.
SQL> ALTER TABLESPACE "SYSTEM" ADD DATAFILE ' +DATA' SIZE 100M;
Tablespace altered.
Now we can see it has been added to the default location of Oracle_home/dbs. Since everything that will use this datafile have to throw an error and our backps were also regurlay failing because of this.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DATA/test/system01.dbf
+DATA/test/sysaux01.dbf
+DATA/test/undotbs01.dbf
+DATA/test/users01.dbf
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +DATA
SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME FILE_ID
----------------------------------------------------------------------
+DATA/test/system01.dbf 1
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +DATA 5
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 02:09:44 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database Buffers 243269632 bytes
Redo Buffers 2842624 bytes
Database mounted.
SQL>
++ Open a new session:
---------------------
[oracle@dbstnd ~]$ . oraenv
ORACLE_SID = [oracle] ? TEST
The Oracle base has been set to /u01/app/oracle
[oracle@dbstnd ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 13 02:11:09 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2215202024, not open)
RMAN> copy datafile 5 to '+DATA';
Starting backup at 13-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/dbhome_1/ dbs/ +DATA
output file name=+DATA/test/datafile/system.273.917057511 tag=TAG20160713T021150 RECID=1 STAMP=917057510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy "+DATA/test/datafile/system.273.917057511"
Then open the database which is mounted:
---------------------------------------
SQL> alter database open;
Database altered.
SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME FILE_ID
----------------------------------------------------------------------
+DATA/test/system01.dbf 1
+DATA/test/datafile/system.273.917057511 5
We reproduced this issue in one of our test environment.
Steps to reproduce the issue:
[oracle@dbstnd ~]$ ps -ef | grep pmon
grid 18985 1 0 00:08 ? 00:00:00 asm_pmon_+ASM
oracle 28511 1 0 01:41 ? 00:00:00 ora_pmon_TEST
oracle 28983 28923 0 01:53 pts/5 00:00:00 grep --color=auto pmon
[oracle@dbstnd ~]$ . oraenv
ORACLE_SID = [TEST] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 01:53:51 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
TEST OPEN
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DATA/test/system01.dbf
+DATA/test/sysaux01.dbf
+DATA/test/undotbs01.dbf
+DATA/test/users01.dbf
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbstnd ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 13 01:56:19 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2215202024)
RMAN> backup database plus archivelog;
Starting backup at 13-JUL-16
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
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=2 RECID=1 STAMP=917056597
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/annnf0_tag20160713t015637_0.268.917056599 tag=TAG20160713T015637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
Starting backup at 13-JUL-16
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=+DATA/test/system01.dbf
input datafile file number=00002 name=+DATA/test/sysaux01.dbf
input datafile file number=00003 name=+DATA/test/undotbs01.dbf
input datafile file number=00004 name=+DATA/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/nnndf0_tag20160713t015639_0.269.917056599 tag=TAG20160713T015639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/ncsnf0_tag20160713t015639_0.270.917056625 tag=TAG20160713T015639 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
Starting backup at 13-JUL-16
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=3 RECID=2 STAMP=917056626
channel ORA_DISK_1: starting piece 1 at 13-JUL-16
channel ORA_DISK_1: finished piece 1 at 13-JUL-16
piece handle=+DATA/test/backupset/2016_07_13/annnf0_tag20160713t015706_0.272.917056627 tag=TAG20160713T015706 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
RMAN> restore database preview;
Starting restore at 13-JUL-16
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1014.30M DISK 00:00:16 13-JUL-16
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160713T015639
Piece Name: +DATA/test/backupset/2016_07_13/nnndf0_tag20160713t015639_0.269.917056599
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 931310 13-JUL-16 +DATA/test/system01.dbf
2 Full 931310 13-JUL-16 +DATA/test/sysaux01.dbf
3 Full 931310 13-JUL-16 +DATA/test/undotbs01.dbf
4 Full 931310 13-JUL-16 +DATA/test/users01.dbf
List of Archived Log Copies for database with db_unique_name TEST
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 3 A 13-JUL-16
Name: +DATA/test/archivelog/2016_07_13/thread_1_seq_3.271.917056627
Media recovery start SCN is 931310
Recovery must be done beyond SCN 931310 to clear datafile fuzziness
Finished restore at 13-JUL-16
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 02:00:02 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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
Here we added a datafile with white space.
SQL> ALTER TABLESPACE "SYSTEM" ADD DATAFILE ' +DATA' SIZE 100M;
Tablespace altered.
Now we can see it has been added to the default location of Oracle_home/dbs. Since everything that will use this datafile have to throw an error and our backps were also regurlay failing because of this.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DATA/test/system01.dbf
+DATA/test/sysaux01.dbf
+DATA/test/undotbs01.dbf
+DATA/test/users01.dbf
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +DATA
SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME FILE_ID
----------------------------------------------------------------------
+DATA/test/system01.dbf 1
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +DATA 5
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@dbstnd ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 13 02:09:44 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database Buffers 243269632 bytes
Redo Buffers 2842624 bytes
Database mounted.
SQL>
++ Open a new session:
---------------------
[oracle@dbstnd ~]$ . oraenv
ORACLE_SID = [oracle] ? TEST
The Oracle base has been set to /u01/app/oracle
[oracle@dbstnd ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 13 02:11:09 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2215202024, not open)
RMAN> copy datafile 5 to '+DATA';
Starting backup at 13-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/dbhome_1/ dbs/ +DATA
output file name=+DATA/test/datafile/system.273.917057511 tag=TAG20160713T021150 RECID=1 STAMP=917057510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-JUL-16
RMAN> switch datafile 5 to copy;
datafile 5 switched to datafile copy "+DATA/test/datafile/system.273.917057511"
Then open the database which is mounted:
---------------------------------------
SQL> alter database open;
Database altered.
SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME FILE_ID
----------------------------------------------------------------------
+DATA/test/system01.dbf 1
+DATA/test/datafile/system.273.917057511 5
No comments:
Post a Comment