1:备份oracle数据库
2:备份控制文件到trace(以备需要新建控制文件)
代码如下 | 复制代码 |
SQL> alter database backup controlfile to trace; Database altered. SQL> oradebug setmypid Statement processed. SQL> oradebug tracefile_name /u01/app/admin/easy/udump/easy_ora_18830.trc SQL> host cat /u01/app/admin/easy/udump/easy_ora_18830.trc ...... -- -- Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "EASY" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/easy/redo01.log' SIZE 11200K, GROUP 2 '/oradata/easy/redo02.log' SIZE 11200K -- STANDBY LOGFILE DATAFILE '/oradata/easy/system01.dbf', '/oradata/easy/undotbs01.dbf', '/oradata/easy/sysaux01.dbf', '/oradata/easy/users01.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/easy/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- -- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "EASY" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/oradata/easy/redo01.log' SIZE 11200K, GROUP 2 '/oradata/easy/redo02.log' SIZE 11200K -- STANDBY LOGFILE DATAFILE '/oradata/easy/system01.dbf', '/oradata/easy/undotbs01.dbf', '/oradata/easy/sysaux01.dbf', '/oradata/easy/users01.dbf' CHARACTER SET ZHS16GBK ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/flash_recovery_area/EASY/archivelog/2014_11_05/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/easy/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- |
代码如下 | 复制代码 |
SQL> select count(*) from dba_objects where status ='INVALID'; COUNT(*) ---------- 0 SQL> select comp_name,status from dba_registry; COMP_NAME STATUS ---------------------------------------- ---------------------- Oracle Database Catalog Views VALID Oracle Database Packages and Types VALID Oracle Workspace Manager VALID JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID Oracle Expression Filter VALID Oracle Data Mining VALID Oracle Text VALID Oracle XML Database VALID Oracle Rules Manager VALID COMP_NAME STATUS ---------------------------------------- ---------------------- Oracle interMedia VALID OLAP Analytic Workspace VALID Oracle OLAP API VALID OLAP Catalog VALID Spatial VALID Oracle Enterprise Manager VALID 17 rows selected. |
代码如下 | 复制代码 |
--如果迁移的是oracle9i的数据库,执行如下命令: SQL> STARTUP MIGRATE --如果是Oracle10g或11g,执行如下命令: SQL> STARTUP UPGRADE SQL> SPOOL EASY.log; SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql SQL> SPOOL OFF; |
代码如下 | 复制代码 |
SQL> conn / as sysdba ----> Remove OLAP Catalog SQL> @?/olap/admin/catnoamd.sql ----> Remove OLAP API SQL> @?/olap/admin/olapidrp.plb SQL> @?/olap/admin/catnoxoq.sql ----> Deinstall APS - OLAP AW component SQL> @?/olap/admin/catnoaps.sql SQL> @?/olap/admin/cwm2drop.sql ---只需要在11g中执行。在10g中,catnoamd.sql 脚本中已经包含了该脚本,所以不用执行 You could getsome invalid objects under SYS and PUBLIC owner, and they are the old duplicate OLAPSYS objects copied under these schemas when Olap has been installedpreviously. Generally, theOlap objects are named with context like %OLAP%, %AWM%, or other Olap word keys intheir "object_name" field, however, if it needs a help to recognizethem, then please contact Oracle Support and create a Service Request toget assistance for this question. Note that catnoadm.sql could fail from 10.1.0.5 to 11.1.0.7release. Due to the factthat it refers to three scripts which don't get shipped until 11.2 this scriptwill fail. Besides that itwill error on 7 non-existing synonyms to drop. Prior 11.2, execute these three dropsynonym statements: SQL> drop public synonym OlapFactView; SQL> drop public synonym OlapDimView; SQL> drop public synonym DBMS_ODM; |
代码如下 | 复制代码 |
SQL> @?/rdbms/admin/utlrp.sql |
代码如下 | 复制代码 |
SQL> @?/olap/admin/olap.sql SYSAUX TEMP; SQL> @?/rdbms/admin/utlrp.sql --这次会非常快 |
代码如下 | 复制代码 |
SQL> l 1* select count(*) from dba_objects where status<>'VALID' SQL> col comp_name for a40 SQL> select comp_name,status from dba_registry; COMP_NAME STATUS ---------------------------------------- ---------------------- Oracle Database Catalog Views VALID Oracle Database Packages and Types VALID Oracle Workspace Manager VALID JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID Oracle Expression Filter VALID Oracle Data Mining VALID Oracle Text VALID Oracle XML Database VALID Oracle Rules Manager VALID COMP_NAME STATUS ---------------------------------------- ---------------------- Oracle interMedia VALID OLAP Analytic Workspace VALID Oracle OLAP API VALID OLAP Catalog VALID Spatial VALID Oracle Enterprise Manager VALID |
代码如下 | 复制代码 |
begin update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$ where owner#=0 and type#=29 and short(+)=name and nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler'); commit; declare cursor C1 is select 'DROP JAVA DATA "'||u.name||'"."'||o.name||'"' from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#; ddl_statement varchar2(200); iterations number; previous_iterations number; loop_count number; my_err number; begin previous_iterations := 10000000; loop select count(*) into iterations from obj$ where type#=56; exit when iterations=0 or iterations >= previous_iterations; previous_iterations := iterations; loop_count := 0; open C1; loop begin fetch C1 into ddl_statement; exit when C1%NOTFOUND or loop_count > iterations; exception when others then my_err := sqlcode; if my_err = -1555 then exit; else raise; end if; end; initjvmaux.exec(ddl_statement); loop_count := loop_count + 1; end loop; close C1; end loop; end; commit; initjvmaux.drp('delete from java$policy$shared$table'); update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$ where owner#=0 and type#=29 and short(+)=name and nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler'); commit; end; / |
代码如下 | 复制代码 |
create or replace java system / |
代码如下 | 复制代码 |
It may be necessary to recompilethe Java objects with ncomp:i.e. % ncomp-user scott/tiger Hello.class |