Initialization Parameter

How to Resolve ORA-02097: Parameter Cannot be Modified Because Specified Value is Invalid

Posted on
When you ALTER SYSTEM to modify an initialization parameter, it sometimes fails with ORA-02097 ORA-02097: parameter cannot be modified because specified value is invalid Reproduce ORA-02097 Let’s me show you some examples that can reproduce ORA-02097 and I will explain it more detailed along with the context. Here I would like to change DB_RECOVERY_FILE_DEST location. […]
Archived Log File

How to Archive Redo Logs to Respective Local Destinations of RAC

Posted on
For a RAC database, we may want to archive the redo logs to local destinations of instances rather than ASM. That is, put instance #1, #2 archive thread #1, #2 redo logs to their respective local destinations. To achieve this goal, LOG_ARCHIVE_DEST_1 should be setup more granularly by sid like this way:SQL> alter system set log_archive_dest_1=’location=/oracle/oradata/arch1′ […]
Administration

How to Map Timestamp to SCN or Vice Versa

Posted on
If you are trying to do an incomplete recovery (also called Point-In-Time Recovery, PITR), you may want to know a System Change Number (SCN) using a TIMESTAMP as an input, or vice versa. Oracle provides two functions: timestamp_to_scn and scn_to_timestamp for DBA to map, let’s describe the two functions below: SQL> DESC timestamp_to_scn; FUNCTION timestamp_to_scn RETURNS […]
Administration

How to Check Current System Change Number (SCN)

Posted on
System Change Number Some DBAs will schedule a cron job to capture System Change Number (SCN) periodically to save as a Timestamp/SCN mapping reference. To achieve this goal, you can try the following statements. SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF TZH:TZM’; Session altered. SQL> COLUMN SYSTIMESTAMP FORMAT A35; SQL> SELECT systimestamp, current_scn FROM […]