Atikh's DBA blog
  • Home
  • Oracle
  • MySQL
  • MongoDB
  • PostgreSQL
  • Snowflake
  • About Me
  • Contact Us

Switching between ARCHIVELOG Mode and NOARCHIVELOG mode Oracle Database

 Atikh Shaikh     oracle     No comments   

In this post we are going to discuss about how to convert no ARCHIVELOG mode database to archive lo mode and vice versa.
techondba-oracle-archivelog-noarchivelog
archivelog-noarchivelog

Archive log mode verification using ARCHIVELOG list method, below we can database is in archive log mode with current and oldest log sequence.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch/techon_db
Oldest online log sequence     8485
Next log sequence to archive   8490

Current log sequence           8490

Below are steps to convert database to NOARCHIVELOG mode


--clean shutdown the database

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

--startup database in mount mode
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1811910416 bytes
Fixed Size                   732944 bytes
Variable Size            1056964608 bytes
Database Buffers          738197504 bytes
Redo Buffers               16015360 bytes

Database mounted.
SQL>

--alter database to NOARCHIVELOG
SQL> alter database noarchivelog;

Database altered.

--open the database
SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch/techon_db
Oldest online log sequence     8485
Current log sequence           8490

Now we will convert database to NOARCHIVELOG mode. Follow the same method as above. Use alter database ARCHIVELOG instead of alter database NOARCHIVELOG
We need to set below parameters before converting it to ARCHIVELOG. Make it through either pfile or spfile (using alter system command)


log_archive_dest='/u01/arch/techon_db'
log_archive_format='%t_%s_%r.arc'
log_archive_start=true

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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1811910416 bytes
Fixed Size                   732944 bytes
Variable Size            1056964608 bytes
Database Buffers          738197504 bytes
Redo Buffers               16015360 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch/techon_db
Oldest online log sequence     8485
Next log sequence to archive   8490
Current log sequence           8490

Verify if ARCHIVELOG is working fine by issue switch log.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch/techon_db
Oldest online log sequence     8488
Next log sequence to archive   8493
Current log sequence           8493


  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Author

Atikh Shaikh
View my complete profile

Categories

  • MongoDB (18)
  • Oracle 12c (30)
  • Oracle12cR2 New Feature (3)
  • PostgreSQL (20)
  • RMAN (10)
  • Snowflake (8)
  • mysql (23)
  • oracle (74)

Blog Archive

  • ▼  2018 (38)
    • ▼  November (25)
      • Introduction On MySQL
      • SQL Modes in MySQL
      • MySQL Version explained
      • MySQL Storage Engines (Part 1)
      • MySQL Storage Engines (Part 2)
      • Login to MySQL Database on Linux
      • Error Code: 1419. You do not have the SUPER privil...
      • Create MySQL database on Linux
      • Create New User in MySQL Database
      • MySQL Default database | MySQL System Database
      • Oracle ASM Basic Commands
      • Oracle Database Schema Refresh
      • Create User in Oracle Database Pre-12c and 12c
      • RMAN Disk backup and List or Report RMAN Backup
      • RMAN Backup of Single Datafile and List Backup
      • Difference Between User, Service account and Schem...
      • Create MySQL Database Backup | mysqldump mysqlbackup
      • Switching between ARCHIVELOG Mode and NOARCHIVELOG...
      • Introduction to MongoDB
      • Introduction to PostgreSQL
      • Installation of PostgreSQL 9.6 on Windows
      • Installation of MongoDB on Windows
      • Oracle DBA Scripts
      • Databases, Collections and Documents in MongoDB
      • CRUD (Create, Read, Update, Delete) Operations in ...
    • ►  December (13)
  • ►  2019 (33)
    • ►  January (15)
    • ►  February (6)
    • ►  March (2)
    • ►  April (5)
    • ►  May (5)
  • ►  2020 (5)
    • ►  April (1)
    • ►  May (2)
    • ►  July (2)
  • ►  2021 (8)
    • ►  June (3)
    • ►  July (3)
    • ►  August (1)
    • ►  December (1)
  • ►  2022 (33)
    • ►  May (3)
    • ►  June (10)
    • ►  July (3)
    • ►  August (4)
    • ►  September (8)
    • ►  October (3)
    • ►  November (2)
  • ►  2023 (14)
    • ►  February (1)
    • ►  April (5)
    • ►  May (2)
    • ►  June (1)
    • ►  September (1)
    • ►  October (1)
    • ►  December (3)
  • ►  2024 (5)
    • ►  January (2)
    • ►  March (3)
  • ►  2025 (6)
    • ►  March (1)
    • ►  April (3)
    • ►  May (2)

Popular Posts

  • ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]
    I was trying to export the schema in my windows PC, it got stuck with below error    C:\Users\shaik\Videos\technodba exp>expdp userid...
  • PostgreSQL : How to get data directory location for PostgreSQL instance
    Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know th...
  • Oracle 23ai : Use of NOVALIDATE Constraints in IMPDP
    While performing impdp operations in the Oracle database, Oracle performs validation checks for every constraint on the imported table, that...
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    In previous articles, we have learned about user creation and grants  in MySQL in detail, but there are a few privileges called global priv...
  • Oracle Dataguard Broker Configuration (DGMGRL)
    Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch...

Labels

oracle Oracle 12c mysql PostgreSQL MongoDB oracle 19c Oracle23c oracle19c Orale PDB-CDB oracle12c python AWS Oracle ASM Virtualbox pluggable database storage engine

Pages

  • Disclaimer
  • Privacy Policy

Follow TechnoDBA

Copyright © Atikh's DBA blog | Powered by Blogger