Saturday, 3 December 2022

Oracle Cloud Infrastructure (OCI) Tutorial for Beginners

 Oracle Cloud Infrastructure (OCI) Tutorial for Beginners


Presentation by:

www.EasyReliable.COM

Phone:  +080-41156843/+91 9606734482

Email:  easyreliable@gmail.com/support@easyreliable.com

Website: http://www.easyreliable.com

 

l  Cloud Over Overview

l  Oracle Cloud Over Overview

l  OCI Key Concepts and Terminology

l  Identify and Access Management

l  Networking - VCN, FastConnect, Load Balancer

l  Storage Services(Local NVMe,Block,Object Storage etc)

l  Oracle Database on OCI

l  Autonomous Database



Cloud Overview

Three primary models of cloud computing typically implemented as services are Infrastructure, Platform, and Software as a Service—or IaaS, PaaS and SaaS.

•   IaaS   A collection of servers, storage, and network infrastructure onto which you deploy your platform and software. This is most akin to provisioning your own hardware in an on-premises data center. Teams of hardware engineers, storage specialists, network specialists, system administrators, and database administrators are usually involved in installing and configuring on-premises infrastructure. With IaaS, no hardware engineers or storage specialists are required. A good cloud architect (like you) is all that is required to design and provision this infrastructure. The cloud vendor provides the hardware engineers and storage specialists.

•   PaaS   A collection of one or more preconfigured infrastructure instances usually provided with an operating system, database, or development platform onto which you can deploy your software. The primary benefit of PaaS is convenience as the cloud vendor provides and supports the underlying infrastructure and platform. A subset of PaaS is Database as a Service (DBaaS).

 SaaS   Applications are deployed on a cloud and all you do is access them through your browser. These could range from webmail to complex ERP and BI Analytic systems.

Oracle Cloud encompasses the Oracle Public Cloud, which represents a collection of infrastructure, platforms, and applications exposed as services on cloud.oracle.com

 

Oracle Cloud is a cloud computing service offered by Oracle Corporation providing servers, storage, network, applications and services through a global network of Oracle Corporation managed data centers. The company allows these services to be provisioned on demand over the Internet.

Oracle Cloud provides Infrastructure as a Service (IaaS), Platform as a Service (PaaS), Software as a Service (SaaS), and Data as a Service (DaaS). These services are used to build, deploy, integrate, and extend applications in the cloud. This platform supports numerous open standards (SQL, HTML5, REST, etc.), open-source applications (Kubernetes, Hadoop, Kafka, etc.), and a variety of programming languages, databases, tools, and frameworks including Oracle-specific, Open Source, and third-party software and systems

 

 Regions and Availability Domains

Oracle Cloud Infrastructure is physically hosted in regions and availability domains. A region is a localized geographic area, and an availability domain is one or more data centers located within a region . A region is composed of one or more availability domains.

Oracle Cloud Infrastructure resources are either region-specific, such as a virtual cloud network, or availability domain-specific, such as a compute instance.

 Availability domains are isolated from each other, fault tolerant, and very unlikely to fail simultaneously or be impacted by the failure of another availability domain.

 When you configure your cloud services, use multiple availability domains to ensure high availability and to protect against resource failure.

Be aware that some resources must be created within the same availability domain, such as an instance and the storage volume attached to it

  

Currently Oracle operate in 16 region .This include 11 commercial region and 5 government region. These region is inter connected with azure as well

OCI Key Concepts and Terminology

 

• A tenancy is synonymous with your cloud account and comprises a hierarchy of compartments with the root compartment at the top. There can be many compartments, and as of this writing, compartments may have child compartments nested six levels deep. The below Figure lists the root compartment (RC) along with three nested or child compartments (subcompartments) named Lab, Managed Services, and Managed Compartment ForPaaS

       Compartments :Compartments allow you to organize and control access to your cloud resources. A compartment is a collection of related resources (such as instances, virtual cloud networks, block volumes) that can be accessed only by certain groups that have been given permission by an administrator. A compartment should be thought of as a logical group and not a physical container. When you begin working with resources in the Console, the compartment acts as a filter for what you are viewing. When you sign up for Oracle Cloud Infrastructure, Oracle creates your tenancy, which is the root compartment that holds all your cloud resources. You then create additional compartments within the tenancy (root compartment) and corresponding policies to control access to the resources in each compartment. When you create a cloud resource such as an instance, block volume, or cloud network, you must specify to which compartment you want the resource to belong. Ultimately, the goal is to ensure that each person has access to only the resources they need.

       Users :An OCI user is an individual or system that requires access to OCI resources. There are three types of users:

1.        Local users

2.        Federated users

3.       Provisioned (or synchronized) users

 

       Local users are created and managed in OCI’s IAM service. Local users can only access OCI services. For example, user Jason is created using OCI’s IAM service by navigating to Identity | Users and selecting Create User. After providing a name and description and choosing Create, a new local user is created. This user has a local password and, by default, is capable of logging in to the OCI console. When the tenancy is provisioned, the administrator receives a customized URL for your cloud account and a base URL, as in these examples:When you connect to the console using either of these URLs (explicitly specifying the tenancy when using the latter URL), you will be challenged for an OCI username and password. Once these credentials are provided, you sign in to the console with your local user.

       Federated users are created and managed in an identity provider outside of OCI’s IAM service such as Microsoft Active Directory or Oracle Identity Cloud Service (IDCS). The identity provider discussed from here on will be IDCS, but the principles discussed next apply to other identity providers as well.

 

 

        Provisioned users are automatically created in OCI’s IAM service based on federated users in an identity provider. A provisioned user does not exist without a corresponding federated user. If your tenancy has been federated to another identity provider and you attempt to access the OCI console using the preceding URLs, you will be prompted to either use a single sign-on (SSO) credential or to specify your local username and password. Provisioned users allow federated users to sign in to the OCI console using a password managed by their identity provider

       Groups :OCI users are organized into groups. A user may belong to many groups. When your OCI account is created, a default Administrators group is created. The Administrators group initially has a single member—the user that was created when the tenancy was provisioned. As an administrator, you may create additional administrator users and add them to this group or create other groups for duty separation. The administrator users have complete control over all resources in the tenancy so access to this group should be tightly regulated. It is good practice to set up groups for teams of users who perform similar work.

       Policies :Policies that determines how groups of users interact with OCI resources that are grouped into compartments. You may want the HR application administrators to manage all resources in a compartment dedicated to the HR department: Allow group HR Admins to manage all-resources in compartment HR.This policy statement expressed in simple language is all that is required to authorize the users that belong to the HR Admins group to manage all resources in the HR compartment. The manage verb is the most powerful and includes all permissions for the resource. The policy statements are submitted as free-form text. As of this writing, there is no tool provided to assist with constructing these policy statements.

       Virtual Cloud Network (VCN) :A virtual cloud network is a virtual version of a traditional network—including subnets, route tables, and gateways—on which your instances run. A cloud network resides within a single region but includes all the region's availability domains. Each subnet you define in the cloud network can either be in a single availability domain or span all the availability domains in the region (recommended). You need to set up at least one cloud network before you can launch instances. You can configure the cloud network with an optional internet gateway to handle public traffic, and an optional IPSec VPN connection or FastConnect to securely extend your on-premises network.

       A Load Balancer (LB) is a network device you may provision that receives incoming traffic on an IP address and routes the traffic to one or more underlying instances. The OCI LB service is a regional service that distributes traffic to instances either within the same availability domain or across multiple availability domains.

       The protocol and ports being serviced by an LB are specified in an entity called the Listener. When creating an LB, you specify the VCN in which incoming traffic is accepted as well as whether it will be a private or public LB. You also choose the shape of the LB, which limits the speed at which network traffic is routed. LBs are commonly used to support high availability and scaling out of web servers.

        LBs distribute traffic to backend servers based on a set of policies known as    a backend set. Routing algorithms, including Weighted Round-Robin, IP Hash, and Least Connections, are specified when creating the backend set.

 

       FastConnect : which provides a dedicated, high-speed, private connection between OCI and your existing on-premises infrastructure. FastConnect requires that you must be either collocated with Oracle in a FastConnect location or that you connect through a third-party FastConnect provider that is already connected to Oracle

       Compute :When you provision a compute instance, you can choose a virtual machine (VM) or a bare-metal (BM) server. Bare-metal servers provide your instance with exclusive use of the hardware. Not sharing hardware with other instances comes at a cost and bare-metal instances are more expensive that similarly sized virtual machines. BM servers are only available with a much higher CPU and memory footprint than entry-level VMs.

       Instance :An instance is a compute host running in the cloud. An Oracle Cloud Infrastructure compute instance allows you to utilize hosted physical hardware, as opposed to the traditional software-based virtual machines, ensuring a high level of security and performance.

 

       Path Route Sets specify a set of rules to route requests to different backend sets but this is optional and is only used if this level of sophistication is necessary. Finally, backend sets reference one or more hostnames, which are the target compute instances, which may be running a web server.

       Domain Name Service :OCI also provides a Domain Name Service (DNS) that lets you create and manage DNS zones, add records to zones, and allow the VCN to resolve DNS queries from your on-premises domain and vice versa. One of the primary services DNS provides is hostname resolution. For example, it is DNS that allows you to connect to http://cloud.oracle.com instead of http://23.9.97.203. This abstraction provides network resiliency to underlying network changes.

       Dynamic Routing Gateway and FastConnect :Connecting your existing on-premises infrastructure with your OCI VCN is a common step in the journey to OCI. This connectivity is enabled from OCI using a Dynamic Routing Gateway (DRG) that connects to an on-premises router created in OCI as Customer Premises Equipment (CPE). Your on-premises network is then bridged to your VCN using an encrypted IPSec VPN tunnel.

       Resources :Typical on-premises IT infrastructure resources include servers, SANs, and network infrastructure. OCI infrastructure resources have a parallel definition and refer to artifacts, including compute instances, block storage volumes, object storage buckets, file system storage, virtual cloud networks (VCNs), load balancers, and Dynamic Routing Gateways.

       OCI resources are categorized by resource-types. An individual resource-type is the most granular and includes vcn, subnet, instance, and volume resources. Individual resource-types are grouped into family resource-types such as virtual-network-family, instance-family, and volume-family. Resource-types may also be referenced as an aggregation of all resources at both compartment and tenancy levels as all-resources. These resource-types are important for defining resource management policies.

       Bare Metal Host : Oracle Cloud Infrastructure provides you control of the physical host (“bare metal”) machine. Bare metal compute instances run directly on bare metal servers without a hypervisor. When you provision a bare metal compute instance, you maintain sole control of the physical CPU, memory, and network interface card (NIC).You can configure and utilize the full capabilities of each physical machine  as if it were hardware running in your own data center. You do not share the physical machine with any other tenants.

 

        Shape: In Compute, the shape specifies the number of CPUs and amount of memory allocated to the instance. Oracle Cloud Infrastructure offers shapes to fit various computing requirements.. In Load Balancing, the shape determines the load balancer's total pre-provisioned maximum capacity (bandwidth) for ingress plus egress traffic. Available shapes include 100 Mbps, 400 Mbps, and 8000 Mbps

.

       Key Pair :A key pair is an authentication mechanism used by Oracle Cloud Infrastructure. A key pair consists of a private key file and a public key file. You upload your public key to Oracle Cloud Infrastructure. You keep the private key securely on your computer. The private key is private to you, like a password. Key pairs can be generated according to different specifications. Oracle Cloud Infrastructure uses two types of key pairs for specific purposes:

       Instance SSH Key pair: This key pair is used to establish secure shell (SSH) connection to an instance. When you provision an instance, you provide the public key, which is saved to the instance's authorized key file. To log on to the instance, you provide your private key, which is verified with the public key.

       API signing key pair: This key pair is in PEM format and is used to authenticate you when submitting API requests. Only users who will be accessing Oracle Cloud Infrastructure via the API need this key pair.

       Image :The image is a template of a virtual hard drive that defines the operating system and other software for an instance, for example, Oracle Linux. When you launch an instance, you can define its characteristics by choosing its image. Oracle provides a set of images you can use. You can also save an image from an instance that you have already configured to use as a template to launch more instances with the same software and customizations.

       Storage :Once your compute instance is provisioned, it will have a boot volume and usually no other storage. Four storage types are available on OCI:

        Block volumes

       Object storage

       Archive storage

        File storage

.

Managing Tags and Tag Namespaces :Tagging is a service available to all OCI tenants by default. Tagging in not an IAM concept but is being discussed here to encourage the best practice of tagging your resources in a planful manner. As your OCI estate expands, resource sprawl is inevitable and tagging from the beginning is a great way to remain organized and in control of your OCI resources.

A tag is simply a key-value pair that you associate with a resource. There are two types of tagging: free-form and defined tags.

1) Free-Form Tags

Free-form tags are limited and offer a pretty basic form of tagging. You can apply as many tags as you want to a resource, but there is a 5-kilobyte JSON limitation on all applied tags and their values per resource.

2) Defined Tags

Defined or schema tagging is the recommended enterprise-grade mechanism for organizing, reporting, filtering, managing, and performing bulk actions on your OCI resources.

Defined tags rely on a tenant-wide unique namespace that consists of tag keys and tag values. The tag namespace serves as a container for use with IAM policies.

Networking - VCN, FastConnect, Load Balancer

 

CIDR : The dominant version of network addressing is Internet Protocol version 4 (IPv4). There is a growing prevalence of IPv6 addressing, but the de facto standard remains IPv4 addressing. In the early days of the Internet (1981–93), the 32-bit IPv4 address space was divided into address classes based on the leading four address bits and became known as classful addressing.

The class A address space accommodated 128 networks with over 16 million addresses per network while the class B address space accommodated 16,384 networks with 65,536 addresses per network;

finally, the class C address space accommodated over 2 million networks with 256 addresses per network.

 Class A network blocks are too large and class C network blocks are too small for most organizations so many class B network blocks were allocated although they were still too large in most cases. Classful addressing was wasteful and accelerated the consumption of available IP addresses. To buy time before the IP exhaustion problem manifests, a new scheme known as Classless Inter-Domain Routing (CIDR) was introduced in 1993.

 

CIDR notation is based on an IPv4 or IPv6 network or routing prefix separated by a slash from a number indicating the prefix length. OCI networking uses IPv4 addressing so the address length is 32 bits.

Consider the block of IPv4 addresses specified with the following CIDR notation: 192.168.0.1/30.

CIDR notation may be divided into two components, a network identifier and a host address space. The network identifier is represented by the number of bits specified by the network prefix. The second part is the remaining bits that represent the available IP address space. The routing or network prefix is 30, which means that 30 of the 32 bits in this address space are used to uniquely identify the network while 2 bits are available for host addresses.

In binary, 2 bits let you represent 00, 01, 10 and 11. Therefore, four addresses are available in the host address space.

CIDR notation allows you to calculate the IP address range, the netmask, and the total number of addresses available for host addresses. The netmask, also known as a subnet mask, may be derived from the CIDR notation as follows:

1.   Convert the IP address part to binary notation, with 8-bit parts (octets).

2.   Take the leading bits from 1 to the network prefix and convert these bits to ones.

3.   Convert the remaining bits to zeroes.

4.   Convert the resultant binary string to decimal format.

 


 


Monday, 14 November 2022

How to do Table Partitioning in Oracle with Example

Table Partitioning

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, 
enabling these database objects to be managed and accessed at a finer level of granularity.

Partition pruning - selecting from specific partition is called as partition pruning. adding predicates to the where clause including the partition key(partition columns)

Advantages of Partitioning 

  •  Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
  •  Partitioning improves query performance. 
  •  Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.
  •  Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. 
  •  Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
  •  Partitioning can be implemented without requiring any modifications to your applications. 
For example, you could convert a non-partitioned table to a partitioned table without needing 
to modify any of the SELECT statements or DML statements which access that table. You do not
need to rewrite 

Partitioning Methods

Oracle provides the following partitioning methods:

• Range Partitioning 

Divide the table based on a range of values.

• List Partitioning 

Divide the table based on a list of values.

• Hash Partitioning

Divide the table based on hash key generated from the values of a column.

• Composite Partitioning 

Helps in dividing your partition into sub partitions, by combining various partitioning
methods.

Range-Hash composite Partition

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
SUBPARTITION BY HASH (invoice_no)
SUBPARTITIONS 8
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')),
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY'));

• Interval Partitioning 

Interval Partitioning automatically creates time-based partitions as new data is added

Interval Table Partition in Oracle 

Once your table has been partitioned, you may come across a problem in the maintenance of the data. 

Let us say that the partitioning is based on the value of the PUBLISHED_DATE of a CD or phonograph record.

For example, when you insert a row with a date, the row is inserted into the first partition. Later,

you discover that the date is incorrect. You attempt to update the date, but you get an error message:

ORA-14402: updating partition key column would cause a partition change

Oracle has stopped the update because it would cause the row to belong to a different partition. 

Oracle has implemented a new feature, called row movement, which allows you to make this kind of update. 

When you complete the update, the row moves to the appropriate partition automatically.

Enable Row Movement

To change a partitioned table to allow row movement, use this command: 

ALTER TABLE tablename ENABLE ROW MOVEMENT; 

To change it back to not allowing row movement, use this command:

ALTER TABLE tablename DISABLE ROW MOVEMENT; 

The next lesson covers renaming, moving, and coalescing partitions.

Row movement: The automatic shifting of a row from one partition to another when the value of the partitioning column in the row changes 

so that the row belongs in a different partition.

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.

Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition. 

Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result:

ORA-14400: inserted partition key does not map to any partition 

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem. 


An Interval Partitioning Example

Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table's definition by defining one or more range partitions and including a specified interval.  For example, consider the following table:

create table pos_data (

   start_date        DATE,

   store_id          NUMBER,

   inventory_id      NUMBER(6),

   qty_sold          NUMBER(3)

)

PARTITION BY RANGE (start_date)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),

   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))

); 

Here, two partitions have been defined and an interval of one month has been specified.  If data is loaded into this table with a later date than the greatest defined partition, Oracle will automatically create a new partition for the new month.  In the table above, the greatest defined interval is between July 1, 2015 and August 1, 2015. 

Inserting a row that has a date later than August 1, 2015 would raise an error with normal range partitioning.  However, with interval partitioning, Oracle determines the high value of the defined range partitions, called the transition point, and creates new partitions for data that is beyond that high value.

insert into pos_data (start_date, store_id, inventory_id, qty_sold)

values ( '15-AUG-07', 1, 1, 1);

SELECT TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE

FROM  DBA_TAB_PARTITIONS WHERE

   TABLE_NAME='POS_DATA'

ORDER BY PARTITION_NAME;


PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Notice that a system generated partition named SYS_P81 has been created upon inserting a row with a partition key greater than the transition point.  Oracle will manage the creation of new partitions for any value beyond the high value.  Therefore, the values do not need to be inserted in sequence.

Since the partitions are named automatically, Oracle has added a new syntax in order to reference specific partitions effectively.  The normal way to reference a specific partition is to use the partition (partition_name) in the query:

select * from pos_data partition (SYS_P81);

However, it would be cumbersome to look up the system generated partition name each time.  Therefore, the new syntax to specify a partition is by using the partition for (DATE) clause in the query:

select * from pos_data partition for (to_date('15-AUG-2007','dd-mon-yyyy')); 

Another useful feature of partitioning is the ability to distribute partitions across different tablespaces.  With interval partitioning, this can be accomplished by naming all of the tablespaces in the table definition's "store in" clause.  The system created partitions are then assigned to different tablespaces in a round robin manner.  For example, if the choice was to distribute the table across three tablespaces - tablespaceA, tablespaceB, and tablespac eC - use the following clause in the table definition.


INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

STORE IN (tablespaceA, tablespaceB, tablespaceC)

Restrictions on Interval Partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

  • Cannot be used for index organized tables
  • Must use only one partitioning key column and it must be a DATE or NUMBER
  • Cannot create domain indexes on interval partitioned tables

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using alter table <table_name> set interval(expr).

Consider this range partitioned table:

create table pos_data_range (

   start_date        DATE,

   store_id          NUMBER,

   inventory_id      NUMBER(6),

   qty_sold          NUMBER(3)

)

   PARTITION BY RANGE (start_date)

   PARTITION pos_data_p0 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),

   PARTITION pos_data_p1 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))

); 


If a row with a date of August 15, 2015 is inserted into the table, it will cause an error.

SQL> insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)

  2  values ( '15-AUG-07', 1, 1, 1);

insert into pos_data_range (start_date, store_id, inventory_id, qty_sold)

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition


This range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Interval partitioning is now enabled, and the row with 15-AUG-07 can be inserted without error since Oracle will automatically create the new partition. To convert the table back to only range partitioning, use the following command:

alter table pos_data_range set INTERVAL();

The table is converted back to a range partitioned table and the boundaries for the interval partitions are set to the boundaries for the range partitions.

Using the same syntax, the interval can also be changed for existing interval partitioned tables.  If changing the original table to be partitioned every three months instead of monthly, use:

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

After inserting a row with the date of 15-NOV-07, a new partition is automatically generated with a high value of 01-DEC-07.


insert into

   pos_data (start_date, store_id, inventory_id, qty_sold)

values

   ('15-NOV-07', 1, 1, 1);


SELECT

   TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE

FROM

   Remote DBA_TAB_PARTITIONS

WHERE

   TABLE_NAME='POS_DATA'

ORDER BY

   PARTITION_NAME;


PARTITION_NAME    HIGH_VALUE 


POS_DATA_P0       TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P84    TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


The tablespace storage of the interval partitioned table can also be changed using a similar syntax.  For example, when using a round robin tablespace assignment for the table between tablespace1 to tablespace3, issue the following command:

alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);


Oracle interval partitioning offers a very useful extension to range partitioning.  This greatly improves the manageability of range partitioned tables.  In addition to providing system generated new partitions, Oracle has provided a new syntax to simplify the reference of specific partitions. 

Realtime Example

CREATE TABLE EASYOWNER.EASYTABLE_PART(ADDRESSID NUMBER(38),

BANK_ID VARCHAR2(8 CHAR),

ISADDRESSVERIFIED VARCHAR2(1 CHAR),

NAME_ALT1 VARCHAR2(80 CHAR),

CORP_REP_ID NUMBER(38),

CITY VARCHAR2(200 CHAR),

START_DATE DATE,

END_DATE DATE,

RESIDENTIALSTATUS VARCHAR2(25 CHAR),

USERFIELD5 VARCHAR2(200 CHAR),

USERDATEFIELD1 DATE,

NAME VARCHAR2(100 CHAR),

SALUTATION_CODE VARCHAR2(5 CHAR),

PHONENO1 VARCHAR2(50 CHAR),

PHONENO1LOCALCODE VARCHAR2(20 CHAR),

CITY_CODE VARCHAR2(5 CHAR),

STATE_CODE VARCHAR2(5 CHAR),

COUNTRY_CODE VARCHAR2(5 CHAR),

CORE_CUST_ID VARCHAR2(9 CHAR),

NONCUSTOMERID NUMBER(38),

IS_ADDRESS_PROOF_RCVD VARCHAR2(1 CHAR),

SWIFT_NAME VARCHAR2(35 CHAR),

TMDATE DATE DEFAULT sysdate,

BUSINESS_DATE DATE,

CREATED_DATE DATE,

LST_UPD_JOB_ID NUMBER(19))

NOCOMPRESS 

TABLESPACE EASYOWNER_DATA

RESULT_CACHE (MODE DEFAULT)

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MAXSIZE          UNLIMITED

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

PARTITION BY RANGE (BUSINESS_DATE)

INTERVAL( NUMTODSINTERVAL(1,'DAY'))

(  

   PARTITION part1 VALUES LESS THAN (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

    LOGGING

    NOCOMPRESS 

    TABLESPACE EASYOWNER_DATA

    PCTFREE    10

    INITRANS   1

    MAXTRANS   255

    STORAGE    (

                MAXSIZE          UNLIMITED

                BUFFER_POOL      DEFAULT

                FLASH_CACHE      DEFAULT

                CELL_FLASH_CACHE DEFAULT

               )

);


select max(partition_position) from dba_tab_partitions where table_owner='EASYOWNER' and table_name='EASYTABLE_PART';


select max(partition_position) from dba_tab_partitions where table_owner='EASYOWNER' and table_name='EASYTABLE_PART';



select partition_name from dba_tab_partitions where table_owner='EASYOWNER' and table_name='EASYTABLE_PART;


SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='STG_ADDRESS_PART'
ORDER BY
   PARTITION_NAME;



Range Interval -Hash composite Partition with Real-Time Example


1)


Creating a composite interval-hash partitioned table


CREATE TABLE sales_interval_hash   

  ( prod_id       NUMBER(6)    

  , cust_id       NUMBER    

  , time_id       DATE    

  , channel_id    CHAR(1)    

  , promo_id      NUMBER(6)    

  , quantity_sold NUMBER(3)    

  , amount_sold   NUMBER(10,2)    

  )    

 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))    

 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4    

 (PARTITION before_2016 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))    

 )



SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32) 

FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_INTERVAL_HASH'



2) CREATE TABLE "testuser"."TEST_TABLE_BKP_PART" ("TEST_SRL_NUM" VARCHAR2(12 CHAR),

"B2K_ID" VARCHAR2(12 CHAR),

"B2K_TYPE" VARCHAR2(5 CHAR),

"SOL_ID" VARCHAR2(8 CHAR),

"TEST_DATE" DATE,

"TEST_AMT" NUMBER(20,4),

"SYS_MAN_FLG" CHAR(1),

"LAST_TESTSMITTED_BY" VARCHAR2(15 CHAR)

)

TABLESPACE TEST_TBLSPC

PARTITION BY RANGE (TEST_DATE) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

SUBPARTITION BY HASH ("TEST_SRL_NUM") SUBPARTITIONS 128 STORE IN (TEST_TBLSPC)

(

PARTITION part1 VALUES LESS THAN (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TEST_TBLSPC,

PARTITION part2 VALUES LESS THAN (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part3 VALUES LESS THAN (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part4 VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part5 VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part6 VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part7 VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part8 VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part9 VALUES LESS THAN (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part10 VALUES LESS THAN (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part11 VALUES LESS THAN (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part12 VALUES LESS THAN (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part13 VALUES LESS THAN (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part14 VALUES LESS THAN (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part15 VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part16 VALUES LESS THAN (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part17 VALUES LESS THAN (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part18 VALUES LESS THAN (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part19 VALUES LESS THAN (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part20 VALUES LESS THAN (TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part21 VALUES LESS THAN (TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part22 VALUES LESS THAN (TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part23 VALUES LESS THAN (TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part24 VALUES LESS THAN (TO_DATE(' 2023-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))TABLESPACE TEST_TBLSPC,

PARTITION part25 VALUES LESS THAN (TO_DATE(' 2023-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE TEST_TBLSPC

)

NOCACHE

NOPARALLEL

MONITORING

ENABLE ROW MOVEMENT;


select partition_name,partition_position,high_value,interval,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='TEST_TABLE_BKP_PART';

set pages 9999
col SUBSTR(TABLE_NAME,1,32) for a30
col SUBSTR(PARTITION_NAME,1,32) for a30
col SUBSTR(SUBPARTITION_NAME,1,32) for a30
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32),TABLESPACE_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME ='TEST_TABLE_BKP_PART';


SQL> select partition_name,partition_position,high_value,interval,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='TEST_TABLE_BKP_PART';

PARTITION_NAME                                     PARTITION_POSITION HIGH_VALUE                                                                       INT TABLESPACE_NAME
-------------------------------------------------- ------------------ -------------------------------------------------------------------------------- --- ------------------------------
PART1                                                               1 TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART2                                                               2 TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART3                                                               3 TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART4                                                               4 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART5                                                               5 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART6                                                               6 TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART7                                                               7 TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART8                                                               8 TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART9                                                               9 TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART10                                                             10 TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART11                                                             11 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART12                                                             12 TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART13                                                             13 TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART14                                                             14 TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART15                                                             15 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART16                                                             16 TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART17                                                             17 TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART18                                                             18 TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART19                                                             19 TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART20                                                             20 TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART21                                                             21 TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART22                                                             22 TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART23                                                             23 TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART24                                                             24 TO_DATE(' 2023-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC
PART25                                                             25 TO_DATE(' 2023-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  TEST_TBLSPC

25 rows selected.

SQL> set pages 9999
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32),TABLESPACE_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME ='TEST_TABLE_BKP_PART';SQL>

SUBSTR(TABLE_NAME,1,32)        SUBSTR(PARTITION_NAME,1,32)    SUBSTR(SUBPARTITION_NAME,1,32)           TABLESPACE_NAME
------------------------------ ------------------------------ ---------------------------------------- ------------------------------
TEST_TABLE_BKP_PART   PART1                          SYS_SUBP81663                            TEST_TBLSPC
TEST_TABLE_BKP_PART   PART1                          SYS_SUBP81664                            TEST_TBLSPC
TEST_TABLE_BKP_PART   PART1                          SYS_SUBP81665                            TEST_TBLSPC
TEST_TABLE_BKP_PART   PART1                          SYS_SUBP81666                            TEST_TBLSPC

SQL>

SQL>  create index testuser.MSG_pk_ind on testuser.TEST_TABLE_BKP_PART ( TEST_DATE ) tablespace TEST_TBLSPC local;

Index created.

select * from TEST_TABLE_BKP_PART partition (part1);

Table Purging Activity


02:50:26 SQL> select host_name from from v$instance;

HOST_NAME
----------------------------------------------------------------
TEST_HOST

02:50:48 SQL> select name from v$database;

NAME
---------
EASY_DB

02:51:12 SQL> select segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name='EASY_TABLE' group by segment_name;

SEGMENT_NA SUM(BYTES)/1024/1024/1024
---------- -------------------------
EASY_TABLE                70.7070313

02:51:24 SQL> col PARTITION_NAME for a30
select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_NAME='EASY_TABLE';
02:51:40 SQL> 
PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------------------------------------
P1                                              1 20200501
SYS_P10146                                      2 20211301
SYS_P10786                                      3 20220201
SYS_P11406                                      4 20220301
SYS_P11966                                      5 20220401
SYS_P12586                                      6 20220501
SYS_P13186                                      7 20220601
SYS_P13806                                      8 20220701
SYS_P14391                                      9 20220801
SYS_P15011                                     10 20220901
SYS_P15631                                     11 20221001

PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------------------------------------
SYS_P16231                                     12 20221101
SYS_P16836                                     13 20221201
SYS_P17436                                     14 20221301

14 rows selected.

02:51:40 SQL> select index_name,status,degree from dba_indexes where table_name='EASY_TABLE';

INDEX_NAME                                                                                                                       STATUS   DEGREE
-------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------
EASY_TABLE_NDX2                                                                                                                  VALID    1
INDX_EASY_TABLE4                                                                                                                 VALID    1
IND_TRAN_UDATE_TIME                                                                                                              VALID    1
IK_EASY_TABLE_ORGDEV                                                                                                             VALID    1
EASY_TABLE_NDX                                                                                                                   VALID    1
NDX_EASY_TABLE3                                                                                                                  VALID    1

6 rows selected.

02:51:58 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P10146;

Table altered.

02:59:17 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P10786;

Table altered.

02:59:29 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P11406;

Table altered.

02:59:41 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P11966;

Table altered.

02:59:51 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P12586;

Table altered.

03:00:02 SQL> alter table OWNER.EASY_TABLE drop partition SYS_P13186;

Table altered.

03:00:12 SQL> col PARTITION_NAME for a30
select PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_partitions where TABLE_NAME='EASY_TABLE';
03:00:29 SQL> 
PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------------------------------------------------------------------
P1                                              1 20200501
SYS_P13806                                      2 20220701
SYS_P14391                                      3 20220801
SYS_P15011                                      4 20220901
SYS_P15631                                      5 20221001
SYS_P16231                                      6 20221101
SYS_P16836                                      7 20221201
SYS_P17436                                      8 20221301

8 rows selected.

03:00:29 SQL> select index_name,status,degree from dba_indexes where table_name='EASY_TABLE';

INDEX_NAME                                                                                                                       STATUS   DEGREE
-------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------
EASY_TABLE_NDX2                                                                                                                  UNUSABLE 1
INDX_EASY_TABLE4                                                                                                                 UNUSABLE 1
IND_TRAN_UDATE_TIME                                                                                                              UNUSABLE 1
IK_EASY_TABLE_ORGDEV                                                                                                             UNUSABLE 1
EASY_TABLE_NDX                                                                                                                   UNUSABLE 1
NDX_EASY_TABLE3                                                                                                                  UNUSABLE 1

6 rows selected.

03:00:44 SQL> alter index OWNER.EASY_TABLE_NDX  rebuild parallel 12;

Index altered.

03:02:17 SQL> 03:02:17 SQL> alter index OWNER.NDX_EASY_TABLE3 rebuild parallel 12;

Index altered.

03:03:57 SQL> 03:03:57 SQL> 
03:04:32 SQL> alter index OWNER.IK_EASY_TABLE_ORGDEV rebuild parallel 12;

Index altered.

03:06:03 SQL> 03:06:03 SQL> alter index OWNER.EASY_TABLE_NDX2 rebuild parallel 12;

Index altered.

03:07:32 SQL> 03:07:32 SQL> 
03:07:53 SQL> alter index OWNER.INDX_EASY_TABLE4 rebuild parallel 12;

Index altered.

03:09:12 SQL> 03:09:12 SQL> 
03:09:21 SQL> alter index OWNER.IND_TRAN_UDATE_TIME rebuild parallel 12;

Index altered.

03:10:44 SQL> 03:10:44 SQL> 
03:10:48 SQL> select index_name,status,degree from dba_indexes where table_name='EASY_TABLE';

INDEX_NAME                                                                                                                       STATUS   DEGREE
-------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------
EASY_TABLE_NDX2                                                                                                                  VALID    12
INDX_EASY_TABLE4                                                                                                                 VALID    12
IND_TRAN_UDATE_TIME                                                                                                              VALID    12
IK_EASY_TABLE_ORGDEV                                                                                                             VALID    12
EASY_TABLE_NDX                                                                                                                   VALID    12
NDX_EASY_TABLE3                                                                                                                  VALID    12

6 rows selected.

03:10:58 SQL> alter index OWNER.EASY_TABLE_NDX  noparallel;

Index altered.

03:11:10 SQL> alter index OWNER.NDX_EASY_TABLE3 noparallel;

Index altered.

03:11:19 SQL> alter index OWNER.IK_EASY_TABLE_ORGDEV noparallel;

Index altered.

03:11:27 SQL> alter index OWNER.EASY_TABLE_NDX2 noparallel;

Index altered.

03:11:35 SQL> alter index OWNER.INDX_EASY_TABLE4 noparallel;

Index altered.

03:11:44 SQL> alter index OWNER.IND_TRAN_UDATE_TIME noparallel;

03:11:54 SQL> alter index OWNER.IND_TRAN_UDATE_TIME noparallel;

Index altered.

03:12:10 SQL> select index_name,status,degree from dba_indexes where table_name='EASY_TABLE';

INDEX_NAME                                                                                                                       STATUS   DEGREE
-------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------
EASY_TABLE_NDX2                                                                                                                  VALID    1
INDX_EASY_TABLE4                                                                                                                 VALID    1
IND_TRAN_UDATE_TIME                                                                                                              VALID    1
IK_EASY_TABLE_ORGDEV                                                                                                             VALID    1
EASY_TABLE_NDX                                                                                                                   VALID    1
NDX_EASY_TABLE3                                                                                                                  VALID    1

6 rows selected.

03:12:24 SQL> set lines 300 pages 1000
col owner for a30
col object_type for a30
select owner ,object_name object_type ,status from dba_objects where object_name='EASY_TABLE';
03:12:40 SQL> 03:12:40 SQL> 03:12:40 SQL> 
OWNER                          OBJECT_TYPE                    STATUS
------------------------------ ------------------------------ -------
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
OWNER                         EASY_TABLE                     VALID
BZ1125                         EASY_TABLE                     VALID

10 rows selected.

03:12:40 SQL> 


======================

alter table sales move partition  sales_2009 compress;

alter table sales move partition sales_2009 tablespace crappy_old_disk;

alter tablespace crappy_old_disk read only;

alter index sales_IX partition sales_ix_2009 unusable;

From 12c onward

alter table sales modify partition sales_2012 indexing off;


Exchange partition

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

alter table sales exchange partition sales_2013 with table New_table;

Monday, 7 November 2022

Logs are not shipped to the physical standby database in oracle

 Logs are not shipped to the physical standby database in oracle 


May encounter different types of errors depending on the situations:


  • Logs are not shipped at log switch or fal archiving fails
  • Value of applied column on the primary is not consistent as that on the standby
  • RMAN does not delete logs and results in error RMAN-08137
  • In some rare cases ORA-12154 may show up in the primary alert log
  • Other possible errors ORA-3113, ORA-3135
  • WARN: ARCH: Terminating pid <PID> hung on an I/O operation krsv_proc_kill: Killing 1 processes (Process by index)


CHANGES

Problem usually occurs after OS or network errors, or restarting the primary or standby instance or reboot the primary or standby node that abruptly crashes log shipping between the primary and standby


CAUSE

ARCx processes on the primary stuck on the network forever or that are responsible to update the APPLIED column get stuck and can not recover themselves.

Additionally these processes that may be used for local and remote archiving, heartbeat and FAL fetching logs on the primary.

So when they are all stuck and reach the maximum number of values specified in log_archive_max_processes, they can cause ambiguous errors as shown above.


The worst case would be all ARCx processes on the primary are stuck and they couldn't do local archiving, so that all online redo log files are full which causes the primary database hangs.

This may be due to standby db crash, network errors or some abrupt outage on the standby or primary.

The other common cause is the firewall.


SOLUTION

ARCx processes on the primary need to be restarted.

Assuming that log transport from the primary is configured by log_archive_dest_2.

Please perform the following:


1) If the Data Guard Broker is running, disable Data Guard Broker on both primary and standby:

SQL> alter system set dg_broker_start=FALSE;


2) On the Primary Database:

- Set log transport state to DEFER status:

SQL> alter system set log_archive_dest_state_2='defer';

SQL> alter system switch logfile;

- Reset log_archive_dest_2

SQL> show parameter log_archive_dest_2   -------------------> Note this value

SQL> alter system set log_archive_dest_2 = '';

- Switch logfiles on the Primary

SQL> alter system switch logfile;


3) On the Standby Database:


- Cancel Managed Recovery

SQL> alter database recover managed standby database cancel;

- Shutdown the Standby Database

SQL> shutdown immediate


4) On the Primary: kill the ARCx Processes and the Database will respawn them automatically immediately without harming it.


ps -ef | grep -i arc

kill -9 <ospid of ARC process> <another ospid of ARC process> ...


5) On standby server, startup Standby Database and resume Managed Recovery


SQL> startup mount;

SQL> alter database recover managed standby database [using current logfile] disconnect;


6) Re-enable Log Transport Services on the Primary:

SQL>Alter system set log_archive_dest_2='Service=..............' ;        Set log_archive_dest_2 (Which was reset in step 2) to its original value

SQL> alter system set log_archive_dest_state_2='enable' ;


At this point all the ARCx processes should be up and running on the Primary.


7) Re-enable the Data Guard Broker for both, Primary and Standby if applicable:

SQL> alter system set dg_broker_start=true;

8) Please work with your Network Administrator to make sure the following Firewall Features are disabled.


  • SQLNet fixup protocol
  • Deep Packet Inspection (DPI)
  • SQLNet packet inspection
  • SQL Fixup
  • SQL ALG (Juniper firewall)

NOTE : To enable SQLNET tracing refer,


step by Step Method to Enable SQLNET(Server Tracing) Dynamically on Dataguard (Doc ID 2010476.1) 


The wording and features can vary by vendor but all the above have some impact on some packets (not all packets are affected).

Some firewalls can have an adverse effect on certain SQL packets transported across them (again, some not all).

In some rare cases if the above procedure doesn't work, you have to restart the primary instance to make the log shipping resuming again.


Troubleshooting Data Guard Log Transport Services in oracle

 Troubleshooting Data Guard Log Transport Services in oracle

Log Transport Services Overview:

Data Guard is using an Oracle NET-Connection from the Primary to the Standby Database. The Initialization Parameter ‘log_archive_dest_n’ is used for the Transport Services to a particular Standby Database. This Parameter consists of several Attributes to configure the Log Transport Services to the corresponding Standby Database.

If there is a log_archive_dest_n defined on the Primary Database which is pointing to a Standby Database (ie. the ‘SERVICE’-Attribute is used), the Primary Database tries to connect to the Standby Database using the Descriptor or TNS-Alias given in that Attribute. The LGWR-Process creates a Worker Process (LNS-, NSS-, NSA- or TTXX-Process) which is responsible for the Connection to the Standby Database and Transporting the Redo. The corresponding Server Process is a RFS-Process on the Standby Database. In Addition there is an ARCH-Process connected to the Standby Database responsible for the Heartbeat and Gap Resolution Mechanism.

Troubleshooting Steps:

 First of all verify if you can connect to the Standby Database at all. An easy Way to find out is simply open a SQL*PLUS-Session from the Primary Database ORACLE_HOME and try to connect to the Standby Database using the same Descriptor or TNS-Alias given in the ‘SERVICE’-Attribute of log_archive_dest_n:


SQL> connect sys/<Password>@<Standby Service> as sysdba

SQL> select database_role, db_unique_name from v$database;


If it connects and returns the correct Database Role and db_unique_name the Connection basically looks fine. If it returns any Error, likely the Heartbeat is hitting the same Error (you should see in the ALERT.LOG). In that Case you can follow up on

Note 1432367.1: Troubleshooting - Heartbeat failed to connect to standby 

Each log_archive_dest_n has a corresponding Initialization Parameter to toggle the Status for this Destination. This Parameter is called ‘log_archive_dest_state_n’. Verify it is set to ‘enable’ for the corresponding log_archive_dest_n.

Ensure there is sufficient Bandwith available on the Network. It must be able to cope with the Redo Generation Rate at any Time – even during Peak Times. See

Run the ORAchk-Tool against your Configuration to verify if your current Setup matches with the recommended Setup. You can get further Information about ORAchk here:

Note 1268927.2: ORAchk - Health Checks for the Oracle Stack 

Verify if there is any active Firewall or Router in the Network Connection between the Primary and Standby Database. Those can have Features enabled which are able to modify TCP-Packets which can impact the SQLNET-Communication and raise Errors like ORA-3135 or Redo Corruptions in the Log Transport Services

SERVICE

DB_UNIQUE_NAME

VALID_FOR

Compare the Settings for the DG_CONFIG in the Initialization Parameter ‘log_archive_config’ with the db_unique_name for your remote Archive Destinations. It must contain the db_unique_names from the Primary and all its Standby Databases. It must be set to the same Value on all the participating Standby Databases, too.

In order to have most Benefit, Standby RedoLogs should be configured and used by Log Transport Services. Review

Note 219344.1: Usage, Benefits and Limitations of Standby Redo Logs (SRL)

If the Data Guard Broker is used, note that all Initialization Parameters and Attributes are maintained by the Data Guard Broker. If you want to modify anything you have to modify the corresponding Data Guard Broker Property. In Case of any Problems you can also review the Data Guard Broker Logfile (DRC<SID>.LOG) for each Instance. It can be found in the ‘trace’-Directory of each Instance (see the Initialization Parameters 'background_dump_dest' or 'diagnostic_dest' for the exact Paths).

To gather further Tracing Information you can enable Archive Tracing on the Primary and Standby Database. Archive Tracing can be enabled via the Initialization Parameter ‘log_archive_trace’. There are different Levels available. Enabling different Levels at the same Time can be done by setting ‘log_archive_trace’ to the Sum of the desired Levels. Setting back to ‘0’ disables the Tracing. Relevant and common Levels for Log Transport Services are:

For the Primary Database:


1:            Tracks archiving of log files

2:            Tracks archive status by archive log file destination

64:         Tracks ARCn process state activity

128:       Tracks FAL server process activity

512:       Tracks LGWR redo shipping network activity

2048:     Tracks RFS/ARCn ping heartbeat


For the Standby Database:


1:            Tracks archiving of log files

2:            Tracks archive status by archive log file destination

64:          Tracks ARCn process state activity

256:       Tracks RFS Logical Client

1024:     Tracks RFS physical client

2048:     Tracks RFS/ARCn ping heartbeat


There are some more Levels which are concerning local Archiving and Log Apply Services which can be set as well if required. See

for a full List of available Levels. The Tracing Output can be found in the current Process-Tracefiles (eg. LGWR, NSS, RFS…). You can review those Traces to get detailed Information.


 


What is an Archive Gap And its possible Solution


 What is an Archive Gap And its possible Solution

PURPOSE

 This Document show the various Possibilities to detect and resolve a Redo Gap on a Standby Database.

 

What is an Archive Gap ??

 

An Archive Gap is a Range of missing Redo on the Standby Site that prevents Log Apply Services to proceed. This typically happens when the Standby Site is unable to receive Redo from the Primary Database or the Redo Information is not available on the Standby Database. Possible and common Causes for Archive Gaps are:

Network Disconnects or stop of Log Transport Services

Outages of the Standby Database

Misconfigurations of Log Transport Services

I/O-Issues on the Standby Site

Manual Deletion of ArchiveLogs before they are applied to the Standby

Insufficient Bandwith in the Network between the Primary and Standby Site

Once there is an Archive Gap on the Standby Database the Log Apply Services will get stuck until the Gap is resolved, ie. the missing Redo in Form of ArchiveLogs is fetched and made available on the Standby Site. Log Apply Services then can pick it up and proceed.

 

Methods of Gap Resolution


There are 4 Possibilities to resolve an Archive Gap on a Standby Database. Those are discussed below.

Automatic Gap Resolution

Automatic Gap Resolution is performed automatically by the Log Transport Services. Basically the currently transferred Redo is compared with the last received. If there is a Mismatch, it is detected by the receiving RFS-Process on the Standby Database, which will automatically request the missing Log Sequence from the Primary Database again via the ARCH-RFS Heartbeat Ping. This Type of Gap Resolution is using the Service defined in log_archive_dest_n on the Primary Database serving this Standby Database. In Addition the ARCH-RFS Heartbeat Ping performs a Poll of the current Sequence to detect an Archive Gap. If there is one detected it will get resolved the same Way. Once a Gap is resolved the Transport Process (ARCH or LGWR) is notified about the Resolution of the Gap. For Automatic Gap Resolution there is no special Setting or monitoring required.

 

FAL (Fetch Archive Log) Gap Resolution

Once an ArchiveLog is received or archived from a Standby RedoLog on the Standby Database, it is registered in the Standby Controlfile (you can query the Registration by v$archived_log on a Physical Standby Database and dba_logstdby_log on a Logical Standby Database). If such a File is missing or corrupted for any Reason (eg. it got deleted by Fault), FAL is called to perform a Gap Resolution. This is the Case because such missing Logfiles are typically detected by the Log Apply Services on the Standby Database. Those are working independent from the Log Transport Services and do not have a direct Link to the Primary Database. To use FAL, there must be one or two (prior Oracle 11.2.0) Initialization Parameters setup on the Standby Database:

FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested. This can either be the Primary Database, but also another Standby-, ArchiveLog Repository- or Far Sync Standby (> Oracle 12.1.0) Database inside the Data Guard Configuration. It is possible to specify multiple Service Names (Comma separated). FAL will then sequentially attempt those Databases to resolve the Gap.

FAL_CLIENT (< Oracle 11.2.0): Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from the FAL_SERVER Database(s) back to the Standby Database (ie. that’s the Destination where the FAL_SERVER Database should send the Redo to). Ensure this TNS-Alias exists in the TNSNAMES.ORA of your FAL_SERVER Database(s). This Parameter is not required any more since Oracle 11.2.0. However you have to ensure there exists a corresponding log_archive_dest_n on your FAL_SERVER Database(s) which is pointing to the Standby Database requesting the Gap Resolution.

Once the Log Apply Services detect an Archive Gap it sends a FAL Request to the FAL_SERVER handing over the FAL_CLIENT (or db_unique_name for Version > 11.1.0). An ARCH-Process on the FAL_SERVER tries to pick up the request Sequence(s) from that Database and sends it back to the FAL_CLIENT (or uses the Destination valid for this db_unique_name). If the first FAL_SERVER is not able to resolve the Gap, the next FAL_SERVER in the List will be attempted. If it cannot be resolved by all FAL_SERVERs the FAL-Request fails and a corresponding Message will be put in the ALERT.LOG of the Standby Database.

In order to successfully complete a Gap Request the requested ArchiveLog Sequence(s) must be available on the FAL_SERVER Database (on Disk and the corresponding Entry in the Controlfile).

FAL is available since Oracle 9.2.0 for Physical Standby Database and Oracle 10.1.0 for Logical Standby Databases.

 

Manual Gap Resolution

If an Archive Gap cannot be resolved automatically by any of the previously mentioned Methods, you can still try to manually resolve an Archive Gap.

You can query v$archive_gap on a Physical Standby Database or dba_logstdby_log on the Logical Standby Database to determine a current Archive Gap, eg.

 

On Physical standby


SQL> select * from v$archive_gap;

On Logical standby

SQL> select thread#, sequence# from dba_logstdby_log l where next_change# not in

     (select first_change# from dba_logstdby_log where l.thread#=thread#)

     order by thread#, sequence#;

 

Now copy the returned Sequences to the Standby Database manually to the desired Location. If the missing are not yet registered on the Standby Database, you have to register them before the Log Apply Services are able to read those Logfiles. You can register ArchiveLogs using

Physical Standby:

SQL> alter database register logfile ‘<File-Specification>’;

 Logical Standby:

SQL> alter database register logical logfile ‘<File-Specification>’;

 

Once they are registered Log Apply Services will pick up the ArchiveLogs and proceed.

 

Roll forward using Incremental Backup (Physical Standby only)

If a Gap cannot be resolved with any of the previous Methods, the Gap is quite large and may take a long Time to resolve or the Gap cannot be resolved because the missing ArchiveLogs do not exist on any Database any more, you can still roll forward a Physical Standby Database using an incremental Backup from SCN. This Feature is available since Oracle 10.2.0. The Idea is to record the latest SCN applied to the Standby Database, then create an incremental Backup from that SCN on Primary Database using RMAN together with a Backup of the current Controlfile (as Standby Controlfile).

Then we first replace the old Standby Controlfile with the Standby Controlfile from the incremental Backup and apply the incremental Backup on the Standby Database. This is a real fast and easy Way to bring a Standby Database back close to the current Status of the Primary Database. Since the Steps to take can be different in the various Releases, please look into Chapter

“Using RMAN Incremental Backups to Roll Forward a Physical Standby Database”


Sunday, 6 November 2022

ORA-00392: log 3 of thread 1 is being cleared, operation not allowed

 

Problem Description

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

We restored NON-PROD Database from RMAN full backup(Production) but while opening database it is throwing below error

 

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

ORA-00392: log 3 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03a.log'

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03b.log'

 

Database is in mount stag, and we are not able to drop online redo logfile As it is current file.

 

SQL> SELECT group#,sequence#,status,next_change# FROM V$LOG;

 

GROUP# SEQUENCE# STATUS NEXT_CHANGE#

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

1 0 UNUSED 6.0919E+12

2 0 UNUSED 6.0919E+12

10 0 UNUSED 6.0919E+12

9 0 UNUSED 6.0919E+12

3 0 CLEARING_CURRENT 1.8447E+19

 

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE DROP LOGFILE GROUP 3

*

ERROR at line 1:

ORA-01623: log 3 is current log for instance EASYDB (thread 1) - cannot drop

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03aa.log'

ORA-00312: online log 3 thread 1: '/db1/oraredo/EASYDB/EASYDB_redo_03bb.log'

 

To fix this issue, we renamed group3 where space is available and created dummy files

 

a) rename problematic online redo file

alter database rename file '/db1/oraredo/EASYDB/EASYDB_redo_03aa.log' to '/db1/orafra/EASYDB/EASYDB_redo_03aa.log';

alter database rename file '/db1/oraredo/EASYDB/EASYDB_redo_03bb.log' to '/db1/orafra/EASYDB/EASYDB_redo_03bb.log';

 

b) create dummy file using os touch command 

{orauat@EASYSERVER:db1/orafra/EASYDB}bdf .

{orauat@EASYSERVER:db1/orafra/EASYDB}touch EASYDB_redo_03bb.log

{orauat@EASYSERVER:db1/orafra/EASYDB} touch EASYDB_redo_03aa.log

{orauat@EASYSERVER:/db1/oraredo/EASYDB}sqlplus

 

SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 6 23:55:11 2022

 

SQL> show parameter control

 

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     21

control_files                        string      /db1/oraredo/EASYDB/control/cont

                                                 rol01.ctl, /db1/orafra/EASYDB/co

                                                 ntrol/control02.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> !ls -lr /db1/oraredo/EASYDB/control/control01.ctl

-rw-r-----   1 orauat     oinstall   88719360 Nov  7 00:05 /db1/oraredo/EASYDB/control/control01.ctl

 

SQL> !ls -ltr /db1/orafra/EASYDB/control/control02.ctl

-rw-r-----   1 orauat     oinstall   88719360 Nov  7 00:05 /db1/orafra/EASYDB/control/control02.ctl


c) clear problematic online redo log file 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

SQL>  alter database clear unarchived logfile group 3;

 

Database altered.

 d) check status , it is changed now

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;

 

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#

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

         9          1          0          2 YES UNUSED              6.0919E+12

        10          1          0          2 YES UNUSED              6.0919E+12

         1          1          0          2 YES UNUSED              6.0919E+12

         2          1          0          2 YES UNUSED              6.0919E+12

         3          1          0          2 NO  CURRENT             6.0919E+12

 

e) started database with resetlogs option and stop and start gracefully

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

EASYDB      READ WRITE

 

SQL>

 

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

EASYDB      READ WRITE

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size                 19289376 bytes

Variable Size            5502929632 bytes

Database Buffers         3053453312 bytes

Redo Buffers               14262272 bytes

Database mounted.

Database opened.

SQL>


select name,open_mode from v$database;