Exadata X8M-2 & PCA X8-2 -- Part 3 Migration / Datapump for relatively small tables and indexes + Datapump dump file I/O

We have used online redefinition for moving big tables with big LOB columns from one tablespace to another and we have seen it in the previous post.

In this post, we will take a look at the datapump process that we used for moving the small sized tables ( LOB-free tables smaller than 10GBs -- )

Datapump is a tool that we use in logical migrations, some logical backups and in some other daily operations, sometimes for the upgrades and I think you all know that. So this blog post is written for telling you the story, the general process -- not the details of the well known datapump tool. 

Let's start with an interesting event that we suddenly found ourself in..  That is, datapump export was very slow. We were exporting the data into an ACFS mount exported from an Exadata (using NFS --through a 10 gig network).. So it shouldn't be anywhere near slow.. But! suprisingly it was...

We saw datapump dump file I/O events in the parallel export sessions.. So we suspected from the ACFS..  We did a couple of dd tests on the server (HP UX in this case).. Pure sequential I/O to the NFS share was slow.. However; dd tests on Exadata side performed well.. Then we saw that a parallel & full rman backup was running on Exadata side. A backup software (through a rman channel) was transffering the blocks to a network storage.. There was no backup network there.. So it was the network bandwitdh causing the slowness.. Our ACFS I/O was based on NFS in the first place, so cancelled the backup and everything went fine!  -- an interesting event, right? 

Anyways, just for the quick info, we are moving tables from one tablespace to another just to get rid of an offline, unrecoverable datafile because it prevents us using XTTS for our main goal. That is, migrating one of our database from HPUX to Exadata X8-2M..

Let's tell this datapump part of the story like we're doing it right now;

We have a limited downtime, so the process should be quick.. This is a production and that's why the process should be complete & error-free as well..

Here is the process;

• Before importing the tables, we disable the DDL trigger (usually we have at least one in almost every customer environment, and usually they create problems -- grant-related access problems --during import/impdp ..)
• We also ensure that we have the required free space in the target tablespace and we also ensure the users of the objects that we are planning to move, have quota on the target tablespace.
• We check the current situation, I mean row counts, table counts, constraints, MV logs, index counts, object validity, grants etc, and we note all the check that we do at this point.. ( we will compare them with the checks that we will do after we complete movimng the objects to the target tablespace)

Some easy examples for the check queries:

select table_name from dba_tables where tablespace_name='ERMAN_TBS';

select count(*) from dba_tables where tablespace_name='ERMAN_TBS'; 

select  index_name from dba_indexes where tablespace_name='ERMAN_TBS';

select  count(*) from dba_indexes where tablespace_name='ERMAN_TBS';

select index_name from dba_indexes where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select count(*) from dba_indexes where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select trigger_name from dba_triggers where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select count(*) from dba_triggers where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select * from dba_tab_privs where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select count(*) from dba_tab_privs where table_name in (select table_name from dba_tables where tablespace_name='ERMAN_TBS');

select owner, object_type, count (*) from dba_objects where status='INVALID' group by owner, object_type order by 1,2;

• We get our downtime, shutdown the apps tier, shutdown the db listener, ensure we have no sessions in the database, no session other than us.. Ofcourse we can also take other session-blocking-actions, but these are enough in lots of the cases..
• We export the tables we interested in;

Example command:

nohup expdp \"/ as sysdba\" DIRECTORY=ERM_DIR  EXCLUDE=TABLE:\"IN\(\'S_ERMAN\',\'S_ERMAN_INTERIM\',\'S_ERM_EMP\',\'S_ERM_LOG\',\'S_ERM_ITEM\',\'S_ERM_RP_LOG\'\)\"   DUMPFILE=ERM_%U.dmp  LOGFILE=ERM_20210813.log TABLESPACES=ERMAN  PARALLEL=8 &

• Then we import them into the new tablespace. I will not give all the details about the datapump arguments used in this example. I think you already understand what we are trying to do here, so the arguments are aligned with our goal. (If not read above once again + read the previous blog post..)

nohup impdp \"/ as sysdba\" DIRECTORY=ERM_DIR DUMPFILE=SDATA_%U.dmp LOGFILE=imp_ERM_20210813.log table_exists_action=replace remap_tablespace=ERMAN:ERMAN_NEW PARALLEL=8 &       

• After the datapump import, we do our checks(object comparison etcs..), compile invalids, take the corrective actions for the errors that we may have during the import..

• Finally, we enable the DDL trigger (if we have one) and start our listener + applications

That's it.. 

At this point, we can get rid of that problematic datafile residing in the old tablespace.. We moved everything from that tablespace to the new one, right? It is time to drop the old tablespace (optional) and to continue on our way with the Exadata XTTS migration.

Exadata X8M-2 & PCA X8-2 -- Part 3 Migration / ONLINE REDEFINITION for Big LOB Tables

In the previous post, I mentioned that we decided to move the objects residing in the problematic tablespace ( having an offline datafile) to a new tablespace and we decided on a hybrid method for accomplishing this task. We planned to move the big LOB tables using the Online Redefinition .. In addition to that, we decided to use datapump (expdp - impdp) to move the small sized tables to the new tablespace with a minimal downtime.

In this blog post, I will take a look at the Online Redefinition side of the work and give you the general information about the process.

First of all , we couldn't move tables online with the alter table move online clause, since the database version was 12.1.

We couldn't use expdp-impdp to move the big LOB tables, because it required a big downtime +  the LOBs were Basicfile -- expdp can not leverage parallelism for the Basicfile lobs.

So, we decided to move those big lob tables (some of them were bigger than 1.5 TB) using online redefitinion and it almost perfectly worked!

Note that, no one needs to be afraid of the online redefinition as it is used with basic API calls and it is pretty straight forward. It is based on the materialized views (MVs) and refresh technology that we already have in Oracle Databases for a very long time.. 

What happens in the background is; a MV based refresh and alter table rename based switch operations..

So, a MV is created, then it is fully refreshed and then it is synched with the fast refreshes (by the help of the MV logs) and then the object names are switched -- just after the final last sync... 

All of these are done via API. So you don't need to know the internals to do the work..

Let's go through the process with an example;

We first created our target/new tablespace with the CRATE TABLESPACE caluse;

CREATE TABLESPACE ERMAN_NEW DATAFILE '+ERM' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE 32767M, '+ERM' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE 32767M, '+ERM' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE 32767MLOGGING
DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON;

We checked if we can do use online redefition for moving the table that we interested in.

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('ERMAN','S_ERMAN', DBMS_REDEFINITION.CONS_USE_ROWID); END; /

We checked the source LOB columns.. We didn't want to get ORA-01555 or ORA-22924 in the middle of our operation.. So we used a big enough LOB retention (based on undo retention) or PCTVERSION 100 for the source LOB columns.. ( we alter them that way..)

Note that; if you see ORA-22924 in the same error stack with ORA-01555 then it means; the problem is directly related with the LOBs.

Error: ORA-22924
Text: snapshot too old
---------------------------------------------------------------------------
Cause: The version of the LOB value needed for the consistent read was already overwritten by another
writer.

Note that, we use PCTVERSION or RETENTION, not both at the same time.

select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('S_ERMAN');

In our case we used PCTVERSION.. In our case, using PCTVERSION was a more guarenteed method.

Some examples for altering the LOBs -> 

alter table ERMAN.S_ERMAN modify lob(VIDEO) (retention);
alter table SIEBEL.S_ERMAN modify lob(PICTURE) (PCTVERSION 100);

Well, after creating the new tablespace and altering the LOBs, we created our target table.. Target table had the same structure as the source ( ofcourse we could do some improvements in the definitions in the definition, like changing the LOBS from basicfile to securefile).. We created the new table in the new ta lespace..

CREATE TABLE ERMAN.S_ERMAN_NEW(
ROW_ID VARCHAR2(15 CHAR), CREATED DATE DEFAULT sysdate, CREATED_BY VARCHAR2(15 CHAR), LAST_UPD DATE DEFAULT sysdate,
.....
...

LOB (PICTURE) STORE AS SECUREFILE ( TABLESPACE ERMAN_NEW ENABLE STORAGE IN ROW CHUNK 8192 RETENTION AUTO NOCACHE LOGGING STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ))

...
LOGGING NOCOMPRESS NOCACHE MONITORING ENABLE ROW MOVEMENT;

We checked our redologs and ensure they are aligned to support this heavy operation.

SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP#;

We might add some redologs if we thought that it is required for the operation.. ( Big tables would produce big redo -- and archivelogs)

Example: ALTER DATABASE ADD LOGFILE GROUP 4 ('+ERMAN','+RECO') SIZE 2048M;

Well, we started our redefitinion operation finally :) -> 

--we started it with 4 parallel in the case.

ALTER SESSION FORCE PARALLEL DML PARALLEL 4; 

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4; 

BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( uname => 'ERMAN', orig_table => 'S_ERMAN', int_table => 'S_ERMAN_NEW', options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

 END; /

Note that, if we have a primary key in the source table, then we use CONS_USE_PK instead of CONS_USE_ROWID..

START_REDEF_TABLE takes some time as it is where the initialization/full copy happens.

After the START_REDEF_TABLE completed, we copied the dependent objects from the source table to target table.

DECLARE error_count pls_integer := 0; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ERMAN', 'S_ERMAN', 'S_ERMAN_NEW', dbms_redefinition.cons_orig_params, FALSE,TRUE,TRUE,FALSE, error_count); 

DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count)); END; /

Note that, if you  have materialized view logs on the table that you want to redef, then you should consider using copy_mvlog argument of copy_table_dependents.. It should be TRUE (by default it is false).. Alternatively, you should leave it FALSE , and recreate MV logs after you finish redef.

You can check the definition of the arguments used in COPY_TABLE_DEPENDENTS procedure, but one thing to point out for our case is that, we told the procedure to not to copy the triggers.. 

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2, copy_indexes IN PLS_INTEGER := 1, copy_triggers IN BOOLEAN := TRUE, copy_constraints IN BOOLEAN := TRUE, copy_privileges IN BOOLEAN := TRUE, ignore_errors IN BOOLEAN := FALSE, num_errors OUT PLS_INTEGER, copy_statistics IN BOOLEAN := FALSE);

We did so, because of a bug in our release , an ORA-600 produced in the later sync phase.. 

(ORA-00600: internal error code, arguments: [kglidinsi1], [2], [], [], [], [], [], [], [], [], [], [])

So we decided to create the triggers manually just after the redef is finished / just before letting applications to access the new-current table..

Just after copying the dependents objects, we put an exclusive lock in the table.. (ofcourse relevant application modules were stopped at the same time)  Normally, you don't have to do this but we just wanted to guarantee the no-access. We wanted to guarantee that because in case of a failure in the later stages of the redefitinion, we might want to continue the redefitinion manually.. I mean dropping the MV and making the new table the actual one manually. 

--again; it is not a must.

LOCK TABLE ERMAN.S_ERMAN IN EXCLUSIVE MODE;

So before finishing our redef , we compared the target and source tables and ensured they are structurally similar. We also checked the invalid object count just in case. We even checked the row counts -- just in case.

select * from dba_objects where status='INVALID'; 

select * from dba_triggers where table_name='S_ERMAN'; 

select * from dba_indexes where status!='VALID'; 

select count(*) from ERMAN.S_ERMAN;
select count(*) from ERMAN.S_ERMAN_NEW;
select count(*) , 'source_table' from dba_constraints where table_name='S_ERMAN' UNION ALL select count(*) ,'interim_table' from dba_constraints where table_name='S_ERMAN_NEW'; 

select count(*) , 'source_table' from dba_triggers where table_name='S_ERMAN' UNION ALL select count(*) ,'interim_table' from dba_triggers where table_name='S_ERMAN_NEW';

select count(*) , 'source_table' from dba_indexes where table_name='S_ERMAN' UNION ALL select count(*) ,'interim_table' from dba_indexes where table_name='S_ERMAN_NEW';

Well at this point we synchronized the new table; ( a fast refresh)

EXEC DBMS_REDEFINITION.sync_interim_table('ERMAN','S_ERMAN','S_ERMAN_NEW'); 

Lastly, we finished the redef, and our new table was actualized at this point.

.EXEC DBMS_REDEFINITION.finish_redef_table('ERMAN','S_ERMAN','S_ERMAN_NEW');

Note, in case you get any errors in this stage, you should check the detail of the error and get the information about which stage it failed and then you may find a way to continue manually from there.. However, what is recommended is to abort the redef, solve the problem and start another redef/retry.

Abort redef example:

EXEC DBMS_REDEFINITION.abort_redef_table('ERMAN','S_ERMAN','S_ERMAN_NEW');

Well, after the redef was finished, we checked the new table and ensured it is actualized and its name was switched with the old current table and it was now in the new tablespace..

select table_name,tablespace_name from dba_tables where table_name in('S_ERMAN','S_ERMAN_NEW');

Note that, S_ERMAN is the new table now and it is in the new tablespace.. So names/tables were switched!

2 important notes on 2 dangerous points;

1) Table statistics are important. If you do not copying statistics in the copy dependents phase, then you may get yourself in to trouble. So either copy them in the copy dependents phase or copy them before starting the redef phase -- manually (this is also a little adventurous --  I mean copying table stats from one table to another in the same database is a little tricky and requires a hack and it is not always successful..)

Statistics level is also important.. In some environments, column-level should also be there..

Stats collection example: 

EXEC dbms_stats.gather_table_stats('ERMAN','S_ERMAN', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL', degree => 8 ,cascade => true, estimate_percent => dbms_stats.auto_sample_size);

Note that, we also have copy_statistics argument available in copy_table_dependents.. So we may even make redefinition to that statistics task during our redef process...

2) Constraints may be created in the target table without enable validate.. So you might need to alter the target table to make them enable validate..

Well, before finishing the work we did the following checks;

--Check errors 

 SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS;

- VERIFY THAT THE PRMARY KEY WAS COPIED TO THE REDEFINED TABLE 

 select DBMS_METADATA.GET_DDL('TABLE','S_ERNAN','TEST') FROM DUAL;

We might drop the old table.. But we didn't.. We didn't have a space pressure and the migration that we would do later was based on XTTS method. So we have the opportunity to tell the XTTS not to migrate the old tablespace in the first place.

-- DROP THE OLD TABLE (as it is no longer needed) 

 drop table S_ERMAN_NEW;

Okay. I suppose we are done for today :)

Next post will be about those remaining small tables which were moved using datapump.. 

I know using datapump is superclear for most of you, but we are telling our story here, story of a big migration project :) So I try to explain every stage and want to share real life cases as much as I can :) 

Stay tuned.

Exadata X8M-2 & PCA X8-2 -- Part 3 Migration / OFFLINE Datafile preventing XTTS

Quick intro; Already migrated the DWH to Exadata X8-2M and saw a dramatic increase in ETL performance without even touching anything, without even doing any migration improvements :)

Today's blog post is about a different topic though.. I mean it is related with this migration project, but it is not directly related with the migration operations. So this is something that prevented us migrating a database to Exadata X8-2M using XTTS method. (Remember; in this project, our source platform is HP-UX )

The reason preventing us implementing our migration method was an offline datafile.. It was added into an important tablespace years ago, but then it was taken offline somehow .. As you may guess, we could not make it back online as we didn't have the archivelogs generated years ago..

In short, we had an offline datafile in the customer's database and it could not be recovered.. So, we couldn't drop that datafile. It needed recovery..

That datafile was already empty and it was just an unnecessary blocker..

Anyways.. Because of this datafile, XTTS method was encountering the error ORA-20001: TABLESPACE(S) IS READONLY OR, OFFLINE JUST CONVERT, COPY.

Basically, XTTS didn't like that file (because it was in offline status) and  that's why the perl scripts produced ORA-20001..

As you may guess, we modified the XTTS script.. (perl is my favorite language in these kinds of system programming tasks :)  In order to do this we needed to update the XTTS perl scripts, the one named "xttprep.tmpl" specifically..So we told XTTS to ignore this file in the relevant tablespace and migrated all the other files without getting any errors. Ofcourse we did it in the TEST environment.

We updated the xttprep.tmpl and made it not to get the problematic datafile in the first place..

We did the update in the related part of the file as follows;

$ELSE
gv$datafile
$END d
, v$tablespace t
WHERE d.ts# = t.ts#
AND d.file# not in (90) "******* <-- WE ADDED the FILE NO OF THE PROBLEMATIC FILE"
AND d.inst_id = USERENV('INSTANCE')
AND t.name IN (
%%TABLESPACES%%
)

With this action, ORA-20001 was not produced and we could continue working.. However; we couldn't take the necessary action for accomplishing the last task of the XTTS method.. We just couldn't make the source datafile offline for the final sync.. So modifying the script didn't solve the issue completely..

--Note that we still tried to be aligned with the documentation and XTTS method and we just didn't feel good with this modification already.

We had also another workaround and it was focused on getting rid of this file:)

That workaround was based on an update to the internal tables; 

<SQL>delete file$ where FILE#=65;
<SQL>commit;
<SQL>shut immediate
<SQL>startup

We implemented this workaround in the test environment and didn't encounter any problems.. However; we couldn't take Oracle Support's approval for this and that's why we didn't take it in to production.. A big risk right?..

Well, it was not recommended to manually modify the data dictionary (delete file$ where FILE#=65;) as this might lead to other inconsistencies in the database.

Let's see the facts;

• If a datafile in a locally managed tablespace is offline, it cannot be dropped. DROP Datafile And Its Restrictions ( Doc ID 781225.1 )
• BBED is not available anymore in latest versions. (BBED is one of my favorite, so it could save us here, but no luck..)
• As a prerequisite for XTTS procedure , all the datafiles in source tablespace should be online.
• The set of tablespaces being moved must all be online, and contain no offline data files. Tablespaces must be READ W9RITE. Doc : V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup ( Doc ID 2471245.1 )

So we needed to follow the hard and log way to fix this problem.. Available options:

• Use datapump for the Exadata migration.
• Use Goldengate (or any other application that can do CDC -- Oracle to Oracle -- such as STRIIM) for Exadata migration.
• Create a new tbs and use CTAS or alter table move to move the objects to the new tbs and lastly drop the old one, a tablespace-level drop. Once the tablespace is dropped, use XTTS for Exadata migration.
• Create a new tbs. Use alter table move online (in our case it couldn't be implemented due to rdbms version) to the new tbs and lastly drop the old tbs.. Once the tablespace is dropped, use XTTS for Exadata migration.
• Create a new tbs. Use ONLINE REDEFINITION (in our case it couldn't be implemented due to rdbms version) to the new tbs and lastly drop the old tbs.. Once the tablespace is dropped, use XTTS for Exadata migration.
• Create a new tbs. Use DATAPUMP (in our case it couldn't be implemented due to rdbms version) to the new tbs and lastly drop the old tbs.. Once the tablespace is dropped, use XTTS for Exadata migration.

Well.. The available options... All had different advantages and disadvantages.. They all had different prereqs and downtime requirements.. Goldengate-like applications required License.. Datapump-based Exadata migration methods were introducing extra downtime..

So we decided to fix this problem in place.. We decided to get rid of that datafile and designed a hybrid solution based on 2 different solution options.. Well.. We used Datapump + ONLINE REDEFINITION to move the data from the problematic tablespace to the new one..

We had 2 TB to move and we had big big LOB columns there.. So we migrated the tables ,which had LOB columns, with online redefitinion (you can think about it as an online move operation) and then we used datapump to move the small-sized tables from the problematic tablespace to the newly created one.. (this required a short downtime) 

It was a long and exciting journey, which will be explained in my next blog post..

Stay tuned :)

Erman Arslan's Oracle Forum / 7 July 2021 - 3 August 2021 - "Questions and Answers Series"

 Question: How much time do you spend/lose?

Answer: Well, how much time I gain? :) 

Remember, you can ask questions and get remote support using my forum.
Just click on the link named "Erman Arslan's Oracle Forum is available now.
Click here to ask a question", which is available on the main page of Erman Arslan's Oracle Blog
-- or just use the direct link:

https://ermanarslan.blogspot.com/p/forum.html

 

Come on, let's see what we've been up to in July+ in the beginning of August 2021. #amaerman

Do not forget to read the blog posts too :)

Refresh DB ORACLE_HOME by big

PDB Using txkCreatePDB.pl PDB conversion failed by baig

Oracle homes refresh in rac environment to test latest psu by satish

Browser supporting R12.2.5 forms users by satish

HTML Agent by big

logminer read rate downstream DB by Roshan

XML issues in oracle ebs R12, by Nabila Fatima

Global database name cannot be left blank by big

Restore backups to new host by satish

exadata wait eventleri by ilker kaysı

NFS requirement by Roshan

Error running impdp 12.1.0.2.0 ORA-31626: job does not exist by satish

Weblogic by latifa

EBS 12.2 upgrade to 19C by mmanavat

Query on custom concurrent programs post EBS upgrade to 12.2 by sravan

Query on using ad_zd.grant_privs by sravan

adcfgclone.pl fails on PATCH Error while generating listener.ora by big

Oracle EBS R12.2.4 Login CO by AGS7101

Execute on package r12.2 by satish

Oracle ebs login page not coming up. by Nabila Fatima

Database size after Duplicat by big

Restore backup after crash r12.2 by satish

Backup testing failed using data protector tool by satish

ERROR in ADOP PREPARE PHASE by neldyan14

Oracle OAS 5.5 integration with Oracle OAM by Zaheer