Monday, November 18, 2013

Resizing MGMT_TABLESPACE in Grid Control 12c

We noticed the MGMT_TABLESPACE in our 12c Grid Control instance was very big. Almost 380gb of data was stored in this tablespace.
Because of all the targets registered in this Grid Control instance and the frequent usage of this Grid Control, I did not think anything was wrong.
Untill someone looked into this and found out it was a bug in 12c...

Note 1502370.1 describes this bug and also the solution for this. I had to install a patch and truncate the em_job_metrics table. After applying the patch and truncating the table, there was only around 10gb of data left in the MGMT_TABLESPACE.
Because I already experienced something like this 5 years ago in a 10g OEM instance (
http://basklaassen.blogspot.com/2008/06/resizing-oem-database-10g.html), I knew that is was not easy to reclaim the space in this tablespace. And we had a a lot to reclaim, only 10gb data in this tablespace of 380gb...

I decided to check my Oracle support, maybe someone els already had the same problem. I found bug 17461366, making it impossible to reorg the mgmt_tablespace because of aq objects. Same problem I faced five years ago. Because I really wanted to reclaim the free space (370gb), I decided to follow my own notes. Allthough these steps ware performed on a 10g OEM environment and now I was working with a 12c Grid Control environment.

Just like 5 years ago, I could think of two ways to reclaim the space.
1. export mgmt_tablespace, drop the tablespace and import it again 
2. export sysman, drop repository and, run scripts from the oms_home and import sysman again

Export MGMT_TABLESPACE

Unfortunately, dropping the mgmt_tablespace was a mission impossible. The export succeeded without errors, but I was not able to drop the tablespace. After dropping some related objects that were showing in the errors I recieved, I decided this was not going to work. I restored the database and executed option 2.

Export Sysman user(s)

I decided to follow note 388090.1. This note describes a platform migration for a 10g Grid Control environment, but I could not find any document about a 12c Grid Control environment.

 Just to be sure, I also exported (expdp) the other sysman users (sysman_apm, sysman_mds, sysman_opss and sysman). Also set job_queue_processes to zero
 
The next step was to drop the current repository using the repmanger utility ($OMS_HOME/sysman/admin/emdrep/bin)
The repmanager also did not drop the mgmt_tablespace, I had to drop the tablespace myself. But after deleting the repository I was able to drop the tablespace. The drop repository did drop all the sysman users and other tablespaces

According to the note, before the impdp, I had to run some scripts from the $OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin directory.
These script should create the sysman users and tablespaces. The following sql's were executed :
- admin_create_tablespaces.sql
- admin_create_repos_user.sql
- admin_pre_import.sql
- admin_sys_procs.sql
- admin_profiles.sql
- admin_grants_repos_user.sql
- admin_grants_view_user.sql

Now, you should be ready to inport the sysman scheme again (impdp). But the import showed a lot of errors. Mean reason, the sysman_ro user was not available and the mgmt_ad4j_ts tablespace was not created. Again I decided to drop the sysman user again and start over again. But this time, instead of running the scripts before the import I created a whole new repository using Repmanager.

The new repository did have the sysman_ro user and also the missing tablespace.
At this time I started the import againg, using the 'table_exist_action=replace' option. The repository creation already created the sysman objects, so I wanted the impdp to replace the already created tables with the tables from the dump. I noticed one table creation error in the logfile, the em_job_type_creds_info table was not created by impdp. I had to create this one after the impdp.
Also the other sysman users were not created by the repmanager. I had to create and import these users  (sysman_.mds, sysman_apm and sysman_opss). 

After the import I returned to the note again, to check for post import steps.
Again in $OMS_HOME/sysman/admin/emdrep/sql/core/latest/admin
- admin_recompile_invalid.sql
- admin_create_synonyms.sql
- admin_post_import.sql

Reset the job_queue_processes to its original  value and submit the EM dbms jobs
- admin_submit_dbms_jobs.sql


After compiling, there were still problems with aq objects.
I dropped the aq objects (exec dbms_aqadm.drop_queue_table) and created (dbms_aqadm.create_queue_table) them again. This solved the problemens and also cleared the invalid objects

At this point, there were no invalid objects anymore, so the oms could be started again to see what happens.
Oms started oke and I was able to login 12c Grid Control.

With 12c Grid Control running, I noticed the oms was bouncing every 12 minutes by itself.
I checked the  emctl.msg file in /gc_inst/em/EMGC_OMS1/sysman/log for errors and found the following error.

HealthMonitor Nov 15, 2013 12:57:48 PM PbsAdminMsgListener error: PbsAdminMsgListener thread timed out.
Critical error err=3 detected in module PbsAdminMsgListener
OMS will be restarted. A full thread dump will be generated in the log file






It seemed the em_cntr_queue was missing ? I checked and found out some other queues were also missing (not in the dump ?) I recreated the missing queues (check the queues in an ota Grid environment) and that solved the restart error. Now oms was not restarting by itself anymore.

Reclaimable free space 

After these steps (took me two days...) I reclaimed 370gb. The mgmt_tablespace is now 10gb (instead of the 380gb before). The total size of the entiry database was shrinked from more then 400gb to 22gb !!


57 comments:

Unknown said...

"I noticed one table creation error in the logfile, the em_job_type_creds_info table was not created by impdp. I had to create this one after the impdp."

Hi
How did you do this part?

I am in the same place as you. However I have simply exported all the sysman* users, dropped them, reduced the datafiles to their min value, and now I am importing again. My only import issue is this table and some downstream resultant issues, procedures when depend on this table etc.

thanks/

21st Century Software Solutions said...

Oracle 12c Grid control: Oracle 12c Grid control Training, Oracle 12c Grid control Online Training, Oracle 12c Grid control Corporate Training, Best Oracle 12c Grid control Training, Expert Oracle 12c Grid control Training
http://www.21cssindia.com/courses/oracle-enterprise-manager-grid-control-12c-228.html
Oracle Enterprise Manager Grid Control 12c
Introduction
Course overview
Key Challenges for Administrators
What Is Enterprise Manager Cloud Control?
Built-in and Integrated Manageability
Configuration Management
Reviewing Enterprise Manager Core Concepts
Review Oracle Enterprise Manager architecture
If you are seeking training and support you can reach me on 91-9000444287

Unknown said...


The information you posted here is useful to make my career better keep updates..If anyone want to become an oracle certified professional reach FITA Oracle Training Institutes in Chennai, which offers Best Oracle Training in Chennai with years of experienced professionals.

Unknown said...

Hadoop Training Chennai

Hi, I am Christina lives in Chennai. I am technology freak. I did Hadoop Training in Chennai at FITA. This is useful for me to make a bright career in IT field.

Hadoop Course in Chennai

Unknown said...

SAP Training Institute in Chennai

Thanks for sharing this valuable information.and I gathered some information from this blog. I did SAP Training in Chennai, at FITA Academy which offer best SAP Course in Chennai with years of experienced professionals.

SAP Institutes in Chennai

Unknown said...

Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing. AWS Training in chennai | AWS Training chennai | AWS course in chennai

jade said...

I found bug 17461366, making it impossible to reorg the mgmt_tablespace because of aq objects. Proact Financial

Anonymous said...

It's a pitty that people use this very good blog to promote themselves! Please stay on the topic with your comment!

We suffer on the same problem: OMS tablespaces got too big. Next two weeks I'm absent but then I'd like to try out your reorganization. Great description. Thanks!

Hanna

SAM said...

Bookmarked for the further reference, Thanks for the Wonderful Info.

Tents | Tents South Africa | Tents for sale | Tents Manufacturers | Tents South Africa | Alpine Tents | Aluminium Tents | Alpine Marquee | Plastic Chair | Mobile Chillers | Tiffany Chair | Tent | Tents SA | Mobile Freezers | Bedouin Tents | Party Tent | Tents Manufacturer | Pagoda Tents | Emergency tents | Disaster Tents | Army Tents UK | Army tents | Canvas Tents | Manufacturers of Tents | Aluminium Tents for sale | Bedouin Tents | Alpine Marquees | Alpine Tent | Frame Tents | Aframe Tents | Relief Tents | Party Tents | Refugee Tents | Tent for sale | Tents Nigeria | Cheap Tents | Tents Namibia | Marquees

Unknown said...

You have stated definite points about the technology that is discussed above. The content published here derives a valuable inspiration to technology geeks like me. Moreover you are running a great blog. Many thanks for sharing this in here.

Salesforce Training in Chennai
Salesforce Training in Chennai
Salesforce Training in Chennai

Unknown said...

This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing.
Regards,
cognos Training in Chennai|COGNOS Training Institute in Chennai|Cognos Training Institute

Unknown said...

This technical post helps me to improve my skills set, thanks for this wonder article I expect your upcoming blog, so keep sharing.
Regards,
SAP training in chennai|SAP course in chennai|SAP Training in Chennai|SAP Course in Chennai

Unknown said...

Hi this is Yasmin from Chennai. I am regular contributor of
your blog. You have done really great job. Thanks for sharing this informative
post. Keep blogging.
Regards

Oracle Apps Training in Chennai | Oracle Apps Training in Chennai

Unknown said...

Hi, I am really happy to found such a helpful and fascinating post that is written in well manner. Thanks for sharing such an informative post. keep update your blog.
Regards.
Big Data Training in Chennai

Valves and Flanges Exporter said...

Valves and Flanges | Flanges Manufacturer | Flanges Exporter | Falcon Engineers

1croreprojects said...

Thanks for sharing this valuable information.
ieee java projects in chennai
ieee dotnet projects in chennai
mba projects in chennai
be projects in chennai
ns2 projects in chennai
mca projects in chennai
bulk projects in chennai

Sgraph Infotech said...

Nice to know about all these things and it seems to be quite interesting to know about this topic.


pl sql training institutes in bangalore


Unknown said...

You noticed the MGMT_TABLESPACE in our 12c Grid Control instance was very big using oracle . Almost 380gb of data was stored in this tablespace. All the targets are registered in this Grid Control instance and the frequent usage of this Grid Control.

Oracle dba certification cost in chennai said...

This is first time I come across your site. It's useful post. Thanks to shared this details with me.
Oracle courses | DBA course

OGEN Infosystem (P) Limited said...

Wow, Nice blog. Thank you so much for the efforts of this blog. Visit for
SEO Service in Delhi

Unknown said...

Your information's are very much helpful for me to clarify my doubts.
keep update more information's in future.
android training institutes in bangalore
Android Course in Anna Nagar
Android Courses in T nagar
Android Training Institutes in OMR

Unknown said...

Your information's are very much helpful for me to clarify my doubts.
keep update more information's in future.
Selenium Training in Sholinganallur
Selenium Certification Training in T nagar
selenium classes in bangalore
selenium training in bangalore with placement

Unknown said...

Brilliant ideas that you have share with us.It is really help me lot and i hope it will help others also.update more different ideas with us.
best devops training in bangalore
devops course in bangalore
devops Training in Chennai Anna Nagar
devops Course in Anna Nagar

Adhuntt said...

Very Useful information.
SEO in Chennai.

sunshineprofe said...

But he’s trying none the less. I’ve been using Movable-type on several websites for about a year and am anxious about switching to another platform. I have heard great things about blogengine.net.
fire and safety course in chennai

Mobile app development company in toronto said...

Very Nice Article keep it up...! Thanks for sharing this amazing information with us...! keep sharing

pixel studios said...

Nice article. it is very useful and informative post. I like very much. Thank you for your sharing.
Digital Marketing Company In Chennai | Ecommerce Website Development Company In Chennai

App Development Company in Delhi said...

Nice and interesting post,I appreciate your hard work,keep uploading more, Thank you for sharing valuable information.

Adhuntt said...

Interesting article Instagram and Facebook have provided an amazing place for new brands to grow and flourish. We can find the perfect niche for your brand on the best social media platforms.

service care said...

I think this is a great site to post and I have read most of contents and I found it useful for my Career .Thanks for the useful information. Good work.Keep going.
best mobile service center in chennai
mobile service center in velacherry
mobile service center in vadapalani

Darshana M said...
This comment has been removed by the author.
Rathinam said...


I saw your all post, I truly loved! I want more info relating to this and thanks a lot about this discussing...

Embedded System Course Chennai
Embedded System Courses in Chennai
Excel Training in Chennai
Corporate Training in Chennai
Tableau Training in Chennai
Oracle DBA Training in Chennai
Unix Training in Chennai
Placement Training in Chennai
Embedded Training in Vadapalani
Embedded Training in Porur

Vijiaajith said...

Interesting blog, keep posting...
freeinplanttrainingcourseforECEstudents
internship-in-chennai-for-bsc
inplant-training-for-automobile-engineering-students
freeinplanttrainingfor-ECEstudents-in-chennai
internship-for-cse-students-in-bsnl
application-for-industrial-training

Savanah Eden said...

We help you to get high traffic for your websites using latest ethics,visit us
Linkedin Marketing company chennai
Erp software development company in chennai
seo company in chennai
Web portal development company in chennai

svrtechnologies said...

Thanks for Sharing Such an Informative Post...

oracle training

ishu said...

Thanks for sharing this useful blog. why is it trending and how does it work? Digital marketing is nothing but marketing a product and services or business using digital platforms to get favorable or good results. This is the best platform for large, medium, and small businesses to get an online identification and reach out to a large customer base thereby positively increasing the chances of driving new business. digital marketing in Chennai is one that provides all kinds of services to its clients so that the visibility of their client's website improves phenomenally on top search engines.
digital marketing in Chennai | digital marketing in velachey | seo company in Chennai | seo in Chennai | best digital marketing company in Chennai | digital marketing services in Chennai | top seo companies in chennai | top digital marketing companies in Chennai | google authorised agency in Chennai

j said...

gretings from my used bookstore

Joyal said...

Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision.
Oracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore

surya said...

Informative blog post. Thanks for this useful Post...
Angular JS Training in Chennai | Certification | Online Training Course | Angular JS Training in Bangalore | Certification | Online Training Course | Angular JS Training in Hyderabad | Certification | Online Training Course | Angular JS Training in Coimbatore | Certification | Online Training Course | Angular JS Training | Certification | Angular JS Online Training Course

Devi said...

I went through your blog its really interesting and holds an informative content. oracle training in chennai

Gorkha Excellence Security Force Pvt Ltd said...

Best Security Company in Chennai
Best Security Agency in Chennai
Best Security Agency in Trichy
Best Security Company in India
Best Security Agency in Chennai


Top Security Company in Chennai
Top Security Agency in Chennai
Top Gorkha Agency in Chennai

Anonymous said...

nice

nice

nice

nice

Hemapriya said...

Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
AWS Training in Chennai
AWS Online Training
AWS Training in Coimbatore

Technogeekscs said...

This concept is a good way to enhance knowledge. thanks for sharing..
Best RPA Training in Pune

Venkygaming said...

Hi,im venky here to tell that the blog explation is clear and more helpful yoo.
Digital marketing in Telugu

nive said...

this is amazing article...its very interesting to read....
Autocad CREO cad centre in coimbatore 2021
Autocad CREO training in coimbatore
Autocad CREO acadamy in coimbatore 2021
Autocad CREO institutes in coimbatore
Best Autocad CREO coaching in coimbatore
Autocad CREO courses in coimbatore
Autocad CREO classes in coimbatore
Cad centre in coimbatore
Best Autocad CREO training institute in coimbatore

Unknown said...

Grab Data Science Certification in Chennai for skyrocketing your career with Infycle Technologies, the best Software Training & Placement institutes in and around Chennai. In addition to the Certification, Infycle also gives the best placement training for personality tests, interview preparation, and mock interviews for leveling up the candidate's grades to a professional level.

Quikads said...

Post your ads on Quikads. Quikads is an online classified ads platform in Bangladesh. Here you can buy or sell your brand new or second-hand products easily.

Hussey said...

Happy to read the informative blog. Thanks for sharing
IELTS Coaching Center in Chennai
best ielts coaching centre in chennai


rohit said...

crm software for small business
Thank you for making this blog and for sharing useful information. Continue doing your passion and keep on blogging.

Lakshman said...

Here is the best Azure DevOps Training in Chennai from Infycle Technologies, best software training in chennai. And we issue other technical courses like Power BI, Cyber Security, Graphic Design and Animation, Block Security, Java, Oracle, Python, Big data, Azure, Python, Manual and Automation Testing, DevOps, Medical Coding etc., and we also give best technical trainers with excellent training 100+ Live Practical Sessions with Real-Time scenarios at the resolution period of the course the freshers, experienced, and Tech professionals will be able to obtain more knowledge of the course and be able to get through the interviews on top MNC’s with an amazing package. for more queries approach us on 7504633633, 7502633633.

Cloud Telephony Software said...

Thanks for sharing such useful information with us. I hope you will share some more info about your blog. Please keep sharing. We will also provide HR Software

Shivam said...

Find the best-fixed mortgage rate in London that works perfectly for you. We make it easy to compare rates in London big banks and top brokers for free. Best mortgage rate in London

Neelesh said...

Thanks for sharing this informative blog.
Resorts for team outing in Ooty

Mobile App Development Company in Chennai said...

Thank you very nice sharing.

ilam said...

selenium training in chennai

ilam said...

Looking for the bestselenium training in chennai? ideal for anyone aiming to master automation testing. This course covers essential topics, from Selenium basics to advanced frameworks, guided by experienced professionals. Students gain hands-on experience with real-world projects and in-depth training to enhance career opportunities in software testing. Join Testleaf’s Selenium certification program for a quality learning experience and industry-aligned knowledge. Enroll today to advance your automation testing skills! call now to get more information about selenium training - (+91) 89 2541 1172