BYTE vs CHAR in NLS_LENGTH_SEMANTICS Most of databases set NLS_LENGTH_SEMANTICS as BYTE by default, but there are some databases in Non-English country will take CHAR as the default unit of data type for CHAR and VARCHAR2.
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. […]
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′ […]
A misconfigured database is a database which was set with wrong values of initialization parameters in SPFILE, it will prevent the database from startup. Let’s see a typical example that can reproduce the problem: Reproducing ORA-00837 We are trying to change MEMORY_TARGET. First of all, we should check the original value. SQL> show parameter memory […]
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 […]
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 […]