ORA-918 raised after migrating from 10g to 11g

After migration from Oracle 10g to 11g, we found an invalid object, which was valid in 10g database: SQL> alter procedure FXDM.SP_STAT_MAKER_P compile; Warning: Procedure altered with compilation errors. SQL> show errors Errors for PROCEDURE FXDM.SP_STAT_MAKER_P: LINE/COL ERROR ——– —————————————————————– 479/4 PL/SQL: SQL Statement ignored 481/118 PL/SQL: ORA-00918: column ambiguously defined According to Query Fails… Continue reading ORA-918 raised after migrating from 10g to 11g

Understanding SCN and checkpoint in Oracle

I have found that the following articles are extremely useful: SCN and Checkpoint (1431133.1) Automatic Checkpoint Tuning (270571.1) Checkpoint Tuning and Troubleshooting Guide (147468.1) Master Note: Overview of Database Checkpoints (1490838.1) http://oracledbascriptsfromajith.blogspot.jp/2009/05/understanding-scnsystem-change-number.html https://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/ The current SCN can be obtained by either of the following queries: select dbms_flashback.get_system_change_number scn from dual; select current_scn from v$database; It… Continue reading Understanding SCN and checkpoint in Oracle

Consistent export using expdp with flashback_scn

The exp utility used the CONSISTENT=Y parameter to indicate the export should be consistent to a point in time. By default the expdp utility exports are only consistent on a per table basis. If you want all tables in the export to be consistent to the same point in time, you need to use the… Continue reading Consistent export using expdp with flashback_scn

Manually migrate to ASM with minimal downtime

select * from v$version; select log_mode from v$database; select name from v$datafile UNION select name from v$tempfile; select name from v$controlfile; select member from v$logfile; RMAN> backup incremental level 0 as copy database tag ‘ASM_Migration’ format ‘+DATADG’; SQL> alter system set db_create_file_dest=’+DATADG’; SQL> alter system set db_recovery_file_dest_size=10G; SQL> alter system set db_recovery_file_dest=’+DATADG’; SQL> alter database… Continue reading Manually migrate to ASM with minimal downtime

Migrating to Oracle ASM with EM

Steps could be found at : https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_em.htm#OSTMG94276 In case you forgot to click the “View Status” in step 9, you can still access the following URL to retrieve job status: https://%5Byour_server_id%5D:5500/em/console/jobs/jobsTable What’s behind the scene The operation was divided into two phases: preparation and migration. In preparation, RMAN performs backup as copy and switches logfile:… Continue reading Migrating to Oracle ASM with EM

Understanding Character Set in Oracle

When we’re woking with multiple character sets (such as GBK and UTF-8), Chinese characters (or other languages) might be displayed in some odd codes. Therefore we need to pay attention to the underlying principles of character sets. In computing, a character encoding is used to represent a repertoire of characters by some kind of an… Continue reading Understanding Character Set in Oracle

Resolving OC4J Configuration issue

[oracle@node1 ~]$ emctl start console OC4J Configuration issue. /u01/app/11.2.0/grid/sysman/j2ee not found. We need to reconfigure db console: [oracle@node1 ~]$ emca -config dbcontrol db STARTED EMCA at Sep 1, 2015 5:10:09 AM EM Configuration Assistant, Version 11.2.0.3.0 Production Copyright (c) 2003, 2011, Oracle. All rights reserved. Enter the following information: Database SID: mydb    <– input… Continue reading Resolving OC4J Configuration issue