ORACLE DATABASE PROBLEM AND SOLUTIONS

Saturday, January 14, 2023

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Cause : database was crashed, could be database shut abort or database was not clean shutdown.

check alert log as well to get more details .

workaround :

[oracle@10.1.1.0 ~]$

[oracle@10.1.1.0 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 10:04:10 2023

Version 19.3.0.0.0

Connected to an idle instance.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size 8897072 bytes

Variable Size 385875968 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7876608 bytes

Database mounted.

SQL>

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

SQL>

SQL> alter database archivelog;

alter database archivelog

ERROR at line 1:

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> alter database open;

Database altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@10.1.1.0 ~]$

[oracle@10.1.1.0 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 14 10:23:27 2023

Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610609200 bytes

Fixed Size 8897072 bytes

Variable Size 385875968 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7876608 bytes

Database mounted.

SQL>

SQL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL>

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

SQL>

ANURAG PANDEY

I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert . Database Administration and Implementation. Provide technical guidance and work with global team. Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible

Friday, January 13, 2023

Listener.ora & Tnsnames.ora file for Oracle Database

listener.ora file (with static listener)

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

cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0/dbhome1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.19.48)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ddb)

(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1)

(SID_NAME = ddb)

)

tnsnames.ora file

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

cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0/dbhome1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DDB=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.19.48 )(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ddb)

)

ANURAG PANDEY

How To Create Database Using DBCA in Silent Mode on Linux – 19C

Prerequisite :

Oracle 19c software already install
Oracle Environment variable should be set
create database using dbca
create or update listener.ora ,tnsnames.ora
verify database

Command for database create:

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

Login as oracle user
execute on terminal

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ddb -sid ddb -sysPassword Rdx@1234567890 -systemPassword Rdx@1234567890 -createAsContainerDatabase false -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS -characterSet AL32UTF8 -totalMemory 2048 -recoveryAreaDestination /u01/app/oracle/fast_recovery_area -sampleSchema true

[oracle@10.1.1.0~]$

[oracle@10.1.1.0~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName ddb -sid ddb -sysPassword Rdx@1234567890 -systemPassword Rdx@1234567890 -createAsContainerDatabase false -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS -characterSet AL32UTF8 -totalMemory 2048 -recoveryAreaDestination /u01/app/oracle/fast_recovery_area -sampleSchema true

Prepare for db operation

10% complete

Copying database files

40% complete

Creating and starting Oracle instance

42% complete

46% complete

50% complete

54% complete

58% complete

60% complete

Completing Database Creation

66% complete

69% complete

70% complete

Executing Post Configuration Actions

100% complete

Database creation complete. For details check the logfiles at:

/u01/app/oracle/cfgtoollogs/dbca/ddb.

Database Information:

Global Database Name:ddb

System Identifier(SID):ddb

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ddb/ddb.log" for further details.

[oracle@10.1.1.0~]$

[oracle@10.1.1.0~]$ cat /etc/oratab

# This file is used by ORACLE utilities. It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator. A new line terminates

# the entry. Lines beginning with a pound sign, '#', are comments.

# Entries are of the form:

# $ORACLE_SID:$ORACLE_HOME:<N|Y>:

# The first and second fields are the system identifier and home

# directory of the database respectively. The third field indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

# Multiple entries with the same $ORACLE_SID are not allowed.

ddb:/u01/app/oracle/product/19.0/dbhome1:N

[oracle@10.1.1.0~]$

[oracle@10.1.1.0~]$ . oraenv

ORACLE_SID = [india] ? ddb

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@10.1.1.0~]$

[oracle@10.1.1.0~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 13 14:19:03 2023

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select name ,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

DDB READ WRITE PRIMARY

SQL>

ANURAG PANDEY

Tuesday, December 27, 2022

setup password less connectivity between two Linux server using ssh-keygen

setup password less connectivity between two Linux server

-bash-4.2$

-bash-4.2$ ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):

Created directory '/var/lib/pgsql/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.

Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:XHvRME2FNaXO6wAHk1wjPpjdzHwAJX6RY7yrvSfm7/g postgres@ip-172-31-86-192.ec2.internal

The key's randomart image is:

+---[RSA 2048]----+

| ++O+.==|

| B X=Bo..|

| o @.Bo+ |

| . . *.= |

| S o o.o |

| +. . |

| o. . |

| . ++. |

| o+BE |

+----[SHA256]-----+

-bash-4.2$

-bash-4.2$ ssh-copy-id postgres@172.31.85.11

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

postgres@172.31.85.11's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'postgres@172.31.85.11'"

and check to make sure that only the key(s) you wanted were added.

-bash-4.2$

try to test the login :

-bash-4.2$ ssh 172.31.85.11

Last login: Tue Dec 27 10:41:27 2022

__| __|_ )

_| ( / Amazon Linux 2 AMI

___|\___|___|

https://aws.amazon.com/amazon-linux-2/

-bash-4.2$

ANURAG PANDEY