ORA-27125: unable to create shared memory segment Linux-x86_64 Error: 28: No space left on device

 

When trying to start up an Oracle database, the below errors occur:

ORA-27125: unable to create shared memory segment
Linux-x86_64 Error: 28: No space left on device
Additional information: 3773
Additional information: 3221225472

CAUSE

Kernel parameter kernel.shmall is not set properly.

RESOLUTION

Make sure kernel.shmall is given the appropriate value.

1)  Calculate the value of shmall.

getconf PAGE_SIZE

4096
Shmall=total size of the SGAs /PAGE_SIZE.
If the total SGA size is 480GB, then it would be 1024 * 1024 * 1024 * 32 / 4096 = 8388608  

2) Edit /etc/sysctl.conf
kernel.shmall = 8388608 

3) Apply the change
# sysctl -p  

4) Check shmall value after change.
# sysctl -A | grep shmall

5) Start up database successfully.

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Simple Performance tuning for freshers – Easy steps

 

1. To list long-running forms user sessions

select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;

2. To list inactive Sessions respective username

SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;

3. To find session-id with a set of SPIDs

select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);

4. To find SQL Text given SQLHASH & SQLADDR

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;

5.Checking for Active Transactions SID

select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

6. Session details from Session longops

select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

7. Session details with SPID

select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/

8. To find Undo Generated For a given session

select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;

9. To list the count of connections from other machines

select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

10. To get the total count of sessions and processes

select count(*) from v$session;
select count(*) from v$process;
select (select count() from v$session) sessions, (select count() from v$process) processes from dual;

11. To find SQL text through SQL address

select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;

12. To find SQL text for different SQL hash value

select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

13. Session details associated with SID and Event waiting for

set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

14. Active Sessions running for more than 1 hour

set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”,
ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

15.SQLs Running from longtime

alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from v$session_longops;

16. Last/Latest Running SQL

set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc;

17. Current Running SQLs

set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id;

18. Current Running SQLs

set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select  sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;

Please find out all of our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

REDO LOG FILE

Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

SQL> select * from v$log;

SQL> select group#,status,sequence# from v$log;

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 CURRENT                  91
         2 INACTIVE                 89
         3 INACTIVE                 90

SQL> select * from v$logfile;

Active (Current) and Inactive Redo Log Files

Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.

Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.

If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Add New redo log file

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/TESTDB/redo04.dbf' 
size 100m;

Database altered.

Add Member to an existing group

SQL> alter database add logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log' 
to group 4;

Database altered.

Drop member

SQL> alter database drop logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log';

Database altered.

Drop Logfile Group

SQL> alter database drop logfile group 4;

Database altered.


SQL> select group#,status from v$log;

    GROUP#                STATUS
----------            ----------------
         1               CURRENT
         2               INACTIVE
         3               INACTIVE

Adding Two  member

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log',
'/u01/app/oracle/oradata/TESTDB/redo04C.log') size 100m;

Database altered.

SQL> select group#,member from v$logfile;

    GROUP#         MEMBER
----------       ---------------------------                       
         4    /u01/app/oracle/oradata/TESTDB/redo04a.log

         4     /u01/app/oracle/oradata/TESTDB/redo04C.log

Log Switches and Log Sequence Numbers

A log switch is a point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

Each online or archived redo log file is uniquely identified by its log sequence number. During a crash, instance, or media recovery, the database properly applies to redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle 12c to 19c upgrade using AutoUpgrade Method

In this blog, we will see how to upgrade Oracle Database 12c to 19c using AutoUpgrade Tool

What is the AutoUpgrade?

  • The Oracle Database AutoUpgrade utility is a small command-line tool that allows you to upgrade your databases very easily with very little interaction
  • The new AutoUpgrade utility in Oracle 19¢ performs almost 99% of the task by itself, we just have to provide inputs during the initial phase
  • So it performs Prechecks against multiple databases, upgrades multiple databases in one go
  • Also, it does Post upgrade, object recompilation, and time zone up-gradation
  • The only thing you need to provide is a contig file in text format

Which database releases are supported?

  • As a source, the minimum version is Oracle Database 11.2.0.4. onwards

Download the latest auto-upgrade jar file

  • Auto upgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory of Oracle 19c Home
  • Oracle strongly recommends downloading the latest AutoUpgrade version before doing the upgrade
  • You download the most recent version from MOS Note: 2485487.1  AutoUpgrade Tool
  • Once you download this jar file transfer it to the Server and create a new directory and place this file in that directory

cp -r autoupgrade.jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/

Java version:

  • Java version should be 8 or later, which is available by default in Oracle Database homes from release 12.1.0.2 and latest

Create the config file

  • Create New Directory Which will contain your config file and logs
  • mkdir /home/oracle/auto_upgrade_19c

Run Below command to create sample config file which we will use to make final config file.

  • cd /home/oracle/auto_upgrade_19c
  • $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file_config
  • cp sample_config.cfg prod_db_config.cfg
  • Make necessary changes

Modify the config file according to your env

prddb_config.cfg file should have following entry which specifies source and target database home location and DB name and log locations and other information’s:

global.autoupg_log_dir=/home/oracle/auto_upgrade_19c/upg_logs
upg1.dbname=PRDDB
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.2.0.1/db_1
upg1.target_home=/u01/app/oracle/product/19.0.0/db_1
upg1.sid=PRDDB
upg1.log_dir=/home/oracle/auto_upgrade_19c/upg_logs/PRDDB
upg1.upgrade_node=orclagent.localdomain
upg1.target_version=19.12
upg1.run_utlrp=yes
upg1.timezone_upg=yes

Analyze the database using modified config file

Auto upgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.

Execute AutoUpgrade in analyze mode with the below syntax:

export ORACLE HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$PATH:$ORACLE_HOME/jdk/bin

cd /home/oracle/auto_upgrade_19c

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config prddb_config.cfg -mode ANALYZE

We can monitor, manage and control the jobs from the autoupgrade console.

  • lsj – to list the jobs
  • status – to show the job status
  • tasks – shows the tasks executing

Crosscheck and verify all logs before proceeding further

Deploy the upgrade and monitor

  • Auto upgrade Deploy mode performs the actual upgrade of the database from pre-upgrade source database analysis to post-upgrade checks.
  • Before starting Deploy make sure you have taken a backup of your database.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config
prod_db_config.cfg -mode DEPLOY

  • Once the upgrade process is started consider monitoring the logs and database alert logs to see the progress of the upgrade. Auto upgrade logs are available under

/home/oracle/auto_upgrade_19c/upg_logs/

Once the upgrade finishes crosscheck the below.

SELECT VERSION FROM V$TIMEZONE_FILE;
select name, open_mode, version, status from v$database, v$instance;

Post-upgrade task

  • Once the upgrade is successful and all testing is done, drop the restore point.
  • Drop the Guaranteed restore point

select name from v$restore_point;
drop restore point restorepoint_name;

  • Change the compatible parameter
  • After the upgrade, the database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.

show parameter compatible;
alter system set compatible=’19.0.0′ scope=spfile;
shutdown immediate:
startup;
show parameter compatible;

Hope this blog was useful…

Please find out all of our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

 

How to find the bind variable values of the SQL statement using SQLID?

How to find the bind variable values of the SQL statement using SQLID?

SELECT sql_id,  b.name BIND_NAME,  b.value_string BIND_STRING   from gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL
AND sql_id ='&SQLID';

Additional script which may be helpful at a later point in time:

To know the last executed query in the oracle and its hash value,sqlid

SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address =
  (SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;

To know the SQL plan history of a sql using the sqlid:

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

ARCHIVELOG MODE

How to Enable archive log mode

Once you have configured the flash recovery area, you can put the database in ARCHIVELOG mode. Unfortunately, this requires that the database be shutdown first with the shutdown command (however, from earlier in the chapter, we note that shutdown immediate is the best option). Once you have shutdown the database, you will start the database in mount Stage with the startup mount command. Then put the database in ARCHIVELOG mode, and finally open the database. Here is an example of how this all works from the command line.

SQL> archive log list;


Database log mode               No Archive Mode
Automatic archival              Disabled
Archive destination             d:oracleoradataDB10Garchive
Oldest online log sequence      427
Current log sequence            429

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

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> 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                d:oracleoradataDB10Garchive
Oldest online log sequence         426
Next log sequence to archive       427
Current log sequence               428

How to Disable archive log mode 

SQL> archive log list;

Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428


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

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> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.


SQL> archive log list;

Database log mode                NoArchive Mode
Automatic archival               Enabled
Archive destination              d:oracleoradataDB10Garchive
Oldest online log sequence       426
Next log sequence to archive     427
Current log sequence             428

Change Archive log mode and Destination in oracle 19c

[oracle@training ~]$ export ORACLE_SID=training1

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:30:16 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     89
Current log sequence           91


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0

[oracle@training ~]$ mkdir -p /u01/ARC_BKP

[oracle@training ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:38:09 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

To check the current archiving location

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                       TYPE            VALUE
---------------------  ---------------   --------------
db_recovery_file_dest       string      /u01/app/oracle /fast_recovery_area
                                                 
db_recovery_file_dest_size  big integer  8256M

SQL> alter system set DB_RECOVERY_FILE_DEST='/u01/ARC_BKP';

System altered.

SQL> alter system set log_archive_dest=’/u01/ARC_BKP’;

System altered.

SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.


SQL> shut immediate
ORA-01109: database not open
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@training u01]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 28 23:52:05 2021
Version 19.3.0.0.0

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

Connected to an idle instance.


SQL> startup
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> archive log list;
Database log mode                Archive Mode
Automatic archival               Enabled
Archive destination              /u01/ARC_BKP
Oldest online log sequence       89
Next log sequence to archive     91
Current log sequence             91

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

 

Control file Multiplexing

Static and Dynamic

MULTIPLEXING USING SPFILE

SQL> select name from v$controlfile;

NAME
------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl

SQL> alter system set control_files=
'/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl',
'/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl',
'/u01/app/oracle/oradata/TRAINING/control01.dbf' scope=spfile;

System altered.

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


SQL> !cp /u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl 
/u01/app/oracle/oradata/TRAINING/control01.dbf


SQL> startup

ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes

Database mounted.

Database opened.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl
/u01/app/oracle/oradata/TRAINING/control01.dbf


SQL> create pfile from spfile;

File created.

MULTIPLEXING USING PFILE

Multiplexing is the process of mintaining a copy of same control files on different disk drivers (and idealy on different controllers). To multiplex your control files, we copy the control file to multiple locations and change the CONTROL_FILES parameter in the text based initialization file init.ora to include all control files names.

SQL> shutdown 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@training ~]$ cd $ORACLE_HOME/dbs


[oracle@training dbs]$ vi inittraining.ora


*.control_files='/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_.ctl',
'/u01/app/oracle/oradata/TRAINING/controlfile/o1_mf_j9bdm6dk_1.ctl',
'/u01/app/oracle/oradata/TRAINING/control01.dbf'

[oracle@training dbs]$ cp /u01/app/oracle/oradata/TRAINING/control01.dbf 
/u01/app/oracle/oradata/TRAINING/control02.dbf

[oracle@training dbs]$ sqlplus / as sysdba



SQL> startup pfile=$ORACLE_HOME/dbs/inittraining1.ora

ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TRAINING1/controlfile/o1_mf_j9bdm6dk_.ctl
/u01/app/oracle/oradata/TRAINING1/controlfile/o1_mf_j9bdm6dk_1.ctl
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf


SQL> create spfile from pfile;

File created.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

NID Utility in Oracle

DBNEWID is a database utility, in $ORACLE_HOME/bin directory, that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Prior to the introduction of the DBNEWID utility, we used to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, alteration of the internal database identifier (DBID) of an instance was impossible.

The DBID is an internal, unique identifier for a database. Because Recovery Manager(RMAN)  distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem.

NID utility allows us to change

  • Only DBID of a database
  • Only DBNAME of a database
  • Both DBNAME and DBID of a database

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1. Consequently, you should make a backup of the whole database immediately after changing the DBID.

Changing DBNAME & DBID

SQL> select dbid, name from v$database;

   DBID          NAME
----------    ---------
 247698686    TRAINING

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>  STARTUP MOUNT
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production
Version 19.3.0.0.0

[oracle@training ~]$ nid TARGET=sqlplus / as sysdba DBNAME=testdb LOGFILE=testdb.log
Password:

SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'TESTDB' in control file is not 'TRAINING'


[oracle@training ~]$ cd $ORACLE_HOME/dbs
[oracle@training ~]$ vi inittraining1.ora

training1.__data_transfer_cache_size=0
training1.__db_cache_size=1728053248
training1.__inmemory_ext_roarea=0
training1.__inmemory_ext_rwarea=0
training1.__java_pool_size=0
training1.__large_pool_size=16777216
training1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
training1.__pga_aggregate_target=822083584
training1.__sga_target=2432696320
training1.__shared_io_pool_size=117440512
training1.__shared_pool_size=520093696
training1.__streams_pool_size=0
training1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/training1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TRAINING1/control01,dbf','
/u01/app/oracle/oradata/TRAINING1/control02.dbf'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='testdb'
*.db_recovery_file_dest_size=8256m
*.db_recovery_file_dest='/u01/ARC_BKP'
*.db_unique_name='training1'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=training1XDB)'
*.open_cursors=300
*.pga_aggregate_target=771m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2312m
*.undo_tablespace='UNDOTBS1'

[oracle@training dbs]$ . oraenv
ORACLE_SID = [training] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 02:57:59 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup pfile=$ORACLE_HOME/dbs/inittraining.ora
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.
 ‌

SQL> select dbid,name from v$database;
 
      DBID         NAME
----------       ---------
2862448691        TESTDB

SQL> select status,instance_name from v$instance;

STATUS       INSTANCE_NAME
------------ ----------------
OPEN            training


SQL> create spfile from pfile;

File created.

SQL> show parameter spfile;

NAME             TYPE        VALUE
------------- ----------- ------------------------------
spfile         string      /u01/app/oracle/product/19.0.0
                           /dbhome_1/dbs/spfiletraining.ora

Change the only DBNAME

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0
[oracle@training ~]$  nid target=sqlplus / as sysdba  dbname=training setname=yes

DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 21:48:14 2021

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

Password:
Connected to database TESTDB (DBID=2862448691)

Connected to server version 19.3.0

Control Files in database:
    
    /u01/app/oracle/oradata/TRAINING1/control01.dbf
    /u01/app/oracle/oradata/TRAINING1/control02.dbf

Change database name of database TESTDB to TRAINING? (Y/[N]) => y

Proceeding with operation
Changing database name from TESTDB to TRAINING
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf -modified
Control  File/u01/app/oracle/oradata/TRAINING1/control02.dbf  - modified

Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/sysaux.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf  - wrote new name

Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp  - wrote new name

Instance shut down

Database name changed to TRAINING.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.

DBNEWID - Completed succesfully.


[oracle@training ~]$ cd $ORACLE_HOME/dbs

[oracle@training dbs]$ vi inittraining1.ora

training1.__data_transfer_cache_size=0
training1.__db_cache_size=1728053248
training1.__inmemory_ext_roarea=0
training1.__inmemory_ext_rwarea=0
training1.__java_pool_size=0
training1.__large_pool_size=16777216
training1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
training1.__pga_aggregate_target=822083584
training1.__sga_target=2432696320
training1.__shared_io_pool_size=117440512
training1.__shared_pool_size=520093696
training1.__streams_pool_size=0
training1.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/training1/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TRAINING1/control01,dbf','
/u01/app/oracle/oradata/TRAINING1/control02.dbf'
*.db_16k_cache_size=33554432
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='training'
*.db_recovery_file_dest_size=8256m
*.db_recovery_file_dest='/u01/ARC_BKP'
*.db_unique_name='training1'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=training1XDB)'
*.open_cursors=300
*.pga_aggregate_target=771m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2312m
*.undo_tablespace='UNDOTBS1'


[oracle@training dbs]$ . oraenv
ORACLE_SID = [training1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 21:52:50 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/inittraining1.ora'
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size               8899432 bytes
Variable Size            536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers             7876608 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
TRAINING

SQL> select status,instance_name from v$instance;

STATUS       INSTANCE_NAME
--------- ----------------
OPEN         training1

Change only DBID

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - 
Production Version 19.3.0.0.0

[oracle@training dbs]$ nid target=sqlplus / as sysdba

DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 22:58:56 2021

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

Password:
Connected to database TRAINING (DBID=2862448691)

Connected to server version 19.3.0

Control Files in database:
   
    /u01/app/oracle/oradata/TRAINING1/control01.dbf
    /u01/app/oracle/oradata/TRAINING1/control02.dbf

Change database ID of database TRAINING? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2862448691 to 254963316
    
    Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - modified
    Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
    Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - dbid changed

    Datafile /u01/app/oracle/oradata/TRAINING1/sysaux..dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf  - dbid changed
    
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/data01.dbf - dbid changed
    Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - dbid changed
    
    Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - dbid changed
    Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - dbid changed
    Instance shut down

Database ID for database TRAINING changed to 254963316.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed successfully.

[oracle@training dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 22:59:46 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2432695144 bytes
Fixed Size                  8899432 bytes
Variable Size             536870912 bytes
Database Buffers         1879048192 bytes
Redo Buffers                7876608 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID
---------   ----------
TRAINING     254963316

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19C UPGRADE METHODS ALL IN ONE

UPGRADE METHODS :

AUTO UPGRADE SAME SERVER – NEW FEATURE

CDB & PDB USING MANUAL UPGRADE

MANUAL UPGRADE NON CDB

12C TO 19C DBUA UPGRADE

12C TO 19C UPGRADE USING DATAPUMP

12C TO 19C GRID UPGRADE

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19C RMAN CONCEPTS

rman

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP
RECOVER THROUGH RESETLOGS
BLOCK CHANGE TRACKING
RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP
RECOVER LOSS OF ALL ONLINE REDO LOGS 
POINT IN TIME RECOVERY 
RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE
RMAN Database Restore ASM
DATABASE INCARNATION USING RMAN
Restore Tablespace using RMAN
RESTORE SPFILE USING RMAN
RMAN backup Full Database
RMAN Backup Tablespace
RMAN Backup Particular Datafile
RMAN Backup Spfile
RMAN Backup Current Control file
RMAN Backup Archive log Until Sequence
RMAN Backup Archive log Between Sequence
RMAN Backup Archive log Between SCN
RMAN Backup Archive log Until SCN
RMAN Backup Database Plus Archive log
RMAN Backup Database Includes A Control file
RMAN Backup Archive log and All Delete Input
RMAN Backup Archive log All and Skip Inaccessible
LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN
CROSSCHECK BACKUPS Using RMAN
RESTORE CONTROL FILE USING RMAN
Backup-based Cloning of a database using RMAN
RECOVERY CATALOG DATABASE IN RMAN
RMAN ORA ERRORS
DBVERIFY
END-OF-FILE ERROR
LEVEL 0 INCREMENTAL BACKUP
point-in-time recovery using RMAN in 19c
Recover a loss of all online redo log files

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8