How to Enable or Disable Archive log mode in oracle database 11g

Hiya Buddies!!!

We would like share a very simple concept but it is very important i.e. How to enable or disable Archive log mode.

Below are the steps which are described step by step which we have done on test server.

ARCHIVELOG  MODE

It will backup of all transactions. So, helpful in recovery to any point in time.

We can perform Hot/online backup.

NO-ARCHIVELOG MODE

We can't recover to any point in time.

We can't take hot/online database backup.

1. Database is in open mode.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

ORCL      READ WRITE

SQL>

2. Bring down the database.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

3. Start database in Mount stage.

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             356518584 bytes

Database Buffers           92274688 bytes

Redo Buffers                6008832 bytes

Database mounted.

4. Check for database is in Archive mode or not. As below we can Archive mode is disabled.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     559

Current log sequence           561

SQL>

5. Enable the Archive log from below command.

SQL> alter database archivelog;

Database altered.

SQL>

6. Now cross verify the Archive log mode status:

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     559

Next log sequence to archive   561

Current log sequence           561

SQL>

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     560

Next log sequence to archive   562

Current log sequence           562

SQL>

Disable the Archive Log:

We can see below here, what if the database is Opened and try to change Archive log mode:

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any

instance

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  456146944 bytes

Fixed Size                  1344840 bytes

Variable Size             360712888 bytes

Database Buffers           88080384 bytes

Redo Buffers                6008832 bytes

Database mounted.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     560

Next log sequence to archive   562

Current log sequence           562

SQL> alter database noarchivelog;

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     560

Current log sequence           562

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System 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     561

Current log sequence           563

SQL>

Some more useful links:

OID , OAM and ASM log files location and details

How To Start and Stop Informatica Services in Linux/Unix

How to run SQLT for a sqlid in Oracle

Delphix error: DBREFRESH failed due to an errorduring execution: Failed to create mount

Regards,

How to check database version and size in oracle database

Hiya Buddies, Topic: How to check version of the database

Hope you all are doing well…

As we know that we all should know that How to check the database version and on which database version we are working which is very important.

Even it will be asked in our interviews also that on which database you working and what are their features, how much is the database size.

So, below is the command and details, the way we can check the database version and database details with the numeric forms.

But we are not sure the meaning of the version details as like what is the meaning of the 11.2.0.4.0 which would like to share here.

So, we would like to share the knowledge which is just for knowledge purpose which may helpful for you all here…

Below are the commands which helps or used for checking the Database version.

How to check version of the database:

SQL> SELECT * FROM V$VERSION;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Solaris: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> SELECT version FROM V$INSTANCE;

VERSION

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

11.2.0.4.0

SQL>

How to check the Database Size:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

Here are the meanings which may help more to understand which very few people clear about it like as:

11- Major database release number,

2 - Database maintenance release number,

0 - Application server release number,

4 - Component specific release number,

0 - Platform specific release number.

Some more useful links:

How to Unlock EBS apps user in R12

RMAN Issues And Solutions Troubleshooting

Database Prechecks Script before clone

Delphix error: DBREFRESH failed due to an error during execution: Failed to create mount

Regards,

TechOrNontech Team