Resolving error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

  

Error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode: 

This error comes when database is not in archivelog mode, in order to resolve this lets see how it can be done :

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Sep 30 01:17:37 2022

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL:PDB1 (DBID=3054857248)

RMAN> backup database;

Starting backup at 30-SEP-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=467 device type=DISK

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 09/30/2022 01:17:42

RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 30 01:17:49 2022

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu immediate

Pluggable Database closed.

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ unset ORACLE_PDB_SID

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 30 01:18:04 2022

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1073740680 bytes

Fixed Size                  9694088 bytes

Variable Size             301989888 bytes

Database Buffers          759169024 bytes

Redo Buffers                2887680 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SQL> exit

Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0

[oracle@node2 ~]$ rman target/

Recovery Manager: Release 21.0.0.0.0 - Production on Fri Sep 30 01:20:14 2022

Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1644501746)

RMAN> backup database;

Starting backup at 30-SEP-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=456 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxqtjt_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL/pdb1/sysaux01.dbf

input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL/pdb1/system01.dbf

input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/pdb1/undotbs01.dbf

input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/pdb1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D6636086CE3A62E0538DC909C0CE7C/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxs7p8_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 30-SEP-22

channel ORA_DISK_1: finished piece 1 at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/E9D64E6E932B35C5E0538DC909C025DE/backupset/2022_09_30/o1_mf_nnndf_TAG20220930T012018_kmcxsprj_.bkp tag=TAG20220930T012018 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 30-SEP-22

Starting Control File and SPFILE Autobackup at 30-SEP-22

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2022_09_30/o1_mf_s_1116724881_kmcxssyx_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 30-SEP-22

>> Backup completed successfully now.

How REGEXP_LIKE Condition works in Oracle database

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

SQL> select * from scott.emp;

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       101 Scott                Tiger                      2000         10 30-SEP-22

       102 John                 Smith                      2000         10 30-SEP-22

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R. Here is how you will do it :

SQL>  select * from scott.emp where regexp_like(FIRST_NAME,'^R');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with R but 'a' in the middle and m at the end, note here the use of paranthesis and $ sign for middle and last alphabet. Here is how you will do it :

SQL>  select * from scott.emp where regexp_like(FIRST_NAME,'^R(a)m$');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

Lets say if we want find all employees whose first_name end with 'h' , note the use of $ symbol  Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'h$');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       104 Rakesh               Sharma                     4000         10 30-SEP-22

Lets say if we want find all employees whose first_name starts with 'Ra' but it can be Ram or Rakesh so in the middle with paranthesis and pipe we use (m|k) and note it can be anything in the end so here we did not give any last alphabet condition so both Ram and Rakesh are printed. Here is how you will do it :

SQL> select * from scott.emp where regexp_like(FIRST_NAME,'^Ra(m|k)');

     EMPNO FIRST_NAME           LAST_NAME                SALARY       COMM HIREDATE

---------- -------------------- -------------------- ---------- ---------- ---------

       103 Ram                  Hari                       2000         10 30-SEP-22

       104 Rakesh               Sharma                     4000         10 30-SEP-22

How Indentation works in Python

 Lets say we want to write a python program to check whether a variable has a value 10 or not.

Code : 

var = 10

if (var == 10):

print(' A is 10')

Error :

File "<ipython-input-53-61659dc01be1>", line 3

    print(' A is 10')
    ^
IndentationError: expected an indented block

>> As we can see above we get the above error. This means the print statement is not as per expected indentation. Lets see if put it in the right way like below :

var = 10
if (var == 10):
  print(' A is 10')

In above code i try to shift/move the print statement a bit towards right side with a couple of spaces it could also be possible with tab and it works fine now.

 A is 10
In [ ]:

It prints the expected statement now as above we can see the actual cell is displayed .

AWK Essentials for DBAs and System Admins

• The awk command is a very powerful Linux tool and programming language that allows users to process and manipulate data and produce formatted reports. The tool supports various operations for advanced text processing and facilitates expressing complex data selections. In this tutorial, you will learn what the awk command does and how to use it.
• 
  

Scenario :

We need to print unique values from the 1st column. 

[oracle@rhel7 awk]$ awk -F, '{a[$1];}END{for (i in a)print i;}' a.txt

101

102

103

 

We have a file named a.txt

[oracle@rhel7 awk]$ cat a.txt

101,John,Smith

102,Karan,Dodwal

103,Oracle,Guru

# Here we want to check contents of the file

So just like cat, this awk command awk '{print;}' works :

 

[oracle@rhel7 awk]$  awk '{print;}'  a.txt

101,John,Smith

102,Karan,Dodwal

103,Oracle,Guru

 

[oracle@rhel7 awk]$ awk '{print $1}' a.txt

101,John,Smith

102,Karan,Dodwal

103,Oracle,Guru

Print 1st column with comma (,) as separator :

[oracle@rhel7 awk]$ awk -F "," '{print $1}' a.txt

101

102

103

Now lets print 3rd column :

[oracle@rhel7 awk]$ awk -F "," '{print $3}' a.txt

Smith

Dodwal

Guru

Print the lines which matches the pattern 

[oracle@rhel7 awk]$ awk '/Smith|Karan/' a.txt

101,John,Smith

102,Karan,Dodwal

Sum of all values in column 1 

[oracle@rhel7 awk]$ awk -F"," '{x+=$1}END{print x}' a.txt

306

## Printing the first row of a specific column outout from ps -ef | grep output

## First lets grep regular smon process

[oracle@rhel7 awk]$ ps -ef | grep smon

oracle     3742      1  0 16:07 ?        00:00:00 ora_smon_west

oracle     4999   3818  0 17:05 pts/2    00:00:00 grep --color=auto smon

# Now lets grep smon then second column and later first row that gets fetched from the second pipe

[oracle@rhel7 awk]$ ps -ef | grep smon | awk '{print $2}' | awk 'NR==1 {print $1}'

3742

 

Sangam 21 at All India Oracle User Group (AIOUG)

 Sangam is a meeting minds, great Oracle minds meet at this conference, it is largest independent Oracle user group conference, this time like last year it is a virtual event. Join us for deep technical sessions, hands-on-labs, Fireside chats, keynotes, and of course open your mind and expand your network at Sangam21. Book your seats before the slots get full from https://www.aioug.org 

I am speaking at Sangam 21 , my session is on Automation with Ansible for Oracle DBAs , check here for more details https://www.aioug.org/sangam21#speakers

Oracle Data Pump Tracing

Ensure that the user that connects to the database with the Export Data Pump or Import Data Pump utility, is a privileged user (i.e. a user who has the DBA role or the EXP_FULL_DATABASE resp. IMP_FULL_DATABASE role), e.g.:

-- run this Data Pump job with TRACE as a privileged user:

% expdp <LOGIN_USER>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \ 

LOGFILE=<LOG_NAME>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> TRACE=480300 

-- or:

-- make the login user a privileged user:

CONNECT / AS SYSDBA

GRANT exp_full_database TO <LOGIN_USER>;

% expdp <LOGIN_USER>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \  

LOGFILE=<LOG_NAME>.log TABLES=<TABLE_NAME> TRACE=480300  

The majority of errors that occur during a Data Pump job, can be diagnosed by creating a trace file for the Master Control Process (MCP) and the Worker Process(es) only, e.g.:

-- To run a Data Pump job with standard tracing:

-- This results in two trace files in BACKGROUND_DUMP_DEST: 

--    Master Process trace file: <SID>_dm<number>_<process_id>.trc  

--    Worker Process trace file: <SID>_dw<number>_<process_id>.trc  

% expdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \ 

LOGFILE=<LOG_NAME>.log SCHEMAS=<SCHEMA_NAME> TRACE=480300

Each Data Pump component can be specified explicitly in order to obtain tracing details of that component:

-- Summary of Data Pump trace levels:

-- ==================================

  Trace   DM   DW  ORA  Lines

  level  trc  trc  trc     in

  (hex) file file file  trace                                         Purpose

------- ---- ---- ---- ------ -----------------------------------------------

  10300    x    x    x  SHDW: To trace the Shadow process (API) (expdp/impdp)

  20300    x    x    x  KUPV: To trace Fixed table

  40300    x    x    x  'div' To trace Process services

  80300    x            KUPM: To trace Master Control Process (MCP)      (DM)

 100300    x    x       KUPF: To trace File Manager

 200300    x    x    x  KUPC: To trace Queue services

 400300         x       KUPW: To trace Worker process(es)                (DW)

 800300         x       KUPD: To trace Data Package

1000300         x       META: To trace Metadata Package

------- 'Bit AND'

1FF0300    x    x    x  'all' To trace all components          (full tracing)

There is very useful note Note:351598.1 "Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges)"

Combinations of tracing Data Pump components are possible, e.g.:

-- Example of combination (last 4 digits are usually 0300): 

 40300 to trace Process services

 80300 to trace Master Control Process (MCP)

400300 to trace Worker process(es)

------- 'Bit AND'

4C0300 to trace Process services and Master Control and Worker processes

In order to trace all Data Pump components, level 1FF0300 can be specified:

-- Run a Data Pump job with full tracing:

-- This results in two trace files in BACKGROUND_DUMP_DEST:   

--    Master Process trace file: <SID>_dm<number>_<process_id>.trc    

--    Worker Process trace file: <SID>_dw<number>_<process_id>.trc    

-- And one trace file in USER_DUMP_DEST:   

--    Shadow Process trace file: <SID>_ora_<process_id>.trc    

% impdp <LOGIN>/<PASSWORD> DIRECTORY=<DIRECTORY_NAME> DUMPFILE=<DUMP_NAME>.dmp \   

LOGFILE=<LOG_NAME>.log FULL=y TRACE=1ff0300 

Also ensure that the init.ora/spfile initialization parameter MAX_DUMP_FILE_SIZE is large enough to capture all the trace information (or set it to unlimited which is the default value), e.g.:

-- Ensure enough trace data can be written to the trace files:

CONNECT / as sysdba

SHOW PARAMETER max_dump

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size                   string      10M

ALTER SYSTEM SET max_dump_file_size = unlimited SCOPE = both; 

SHOW PARAMETER max_dump 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_dump_file_size                   string      UNLIMITED

Special thanks to Mike Dietrich for sharing this doc id in his session : 

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (Doc ID 286496.1)

Exadata Information Centre

Recently i was asked a question on the important note on the Exadata Information Center and be updated with the latest updates and patches. Here are the details :  

Information Center: Oracle Exadata Database Machine (Doc ID 1306791.2) Exadata 20.1.10.0.0 release and patch (32853086) (Doc ID 2767282.1)