oracle 21c - ORACLEAGENT BLOG

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

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:

LMT AND DMT TABLESPACE

LMT TABLESPACE

The LMT tablespace is implemented by adding EXTENT MANAGEMENT LOCAL clause to 
the tablespace definition.LMT tablespaces automate extent management and remove
the ability to specify the NEXT storage parameter. 

The only exception is when NEXT is used with MINEXTENTS at table creation time.
LMT means Locally Management  system,LMT tablespace size is uniform.
By default uniform size is 1,By default lmt tablespace is Autoallocate.

Auto allocate

SQL> create tablespace india datafile '/u01/app/oracle/oradata/TESTDB/india01.dbf' 
size 50m;

Tablespace created.

Uniform Size

SQL> create tablespace USA datafile '/u01/app/oracle/oradata/TESTDB/USA01.dbf' 
size 50m  extent management local uniform size 512k;

Tablespace created.

Deferred segment creation

SQL> create table india (reg_id number,reg_name varchar2(200));

Table created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
        0
SQL> select count(*) from dba_extents where segment_name='INDIA';

  COUNT(*)
----------
         0

SQL> insert into india values (5,'tamil');

1 row created.

SQL>  select count(*) from dba_segments where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  select count(*) from dba_extents where segment_name='INDIA';

  COUNT(*)
----------
         1

SQL>  create table italy (reg_no number,reg_name varchar2(2000)) 
segment creation immediate;

Table created.


SQL> show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL>  show parameter deferred_segment_creation;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     FALSE

Extents

Continuous blocks it’s called extents,Data Stored in blocks,blocks Stored in extents.
By default Extents Size 64k.

TYPES OF EXTENTS

Initial          - Initial is First No of transaction by default 100.

Next             - Next no of transaction is 200.
  
Minextents       - minimum number of transactions by default 1.

Maxextents       - maximum number of transactions by default Unlimited.

Pct increase     - pct increase by default 100.

BLOCKS

Smallest storage unit it’s called Blocks.After database creation 
we can specify block size but before database creation we can’t change block size.
block size is 2k,4k,8k,16k,32k. By default Block size is 8k.

Check Block Size in Database

SQL> show parameter db_block_size;

NAME                     TYPE        VALUE
--------------------- ----------- --------------
db_block_size           integer     8192

SQL> select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE            TABLESPACE_NAME
----------        ------------------------------
      8192 	        SYSTEM
      8192 		SYSAUX
      8192 		UNDOTBS1
      8192 		TEMP
      8192 		USERS
      8192 		TEST
      8192 		INDIA

create New Tablespace

SQL>  create tablespace spain datafile'/u01/app/oracle/oradata/TESTDB/spain01,dbf'
size 100m blocksize 16k;

Tablespace created.

SQL>  select block_size,tablespace_name from dba_tablespaces;

BLOCK_SIZE        TABLESPACE_NAME
----------      ---------------------
      8192            SYSTEM
      8192            SYSAUX
      8192            UNDOTBS1
      8192  	      TEMP
      8192 	      USERS
      8192 	      TEST
      8192 	      INDIA
     16384 	      SPAIN

Block Utilization Parameters

INITRANS       

MAXTRANS    

PCTUSED        

PCTFREE

Initans is the initial number of transactions by default 1. 
Maxtrans is Maximum number of Transactions default 255.Inserted data Stored in 
pct used by default allocate space is 89%, in case pct used is full Remaining 
data goes to Next pct used. Update happens in  pct free by default allocate space 10%,
pct used and pct free information stored by block header by default allocated space 1%.

Block-level problems

Row chaining

Row migration

Row chaining

Inserted data stored in pct used,that pct used is full remaining data goes to next pct 
used it is called Row chaining.Row chaining occurs when a row can't physically fit 
into an Oracle block. 

Another block is required to store the remainder of the row.Chaining can cause serious 
performance problems and is especially prevalent with those storing multimedia data or 
large binary objects (blobs). 

You should pay special attention to the DB_BLOCK_SIZE parameter 
when you create your database.Block sizes of 4 kilobytes or more are the norm, 
not the exception.

Row migration

Date updates happen for  pct free ,that updated data stored in pct used.pct used is 
full that time remaining data goes to the next pct used that is called row Migration.
Migration of an Oracle row occurs when a row is updated in an Oracle block and the 
amount of free space in the block is not adequate to store all of the row's data. 
The row is migrated to another physical block in the table. 
The problem is that the indexes that refer to the migrated row are still pointing 
to the block where the row used to be, and hence the table reads are doubled. 
Note however that full table scans will scan blocks as they come and will perform the 
same number of reads whether the rows are migrated or not.

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

Check unusable and invalid index in Oracle

Check unusable and not valid Index in Oracle

Below Query will convert all the unusable and not valid indexes in Oracle. The query will cover the complete index with partition index and subpartition index.

The result will give you the rebuild command of the invalid or unusable indexes.
You can directly run that and on sqlplus and make them a valid or usable state.

QUERY :

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';

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

FLASHBACK VERSION QUERY

Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN clause.

Flashback version query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the UNDO_RETENTION parameter.

SQL> CREATE TABLE flashback_version_query_test (id NUMBER(10),description  VARCHAR2(50));

Table created.

SQL> INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN    TO_CHAR(SYSTIMESTAM
-----------   -------------------
    4991867    2021-07-20 22:29:52

SQL> UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.


SQL> UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN  TO_CHAR(SYSTIMESTAM
-----------  -------------------
    4992195  2021-07-20 22:34:30


SQL>  SELECT versions_startscn, versions_starttime,versions_endscn, versions_endtime, 
versions_xid, versions_operation, description from flashback_version_query_test 
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(' 2021-07-20 22:29:52', 'YYYY-MM-DD HH24:MI:SS') 
AND TO_TIMESTAMP('2021-07-20 22:34:30', 'YYYY-MM-DD HH24:MI:SS')WHERE  id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
---------- --------------------- ------------------ -------------- -----------  -----------
 4992172   20-JUL-21 10.33.41  PM  0D0020001B040000   U THREE                                            
4992136    20-JUL-21 10.32.23  PM   0B00050021040000  U TWO
4992172    20-JUL-21 10.33.41  PM                       ONE



SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, 
versions_xid, versions_operation, description FROM  flashback_version_query_test
VERSIONS BETWEEN SCN  4991867 and 4992195 WHERE  id = 1;  

 VERSIONS_STARTSCN VERSIONS_STARTTIME          VERSIONS_ENDSCN VERSIONS_ENDTIME         VERSIONS_XID     V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
 4992172  20-JUL-21 10.33.41 PM                        0D0020001B040000 U THREE
 4992136 20-JUL-21 10.32.23 PM     4992172 20-JUL-21 10.33.41 PM    0B00050021040000 U TWO
                                                       4992136 20-JUL-21 10.32.23 PM                       ONE



SQL>  SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM   
flashback_transaction_query WHERE  xid = HEXTORAW('06000000FA030000');

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:

FLASHBACK DROP TABLE (RECYCLE BIN)

FLASHBACK DROP TABLE

The recycle bin is a logical collection of previously dropped objects. This feature doesn’t use flashback logs or undo, so it is distinct from the other flashback technologies.

Enable/Disable Recycle Bin

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

SQL> alter session set recyclebin = on;

Session altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON

(RECYCLE BIN)

SQL> CREATE TABLE flashback_drop_test (id  NUMBER(10));

Table created.

SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.


SQL> DROP TABLE flashback_drop_test;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07
EST

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1
Rename Table

SQL> show RECYCLEBIN;

ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
S		 BIN$x3mKgoseD/7gUxkBqMAnAA==$0 TABLE	     2021-07-19:17:20:17
SQL> create table s(n number);

Table created.

SQL> FLASHBACK TABLE s TO BEFORE DROP;
FLASHBACK TABLE s TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object


SQL> FLASHBACK TABLE s TO BEFORE DROP rename to b;

Flashback complete.

SQL> select * from b;

no rows selected

SQL> show RECYCLEBIN;

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:

FLASHBACK QUERY Concepts

FLASHBACK QUERY

Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality.

SQL> create table flashback_query_test  (id  number(10));

Table created.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')FROM v$database;

    CURRENT_SCN               TO_CHAR(SYSTIMESTAMP
 --------------            -------------------------
   4933950                     2021-07-20 00:02:36

SQL> INSERT INTO flashback_query_test (id) VALUES (1);

1 row created.

SQL> commit;
  
Commit complete.

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN      TO_CHAR(SYSTIMESTAMP
---------------        --- ---------------------
    4934857                     2021-07-20 00:10:15

SQL> SELECT COUNT(*) FROM flashback_query_test;

  COUNT(*)
----------
         1

SOL> SELECT COUNT(*) FROM   flashback_query_test AS OF SCN 4933950;

 COUNT(*)
----------
     1

SOL> SELECT COUNT(*) FROM   flashback_query_test AS OF SCN 4934857 ;

 COUNT(*)
----------
      0

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

FLASHBACK TABLE

FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error.

The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.

SQL> CREATE TABLE flashback_table_test (id  NUMBER(10));

Table created.

SQL> ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

Table altered.




SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993733

SQL> SELECT current_scn, TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM v$database;

CURRENT_SCN          TO_CHAR(SYSTIMESTAMP
-----------         -------------------------
    4993746           2021-07-20 23:18:30

SQL> INSERT INTO flashback_table_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
    4993843

SQL>FLASHBACK TABLE flashback_table_test TO SCN 4993733;

Flashback complete.

SQL> SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
      0

SQL> FLASHBACK TABLE flashback_table_test TO SCN 4993843;
  
Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
         1

SQL> FLASHBACK TABLE flashback_table_test TO TIMESTAMP 
TO_TIMESTAMP(' 2021-07-20 23:18:30', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>  SELECT COUNT(*) FROM flashback_table_test;

  COUNT(*)
----------
     0

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

TABLESPACE Concepts

Checking Tablespaces in Database

SQL> select name from v$tablespace; 

NAME
------------------------------ 
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA

Create New Tablespace

SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;

Tablespace created.
SQL> select name from v$tablespace;
 NAME
------------------------------
 SYSTEM
 SYSAUX
 UNDOTBS
 TEMP
 DATA
 INDIA

Check Datafile in whole Database

SQL> select tablespace_name,file_name from dba_data_files; 

TABLESPACE_NAME	FILE_NAME
-------------------- --------------------------------------------------
 SYSTEM		  /u01/app/oracle/oradata/ORACLEAGENT/system.dbf 
 SYSAUX	          /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
 UNDOTBS	  /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf 
 DATA		  /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf 
 INDIA	          /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

Check Datafile's in particular Tablespace.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

Check Tablespace size in particular tablespace

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';

SUM(BYTES)/1024/1024
--------------------
2048

How to Check Tablespace size in whole Database

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by 
tablespace_name;

TABLESPACE_NAME	SUM(BYTES)/1024/1024
--------------  --------------------
SYSTEM	          2048
INDIA	           30
SYSAUX	          2048
UNDOTBS	          2048
DATA               60

How to Resize Datafiles
	
SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf

SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' 
resize 60m;

Database altered.

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';

SUM(BYTES)/1024/1024
 
--------------------
60

Add New Datafile

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf


SQL> alter tablespace india add datafile 
'/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf

Rename or Relocate datafiles
   
           after the 12c method

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' 
to '/u02/india03.dbf';

Database altered.

SQL> select file_name from dba_data_files where tablespace_name='INDIA'; 

FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u02/india03.dbf

              before 12c method

SQL> alter tablespace india offline;
 Tablespace altered.


[oracle@training u02]$ cd $ORACLE_BASE/oradata/ORACLEAGENT 

[oracle@training ORACLEAGENT]$ ls -lrth
total 6.4G
-rw-rw----. 1 oracle oracle 501M Jul 1 22:09 temp01.dbf
-rw-rw----. 1 oracle oracle 41M Jul 1 22:09  redo3.log
-rw-rw----. 1 oracle oracle 21M Jul 2 00:03  test02.dbf
-rw-rw----. 1 oracle oracle 31M Jul 2 00:03  test01.dbf
-rw-rw----. 1 oracle oracle 41M Jul 2 00:10  redo1.log
-rw-rw----. 1 oracle oracle 41M Jul 2 00:15  data01.dbf
-rw-rw----. 1 oracle oracle 21M Jul 2 00:15  data02.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:35 system.dbf
-rw-rw----. 1 oracle oracle 61M Jul 2 00:39  india01.dbf
-rw-rw----. 1 oracle oracle 31M Jul 2 00:39  india02.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:41 sysaux.dbf
-rw-rw----. 1 oracle oracle 2.1G Jul 2 00:41 undo1.dbf
-rw-rw----. 1 oracle oracle 41M Jul 2 00:42  redo2.log
-rw-rw----. 1 oracle oracle 10M Jul 2 00:42  control_8.ctl
-rw-rw----. 1 oracle oracle 10M Jul 2 00:42  control_7.ctl 

[oracle@training ORACLEAGENT]$ mv india02.dbf /u02/india04.dbf 

[oracle@training ORACLEAGENT]$ sqlplus / as sysdba
 sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 2 00:44:21 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

SQL> alter tablespace india rename datafile 
'/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india04.dbf';

Tablespace altered.

SQL> alter tablespace india online;

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

Tag Archives: oracle 21c

How to Enable archive log mode

How to Disable archive log mode

Change Archive log mode and Destination in oracle 19c

To check the current archiving location

Static and Dynamic

MULTIPLEXING USING SPFILE

MULTIPLEXING USING PFILE

NID utility allows us to change

Changing DBNAME & DBID

Change the only DBNAME

Change only DBID

LMT TABLESPACE

Auto allocate

Uniform Size

Deferred segment creation

Extents

TYPES OF EXTENTS

BLOCKS

Check Block Size in Database

create New Tablespace

Block Utilization Parameters

Block-level problems

Row chaining

Row migration

Check unusable and not valid Index in Oracle

FLASHBACK VERSION QUERY

FLASHBACK DROP TABLE

Enable/Disable Recycle Bin

(RECYCLE BIN)

FLASHBACK QUERY

FLASHBACK TABLE