Relational Databases

  As you know, when performing backups with the RMAN tool, you can use a   physical standby   instead of the   primary database   to avoid putting extra load on the primary. Since the structure of these two environments (primary and physical standby) is essentially the same, there is not much difference in backup operations between them (the datafiles in these two environments match block by block, provided that the standby is synchronized). The slight difference comes from the  open mode  of these two databases, and this difference becomes noticeable especially when backing up  archive logs . This is because, during an archivelog backup, Oracle executes a  log switch  — and obviously, this log switch cannot be executed in the physical standby environment. The following command was executed in the physical standby environment. The  RMAN-06820  message in the output confirms the points mentioned above: rman target / RMAN> ba...

  As you know, using the   ALTER TABLE … MOVE   command to relocate a table causes the related indexes of that table to become   UNUSABLE . Starting from version  12.2 , using the  ONLINE  keyword in this command prevents the indexes from becoming invalid, and they will remain in a  VALID  state. Example: create index ind on tbl(name); ——— alter table tbl move online; —— - select index_name,status from dba_indexes where index_name = ’IND’; INDEX_NAME STATUS IND VALID As shown in the example above, the status of the index  IND  remains  VALID  even after moving the table  TBL . Next, let’s compare the execution time between the new  ONLINE  form of the command and the traditional one: SQL > alter table tbl move online; Table altered. Elapsed: 00 : 16 : 01.52 ——— - SQL > alter table tbl move; Table altered. Elapsed: 00 : 01 : 03.70 As you can see, moving a table  online  ...

  To implement Data Concurrency and Read Consistency, Oracle stores transaction metadata in part of the data block header. This area of the block header is called the  Interested Transaction List (ITL)   and can contain multiple slots. A transaction that wants to acquire a row must occupy an ITL slot in the block header of the block that contains that row. In the slot the database records information such as the Transaction ID (XID), the Undo Block Address (UBA), and the number of rows the transaction has locked in that block. Each slot belongs to a single transaction, and each transaction can occupy only one slot per block. When a transaction ends (COMMIT or ROLLBACK), its ITL slot becomes reusable by other transactions. The size of each transaction slot is about 24 bytes. Note that physical row addresses (ROWIDs) are not stored in this space: SQL > select type_size from v$type_size where description = ‘TRANSACTION VARIABLE HEADER’; TYPE_SIZE --------- 24 The init...