Thursday, July 22, 2010

Recreate SYSMAN Schema

Following is the hands on procedure to recreate sysman schema.

SQL> drop user sysman cascade;
User dropped.

SQL> drop role MGMT_USER;
Role dropped.

SQL> drop user MGMT_VIEW cascade;
User dropped.

SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
Synonym dropped.

SQL> drop public synonym SETEMVIEWUSERCONTEXT;
Synonym dropped.

SQL> exit

oracle@lab:/opt/app/oracle/product/11/db_1/bin> $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager antarctic 2483 sprod -action drop
Enter SYS user’s password :
Enter repository user name : sysman
Getting temporary tablespace from database…
Found temporary tablespace: TEMP
Checking SYS Credentials … rem error switch
rem error switch
Dropping the repository..
Quiescing DB … Done.
Checking for Repos User … Does not Exist.
Repos User does not exist … Continuing with cleanup …
Dropping Roles/Synonymns/Tablespaces … Done.
Unquiescing DB … Done.
Dropped Repository Successfully.
oracle@lab:/opt/app/oracle/product/11/db_1/bin> ./emca -repos create

STARTED EMCA at Jul 20, 2020 4:41:05 PM
EM Configuration Assistant, Version Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: sprod
Listener port number: 2483
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 20, 2010 4:41:38 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/cfgtoollogs/emca/sprod/emca_2010_07_20_16_41_05.log.
Jul 20, 2010 4:41:39 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Jul 20, 2010 4:54:44 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 20, 2010 4:54:44 PM

SQL> analyze table sysman.mgmt_metrics_1hour validate structure cascade;

Table analyzed.

SQL> analyze index sysman.mgmt_metrics_1hour_pk validate structure;

Index analyzed.


Shailesh said...

Nice and helpful note !!

Ferdous Nadvi said...

Appreciate your feedback Shailesh.