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

Forum is back online ! -- Erman Arslan's Oracle Forum --

We're back online! Thanks Nabble for the support. We are on a different Nabble server now. But nothing is changed in your perspective :) 

You can access the forum directly from the blog as before.. 

Just by clicking the "Erman Arslan's Oracle Forum is available now. Click here to ask a question. " link in the homepage of this blog or by using the direct url : http://ermanarslan.blogspot.com/p/forum.html

We will continue where we left off. Actually it was a few hours break but just saying :)

Planned Outgage ! -- Erman Arslan's Oracle Forum --

We have an outgage due to a planned operation that is currently being performed by our forum host (Nabble).

Our forum is being migrated to its new host at the moment.

Erman Arslan's Oracle Forum will be available soon. Thanks for your understanding and patience...

Note that, due to the short vacation, the questions piled up a bit. I will answer them all, once our forum will be reachable again.

    

Erman Arslan's Oracle Forum / Jun 1 2021 - July 7 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 Jun + in the beginning of July 2021.
Do not forget to read the blog posts too :)

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

Error opening oat page for specific user in r12 by satish

XML materialized view by Roshan

Dynamic footer on RTF template of word by Huy Nguyen

Oracle Apex 20.1 LDAP authentication with Windows Active Directory by Zaheer

Creating materialized view based on OEBS editionables by ANIETO

DB Cloning failing in EBS R12.1.3 after upgrading Database to 19c by soumya

Global database name cannot be left blank by big

oracle cloud 13c by Arsalan

Autoconfig delet profile option by big

How to Implement Signon Password Custom Profile Option R12.2 by satish

ODA- Database migration between 2 ODA Boxes and 19C upgrade for EBS by mmanavat

Changing domain name in r12.2 multinode by satish

forms-c4ws by big

How Can I Restrict Applications Users To Be Signed In Only Once At Any Time Doc ID 375403.1

Connection is long by big

Is Oracle Certification easy to pass? by Tasmina

Restrict user to a single session r12.2.5 by satish

Oracle RAC and PCP in R12.2 by mi_singh@hotmail.com

How do I set db_unique_name properly in CDB.env file? by SriCheb

Data access from one dB to other by satish

Security report r12.2 by satish

Test mail for workflowmailer by big

Concurrent manager for Alert Notification by big

golden gate index issue by Roshan

Dblink from oracle to PostgreSQL-compilation error by satish

pluggable database could not open by Arsalan

Switching EBS Application from primary to Secondary Database by Tabrez

Recoverability Roadmaps & Remediation Options - Oracle, Systems, Apps Technology, Virtualization and Engineered Systems

In this post, I want to share my thoughtson Recoverability, actually my approach that I use in Recoverability Assessments.. Actually, these types of assessments are comprehensive, they even include DR solutions, trainings, recovery processes and the continuous availability.

I start with the readiness.. Readiness in 3 different areas : People, Process and Technology. I review and rank the readiness for key areas that are enablers for availability, resiliency and recoverability by assessing current IT capabilities of the customer.

Once I generate the readiness documents, I do my analysis, determine the gaps and then present my recommendations. I support the customer in execution as well.. ( if they need me there..)

So it is pretty straight forward, but still requires lots of efforts :)

The assesments starts with the information gathering. I just gather the detailed information and do my analysis for a number of attributes in the following areas;  

Operational Staff, Response Plans, Recovery Testing, Program Maintenance, Business Expectations, Production & DR Facilities, Application Infrastructure, Data Restoration and Recovery Network.

During this first phase, we usually meet with the customers. I write down the people, process, and techonology findings. Then, we popuplate tool based discovery reports ( DB , Server, SAN healtchecks, Server grabs & logs etc..)

In the second phase, I create a recommendation list.  Next, I do the remediation roadmap, finalize the recoverability assessment document and lasty I give the final recoverability assessment repsentation (an executive presentation actually)

While analyzing the people and process findings, I check to see if there any any gaps in the following areas; business expectations, production & DR facilities, Application Infra, Data Restoration, Recovery network , Operational Staff, Reponse plan, Recovery Testing, Program maintanence and etc..

Following is an example of the GAPs that may be found in the Recovery network ;

No formal DR program
DR requirements unknown
Lack of formal documented policies or processes

Following is another example of the GAPs that may be found in Data Restoration area;

Lack of service levels with the business

No formal tiering structure

Recovery RTOs / RPOs have not been defined

Lack of recovery expectations

These are big gaps :) and they are here just to give you some examples, but I guess you understand  the scope of the work already..

In the technology analysis phase, I analyze the following layers through the following critieria;

Presenation layer, Login/application, Database, Compute, Storage, Network  --> Production HA, DR, Backup, Archiving.

Some examples for the technology findings in this phase;

A single point of failure (SPOF) exists which would cause a complete outage for the application.

Server configuration is not aligned with the intended high-availability design (cluster is misconfigured).

Well, after the findings, I create a recommendation matrix, and summarize these recommendations..

I analyze the recommendation from the implementation effort and business impact perspectives and then create a matrix to show the risk level / business impact and implementation effort  of each recommendations.

Business Impact goes low to high when you go upwards in the y axis, effort goes high to low when you go right in the x axis.. So,  action items/recommendations in the top right quadrant are given high implementation priority, due to low effort & high impact. So you get the idea..

As for the redmediation options, I give the as-is Architecture, then propose target solutions by considering/analayzing the gaps. There may be more than one solution proposed as part of the Gap Analysis against Recoverability Business Requirements .

Finally I create thre recoverabiliy roadmap and that's it :)

In the recoverability roadmap, I start with the areas of opportunities and build a 18 Months plan. (maybe further) . I list the actions that should be done in near term, in 6-12 Months and in 12-18 Months to reach the target state where we usually have the following;

Increased ROI

Standardized Environment

Ensured Recoverability 

Recoverability and continuous availability services aligned with the business needs

Operational Excellence

Organizational stability

Culture of Ensured availability & DR Services.

That's end of this post. I hope you find it useful.

If you need any advice or consultancy, feel free to contact me.

OVM Manager / Weblogic -- CVE-2019-2725 / deserialization - remote code execution vulnerability

Here is a filtered information for OVM customers, who are complaining about CVE-2019-2725, which is a deserialization vulnerability, a remote code execution vulnerability that is remotely exploitable without authentication.

I guess everyone already knows that OVM uses Weblogic in the backend. Actually this post is for all the ones who use certain versions of Weblogic..  Weblogic 10.3.6 and 12.1.3. 

The solution is simple for the ones who have extended support contract because these Weblogic releases are currently in extended support. This is the reality for both OVM Manager and Weblogic customers.

So, you just apply the latest PSU, or a PSU which fixes the issue and the overlay patch if there is any 

and that's it.. You are done .. (for instanc :e Apr 2019 PSU 12.1.3.0.190416 Patch 29204657 + Overlay Patch 29694149 on 12.1.3.0.190416 for CVE-2019-2725)

OVM Manager customer can also apply the PSU and overlays.. Although Weblogic is bundled with OVM Manager, it is supported to get latest security updates, to the latest minor updates. I mean you can apply PSUs to the Weblogic of OVM Manager..

The questions arises for the ones who don't have extended support for Weblogic. That is if they don't have extended support contract, they won't be able to download any WLS patches for those specific releases.. 

This means  no PSU, no CPU, no overlay...

In this case, they have 2 options.. 

1) They may upgrade their OVM manager to version 3.4.7, which is bundled with Weblogic 12.1.3.0.210119 -- January 2021 Patch Set Update (PSU) for WebLogic Server 12.1.3.0.

2) They may implement the following workaround (but this must be tested, I mean it must be ensured that OVM Manager will not lose any functionalities after these actions);

Delete the wls9_async_response.war & wls-wsat.war packages from Weblogic and restart the OVM Manager+ Weblogic
Disable access to“/_async/*” and “/wls-wsat/” URLs on weblogic.
Test well..

-- you may even implement this access restriction on the firewall level.

MOS References:

Upgrading products bundled with Oracle VM Manager (Doc ID 2195205.1)
Security Alert CVE-2019-2725 Patch Availability Document for Oracle WebLogic Server (Doc ID 2535708.1)

Weblogic -- BI Publisher 12.2.1.3 -- Error running config.sh - java.lang.IllegalArgumentException: ONS configuration failed / oracle.ons.NoServersAvailable: Subscription time out

I want to share a solution for a problem that was recently encountered  in one of our client environments.

The problem was on config.sh.. That is, config.sh was encountering ONS errors.. This was a fresh install and it wasn't expected to be that challenging.

Anyways there were multiple errors recorded in the flow.. 

The first one was ;

oracle.ons.NoServersAvailable: Subscription time out -> 

 java.lang.IllegalArgumentException: ONS configuration failed

BARMedataPlugin related errors were following it;

problem encountered when cleaning up SI using plugin: 

oracle.bi.servicelcm.metadata.BARMetadataPlugin[[java.lang.NullPointerException at sun.nio.fs.UnixFileSystem.getPath(UnixFileSystem.java:272)

UnixFileSystem.getPath and UnixFileSystem.java were foundational. I checked their source code.. But! these were the results actually.

So the config.sh encountered a problem related with the ONS and then it understood that it has failed but continued and tried to clean the mess but can't find the mess / a specific file that it expected to be present in the filesystem.. Probably.. So that error in UnixFileSystem.java was a result of that. 

I like interpreting the logs this way :) They tell me the story  when I read them correctly :) They also give me the execution flow, so that I can guess the internal parts of the execution.

Anyways, I was already familiar with that ONS problem, so I revisited one my earlier posts ->  https://ermanarslan.blogspot.com/2017/09/problem-installing-oracle-fusion.html

I tried that workround documented in that post.. (After all It saved my day earlier..)

The workaround for this was, to supply an argument in the config_internal.sh. (config.sh indirectly executes config_internal.sh) -> oracle.jdbc.fanEnabled=false

However; suprisingly this time it didn't work.. config.sh was still getting those ONS errors in the next runs.

So this time I implemented a fix and here I 'am writing this blog post for it..

The fix was a patch actually..

Patch 26045997: ENABLING DRIVER FAN WITHOUT RUNNING ONS DAEMONS CAUSES CONNECT REQUEST ERROR

So, we applied it to the WLS and then run config.sh without a problem..

Ofcourse these things are based on the DB layer configuration and if that ONS configuration was compatible with the expectations of config.sh, we wouldn't get any errors in the first place.. 

Continuing the life without ONS configuration have some disadvantages, but as far as I see, in most of the clients they are acceptable.. So that patch is the key to save the day :)

Stay tuned..

GTECH -- Summer School 2021 -- Oracle Database & Cloud & Big Data & EBS - Training For Newly Graduates!

Favorite days for my trainer mode :) a quick but efficient training for new engineers who are interested in our subjects. Summer School 2021-- Oracle Database & Cloud & Big Data & EBS - Training For Newly Graduates!

Once in a year, we as GTech provide training for newly graduated engineers.

In this training, we teach Sql, PL/SQL, Oracle Database & Cloud, EBS, OBIEE, BigData, ETL and more.

This year was the fourth time, that I was the lecturer for "Database and Cloud".

See the following blog posts for 2020, 2019 and 2018 Summer Schools ->

https://ermanarslan.blogspot.com/2020/08/gtech-summer-school-2020-oracle.html
https://ermanarslan.blogspot.com/2019/07/gtech-summer-school-2019-oracle.html
https://ermanarslan.blogspot.com/2018/07/summer-school-introduction-to-oracle.html

As usual, the students of the class were so curios about databases and actually Oracle in general..

I tried to shed a light on the important topics like Oracle Database Server Architecture, Oracle Database Process Architecture, background processes, High availability configurations, Cloud Computing (Oracle Cloud Infrastructure + Google Cloud Platform /GCP) , Big Data, NoSQL databases and so on..

The list of topics covered in the training was as follows;

• Introduction to RDBMS
• Introduction to Oracle
• Architecture (Oracle)
• Installation (Oracle) & workshop
• DBA role & DBA tools
• Cloud Computing
• Big Data & NoSQL
• APPS DBA role & EBS System Administration (EBS 12.2)

Speaking at Google-IDC Webinar / Database Modernization -- Google Cloud, Oracle BMS, Cloud SQL , Spanner and more

Speaking at the event, IDC - Google. My topic will be about Database Modernization.
I will explain running Oracle Databases on GCP - Bare Metal, Cloud SQL(Postgresql, MS SQL and MYSQL) and some Google specific database solutions like Spanner.
I will give the motivation and the need for database modernization and will look to the process of obtaining a modern database environment and put some light on the benefits of it.
The event is in Turkey and the presetation will be in Turkish. If you want to join, we will happy to see you among us in the event. Stay tuned!

Registration Link:

--Webinar'a kayıt olmak isteyen arkadaşlar için kayıt linkini aşağıda paylaşıyorum:

https://www.idc.com/mea/events/69019-gercek-zamanl-analitik-ile-bugunden-gelecegi-yaratmak