Delayed block cleanout and ORA-01555

They say delayed block cleanout could cause ORA-01555 and in this post, I did an experiment to try to explain why. –We first alter database undo management as MANUAL and create tablespace and rollback segment drop tablespace rbs_ts including contents and datafiles; create tablespace rbs_ts datafile size 5M autoextend off extent management local segment space… Continue reading Delayed block cleanout and ORA-01555

Understanding transaction, redo and undo record

Stephen Hawking: The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. I once firmly believed that I’m familiar with the concept of transactions in Oracle such as the ACID properties, rollback, commit, etc. However, it suddenly began to dawn on me that I knew nothing about how Oracle actually do those… Continue reading Understanding transaction, redo and undo record

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