19c 환경에서 oracle 유저로 수행
dbua
{}
sys 계정으로 DB에서 수행
@?/olap/admin/catnoamd.sql purge dba_recyclebin;
[oracle@orcl ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 [oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 20:38:25 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @?/olap/admin/catnoamd.sql Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Package dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. View dropped. View dropped. Type dropped. Type dropped. View dropped. Type dropped. Type dropped. Type dropped. Type dropped. PL/SQL procedure successfully completed. Role dropped. PL/SQL procedure successfully completed. 1 row deleted. SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
{}
oracle 유저로 OS에서 수행
cp <ORACLE_HOME(19c)>/rdbms/admin/emremove.sql <ORACLE_HOME(11g)>/rdbms/admin/ emctl stop dbconsole
[oracle@orcl ~]$ cp /u01/app/oracle/product/19c/db_1/rdbms/admin/emremove.sql /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ [oracle@orcl ~]$ emctl stop dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved. https://dbup:1158/em/console/aboutApplication Stopping Oracle Enterprise Manager 11g Database Control ... ... Stopped.
sys 계정으로 DB에서 수행
SET ECHO ON; SET SERVEROUTPUT ON; @?/rdbms/admin/emremove.sql
[oracle@orcl ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1
[oracle@orcl ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 20:43:02 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET ECHO ON;
SQL> SET SERVEROUTPUT ON;
SQL> @?/rdbms/admin/emremove.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/emremove.sql /main/5 2017/05/28 22:46:05 stanaya Exp $
SQL> Rem
SQL> Rem emremove.sql
SQL> Rem
SQL> Rem Copyright (c) 2012, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem emremove.sql - This script removes EM Schema from RDBMS
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script will drop the Oracle Enterprise Manager related schemas and objects.
SQL> Rem This script might take few minutes to complete; it has 6 phases to complete the process.
SQL> Rem The script may take longer if you have SYSMAN and related sessions are active
SQL> Rem from Oracle Enterprise Manager(OEM) application.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Please do following two steps before running this script
SQL> Rem set serveroutput on
SQL> Rem set echo on
SQL> Rem
SQL> Rem
SQL> Rem
SQL> Rem RECOMMENDATIONS
SQL> Rem
SQL> Rem You are recommended to shutdown DB Control application immediately before running this
SQL> Rem OEM repository removal script.
SQL> Rem To shutdown DB Control application, you need to run emctl stop dbconsole
SQL> Rem
SQL> Rem
SQL> Rem Steps to be performed manually (after this script is run)
SQL> Rem
SQL> Rem
SQL> Rem Please note that you need to remove the DB Control Configuration Files
SQL> Rem manually to remove DB Control completly; remove the following
SQL> Rem directories from your filesystem
SQL> Rem <ORACLE_HOME>/<hostname_sid>
SQL> Rem <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
SQL> Rem
SQL> Rem If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,
SQL> Rem then the following directory also needs to be removed from the file system.
SQL> Rem <ORACLE_HOME>/<hostname_sid>.upgrade
SQL> Rem <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
SQL> Rem
SQL> Rem On Microsoft platforms, also delete the DB Console service, generally with name
SQL> Rem OracleDBConsole<sid>
SQL> Rem
SQL> Rem #############################################################################################
SQL> Rem
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/emremove.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/emremove.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem spramani 01/17/17 - fix for 24518751
SQL> Rem spramani 08/03/16 - fix # 24330891
SQL> Rem spramani 07/20/12 - more fix
SQL> Rem spramani 12/21/11 - Created
SQL> Rem
SQL>
SQL>
SQL> DEFINE EM_REPOS_USER ="SYSMAN"
SQL> DEFINE LOGGING = "VERBOSE"
SQL>
SQL> declare
2
3 l_username dba_role_privs.grantee%type;
4 l_user_name dba_role_privs.grantee%type;
5 l_sql varchar2(1024);
6 l_sysman_user number;
7 l_mgmt_users_src number;
8 l_sid number;
9 l_serial number;
10 err number;
11 err_msg varchar2(128);
12 c number;
13 l_removejobs varchar2(1024);
14 l_set_context varchar2(1024);
15 l_client varchar2(16) := ' ';
16 l_context integer := 5;
17 l_verbose boolean := FALSE;
18 l_msg varchar2(1024);
19 l_open_acc number := 0;
20
21 TYPE SESSION_REC IS RECORD
22 (sid v$session.sid%type,
23 serial_no v$session.serial#%type);
24 TYPE SESSION_TYPE IS TABLE OF SESSION_REC;
25 l_sessions SESSION_TYPE;
26
27
28
29 l_job_process_count NUMBER ;
30 TYPE TBSP_ARRAY IS TABLE OF varchar2(64) INDEX BY BINARY_INTEGER ;
31 l_tablespaces TBSP_ARRAY;
32
33 PROCEDURE set_job_process_count(p_count IN NUMBER)
34 IS
35 BEGIN
36 --scope=memory so it will be reset on instance startup
37 -- SID=* to take care of RAC
38 IF p_count >=0
39 THEN
40 EXECUTE IMMEDIATE 'ALTER SYSTEM SET job_queue_processes='
41 ||p_count||' SID=''*'' scope=memory' ;
42 END IF ;
43 EXCEPTION WHEN OTHERS THEN NULL ;
44 END set_job_process_count ;
45
46 PROCEDURE LOG_MESSAGE (verbose boolean, message varchar2)
47 IS
48 BEGIN
49 IF (verbose = TRUE)
50 THEN
51 DBMS_OUTPUT.PUT_LINE(message);
52 END IF;
53 END LOG_MESSAGE;
54
55 FUNCTION get_job_process_count
56 RETURN NUMBER
57 IS
58 l_value NUMBER ;
59 BEGIN
60 SELECT value
61 INTO l_value
62 FROM v$parameter
63 WHERE name = 'job_queue_processes' ;
64 RETURN(l_value) ;
65 EXCEPTION
66 WHEN OTHERS THEN
67 RETURN(10) ;
68 END get_job_process_count ;
69 begin
70 IF (upper('&LOGGING') = 'VERBOSE')
71 THEN
72 l_verbose := TRUE;
73 END IF;
74
75 LOG_MESSAGE(l_verbose,' This script will drop the Oracle Enterprise Manager related schemas and objects.');
76 LOG_MESSAGE(l_verbose, ' This script might take few minutes to complete; it has 6 phases to complete the process.');
77 LOG_MESSAGE(l_verbose,' The script may take longer if you have SYSMAN and related sessions are active');
78 LOG_MESSAGE(l_verbose,' from Oracle Enterprise Manager(OEM) application.');
79 LOG_MESSAGE(l_verbose,' ');
80 LOG_MESSAGE(l_verbose,' ');
81 LOG_MESSAGE(l_verbose,' Recommendations:');
82 LOG_MESSAGE(l_verbose,' ');
83 LOG_MESSAGE(l_verbose,' ');
84 LOG_MESSAGE(l_verbose,' You are recommended to shutdown DB Control application immediately before running this');
85 LOG_MESSAGE(l_verbose,' OEM repository removal script.');
86 LOG_MESSAGE(l_verbose,' To shutdown DB Control application, you need to run: emctl stop dbconsole');
87 LOG_MESSAGE(l_verbose,' ');
88 LOG_MESSAGE(l_verbose,' ');
89 LOG_MESSAGE(l_verbose,' Steps to be performed manually (after this script is run):');
90 LOG_MESSAGE(l_verbose,' ');
91 LOG_MESSAGE(l_verbose,' ');
92 LOG_MESSAGE(l_verbose,' Please note that you need to remove the DB Control Configuration Files');
93 LOG_MESSAGE(l_verbose,' manually to remove DB Control completly; remove the following');
94 LOG_MESSAGE(l_verbose,' directories from your filesystem:');
95 LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/<hostname_sid>');
96 LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>');
97 LOG_MESSAGE(l_verbose,' ');
98 LOG_MESSAGE(l_verbose,' If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to 10.2.0.4,');
99 LOG_MESSAGE(l_verbose,' then the following directory also needs to be removed from the file system.');
100 LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/<hostname_sid>.upgrade');
101 LOG_MESSAGE(l_verbose,' <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade');
102 LOG_MESSAGE(l_verbose,' ');
103 LOG_MESSAGE(l_verbose,' On Microsoft platforms, also delete the DB Console service, generally with name');
104 LOG_MESSAGE(l_verbose,' OracleDBConsole<sid>');
105
106
107 LOG_MESSAGE(l_verbose,'Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle Enterprise Manager related schemas; except SYSMAN ...');
108
109 c := 0;
110 BEGIN
111 select count(1) into l_sysman_user from all_users where username='SYSMAN';
112 IF (l_sysman_user > 0 ) THEN
113 BEGIN
114
115 BEGIN
116 LOG_MESSAGE(l_verbose,'dropping AQ related objests from SYSMAN ...');
117
118 DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);
119 EXCEPTION
120 WHEN OTHERS THEN
121 err := SQLCODE;
122 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: AQ related objects are dropped already or not found');
123 END;
124
125 BEGIN
126 -- reduce job_queue_processes to zero
127 l_job_process_count := get_job_process_count ;
128 set_job_process_count(0) ;
129 LOG_MESSAGE(l_verbose,'saved job_queue_process=' || l_job_process_count || ', set to 0, now removing Oracle EM jobs ...');
130 l_removejobs := 'BEGIN ' || 'SYSMAN' || '.emd_maintenance.remove_em_dbms_jobs; END;';
131 execute immediate l_removejobs;
132 EXCEPTION
133 WHEN OTHERS THEN
134 err := SQLCODE;
135 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: EM jobs are dropped already or not found');
136 END;
137
138 END;
139 END IF;
140 END;
141
142 -- First, drop all users, except SYSMAN who have MGMT_USER role and
143 -- are created by EM. All users created by EM will have a record
144 -- in MGMT_CREATED_USERS table
145 --
146
147 BEGIN
148 select count(1) into l_sysman_user from all_users where username='SYSMAN';
149 IF (l_sysman_user > 0 ) THEN
150 BEGIN
151
152 LOOP -- part 1 main loop
153 -- handle SYSMAN is partially dropped
154 select count(1) into l_mgmt_users_src from all_objects where object_name='MGMT_CREATED_USERS' and owner='SYSMAN';
155 IF(l_mgmt_users_src = 0 ) THEN
156 EXIT;
157 END IF;
158 BEGIN
159 LOG_MESSAGE(l_verbose,'finding users who needs to be dropped ...');
160 l_username := '';
161 BEGIN
162 execute immediate 'select grantee
163 from sys.dba_role_privs
164 where granted_role ='||DBMS_ASSERT.ENQUOTE_LITERAL('MGMT_USER')||
165 ' AND grantee IN (SELECT user_name
166 FROM SYSMAN.MGMT_CREATED_USERS
167 WHERE SYSTEM_USER=0)
168 AND ROWNUM=1'
169 into l_user_name;
170 LOG_MESSAGE(l_verbose,'found user name: ' || l_user_name);
171 l_username := DBMS_ASSERT.ENQUOTE_NAME(l_user_name, FALSE);
172
173 EXECUTE IMMEDIATE 'ALTER USER '||l_username||' ACCOUNT LOCK' ;
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 LOG_MESSAGE(l_verbose,l_username || ' IS ALREADY DROPPED');
177 EXIT; -- THEN RETURN ;
178 END ;
179
180
181 FOR cnt in 1 .. 150 LOOP -- session kill loop
182
183 BEGIN
184 -- FOR crec in (SELECT sid, serial#
185 -- FROM v$session
186 -- WHERE username=l_username
187 -- AND status NOT IN('KILLED'))
188
189 l_sql := 'SELECT sid, serial#
190 FROM v$session
191 WHERE username='|| DBMS_ASSERT.ENQUOTE_LITERAL(l_user_name)||'
192 AND status NOT IN(''KILLED'')';
193 execute immediate l_sql BULK COLLECT INTO l_sessions;
194
195 FOR i in 1..l_sessions.COUNT
196 LOOP -- cursor loop
197 BEGIN
198 LOG_MESSAGE(l_verbose,'killing related sessions : sid= ' || l_sessions(i).sid || ' serial#= ' || l_sessions(i).serial_no || ' ...');
199 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' || DBMS_ASSERT.ENQUOTE_LITERAL(l_sessions(i).sid || ',' || l_sessions(i).serial_no)||' IMMEDIATE';
200 EXCEPTION
201 WHEN OTHERS THEN
202 err := SQLCODE;
203 IF err != -30 THEN
204 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no session found; or already killed.');
205 EXIT;
206 END IF;
207 END;
208 COMMIT;
209 END LOOP; -- end cursor loop
210
211 EXCEPTION
212 WHEN OTHERS THEN
213 err := SQLCODE;
214 IF err != -30 THEN
215 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no session found; or already killed.');
216 EXIT;
217 END IF;
218 END;
219
220 IF SQL%NOTFOUND THEN
221 LOG_MESSAGE(l_verbose,'found [sql%notfound]: no session found; or already killed.');
222 EXIT;
223 END IF;
224
225 COMMIT;
226
227 END LOOP; -- end session killing loop
228 LOG_MESSAGE(l_verbose,' Dropping user : ' || l_username || '...');
229
230 EXECUTE IMMEDIATE 'drop user ' || l_username || ' cascade';
231 exit;
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 LOG_MESSAGE(l_verbose,'found [no_data_found]: no user/corresponding sessions found related to DB Control');
235 EXIT;
236 WHEN OTHERS THEN
237 err := SQLCODE;
238 IF err = -1918 THEN
239 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no DB Control user/corresponding sessions found related to DB Control');
240 EXIT;
241 ELSE
242 IF err = -1940 THEN
243 NULL;
244 ELSE
245 -- keep count of try to drop EM related user and sessions
246 -- give up after 50 try
247
248 c := c+1;
249 IF c > 50 THEN
250 RAISE;
251 END IF;
252 END IF;
253 END IF;
254 END;
255 END LOOP; -- end part main loop
256 END;
257 ELSE
258 LOG_MESSAGE(l_verbose,'SYSMAN IS ALREADY DROPPED');
259 END IF;
260 END;
261
262 BEGIN
263 -- Now, drop the SYSMAN user
264 LOG_MESSAGE(l_verbose,'Finished phase 1');
265 LOG_MESSAGE(l_verbose,'Starting phase 2 : Dropping SYSMAN schema ...');
266
267 c := 0;
268 -- validate user exists
269 select count(1) into l_sysman_user from all_users where username='SYSMAN';
270 IF (l_sysman_user > 0 ) THEN
271 BEGIN
272
273 BEGIN
274 -- SELECT username
275 -- INTO l_username
276 -- FROM dba_users
277 -- WHERE username = 'SYSMAN';
278 -- l_user_name := 'SYSMAN';
279 -- l_username = DBMS_ASSERT.ENQUOTE_NAME(l_user_name);
280 EXECUTE IMMEDIATE 'ALTER USER SYSMAN ACCOUNT LOCK' ;
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 LOG_MESSAGE(l_verbose,'SYSMAN IS ALREADY DROPPED');
284 -- THEN RETURN ;
285 END ;
286
287
288 BEGIN
289 LOOP -- main loop
290 BEGIN
291 FOR cnt in 1 .. 150 LOOP -- session kill loop
292 BEGIN
293 FOR crec in (SELECT sid, serial#
294 FROM gv$session
295 WHERE (username='SYSMAN' OR
296 schemaname='SYSMAN')
297 AND status != 'KILLED')
298 LOOP --cursor loop
299 BEGIN
300 LOG_MESSAGE(l_verbose,'killing related sessions : sid= ' || crec.sid || ' serial#= ' || crec.serial# || ' ...');
301 EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ' ||
302 DBMS_ASSERT.ENQUOTE_LITERAL(crec.sid || ',' || crec.serial#)|| ' IMMEDIATE';
303 EXCEPTION
304 WHEN OTHERS THEN
305 err := SQLCODE;
306 IF err != -30 THEN
307 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: SYSMAN related sessions are already killed; no session found');
308 EXIT;
309 END IF;
310 END;
311 COMMIT;
312 END LOOP; -- cursor loop ends
313 EXCEPTION
314 WHEN OTHERS THEN
315 err := SQLCODE;
316 IF err != -30 THEN
317 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: SYSMAN related sessions are already killed; no session found');
318 EXIT;
319 END IF;
320 END;
321 IF SQL%NOTFOUND THEN
322 LOG_MESSAGE(l_verbose,'found [sql%notfound]: SYSMAN related sessions are already killed; no session found');
323 EXIT;
324 END IF;
325 COMMIT;
326 END LOOP; -- end of session kill loop
327
328 -- END;
329 LOG_MESSAGE(l_verbose,'dropping user : ' || l_user_name || '...');
330 execute immediate 'drop user SYSMAN cascade';
331 set_job_process_count(l_job_process_count) ;
332 exit;
333
334 -- >> START - Dropping the Tablespaces
335 LOG_MESSAGE(l_verbose,'Finished phase 2');
336 LOG_MESSAGE(l_verbose,'Starting phase 3 : Dropping Oracle Enterprise Manager related tablespaces ...');
337
338 LOG_MESSAGE(l_verbose,'No seperate TABLESPACES Exist for EM; all in SYSAUX; no action taken');
339 -- >> END - Dropping the Tablespaces
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 LOG_MESSAGE(l_verbose,'found [ no_data_found]: no sysman/corresponding sessions');
344 EXIT;
345 WHEN OTHERS THEN
346 err := SQLCODE;
347 IF err = -1918 THEN
348 LOG_MESSAGE(l_verbose,'found [sqlcode:1918]: no sysman/corresponding sessions');
349 EXIT;
350 ELSIF err = -1940 THEN
351 NULL;
352 ELSE
353 LOG_MESSAGE(l_verbose,'found [sqlcode:'||err||']: no sysman/corresponding sessions');
354 c := c+1;
355 IF c > 50 THEN
356 RAISE;
357 END IF;
358 END IF;
359 END;
360 END LOOP; -- end of main loop
361 END;
362 LOG_MESSAGE(l_verbose,'SYSMAN dropped');
363 commit;
364 END;
365 ELSE
366 LOG_MESSAGE(l_verbose,'SYSMAN is already dropped');
367 END IF;
368 EXCEPTION
369 WHEN OTHERS THEN
370 set_job_process_count(l_job_process_count) ;
371 RAISE ;
372 END;
373
374 BEGIN
375
376 -- Drop basic roles.
377 LOG_MESSAGE(l_verbose,'Finished phase 3');
378 LOG_MESSAGE(l_verbose,'Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...');
379
380 BEGIN
381 execute immediate 'drop role MGMT_USER';
382 EXCEPTION
383 WHEN OTHERS THEN
384 LOG_MESSAGE(l_verbose,'Role MGMT_USER already dropped');
385 END;
386 --
387 -- Drop the following synonyms related to REPOS Schema
388 --
389 LOG_MESSAGE(l_verbose,'Finished phase 4');
390 LOG_MESSAGE(l_verbose,'Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms ...');
391
392
393 BEGIN
394 FOR crec in (SELECT synonym_name,table_owner,table_name
395 FROM dba_synonyms
396 WHERE owner = 'PUBLIC'
397 AND table_owner = 'SYSMAN')
398 LOOP
399 BEGIN
400 LOG_MESSAGE(l_verbose,'Dropping synonym : ' || crec.synonym_name || ' ... ');
401 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.synonym_name);
402
403 EXCEPTION
404 when others then
405 LOG_MESSAGE(l_verbose,'Public synonym ' || crec.synonym_name ||
406 ' cannot be dropped');
407 -- continue dropping other synonyms.
408 END;
409 END LOOP;
410 END;
411
412 BEGIN
413 LOG_MESSAGE(l_verbose,'Finished phase 5');
414 LOG_MESSAGE(l_verbose,'Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...');
415 FOR crec in (select role from sys.dba_roles where role like 'MGMT_%')
416 LOOP
417 LOG_MESSAGE(l_verbose,'Dropping role: ' || crec.role ||' ...');
418 execute immediate 'drop role ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.role);
419 commit;
420 END LOOP;
421 EXCEPTION
422 when NO_DATA_FOUND THEN
423 LOG_MESSAGE(l_verbose,'Roles like MGMT% do not exist');
424 WHEN OTHERS THEN
425 err := SQLCODE;
426 LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: no MGMT% roles to drop');
427 END;
428
429 -- lock DBSNMP user
430 BEGIN
431 BEGIN
432 LOG_MESSAGE(l_verbose,'Process DBSNMP user');
433 select count(1) into l_open_acc from DBA_USERS where USERNAME ='DBSNMP' and ACCOUNT_STATUS='OPEN';
434 EXCEPTION
435 when NO_DATA_FOUND THEN
436 LOG_MESSAGE(l_verbose,'User DBSNMP does not exist');
437 WHEN OTHERS THEN
438 err := SQLCODE;
439 LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while checking DBSNMP user status');
440
441 END;
442
443 IF (l_open_acc > 0 ) THEN
444 BEGIN
445 execute immediate 'ALTER USER DBSNMP PASSWORD EXPIRE';
446 LOG_MESSAGE(l_verbose,'DBSNMP user password is made expired');
447 EXCEPTION
448 WHEN OTHERS THEN
449 err := SQLCODE;
450 LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while expiring DBSNMP user password');
451 END;
452
453 BEGIN
454 execute immediate 'ALTER USER DBSNMP ACCOUNT LOCK';
455 LOG_MESSAGE(l_verbose,'User DBSNMP is locked');
456 EXCEPTION
457 WHEN OTHERS THEN
458 err := SQLCODE;
459 LOG_MESSAGE(l_verbose,'found [sqlcode: '||SQLCODE||']: while locking DBSNMP user');
460 END;
461
462 END IF;
463 LOG_MESSAGE(l_verbose,'Done processing DBSNMP user');
464 END;
465
466 LOG_MESSAGE(l_verbose,'Finished phase 6');
467 LOG_MESSAGE(l_verbose,'The Oracle Enterprise Manager related schemas and objects are dropped.');
468 LOG_MESSAGE(l_verbose,'Do the manual steps to studown the DB Control if not done before running this script and then delete the DB Control configuration files');
469 commit;
470 END;
471 END;
472 /
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
This script will drop the Oracle Enterprise Manager related schemas and objects.
This script might take few minutes to complete; it has 6 phases to complete the
process.
The script may take longer if you have SYSMAN and related sessions are active
from Oracle Enterprise Manager(OEM) application.
Recommendations:
You are recommended to shutdown DB Control application immediately before
running this
OEM repository removal script.
To shutdown DB Control application, you need to run: emctl stop dbconsole
Steps to be performed manually (after this script is run):
Please note that you need to remove the DB Control Configuration Files
manually to remove DB Control completly; remove the following
directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>
If the dbcontrol is upgraded from lower version, for example, from 10.2.0.3 to
10.2.0.4,
then the following directory also needs to be removed from the file system.
<ORACLE_HOME>/<hostname_sid>.upgrade
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>.upgrade
On Microsoft platforms, also delete the DB Console service, generally with name
OracleDBConsole<sid>
Starting phase 1 : Dropping AQ related objests, EM jobs and all Oracle
Enterprise Manager related schemas; except SYSMAN ...
dropping AQ related objests from SYSMAN ...
saved job_queue_process=1000, set to 0, now removing Oracle EM jobs ...
finding users who needs to be dropped ...
found user name: MGMT_VIEW
found [sql%notfound]: no session found; or already killed.
Dropping user : "MGMT_VIEW"...
Finished phase 1
Starting phase 2 : Dropping SYSMAN schema ...
found [sql%notfound]: SYSMAN related sessions are already killed; no session
found
dropping user : MGMT_VIEW...
SYSMAN dropped
Finished phase 3
Starting phase 4 : Dropping Oracle Enterprise Manager related MGMT_USER role ...
Finished phase 4
Starting phase 5 : Dropping Oracle Enterprise Manager related public synonyms
...
Dropping synonym : ECM_UTIL ...
Dropping synonym : EMD_MNTR ...
Dropping synonym : MGMT$ALERT_ANNOTATIONS ...
Dropping synonym : MGMT$ALERT_CURRENT ...
Dropping synonym : MGMT$ALERT_HISTORY ...
Dropping synonym : MGMT$ALERT_NOTIF_LOG ...
Dropping synonym : MGMT$APPLIED_PATCHES ...
Dropping synonym : MGMT$APPLIED_PATCHSETS ...
Dropping synonym : MGMT$APPL_PATCH_AND_PATCHSET ...
Dropping synonym : MGMT$AUDIT_LOG ...
Dropping synonym : MGMT$AVAILABILITY_CURRENT ...
Dropping synonym : MGMT$AVAILABILITY_HISTORY ...
Dropping synonym : MGMT$BLACKOUTS ...
Dropping synonym : MGMT$BLACKOUT_HISTORY ...
Dropping synonym : MGMT$CLUSTER_INTERCONNECTS ...
Dropping synonym : MGMT$CPF_ADVISORY_INFO ...
Dropping synonym : MGMT$CPF_HOMES_INFO ...
Dropping synonym : MGMT$CPF_PATCH_DATA ...
Dropping synonym : MGMT$CPF_PATCH_INFO ...
Dropping synonym : MGMT$CSA_CLIENTS ...
Dropping synonym : MGMT$CSA_CLIENT_RULE_VIOLS ...
Dropping synonym : MGMT$CSA_COLLECTIONS ...
Dropping synonym : MGMT$CSA_FAILED ...
Dropping synonym : MGMT$CSA_HOST_COOKIES ...
Dropping synonym : MGMT$CSA_HOST_CPUS ...
Dropping synonym : MGMT$CSA_HOST_CUSTOM ...
Dropping synonym : MGMT$CSA_HOST_IOCARDS ...
Dropping synonym : MGMT$CSA_HOST_NICS ...
Dropping synonym : MGMT$CSA_HOST_OS_COMPONENTS ...
Dropping synonym : MGMT$CSA_HOST_OS_FILESYSTEMS ...
Dropping synonym : MGMT$CSA_HOST_OS_PROPERTIES ...
Dropping synonym : MGMT$CSA_HOST_RULES ...
Dropping synonym : MGMT$CSA_HOST_SW ...
Dropping synonym : MGMT$CSM_DOMAIN_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_DAILY ...
Dropping synonym : MGMT$CSM_DOMAIN_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_DOMAIN_HOURLY ...
Dropping synonym : MGMT$CSM_IP_DAILY ...
Dropping synonym : MGMT$CSM_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_IP_HOURLY ...
Dropping synonym : MGMT$CSM_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_MT_DSR_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_DSR_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_DSR_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_DAILY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_IP_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_IP_HOURLY ...
Dropping synonym : MGMT$CSM_MT_METRIC_DETAILS ...
Dropping synonym : MGMT$CSM_MT_URL_DAILY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_MT_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_MT_URL_HOURLY ...
Dropping synonym : MGMT$CSM_REGION ...
Dropping synonym : MGMT$CSM_REGION_DAILY ...
Dropping synonym : MGMT$CSM_REGION_DIST_DAILY ...
Dropping synonym : MGMT$CSM_REGION_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_REGION_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_DAILY ...
Dropping synonym : MGMT$CSM_SUBNET_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_SUBNET_HOURLY ...
Dropping synonym : MGMT$CSM_URL_DAILY ...
Dropping synonym : MGMT$CSM_URL_DIST_DAILY ...
Dropping synonym : MGMT$CSM_URL_DIST_HOURLY ...
Dropping synonym : MGMT$CSM_URL_HOURLY ...
Dropping synonym : MGMT$CSM_WATCHLIST ...
Dropping synonym : MGMT$CS_CONFIG_STANDARDS ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_RULE ...
Dropping synonym : MGMT$CS_EVAL_SUMMARY_STANDARD ...
Dropping synonym : MGMT$DB_CONTROLFILES ...
Dropping synonym : MGMT$DB_CONTROLFILES_ALL ...
Dropping synonym : MGMT$DB_DATAFILES ...
Dropping synonym : MGMT$DB_DATAFILES_ALL ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO ...
Dropping synonym : MGMT$DB_DBNINSTANCEINFO_ALL ...
Dropping synonym : MGMT$DB_FEATUREUSAGE ...
Dropping synonym : MGMT$DB_INIT_PARAMS ...
Dropping synonym : MGMT$DB_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$DB_LICENSE ...
Dropping synonym : MGMT$DB_LICENSE_ALL ...
Dropping synonym : MGMT$DB_OPTIONS ...
Dropping synonym : MGMT$DB_OPTIONS_ALL ...
Dropping synonym : MGMT$DB_REDOLOGS ...
Dropping synonym : MGMT$DB_REDOLOGS_ALL ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS ...
Dropping synonym : MGMT$DB_ROLLBACK_SEGS_ALL ...
Dropping synonym : MGMT$DB_SGA ...
Dropping synonym : MGMT$DB_SGA_ALL ...
Dropping synonym : MGMT$DB_TABLESPACES ...
Dropping synonym : MGMT$DB_TABLESPACES_ALL ...
Dropping synonym : MGMT$DELTA_COMPONENTS ...
Dropping synonym : MGMT$DELTA_COMPONENT_DETAILS ...
Dropping synonym : MGMT$DELTA_FS_MOUNT ...
Dropping synonym : MGMT$DELTA_HARDWARE ...
Dropping synonym : MGMT$DELTA_HOST_CONFIG ...
Dropping synonym : MGMT$DELTA_INIT ...
Dropping synonym : MGMT$DELTA_ONEOFF_PATCHES ...
Dropping synonym : MGMT$DELTA_ORACLE_HOME ...
Dropping synonym : MGMT$DELTA_OS_COMPONENTS ...
Dropping synonym : MGMT$DELTA_OS_COMP_DETAILS ...
Dropping synonym : MGMT$DELTA_OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$DELTA_PATCHSETS ...
Dropping synonym : MGMT$DELTA_PATCHSET_DETAILS ...
Dropping synonym : MGMT$DELTA_VENDOR_SW ...
Dropping synonym : MGMT$DELTA_VIEW ...
Dropping synonym : MGMT$DELTA_VIEW_DETAILS ...
Dropping synonym : MGMT$E2E_1DAY ...
Dropping synonym : MGMT$E2E_HOURLY ...
Dropping synonym : MGMT$E2E_RAW ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY1 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY2 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY3 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY4 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY5 ...
Dropping synonym : MGMT$ECM_CONFIG_HISTORY_KEY6 ...
Dropping synonym : MGMT$ECM_CURRENT_SNAPSHOTS ...
Dropping synonym : MGMT$ECM_VISIBLE_SNAPSHOTS ...
Dropping synonym : MGMT$EM_HOMES_PLATFORM ...
Dropping synonym : MGMT$ESA_ALL_PRIVS_REPORT ...
Dropping synonym : MGMT$ESA_ANY_DICT_REPORT ...
Dropping synonym : MGMT$ESA_ANY_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_AUDIT_SYSTEM_REPORT ...
Dropping synonym : MGMT$ESA_BECOME_USER_REPORT ...
Dropping synonym : MGMT$ESA_CATALOG_REPORT ...
Dropping synonym : MGMT$ESA_CONN_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_CREATE_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_DBA_GROUP_REPORT ...
Dropping synonym : MGMT$ESA_DBA_ROLE_REPORT ...
Dropping synonym : MGMT$ESA_DIRECT_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_EXMPT_ACCESS_REPORT ...
Dropping synonym : MGMT$ESA_KEY_OBJECTS_REPORT ...
Dropping synonym : MGMT$ESA_OH_OWNERSHIP_REPORT ...
Dropping synonym : MGMT$ESA_OH_PERMISSION_REPORT ...
Dropping synonym : MGMT$ESA_POWER_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_PUB_PRIV_REPORT ...
Dropping synonym : MGMT$ESA_SYS_PUB_PKG_REPORT ...
Dropping synonym : MGMT$ESA_TABSP_OWNERS_REPORT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REPORT ...
Dropping synonym : MGMT$ESA_TRC_AUD_PERM_REP_NT ...
Dropping synonym : MGMT$ESA_WITH_ADMIN_REPORT ...
Dropping synonym : MGMT$ESA_WITH_GRANT_REPORT ...
Dropping synonym : MGMT$GROUP_DERIVED_MEMBERSHIPS ...
Dropping synonym : MGMT$GROUP_FLAT_MEMBERSHIPS ...
Dropping synonym : MGMT$GROUP_MEMBERS ...
Dropping synonym : MGMT$GRP_METRICS_DAILY ...
Dropping synonym : MGMT$GRP_METRICS_HOURLY ...
Dropping synonym : MGMT$GRP_METRICS_RAW ...
Dropping synonym : MGMT$HA_BACKUP ...
Dropping synonym : MGMT$HA_FILES ...
Dropping synonym : MGMT$HA_FILES_ALL ...
Dropping synonym : MGMT$HA_INFO ...
Dropping synonym : MGMT$HA_INFO_ALL ...
Dropping synonym : MGMT$HA_INIT_PARAMS ...
Dropping synonym : MGMT$HA_INIT_PARAMS_ALL ...
Dropping synonym : MGMT$HA_MTTR ...
Dropping synonym : MGMT$HA_RMAN_CONFIG ...
Dropping synonym : MGMT$HA_RMAN_CONFIG_ALL ...
Dropping synonym : MGMT$HOMES_AFFECTED ...
Dropping synonym : MGMT$HOSTPATCH_GROUPS ...
Dropping synonym : MGMT$HOSTPATCH_GRP_COMPL_HIST ...
Dropping synonym : MGMT$HOSTPATCH_HOSTS ...
Dropping synonym : MGMT$HOSTPATCH_HOST_COMPL ...
Dropping synonym : MGMT$HW_NIC ...
Dropping synonym : MGMT$INTERFACE_STATS ...
Dropping synonym : MGMT$JOBS ...
Dropping synonym : MGMT$JOB_ANNOTATIONS ...
Dropping synonym : MGMT$JOB_EXECUTION_HISTORY ...
Dropping synonym : MGMT$JOB_NOTIFICATION_LOG ...
Dropping synonym : MGMT$JOB_STEP_HISTORY ...
Dropping synonym : MGMT$JOB_TARGETS ...
Dropping synonym : MGMT$MESSAGES ...
Dropping synonym : MGMT$METRIC_CATEGORIES ...
Dropping synonym : MGMT$METRIC_COLLECTION ...
Dropping synonym : MGMT$METRIC_CURRENT ...
Dropping synonym : MGMT$METRIC_DAILY ...
Dropping synonym : MGMT$METRIC_DETAILS ...
Dropping synonym : MGMT$METRIC_ERROR_CURRENT ...
Dropping synonym : MGMT$METRIC_ERROR_HISTORY ...
Dropping synonym : MGMT$METRIC_HOURLY ...
Dropping synonym : MGMT$MISSING_TARGETS ...
Dropping synonym : MGMT$MISSING_TARGETS_IN_GROUPS ...
Dropping synonym : MGMT$ORACLE_SW_ENT_INSTALL ...
Dropping synonym : MGMT$ORACLE_SW_ENT_TARGETS ...
Dropping synonym : MGMT$ORACLE_SW_GRP_INSTALL ...
Dropping synonym : MGMT$ORACLE_SW_GRP_TARGETS ...
Dropping synonym : MGMT$OS_COMPONENTS ...
Dropping synonym : MGMT$OS_FS_MOUNT ...
Dropping synonym : MGMT$OS_HW_SUMMARY ...
Dropping synonym : MGMT$OS_KERNEL_PARAMS ...
Dropping synonym : MGMT$OS_PATCHES ...
Dropping synonym : MGMT$OS_PROPERTIES ...
Dropping synonym : MGMT$OS_SUMMARY ...
Dropping synonym : MGMT$PATCH_ADVISORIES ...
Dropping synonym : MGMT$POLICIES ...
Dropping synonym : MGMT$POLICY_PARAMETERS ...
Dropping synonym : MGMT$POLICY_VIOLATION_CONTEXT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CTXT ...
Dropping synonym : MGMT$POLICY_VIOLATION_CURRENT ...
Dropping synonym : MGMT$POLICY_VIOLATION_HISTORY ...
Dropping synonym : MGMT$POLICY_VIOL_ANNOTATIONS ...
Dropping synonym : MGMT$POLICY_VIOL_NOTIF_LOG ...
Dropping synonym : MGMT$RACDB_INTERCONNECTS ...
Dropping synonym : MGMT$SOFTWARE_COMPONENTS ...
Dropping synonym : MGMT$SOFTWARE_COMPONENT_ONEOFF ...
Dropping synonym : MGMT$SOFTWARE_COMP_PATCHSET ...
Dropping synonym : MGMT$SOFTWARE_DEPENDENCIES ...
Dropping synonym : MGMT$SOFTWARE_HOMES ...
Dropping synonym : MGMT$SOFTWARE_HOME_PROPERTIES ...
Dropping synonym : MGMT$SOFTWARE_ONEOFF_PATCHES ...
Dropping synonym : MGMT$SOFTWARE_OTHERS ...
Dropping synonym : MGMT$SOFTWARE_PATCHES_IN_HOMES ...
Dropping synonym : MGMT$SOFTWARE_PATCHSETS ...
Dropping synonym : MGMT$STEPS ...
Dropping synonym : MGMT$STEP_GROUPS ...
Dropping synonym : MGMT$STEP_METRICS_DAILY ...
Dropping synonym : MGMT$STEP_METRICS_HOURLY ...
Dropping synonym : MGMT$STEP_METRICS_RAW ...
Dropping synonym : MGMT$STORAGE_REPORT_DATA ...
Dropping synonym : MGMT$STORAGE_REPORT_DISK ...
Dropping synonym : MGMT$STORAGE_REPORT_ISSUES ...
Dropping synonym : MGMT$STORAGE_REPORT_KEYS ...
Dropping synonym : MGMT$STORAGE_REPORT_LOCALFS ...
Dropping synonym : MGMT$STORAGE_REPORT_NFS ...
Dropping synonym : MGMT$STORAGE_REPORT_PATHS ...
Dropping synonym : MGMT$STORAGE_REPORT_VOLUME ...
Dropping synonym : MGMT$TARGET ...
Dropping synonym : MGMT$TARGET_ASSOCIATIONS ...
Dropping synonym : MGMT$TARGET_COMPONENTS ...
Dropping synonym : MGMT$TARGET_COMPOSITE ...
Dropping synonym : MGMT$TARGET_FLAT_MEMBERS ...
Dropping synonym : MGMT$TARGET_MEMBERS ...
Dropping synonym : MGMT$TARGET_METRIC_COLLECTIONS ...
Dropping synonym : MGMT$TARGET_METRIC_SETTINGS ...
Dropping synonym : MGMT$TARGET_POLICIES ...
Dropping synonym : MGMT$TARGET_POLICY_EVAL_SUMM ...
Dropping synonym : MGMT$TARGET_POLICY_SETTINGS ...
Dropping synonym : MGMT$TARGET_PROPERTIES ...
Dropping synonym : MGMT$TARGET_TYPE ...
Dropping synonym : MGMT$TARGET_TYPE_DEF ...
Dropping synonym : MGMT$TARGET_TYPE_PROPERTIES ...
Dropping synonym : MGMT$TEMPLATES ...
Dropping synonym : MGMT$TEMPLATE_METRICCOLLECTION ...
Dropping synonym : MGMT$TEMPLATE_METRIC_SETTINGS ...
Dropping synonym : MGMT$TEMPLATE_POLICY_SETTINGS ...
Dropping synonym : MGMT$TXN_PERF_DAY ...
Dropping synonym : MGMT$TXN_PERF_HOUR ...
Dropping synonym : MGMT$TXN_PERF_RAW ...
Dropping synonym : MGMT_ADMIN ...
Dropping synonym : MGMT_AVAILABILITY ...
Dropping synonym : MGMT_TARGET_BLACKOUTS ...
Dropping synonym : MGMT_COLLECTION_PROPERTIES ...
Dropping synonym : MGMT_CREDENTIAL ...
Dropping synonym : MGMT_CURRENT_AVAILABILITY ...
Dropping synonym : MGMT_CURRENT_METRICS ...
Dropping synonym : MGMT_CURRENT_METRIC_ERRORS ...
Dropping synonym : MGMT_CURRENT_SEVERITY ...
Dropping synonym : MGMT_DELTA ...
Dropping synonym : MGMT_DELTA_ENTRY ...
Dropping synonym : MGMT_DELTA_ENTRY_VALUES ...
Dropping synonym : MGMT_DELTA_IDS ...
Dropping synonym : MGMT_DELTA_ID_VALUES ...
Dropping synonym : MGMT_DELTA_VALUE ...
Dropping synonym : MGMT_DELTA_VALUES ...
Dropping synonym : MGMT_GLOBAL ...
Dropping synonym : MGMT_GUID_ARRAY ...
Dropping synonym : MGMT_GUID_OBJ ...
Dropping synonym : MGMT_IP_TGT_GUID_ARRAY ...
Dropping synonym : MGMT_JOB ...
Dropping synonym : MGMT_JOBS ...
Dropping synonym : MGMT_JOB_EXECPLAN ...
Dropping synonym : MGMT_JOB_EXECUTION ...
Dropping synonym : MGMT_JOB_EXEC_SUMMARY ...
Dropping synonym : MGMT_JOB_OUTPUT ...
Dropping synonym : MGMT_JOB_PARAMETER ...
Dropping synonym : MGMT_JOB_SCHEDULE ...
Dropping synonym : MGMT_JOB_TARGET ...
Dropping synonym : MGMT_LOG ...
Dropping synonym : MGMT_LONG_TEXT ...
Dropping synonym : MGMT_MESSAGES ...
Dropping synonym : MGMT_METRICS ...
Dropping synonym : MGMT_METRICS_1DAY ...
Dropping synonym : MGMT_METRICS_1HOUR ...
Dropping synonym : MGMT_METRICS_COMPOSITE_KEYS ...
Dropping synonym : MGMT_METRICS_RAW ...
Dropping synonym : MGMT_METRIC_COLLECTIONS ...
Dropping synonym : MGMT_METRIC_ERRORS ...
Dropping synonym : MGMT_METRIC_THRESHOLDS ...
Dropping synonym : MGMT_NAME_VALUE ...
Dropping synonym : MGMT_NAME_VALUES ...
Dropping synonym : MGMT_PAF$APPLICATIONS ...
Dropping synonym : MGMT_PAF$INSTANCES ...
Dropping synonym : MGMT_PAF$PROCEDURES ...
Dropping synonym : MGMT_PAF$STATES ...
Dropping synonym : MGMT_PAF_JOBS ...
Dropping synonym : MGMT_PAF_PROCS_LATEST ...
Dropping synonym : MGMT_PREFERENCES ...
Dropping synonym : MGMT_SEVERITY ...
Dropping synonym : MGMT_SEVERITY_ARRAY ...
Dropping synonym : MGMT_SEVERITY_OBJ ...
Dropping synonym : MGMT_STRING_METRIC_HISTORY ...
Dropping synonym : MGMT_TARGET ...
Dropping synonym : MGMT_TARGETS ...
Dropping synonym : MGMT_TARGET_MEMBERSHIPS ...
Dropping synonym : MGMT_TARGET_PROPERTIES ...
Dropping synonym : MGMT_TYPE_PROPERTIES ...
Dropping synonym : MGMT_USER ...
Dropping synonym : MGMT_VIEW_UTIL ...
Dropping synonym : SETEMVIEWUSERCONTEXT ...
Dropping synonym : SMP_EMD_AVAIL_OBJ ...
Dropping synonym : SMP_EMD_DELETE_REC_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_NVPAIR ...
Dropping synonym : SMP_EMD_NVPAIR_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_TARGET_OBJ ...
Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Process DBSNMP user
DBSNMP user password is made expired
User DBSNMP is locked
Done processing DBSNMP user
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
{}
sys 계정으로 DB에서 수행
declare list_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); end; /
[oracle@orcl ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 [oracle@orcl ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 20:49:39 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> declare 2 list_failures integer(3) :=0; 3 begin 4 DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); 5 end; 6 / PL/SQL procedure successfully completed. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
{}
19c 경로로 이동하여 sys 계정으로 DB 접속
cd <ORACLE_HOME(19c)>/apex sqlplus / as sysdba
[oracle@orcl ~]$ echo $ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1 [oracle@orcl ~]$ cd /u01/app/oracle/product/19c/db_1/apex [oracle@orcl apex]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 20:53:01 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
{}
업그레이드 전 버전 확인
col comp_name format a35 col status format a12 select comp_name, status, version from dba_Registry where comp_name='Oracle Application Express';
SQL> col comp_name format a35 SQL> col status format a12 SQL> select comp_name, status, version 2 from dba_Registry 3 where comp_name='Oracle Application Express'; COMP_NAME STATUS VERSION ----------------------------------- ------------ ------------------------------ Oracle Application Express VALID 3.2.1.00.12
sys 계정으로 APEX 업그레이드 수행
@apexins SYSAUX SYSAUX TEMP /i/
작업 스크립트 참고 : apex_up.log
업그레이드 후 버전 확인
col comp_name format a35 col status format a12 select comp_name, status, version from dba_Registry where comp_name='Oracle Application Express';
SQL> col comp_name format a35 SQL> col status format a12 SQL> select comp_name, status, version 2 from dba_Registry 3 where comp_name='Oracle Application Express'; COMP_NAME STATUS VERSION ----------------------------------- ------------ ------------------------------ Oracle Application Express VALID 18.2.0.00.12
객체 재컴파일
@?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-02-28 21:10:12
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-02-28 21:10:25
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...(21:10:27) Starting validate_apex for APEX_180200
...(21:10:28) Checking missing sys privileges
...(21:10:28) Re-generating APEX_180200.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(21:10:28) Key object existence check
...(21:10:29) Setting DBMS Registry for APEX to valid
...(21:10:29) Exiting validate_apex
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
