Scribd
Upload
34 views3 pages

OEM Database Cleanup Procedure

Uploaded by

geysjsbsvsg
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
34 views3 pages

OEM Database Cleanup Procedure

Uploaded by

geysjsbsvsg
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

OEM clean up process:

SELECT USERNAME FROM DBA_USERS WHERE USERNAME LIKE 'SYSMAN%';

SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN


('MGMT_ECM_DEPOT_TS', 'MGMT_TABLESPACE', 'MGMT_AD4J_TS');

drop user SYSMAN_TYPES CASCADE;

drop user SYSMAN_MDS cascade;

drop user MGMT_VIEW cascade;

drop user SYSMAN_BIP cascade;

drop user SYSMAN_APM cascade;

drop user BIP cascade;

drop user SYSMAN122140_OPSS cascade;

drop user SYSMAN_RO cascade;

drop user SYSMAN cascade;

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Metadata Services';

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Authorization Policy Manager';

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Oracle Platform Security


Services';

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='OracleBI and EPM'

delete from SCHEMA_VERSION_REGISTRY where COMP_NAME='Service Table';

commit;

drop tablespace MGMT_ECM_DEPOT_TS including contents and datafiles;

drop tablespace MGMT_TABLESPACE including contents and datafiles;

drop tablespace MGMT_AD4J_TS including contents and datafiles;

drop user sysman cascade;

drop user sysman_types cascade;

drop user sysman_stb cascade;

drop user sysman_opss cascade;

drop user sysman_biplatform cascade;

drop user sysman_bip cascade;

drop user sysmanupgr_opss cascade;


drop user sysman_apm cascade;

drop user sysman_ro cascade;

drop user sysman_mds cascade;

drop user cloud_engine_user cascade;

drop user cloud_swlib_user cascade;

drop user mgmt_view cascade;

drop user eus_engine_user cascade;

drop role mgmt_user;

DECLARE
CURSOR l_syn_csr IS
SELECT 'DROP ' ||
CASE owner
WHEN 'PUBLIC'
THEN 'PUBLIC SYNONYM '
ELSE 'SYNONYM ' || owner || '.'
END ||
synonym_name AS cmd
FROM
dba_synonyms
WHERE
table_owner IN (
'SYSMAN',
'SYSMAN_MDS',
'MGMT_VIEW',
'SYSMAN_BIP',
'SYSMAN_APM',
'BIP',
'SYSMAN_OPSS',
'SYSMAN_RO'
);
BEGIN
FOR l_syn_rec IN l_syn_csr LOOP
BEGIN
EXECUTE IMMEDIATE l_syn_rec.cmd;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( '===> ' || l_syn_rec.cmd );
dbms_output.put_line( sqlerrm );
END;
END LOOP;
END;
/

execute dbms_auto_task_admin.disable('auto optimizer stats collection',null,null);

grant connect,resource to admin identified by admin;


grant dba to admin;

unset http_proxy

unset https_proxy

SQL> create pfile = '/tmp/pfile_Ccoorect_Oem.ora' from spfile;

File created.

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /app/oracle/GraphDB/19.0.0/dbs

/[Link]

SQL>

ALTER SYSTEM SET "_allow_insert_with_update_check"=TRUE SCOPE=BOTH;

ALTER SYSTEM SET session_cached_cursors = 300 SCOPE = SPFILE;

ALTER SYSTEM SET shared_pool_size = 640M SCOPE = SPFILE;

ALTER SYSTEM SET pga_aggregate_target=450M scope=both;

ALTER SYSTEM SET sga_target=16640M scope=both;

ALTER SYSTEM SET PARALLEL_MAX_SERVERS = 8 SCOPE=BOTH;

ALTER SYSTEM SET PARALLEL_MIN_SERVERS = 0 SCOPE=BOTH;

execute dbms_auto_task_admin.disable('auto optimizer stats collection',null,null);

cp /etc/oratab /tmp

sed -i -e "s|${ORACLE_SID}:${ORACLE_HOME}:N|${ORACLE_SID}:${ORCALE_HOME}:Y|g"
/tmp/oratab

cp -f /tmp/oratab /etc/oratab

Common questions

Powered by AI

Removing users such as 'BIP' and 'MGMT_VIEW' might impact certain functionalities associated with Oracle Business Intelligence Publisher and Management views. These users are typically associated with specific schemas and jobs that manage reporting, monitoring, and analytics functionalities. Eliminating them ensures a clean slate by removing all roles, privileges, and objects associated with them, but requires further setup if those services will be needed again in the future. The potential impact therefore includes temporary interruptions in specific analytics and management features until they are properly redeployed or reconfigured .

Altering the setting '_allow_insert_with_update_check' enables the database system to allow inserts that include updates checks, which can streamline batch processing and data integrity checks during database operations. This setting is particularly useful in scenarios requiring conditional data operations where insertions go hand in hand with updates, thereby enhancing the efficiency of the system during ongoing post-cleanup configuration and operational setups .

Creating a pfile from an spfile is important in database management as it acts as a backup of the current database configurations. A pfile (parameter file) can be edited manually for making specific configuration changes, whereas an spfile (server parameter file) is a binary file that cannot be directly edited. By creating a pfile, database administrators have the flexibility to adjust configurations manually and then revert or adjust as necessary, facilitating better control over database management post-cleanup operations .

Granting specific permissions, such as 'connect', 'resource', and 'dba', to an admin user post clean-up is necessary to ensure administrative capabilities are maintained. This enables the admin to manage the database effectively, including access to necessary resources, performing recovery operations, and maintaining overall database security. These permissions are critical for setting up new configurations or continuing with database management and monitoring tasks .

Altering system parameters such as 'session_cached_cursors', 'shared_pool_size', 'pga_aggregate_target', and 'sga_target' helps in optimizing the performance of the database post cleanup. These parameters control various aspects of memory allocation and session management, which can significantly influence how efficiently the database runs. Additionally, setting parameters like 'PARALLEL_MAX_SERVERS' and 'PARALLEL_MIN_SERVERS' fine-tunes the database's parallel processing capabilities, thus improving its readiness for workload demands following the cleanup operation .

Disabling 'auto optimizer stats collection' is crucial during the OEM cleanup process to prevent automatic statistical updates that could interfere with the deletion and cleanup operations. Automatic updates might lock objects or temporarily change database performances, affecting the removal processes of users, schemas, and tablespaces. By disabling this feature, the system ensures that the cleanup operations proceed without interruptions or unexpected behavior .

Unsetting HTTP and HTTPS proxy settings during the cleanup process is necessary because proxies can redirect or mask connection endpoints. They may interfere with direct database connections or service calls during cleanup. Ensuring no proxies are set helps in establishing direct and unimpeded connections to the services and endpoints, which is crucial for accurately and efficiently executing cleanup operations .

The OEM clean up process uses a cursor to loop through and drop synonyms associated with certain users, such as SYSMAN and SYSMAN_MDS, which could be public or owned by specific users. This is important because synonyms provide alternative names for database objects. Removing them ensures there are no lingering references to the dropped objects, thus maintaining database integrity and preventing potential errors in future database operations .

Modifying the oratab file, specifically forcing changes from 'N' (no automatic startup) to 'Y' (automatic startup), ensures that the Oracle database automatically starts upon server boot. This configuration is essential for streamlining operations, particularly after a cleanup process, as it ensures that the database is always running and ready for connections after any server reboot, thus enhancing uptime and operational efficiency .

Dropping specific tablespaces and users in the OEM clean up process aims to remove all dependencies and configurations associated with the Oracle Enterprise Manager. This includes cleaning up schemas and tablespaces specific to components like Metadata Services, Authorization Policy Manager, and Oracle Platform Security Services. By doing so, it ensures that no lingering traces remain that could impact future operations, and maintains the database's integrity by freeing up space and avoiding potential conflicts with new installations or upgrades .

You might also like