Shell script to delete old archive logs in RAC databases

 Today, I will be sharing one handy shell script that can be used to delete old archive logs. Considering the archive logs are already backed up and not required for any recovery purpose. In the following example i will be deleting archive that are 5 days old.


vi archive_del.sh

#!/bin/bash

 

# Set the number of days

DAYS_OLD=5

 

# Function to delete archive logs older than specified days for a given ORACLE_SID

delete_old_archivelogs() {

  ORACLE_SID=$1

  export ORACLE_SID

 

  echo "Deleting archive logs older than $DAYS_OLD days for database $ORACLE_SID..."

 

  rman target / <<EOF

    CROSSCHECK ARCHIVELOG ALL;

    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-$DAYS_OLD';

    EXIT;

EOF

 

  if [ $? -eq 0 ]; then

    echo "Archive logs older than $DAYS_OLD days deleted successfully for database $ORACLE_SID."

  else

    echo "Failed to delete archive logs for database $ORACLE_SID."

  fi

}

 

# Source the Oracle environment variables

source /usr/local/bin/oraenv

 

# Get the list of running Oracle database instances (ORACLE_SID) excluding ASM

ORACLE_INSTANCES=$(ps -ef | grep pmon | grep -v grep | grep -v asm | awk '{print $9}' | cut -d'_' -f3)

 

# Loop through each instance and delete old archive logs

for SID in $ORACLE_INSTANCES; do

  delete_old_archivelogs $SID

done


Schedule the above script in crontab as per your requirement.

Overview on Oracle Database Licensing

 

Overview on Oracle Database Licensing

1. Perpetual Licenses

Description: Perpetual licenses are a one-time purchase that allows indefinite use of the software. This type of license is ideal for organizations looking for a long-term solution without the need for recurring payments.

Best For:

  • Companies with stable, long-term database needs.
  • Organizations with the budget to make a significant initial investment.

Key Point: While the initial cost is high, there are no ongoing fees, making it cost-effective over time. However, maintenance and support fees may still apply.

Considerations:

  • Initial Investment: Be prepared for a significant upfront cost.
  • Maintenance: Plan for annual maintenance fees, typically around 22% of the license cost.
  • Scalability: Ensure the license can accommodate future growth without excessive additional costs.

2. Term Licenses

Description: Term licenses are temporary, usually ranging from one to five years. They are more affordable upfront compared to perpetual licenses but need to be renewed at the end of the term.

Best For:

  • Short-term projects.
  • Organizations with limited budgets.

Key Point: Lower initial cost, but recurring payments are required. It provides flexibility for short-term needs without a long-term commitment.

Considerations:

  • Renewal Costs: Plan for recurring costs at each renewal.
  • Project Duration: Align the license term with the project timeline.
  • Scalability: Evaluate if the term license allows for scaling up or down as needed.

3. Oracle Cloud Licensing

Description: Oracle Cloud Licensing is a subscription-based model for accessing Oracle’s cloud services. This model is flexible and scalable, adapting to the changing needs of the organization.

Best For:

  • Businesses seeking flexibility.
  • Organizations that anticipate growth or changes in usage.

Key Point: Monthly or annual fees with the ability to scale resources up or down. Ideal for businesses looking to avoid large upfront investments and wanting the ability to pay-as-you-go.

Considerations:

  • Subscription Fees: Understand the pricing structure and what is included.
  • Flexibility: Take advantage of the ability to scale resources according to demand.
  • Service Level Agreements (SLAs): Review Oracle’s SLAs to ensure they meet your business needs.

4. Licensing Metrics

Processor Licensing: Licensing is based on the number of processors in the server. The cost is determined by multiplying the number of cores by a factor specified in Oracle’s core factor table.

Best For:

  • High-performance environments.
  • Large-scale deployments with significant processing power requirements.

Considerations:

  • Core Factor Table: Use Oracle’s core factor table to calculate the required licenses accurately.

Oracle Processor Core Factor Table

Last Updated:  March 5, 2023

 

Vendor and Processor

Core Processor
Licensing Factor

Sun and Fujitsu UltraSPARC T1 processor (1.0 or 1.2 GHz)
Only named servers including:
Sun Fire T1000 Server, SPARC Enterprise T1000 Server*, with 6 or 8-core 1.0 GHz UltraSPARC T1 processor
Sun Fire T2000 Server, SPARC Enterprise T2000 Server*, with 4, 6, or 8-core
1.0 GHz, or 8 core 1.2 GHz UltraSPARC T1 processor

0.25

Sun Netra T2000, 1.0 or 1.2 GHz UltraSPARC T1 processor

0.25

SPARC T3 processor

0.25

Sun and Fujitsu UltraSPARC T1 1.4 GHz
Only named servers including:
Sun Fire T2000 Server and SPARC Enterprise T2000 Server*, with 8-core, 1.4 GHz UltraSPARC T1 processor

0.5

Sun T6300, 1.4 GHz UltraSPARC T1 processor

0.5

AMD EPYC™ 7XX1 and AMD Opteron™ Models 13XX, 23XX, 24XX, 32XX, 41XX, 42XX, 43XX, 61XX, 62XX, 63XX, 83XX, 84XX or earlier Multicore chips

0.5

Intel® Xeon® Platinum 81XX, Intel® Xeon® Gold 61XX, Intel® Xeon® Gold 51XX, Intel® Xeon® Silver 41XX, Intel® Xeon® Bronze 31XX, Intel Xeon  Series 56XX, Series 65XX, Series 75XX, Series E7-28XX, E7-28XX v2, Series E7-48XX, E7-48XX v2, E7-48XX v3, E7-48XX v4, Series E7-88XX, E7-88XX  v2, E7-88XX v3, E7-88XX v4, Series E5-24XX, E5-24XX v2, E5-24XX v3, Series E5-26XX,  E5-26XX v2,  E5-26XX v3, E5–26XX v4, Series E5-46XX, E5- 46XX v2, E5-46XX v3, E5-46XX v4, E3-15XX v5, Series E3-12XX, E3-12XX v2, E3-12XX v3, E3-12XX v4, E3–12XX v5, E5-14XX v3, E5-14XX v2, E5-16XX v4,
E5-16XX v3, E5-16XX v2, and E5-16XX or earlier Multicore chips

0.5

Intel Itanium Series 93XX or earlier Multicore chips (For servers purchased prior to Dec 1st, 2010)

0.5

Intel or AMD Desktop, Laptop/Notebook, or Netbook Multicore chips

0.5

Sun UltraSPARC T2+

0.5

SPARC64 VII+

0.5

SPARC64 X, SPARC64 X+, SPARC64 XII

0.5

SPARC T4 processor

0.5

SPARC T5

0.5

SPARC M5, SPARC M6, SPARC M7,  SPARC M8

0.5

SPARC S7

0.5

 

Sun and Fujitsu SPARC64 VI, VII

0.75

Sun UltraSPARC IV, IV+, or earlier Multicore chips

0.75

Sun UltraSPARC T2

0.75

HP PA-RISC

0.75

IBM POWER5+ or earlier Multicore chips

0.75

All Single Core Chips

1.0

Intel Itanium Series 93XX  (For servers purchased on or after Dec 1st, 2010)

1.0

Intel Itanium Series 95XX

1.0

IBM POWER6

1.0

IBM POWER7, IBM POWER7+

1.0

IBM POWER8

1.0

IBM System z (z10 and earlier)

1.0

All Other Multicore chips

1.0

* SPARC Enterprise T1000 and SPARC Enterprise T2000 Servers may be sold and branded by Oracle, Sun Microsystems, Fujitsu or Fujitsu Siemens.

 

 

  • Server Configuration: Assess the server’s configuration to ensure compliance with licensing requirements.
  • Cost Efficiency: Compare the cost against the Named User Plus model to determine the most cost-effective approach.

Named User Plus Licensing (NUP): Licenses are based on the number of users or devices accessing the database. This model is beneficial for environments with a limited number of users.

Best For:

  • Smaller user bases.
  • Environments with clearly defined user groups.

Considerations:

  • User Count: Accurately count the number of users or devices to avoid over-licensing.
  • Growth Potential: Plan for potential increases in user count and the associated costs.
  • Compliance: Ensure compliance by maintaining accurate records of user access.

5. Choosing the Right License

Assess Needs: Evaluate your organization’s current and future database requirements. Consider factors such as user base size, database workload, and projected growth.

Scalability: Choose a license that accommodates potential growth without incurring excessive additional costs. Consider both horizontal (adding more servers) and vertical (increasing server capacity) scaling.

User Base: Determine whether processor or user-based licensing is more cost-effective. For environments with a small number of users, Named User Plus licensing may be more economical.

Considerations:

  • Future Growth: Ensure the chosen licensing model can scale with your organization.
  • Budget Constraints: Balance the initial investment with ongoing costs to fit your budget.
  • Licensing Flexibility: Consider if you might need to switch between licensing models as your needs change.

6. Cost Management Strategies

Regular Audits: Conduct regular audits to track database usage and ensure compliance. This helps avoid over-licensing and underutilization, optimizing costs.

Oracle Tools: Use Oracle’s tools for compliance checks and cost management. These tools can provide insights into usage patterns and help identify opportunities for cost savings.

Negotiation: Engage with Oracle representatives to negotiate better terms. Seek advice from licensing experts to ensure you get the best possible deal.

Considerations:

  • Audit Frequency: Schedule regular audits to keep track of changes in usage.
  • Compliance: Use Oracle’s tools to ensure compliance and avoid penalties.
  • Professional Advice: Consult with licensing experts to optimize costs and terms.

7. Best Practices for Oracle Licensing

Maintain Detailed Records: Keep thorough documentation of all licenses and their usage. This aids in audits and compliance checks, ensuring accurate tracking of license deployment.

Stay Informed: Regularly update your knowledge of Oracle’s licensing policies and any changes. Follow Oracle’s official communications and updates to stay compliant.

Consult Professionals: Engage with Oracle licensing experts or third-party consultants. Professional advice can help ensure compliance and optimize licensing costs, providing tailored solutions for your organization.

Considerations:

  • Documentation: Maintain comprehensive records of all licensing agreements and usage.
  • Policy Updates: Stay updated on any changes in Oracle’s licensing policies.
  • Expert Consultation: Regularly consult with professionals to stay compliant and optimize costs.

Conclusion

Understanding and managing Oracle Database licensing is essential for organizations using Oracle products. By selecting the appropriate license type, using the right metrics, and implementing best practices, organizations can ensure compliance and optimize their investments. For detailed information and professional advice, consult Oracle’s official documentation or speak with an Oracle representative.

 

crsctl stats output showing extra cluster resource

For one of our customer, crsctl stat output was showing extra cluster resource.

-bash-5.2$ crsctl stat res -t

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

Name           Target  State        Server                   State details

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

Local Resources

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

ora.LISTENER.lsnr

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.net1.network

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.ons

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.proxy_advm

               OFFLINE OFFLINE      db1                      STABLE

               OFFLINE OFFLINE      db2                      STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

      3        ONLINE  OFFLINE                               STABLE

ora.DATA.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       db2                      STABLE

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       db1                      STABLE

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       db1                      STABLE

ora.RECO.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.REDO.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.asm(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      Started,STABLE

      2        ONLINE  ONLINE       db2                      Started,STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

      3        OFFLINE OFFLINE                               STABLE

ora.cvu

      1        ONLINE  ONLINE       db1                      STABLE

ora.db1.vip

      1        ONLINE  ONLINE       db1                      STABLE

ora.db2.vip

      1        ONLINE  ONLINE       db2                      STABLE

ora.qosmserver

      1        ONLINE  ONLINE       db1                      STABLE

ora.prod.db

      1        ONLINE  ONLINE       db1                      Open,HOME=/u01/app/o

                                                             racle/product/19.3.0

                                                             /dbhome_1,STABLE

      2        ONLINE  ONLINE       db2                      Open,HOME=/u01/app/o

                                                             racle/product/19.3.0

                                                             /dbhome_1,STABLE

ora.scan1.vip

      1        ONLINE  ONLINE       db2                      STABLE

ora.scan2.vip

      1        ONLINE  ONLINE       db1                      STABLE

ora.scan3.vip

      1        ONLINE  ONLINE       db1                      STABLE

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

 

 

However this was 2 node cluster and the output as we can see was showing 3 resources which was causing confusion. After checking MOS, as per Doc ID 2341248.1,

It is an expected behavior in 12.2,with Oracle Flex ASM, you can consolidate all the storage requirements into a single set of disk groups. All these disk groups are mounted by and managed by a small set of Oracle ASM instances running in a single cluster.  Irrespective of number of nodes in the cluster, only three ASM instances will be created

 

 

Solution:-

To disable it run the following.

-bash-5.2$ srvctl config asm

ASM home: <CRS home>

Password file: +DATA/orapwASM

Backup of Password file: +DATA/orapwASM_backup

ASM listener: LISTENER

ASM instance count: 3

Cluster ASM listener: ASMNET1LSNR_ASM

 

 

-bash-5.2$ srvctl modify asm -count all

 

 

-bash-5.2$ crsctl stat res -t

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

Name           Target  State        Server                   State details

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

Local Resources

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

ora.LISTENER.lsnr

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.net1.network

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.ons

               ONLINE  ONLINE       db1                      STABLE

               ONLINE  ONLINE       db2                      STABLE

ora.proxy_advm

               OFFLINE OFFLINE      db1                      STABLE

               OFFLINE OFFLINE      db2                      STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

ora.DATA.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       db2                      STABLE

ora.LISTENER_SCAN2.lsnr

      1        ONLINE  ONLINE       db1                      STABLE

ora.LISTENER_SCAN3.lsnr

      1        ONLINE  ONLINE       db1                      STABLE

ora.RECO.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

ora.REDO.dg(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

ora.asm(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      Started,STABLE

      2        ONLINE  ONLINE       db2                      Started,STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

      1        ONLINE  ONLINE       db1                      STABLE

      2        ONLINE  ONLINE       db2                      STABLE

ora.cvu

      1        ONLINE  ONLINE       db1                      STABLE

ora.db1.vip

      1        ONLINE  ONLINE       db1                      STABLE

ora.db2.vip

      1        ONLINE  ONLINE       db2                      STABLE

ora.qosmserver

      1        ONLINE  ONLINE       db1                      STABLE

ora.prod.db

      1        ONLINE  ONLINE       db1                      Open,HOME=/u01/app/o

                                                             racle/product/19.3.0

                                                             /dbhome_1,STABLE

      2        ONLINE  ONLINE       db2                      Open,HOME=/u01/app/o

                                                             racle/product/19.3.0

                                                             /dbhome_1,STABLE

ora.scan1.vip

      1        ONLINE  ONLINE       db2                      STABLE

ora.scan2.vip

      1        ONLINE  ONLINE       db1                      STABLE

ora.scan3.vip

      1        ONLINE  ONLINE       db1                      STABLE

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

 

As we can see, now the number of resources are showing correct.