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

Quick Reference for Oracle Apps DBA

Hiya Buddies,

Hope you all are rocking either tech or Nontech skilles… If not please visits here we would like to share the skills which may useful either in tech or nontech lifes…

Here we would like to share the very important technical stuff which is useful and helpful in daily tasks.

These will be useful for Quick accees and use it. To make some query and tips handy we thought to share here…

We will keep update whenever we feel that this is important and useful for others here and also it should be handy then would like to share and update here…

How to take CM list details front-end

1. Go to front-end --> Administrator Concurrent manager --> File (at top menu) --> Export.

How to check Archive log Gap in Standby Database:

SQL> select * from v$archive_gap;

Where the log Apply stuck:

SQL> select sequence#,process,status from v$managed_standby;

How to take sql output file in xml format:

set feed off markup html on spool on

SET PAGESIZE 50000

SPOOL file_name.xls

select * from fnd_profile_options ;

SPOOL OFF

ASM alert log file Location:

oracle@ora-data:/sku/app/oracle/diag/asm/+asm/+ASM1/trace$ pwd

/sku/app/oracle/diag/asm/+asm/+ASM1/trace

oracle@ora-data:/sku/app/oracle/diag/asm/+asm/+ASM1/trace$ tail -100f alert_+ASM1.log

How to check 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;

To be continued...

Some more useful links:

How to Study and Prepare for Exams

How to Enable or Disable Archive log mode in oracledatabase 11g

How to export query output to Excel in Oracle SQLDeveloper

How to check notepad++ backup files

Regards,

TechOrNontech Team

Quick Reference for Oracle Database

Hiya Buddies,

We would like to share here a Quick Reference for Oracle Database related which may useful either in Project or Interviews...

Whenever we feel that topic or command is useful for future which may need handy, we would like to update here, so we can use anytime easily...

Please if you have also any such stuff, let us know either in comment box or contact us form. So, we can mentioned here and help everyone.

Oracle Database Startup Phases...

Nomount -- oracle opens and read init.ora file, start the background process.

Mount -- open and read control file for data files locations etc...

Open --  it accesses all of the data files associated with the database

Opening the Database in Restricted Mode

It will only allow users with special privileges to access the database like System administrator or DBAs, even though the database is technically open.

SQL> startup restrict

===========

Database start in RAC:

srvctl start database -d DB_NAME

##### To start instance in mount state #####

srvctl start instance -d <DB_NAME> -i <INSTANCE_NAME> -o mount

srvctl start instance -d <DB_NAME> -i <INSTANCE_NAME>

###### To mount database #####

srvctl start database -d <INSTANCE_NAME> -o mount

Command to Check Status:

srvctl status database -d DB_NAME

Command to Stop database Instance:

srvctl stop database -d <DB_NAME> -o immediate

===========

How to check datafiles Path or locations:

SQL> SELECT NAME FROM V$DATAFILE;

NAME

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

/home/oracle/app/oracle/oradata/orcl/system01.dbf

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

/home/oracle/app/oracle/oradata/orcl/users01.dbf

/home/oracle/app/oracle/oradata/orcl/example01.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

/home/oracle/app/oracle/oradata/orcl/example02.dbf

7 rows selected.

SQL>

SQL> select file_name from dba_data_files;

FILE_NAME

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

/home/oracle/app/oracle/oradata/orcl/users01.dbf

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

/home/oracle/app/oracle/oradata/orcl/system01.dbf

/home/oracle/app/oracle/oradata/orcl/example01.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_19306134

55248703.dbf

/home/oracle/app/oracle/oradata/orcl/example02.dbf

7 rows selected.

SQL>

Some more useful links:

How to Study and Prepare for Exams

How to Enable or Disable Archive log mode in oracledatabase 11g

How to export query output to Excel in Oracle SQLDeveloper

How to check notepad++ backup files

Regards,

TechOrNontech Team

How to Check the Archive Log Gap Between Primary and Standby Database

Hiya Buddies!!!

We would like share here "how to check the archive log gap between the primary and standby database" and also how to take action.

Please find the below steps:

$cat archive_gap.sql

set lines 180

col host_name for a25

prompt SQL>1 output

select thread#,max(sequence#) from gv$archived_log group by thread#;

prompt SQL>2 output

select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;

prompt SQL>3 output

select name,instance_name,host_name,status,to_char(startup_time , 'dd-mon-rr hh24:mi:ss') Start_Time from v$database,gv$instance;

prompt SQL>4 output <Main>

SELECT PROCESS, STATUS, SEQUENCE# FROM gv$MANAGED_STANDBY;

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM gv$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM gv$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM gv$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

SQL> @archive_gap.sql

OUTPUT:

SQL>1 output

   THREAD# MAX(SEQUENCE#)

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

         2         410266

         1         579475

   THREAD# MAX(SEQUENCE#)

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

         2         410257

         1         579462

SQL>2 output

NAME      INSTANCE_NAME    HOST_NAME                 STATUS       START_TIME

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

PRD1  PRD21        ora-data                  OPEN         03-jul-18 04:04:03

PRD1  PRD22        ora-data2                  MOUNTED      03-jul-18 04:04:00

SQL>3 output

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CLOSING          579449

ARCH      CONNECTED             0

RFS       RECEIVING        579476

RFS       RECEIVING        579477

RFS       IDLE                  0

RFS       IDLE             410267

ARCH      CLOSING          410158

ARCH      CLOSING          410159

ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

16 rows selected.

SQL>4 output

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                 579475                579462         13

         1                 579475                579462         13

         1                 579475                579462         13

         1                 579475                579462         13

         2                 410266                410258          8

         2                 410266                410258          8

         2                 410266                410258          8

         2                 410266                410258          8

8 rows selected.

SQL>

Now, As we see the above result the differences / gaps are 13 and 8 as respective nodes...

So, here we have to put database in MRP mode as shown below command.

So, archive sync will start...

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL>

SQL> @archive_gap.sql

Output:

   THREAD# MAX(SEQUENCE#)

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

         1         579507

         2         410282

   THREAD# MAX(SEQUENCE#)

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

         1         579507

         2         410281

NAME      INSTANCE_NAME    HOST_NAME                 STATUS       START_TIME

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

PRD1  PRD21        ora-data                  MOUNTED      04-jul-18 06:01:02

PRD1  PRD22        ora-data2                  MOUNTED      04-jul-18 06:00:57

SQL> SELECT PROCESS, STATUS, SEQUENCE# FROM gv$MANAGED_STANDBY

PROCESS   STATUS        SEQUENCE#

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

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

RFS       IDLE             410283

RFS       IDLE                  0

MRP0      WAIT_FOR_LOG     579508

ARCH      CLOSING          410270

ARCH      CONNECTED             0

ARCH      CONNECTED             0

ARCH      CONNECTED             0

PROCESS   STATUS        SEQUENCE#

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

RFS       IDLE                  0

RFS       IDLE                  0

RFS       IDLE                  0

RFS       RECEIVING             0

RFS       IDLE                  0

RFS       RECEIVING        579508

17 rows selected.

    Thread Last Sequence Received Last Sequence Applied Difference

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

         1                 579507                579507          0

         1                 579507                579507          0

         1                 579507                579507          0

         1                 579507                579507          0

         2                 410282                410282          0

         2                 410282                410282          0

         2                 410282                410282          0

         2                 410282                410282          0

8 rows selected.

SQL>

Hope this may useful and helpful here.

Thanks...