How to check and change Oracle ArchiveLog Mode

Level: Beginner (DBA)

Question: How to check database ArchiveLog mode?

Answer:

To check the database archive log mode you need to
Step 1: Connect as SYSDBA

ex: connect sys/sysdbapass@server as sysdba

Step 2: Execute one of the following commands:
a) ARCHIVE LOG LIST

--In SQL*PLus
SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

b) Select from v$database

SQL> SELECT log_mode
  2  FROM sys.v$database
  3  /

LOG_MODE
------------
NOARCHIVELOG

Question: How to enable ArchiveLog mode?

You can specify the initial archiving mode in the CREATE DATABASE statement. Normally, there is no need to use this option as you can always change the mode later.

Answer:

Step 1: Connect as sysdba user

Step 2: Assuming that you are using spfile for parameter initialization, we need to configure the archive log destination. We will use a simple single destination option. All available options are described in the Oracle documentation.

SQL> ALTER SYSTEM SET log_archive_dest='F:\OraArchivedLogs' SCOPE=spfile;

System altered.

REM Note that this change will only work if db_recovery_file_dest is set to a blank, so you can execute another command just in case:

SQL> ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=spfile;

System altered.

Step 3: Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Backup the database (Cold backup)
Step 5: Start a new instance and mount the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.

Step 6: Change the database archiving mode

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

Step 7: Open the database.

SQL> ALTER DATABASE OPEN;

Database altered.

Step 8: Shut down the database (see Step 3 above)

Step 9: Backup the database (see Step 4 above)

Step 10: Open the database:

SQL> STARTUP
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Next log sequence to archive   36300
Current log sequence           36300

Question: How to disable ArchiveLog mode?

Answer:

Step 1: Connect as sysdba user
Step 2: Shutdown the database
Step 3: Backup the database (Cold backup)
Step 4: Start a new instance and mount the database.
Step 5: Change the archivelog mode:

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

Step 6: Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

List all weekend days for the current month

Problem: List all weekend days for the current month

Problem Level: Beginner/Intermediate

Solution:

We will break down the problem into 2 parts:
1) Get all days for the current month
2) Selecting only weekends

1) Get all days for the current month

Selecting all days from the current months requires one of two things:

• Knowing the first day of the month
• Knowing how to check for the end of the month or how to get the number of days in the month

The first task is fairly simple. We can use TRUNC function to get the first day of the month as follows:

SELECT TRUNC(SYSDATE, 'MONTH') first_day
FROM DUAL

End of the month can be found by adding one month and subtracting one day:

SELECT TRUNC(SYSDATE, 'MONTH') first_day,
       ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)-1 last_day
FROM dual

Now, if you know how to generate a numeric range, you can also generate the date range as follows:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(SYSDATE, 'MONTH')+LEVEL<=ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)

Alternatively, you can control the end of the month by checking if the “current level’s day” has the same month:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH') 

2) Selecting only weekends

In the following post I have altready demonstrated how to reliably get the first and last week days (i.e. weekend), so applying the method presenting in that post to all days in the current month should accomplish our goal:

Week_Start and Week_End custom functions in Oracle

WITH days AS (
SELECT TRUNC(SYSDATE, 'MONTH')+LEVEL-1 AS "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH')
)
SELECT "day", TO_CHAR("day", 'Day') "day of the week"
FROM days
WHERE "day" IN (TRUNC("day", 'DAY'), TRUNC("day", 'DAY')+6)

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

 ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
  1 KING               5000 FORD               3000
  2 SCOTT              3000 JONES              2975
  3 BLAKE              2850 CLARK              2450
  4 ALLEN              1600 TURNER             1500
  5 MILLER             1300 WARD               1250
  6 MARTIN             1250 ADAMS              1100
  7 JAMES               950 SMITH               800

Problem Level: Intermediate/Advanced

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
START WITH rn=1
CONNECT BY rn=1+PRIOR rn

Method/Workaround #2: Using Analytical Function (Level: Advanced)

WITH X AS (
SELECT ename left_name, sal left_sal, 
       LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name, 
       LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
       ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
                       right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
      FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
            FROM emp
            )
      )
PIVOT (MAX(ename) AS name,
       MAX(sal)   AS sal
       FOR (col_no) IN (1 AS left, 2 AS right)
       )
ORDER BY 1

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal, 
                      SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1

Method/Workaround #5: Using Self-Join (Level: Intermediate)

WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
                     b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn 
WHERE mod(a.rn,2)=1   

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

ORA-39087: directory name DATA_PUMP_DIR is invalid

Oracle 12c introduced a new concept of a pluggable database. It comes with many interesting features as well as some nuances that we need to know to perform such tasks as data pump export and import.

The main surprise comes with an undocumented fact that Oracle’s default Data Pump directory object, DATA_PUMP_DIR, cannot be used for data pump export and import.

When you try to attempt running expdp or impdp utilities using DIRECTORY=DATA_PUMP_DIR, you should get the following error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

The solution is quite straightforward: do not use DATA_PUMP_DIR directory object for pluggable db.

You can create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Interview Question: Get the 2nd highest salary in the company

Question: Get the 2nd highest salary in the company.

Question level: Intermediate

We picked just 6 workarounds for this fairly simple problem. Overall, there are at least 15 different approaches available.

Method/Workaround #1 (Level: Beginner)

SELECT MAX(SAL)
FROM (SELECT SAL
      FROM EMP
      WHERE SAL<(SELECT MAX(SAL) 
                 FROM EMP)
     )

Method/Workaround #2 (Level: Intermediate)

SELECT sal
FROM (SELECT sal, ROWNUM rn
      FROM (SELECT SAL
            FROM EMP
            GROUP BY SAL
            ORDER BY 1 DESC)
      WHERE ROWNUM<=2
      )
WHERE RN=2 

Method/Workaround #3 (Level: Intermediate)

SELECT DISTINCT sal
FROM (SELECT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2      

Method/Workaround #4 (Level: Intermediate)

SELECT SAL
FROM (SELECT DISTINCT SAL, DENSE_RANK()OVER(ORDER BY SAL DESC) RK
      FROM EMP)
WHERE RK=2     

Method/Workaround #5 (Level: Advanced)

SELECT A.SAL
FROM EMP A JOIN EMP B ON A.SAL<=b.SAL
GROUP BY a.sal
HAVING COUNT(DISTINCT b.sal)=2

Method/Workaround #6 (Level: Intermediate)

SELECT DISTINCT SAL
FROM emp a
WHERE 2=(SELECT COUNT(DISTINCT sal)
         FROM emp b
         WHERE b.sal>=a.sal) 

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Interview Question: How to Retrieve Unique Values without Using DISTINCT keyword

Question: List unique jobs (from emp table) without using DISTINCT keyword

Question Level: Beginner+

We picked 5 different methods (workarounds) to show in this post. It is possible to produce at least 15 if you read some other posts in this blog.

Method/Workaround #1: Use GROUP BY (Level: Beginner)

SELECT job
FROM emp
GROUP BY job

Method/Workaround #2: Use Analytical functions with a subquery (Level: Intermediate)

SELECT job
FROM(SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
     FROM emp) 
WHERE rn=1

Method/Workaround #3: Use correlated subquery (Level: Intermediate)

SELECT job
FROM emp a
WHERE empno=(SELECT MAX(empno) 
             FROM emp
             WHERE job=a.job)

Method/Workaround #4: Use left join with IS NULL filter (Level: Advanced)

SELECT a.job
FROM emp a LEFT JOIN emp b ON a.job=b.job AND a.empno<b.empno
WHERE b.empno IS NULL

Method/Workaround #5: Use NOT EXIST (Level: Intermediate)

SELECT job
FROM emp a
WHERE NOT EXISTS(SELECT 1
                 FROM emp b
                 WHERE a.job=b.job 
                   AND b.empno>a.empno)

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.