Initialization Parameter

How to Distinguish the Differences Between CHAR and BYTE – By Example

Posted on
Most of databases set NLS_LENGTH_SEMANTICS as BYTE, but there are some databases in Non-English country will take CHAR as the default unit of data type for CHAR and VARCHAR2.In other words, if there is no explicit unit in the columns definition during table creation, the unit of type CHAR and VARCHAR2 column will follow the default NLS_LENGTH_SEMANTICS.Let’s see an […]
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 NUMBER Argument Name […]
Administration

How to Check Current System Change Number (SCN)

Posted on
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 v$database;SYSTIMESTAMP             […]