To do some application and performances tests, I had to copy a database from a third party Linux server to an ODA X7-2M. Looks pretty simple on the paper, but 2 small challenges came into the game. The first was that of course the source database was in Non-OMF while ODA works fully in OMF. The second was that the source database is running 11.2.0.3 which is not supported and cannot be installed on the ODA “lite”. Therefore I had to find a way to copy the database on 11.2.0.4 binaries and get the upgrade done before opening it.

My first idea was of course to do a duplicate of the source database to the ODA.  To get everything ready on ODA side (folders, instance…), I simply created an 11.2.0.4 database using ODACLI CREATE-DATABASE and then shut it down to delete all data files, redo logs and control files.

As duplicate from active database wasn’t possible, I checked the backup of the source database and looked for the best SCN to get to. Once I had defined this I could start preparing my duplicate as following:

RMAN> run {
2> set until scn XXXXXXXXX;
3> allocate channel t1 type disk;
4> allocate channel t2 type disk;
5> allocate channel t3 type disk;
6> allocate channel t4 type disk;
7> allocate auxiliary channel a1 type disk;
8> allocate auxiliary channel a2 type disk;
9> allocate auxiliary channel a3 type disk;
10> allocate auxiliary channel a4 type disk;
11> duplicate target database to 'DBTST1';
12> }

As explained above the first little challenge here was that my target database is in Non-OMF and I wanted to make it “proper” on ODA which means OMF based structure.

Usually in a duplicate you would use db_file_name_convert and log_file_name_convert to change the path of the files. The issue with this solution is that it will not rename files except if you do it file per file.

The second option is to use in RMAN the command SET NEWNAME FOR DATAFILE. Here same “issue” I have to do it file per file and I had more than 180 files. Of course I could easily script it with SQLPlus but the list would be awful and not easy to crosscheck  if I’m missing anything. In addition doing the SETNAME requires to take some precaution as the file name still need to be OMF generated. This can be handled by providing followings string for the filename: 01_mf_<dbname>_%u.dbf

However I still wanted a more “elegant” way. The solution indeed was simply to use SET NEWNAME FOR DATABASE in conjunction with the TO NEW option. This automatically generates a new file name for all database files. The condition there is that following parameters are properly set on the auxiliary database:

  • db_create_file_dest
  • db_create_online_log_dest_n
    Configure from 1 to 5 depending on the number of members you want per redo log group
  • control_files
    Should be reset as new control file(s) name(s) will be generated

So I got finally the following RMAN script to run the duplicate:

RMAN> run {
2> set until scn XXXXXXXXX;
3> set newname for database to new;
4> allocate channel t1 type disk;
5> allocate channel t2 type disk;
6> allocate channel t3 type disk;
7> allocate channel t4 type disk;
8> allocate auxiliary channel a1 type disk;
9> allocate auxiliary channel a2 type disk;
10> allocate auxiliary channel a3 type disk;
11> allocate auxiliary channel a4 type disk;
12> duplicate target database to 'DBTEST1';
13> }

At this point I solved the Non-OMF to OMF conversion issue and almost got a copy of my database on the ODA.

Why almost? Simply because the duplicate failed 🙂

Indeed this is fully “normal” and part of the process. As you know the last step in a duplicate is ALTER CLONE DATABASE OPEN RESETLOGS on the auxiliary database. However the database was still in 11.2.0.3 while the binaries on ODA are 11.2.0.4. The result was the duplicate crashing on last step as binaries are not compatible.

This didn’t really matter as the restore and recover operation worked meaning that my database was on a consistent point in time. Unfortunately simply opening the database with ALTER DATABASE OPEN RESETLOGS UPDATE did not work claiming that the database need media recovery

SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u02/app/oracle/oradata/DBTST1_SITE1/DBTST1_SITE1/datafile/o1_mf_system_f98
0sv9j_.dbf'

My first idea here was to try a RECOVERY UNTIL CANCEL and then try again but I had nothing else than the last archive logs applied during the duplicate to recover 🙁

Another situation where you have to open a database with RESETLOGS is when you restored the control files. So I chose to re-create the control file with an SQL script.

CREATE CONTROLFILE REUSE DATABASE "QUANTUMQ" RESETLOGS ARCHIVELOG
MAXLOGFILES 202
MAXLOGMEMBERS 5
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 33012
LOGFILE
GROUP 1 SIZE 1G BLOCKSIZE 512,
GROUP 2 SIZE 1G BLOCKSIZE 512,
GROUP 3 SIZE 1G BLOCKSIZE 512
DATAFILE
'/u02/app/oracle/oradata/DBTST1_SITE1/DBTST1_SITE1/datafile/o1_mf_system_f980sv9j_.dbf',
'/u02/app/oracle/oradata/DBTST1_SITE1/QUANTUMQ_SITE1/datafile/o1_mf_sysaux_f97x0l8m_.dbf',
'/u02/app/oracle/oradata/DBTST1_SITE1/QUANTUMQ_SITE1/datafile/o1_mf_undotbs1_f97w67k2_.dbf',
'/u02/app/oracle/oradata/DBTST1_SITE1/QUANTUMQ_SITE1/datafile/o1_mf_users_f97w67md_.dbf',
...
...
CHARACTER SET AL32UTF8;

The question  here was where to find the different information for my script as the BACKUP TO TRACE does not work in MOUNT status?

I used following statements

SQL> select type,RECORDS_TOTAL from v$controlfile_record_section;

TYPE			     RECORDS_TOTAL
---------------------------- -------------
DATABASE				 1		==> MAXINSTANCE (is obvious as I'm in single instance :-) )
CKPT PROGRESS				11
REDO THREAD				 8
REDO LOG			       202		==> MAXREDOLOG
DATAFILE			       200		==> MAXDATAFILE
FILENAME			      3056
TABLESPACE			       200
TEMPORARY FILENAME		       200
RMAN CONFIGURATION			50
LOG HISTORY			     33012		==> MAXLOGHISTORY
OFFLINE RANGE			       245
...
...

 

SQL> select group#,members,bytes/1024/1024,blocksize from v$log;

    GROUP#    MEMBERS BYTES/1024/1024  BLOCKSIZE
---------- ---------- --------------- ----------
	 1	    2		 1024	     512
	 2	    2		 1024	     512
	 3	    2		 1024	     512

 

SQL> select '''' || name || ''''||',' from v$datafile order by file# asc;

''''||NAME||''''||','
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'/u02/app/oracle/oradata/DBTST1_SITE1/DBTST1_SITE1/datafile/o1_mf_system_f980sv9j_.dbf',
'/u02/app/oracle/oradata/DBTST1_SITE1/DBTST1_SITE1/datafile/o1_mf_sysaux_f97x0l8m_.dbf',
'/u02/app/oracle/oradata/DBTST1_SITE1/DBTST1_SITE1/datafile/o1_mf_undotbs1_f97w67k2_.dbf',
...
...

 

Once the control file was re-created the OPEN RESETLOGS was still failing with an ORA-01194. Hmm.. same issue.
Then I finally tried to recover the only files I had, the new empty redo logs

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 20833391792 generated at 02/26/2018 14:36:55 needed for
thread 1
ORA-00289: suggestion :
/u03/app/oracle/fast_recovery_area/DBTST1_SITE1/archivelog/2018_02_26/o1_mf_1_
1_%u_.arc
ORA-00280: change 20833391792 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u03/app/oracle/redo/DBTST1_SITE1/onlinelog/o1_mf_1_f983clsn_.log
Log applied.
Media recovery complete.

Successful media recovery, great!

Finally I got my database open in RESET LOG mode!

SQL> alter database open resetlogs upgrade;

Database altered.

At this point I just had to follow the traditional upgrade process from 11.2.0.3 to 11.2.0.4. The last trap was to not forget creating the TEMP files for the tablespace.

SQL> alter tablespace TEMP add tempfile size 30G;

Tablespace altered.

SQL> alter tablespace TEMP add tempfile size 30G;

Tablespace altered.

 

Then the upgrade process is quite easy:

  1. Run utlu112i.sql as pre-upgrade script
  2. Run catupgrd.sql for the upgrade
  3. Restart the database
  4. Run utlu112is.sql as post-upgrade script and make sure no error is shown and all components are valid
  5. Run catuppst.sql to finalize the upgrade
  6. Run utlrp.sql to re-compile the invalid object

Should you forget to add the temp files in the temporary tablespace you will get multiple errors ORA-25153 “Temporay Tablespace Is Empty” (see note 843899.1). Basically the only thing to do in such a case is to add the temp files and re-run catupgrd.sql

Cheers!

Post Views: 490
Thumbnail [60x60]
by
David Hueber