Atikh's DBA blog
  • Home
  • Oracle
  • MySQL
  • MongoDB
  • PostgreSQL
  • Snowflake
  • About Me
  • Contact Us
Showing posts with label Oracle 12c. Show all posts
Showing posts with label Oracle 12c. Show all posts

Oracle ASM Scrubbing

 Atikh Shaikh     oracle, Oracle 12c     No comments   

What is ASM Scrubbing?

 

ASM scrubbing is a process used to identify and repair data corruption issues within an ASM disk group. ASM Scrubbing scans the data stored in the disk group and checks for inconsistencies or corruption. The process typically involves reading each data block, comparing it to expected values, and correcting errors by relying on redundancy mechanisms.

 

The Importance of ASM Scrubbing

 

1. Data Integrity

2. Performance Optimization

3. Redundancy Assurance

4. Preventing Data Loss

5. Proactive Health Monitoring

 

How ASM Scrubbing Works

 

ASM scrubbing uses the redundancy features inherent in the ASM system to detect and correct errors.

The process typically works as follows:

 

1. Scanning Data Blocks: ASM scrubbing scans all the data blocks in a disk group, checking for any signs of corruption, such as bit errors or inconsistencies between mirrored copies.

 

2. Verifying Data Integrity: It cross-references each block with its mirror or redundant copy. If a mismatch is found between the blocks, it flags this as a potential error.

 

3. Correcting Errors: If errors are found, ASM attempts to correct them by retrieving data from the redundant copy (if available). For example, in a mirrored setup, if one disk’s data is corrupted, the data from the other disk is used to restore the correct information.

 

4. Logging and Reporting: During the scrubbing process, any identified errors are logged and reported to the system administrators. This allows administrators to monitor and assess the system’s health over time.

 

5. Automated vs. Manual Scrubbing: While ASM scrubbing can be configured to run automatically on a scheduled basis, it can also be manually initiated if needed. This flexibility allows system administrators to choose when to run the scrub, depending on system load and maintenance schedules.

 

Below commands can be used to perform scrubbing

ALTER DISKGROUP Dg_NAME SCURB POWER LOW;

ALTER DISKGROUP Dg_NAME SCURB FILE 'file-name' REPAIR POWER HIGH FORCE;

ALTER DISKGROUP Dg_NAME SCURB DISK Disk-name  REPAIR POWER HIGH FORCE;

 

REPAIR - this option allows automatic repairs disk corruptions; progress can be seen using V$ASM_OPERATION view in the database

 


Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

ORACLE : Creating a new pluggable database using PDB$SEED

 Atikh Shaikh     oracle, Oracle 12c, oracle19c     No comments   

As a database administrator, you might come across creating a pluggable database, here is the method to create a pluggable database (PDB), Follow the below steps to create a pluggable database using the default pluggable database PDB$SEED

Step 1: Connect to the Container database using admin privileges

 

[oracle@localhost Desktop]$ sqlplus sys/***@free as sysdba

 

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Sun Mar 24 06:50:54 2024

Version 23.2.0.0.0

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

 

Connected to:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Version 23.2.0.0.0

 

 

 

Step 2 : Check existing container databases including pluggable databases

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

 

Here we can see one default pluggable database and one application pdb (freepdb1)

Check the container id of each container database, this will help to identify datafiles/temp file etc associated with a particular container database

 

SQL> select name,con_id,dbid from v$containers;

 

NAME              CON_ID       DBID

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

CDB$ROOT                1 1408182090

PDB$SEED                2  871956951  <-- create a new pdb using this

FREEPDB1                3 1423580300

 

Check the datafile and temp file associated with PDB$SEED to identify the path of datafiles for the new database

SQL> select name from v$datafile where con_id=2;

NAME

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

/opt/oracle/oradata/FREE/pdbseed/system01.dbf

/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf

/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf

                              

SQL> select name from v$tempfile where con_id=2;

NAME

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

/opt/oracle/oradata/FREE/pdbseed/temp01.dbf

 

 

Step 3: Create a directory for new pluggable database datafiles

 

[oracle@localhost Desktop]$

[oracle@localhost Desktop]$ mkdir -p /opt/oracle/oradata/FREE/pdb2

[oracle@localhost Desktop]$

 

 

Step 4 : Create a pluggable database

 

SQL> create pluggable database PDB2 admin user pdb2_user identified by "test123"

default tablespace PDB2_DEFAULT

datafile '/opt/oracle/oradata/FREE/pdb2/PDB2_DEFAULT.dbf' size 100m autoextend on

file_name_convert=('/opt/oracle/oradata/FREE/pdbseed','/opt/oracle/oradata/FREE/pdb2');  2    3    4 

 

Pluggable database created.

 

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

       4 PDB2                   MOUNTED

 

Step 5: Open pluggable database

 

SQL> alter pluggable database PDB2 open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

       2 PDB$SEED               READ ONLY  NO

       3 FREEPDB1               READ WRITE NO

       4 PDB2                   READ WRITE NO

 

Here we can see PDB2 pluggable database has been created successfully.

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Oracle : TNSNAMES.ORA file

 Atikh Shaikh     oracle, Oracle 12c     No comments   

In Oracle, the tnsnames.ora file important configuration file that is used in Oracle database connection's

default location of tnsnames.ora file is $ORACLE_HOME/network/admin but it can be modified or kept at other locations as well and create soft links at the default location. while using a location other than the default location, we need to use the TNS_ADMIN parameter to load settings while starting the listener of the database.


In case you are not sure about the location of tnsnames.ora file, you can execute below commands on Unix shell


$env |grep ORACLE_HOME


or use below command 


echo $ORACLE_HOME


Syntax used in tnsnames.ora file


testdb.domain.com

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =  HOSTNAME.DOMAIN.COM) (PORT = 1521) 

)

(CONNECT_DATA =)

    ( SERVICE_NAME=tstdb.domain.com)

)

This file can be modified very easily, take backup before  modifying anything in this file

on Unix use vi editor to open the file and change anything we want then save using :wq

On Windows, one can use Notepad editor and save using ctrl+S or the save option

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Want to Convert number to text/word form? Here is the solution

 Atikh Shaikh     Oracle 12c, oracle 19c     No comments   

Oracle Database has great functionality that converts any number to its word format, suppose number "2 "you want to write it as "two", you can do it using Oracle database. you may not need it for single double, triple, or quadruple numbers but what if your number is big and it makes you lazy to read it or convert it into Word format, Here is the solution.

Make use of a dummy table dual along with a few other functions such as to_char and to_date, Here is the exact query to convert your number 3434590 into word or text format

SQL> select to_char(to_date(3434590,'j'),'jsp') from dual;

TO_CHAR(TO_DATE(3434590,'J'),'JSP')

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

three million four hundred thirty-four thousand five hundred ninety


Where J stands for Jubilian date and which starts from 1 to 5373484 and JSP stands for Julian dates SPelled(SP)

as there is no direct function in oracle to convert number to words, you can try this method. Here is another simple example

oracle convert number to word



Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Oracle : Database Links - Create, Use and Drop

 Atikh Shaikh     12c, oracle, Oracle 12c, oracle19c     No comments   

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

  • Public 
  • Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users


 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

 

Database

Schema

Connection link

Source details

technodb

tech_owner

technodb

Target details

asgard

tech_as_owner

asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.

 

Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit

Flashback query in oracle

 Atikh Shaikh     oracle, Oracle 12c, oracle 19c     No comments   

You must have heard about the time machine in movies or magazines, just think of an oracle providing the same feature with its databases, and that is called a flashback. 

We can go back in time to query data from the table, of course, there are certain limits to it, but it’s possible, we will learn this with an example. 

Here is the table employee, with 8 rows 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

and this particular snapshot is captured at the below timings 

 

SQL> select sysdate from dual;

 

SYSDATE

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

2022-07-21 23:37:12

 

Now, deleted a couple of rows using the below query

 

SQL> delete from employee where emp_id>305;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

now we can see number of rows in the table 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         6

 

so basically, 2 rows are deleted from the table, what if you want to read those 2 deleted rows, you have the option to use the flashback query option, below is the exact format to achieve it

Check the number of rows based on timestamp, as we saw we had 8  rows preset in the table at 23:37 but later we deleted them, based on this timing, check the number of rows

 

SQL> select count(*) from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');

 

  COUNT(*)

----------

         8

 

see here, we were able to fetch data based on the flashback option, this is just reading, what if you want to insert data back to the table, use the below commands

 

SQL> insert into employee select * from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS') where emp_id>305;

 

2 rows were created.

 

SQL> commit;

 

Commit complete.

 

now check the count in the table, you will be surprised to see, that data is inserted back to the table.

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

 


Read More
  • Share This:  
  •  Facebook
  •  Twitter
  •  Instagram
  •  Pin
  •  linkedin
  •  reddit
Older Posts Home

Author

Atikh Shaikh
View my complete profile

Categories

  • MongoDB (18)
  • Oracle 12c (30)
  • Oracle12cR2 New Feature (3)
  • PostgreSQL (20)
  • RMAN (10)
  • Snowflake (8)
  • mysql (23)
  • oracle (74)

Blog Archive

  • ►  2018 (38)
    • ►  November (25)
    • ►  December (13)
  • ►  2019 (33)
    • ►  January (15)
    • ►  February (6)
    • ►  March (2)
    • ►  April (5)
    • ►  May (5)
  • ►  2020 (5)
    • ►  April (1)
    • ►  May (2)
    • ►  July (2)
  • ►  2021 (8)
    • ►  June (3)
    • ►  July (3)
    • ►  August (1)
    • ►  December (1)
  • ►  2022 (33)
    • ►  May (3)
    • ►  June (10)
    • ►  July (3)
    • ►  August (4)
    • ►  September (8)
    • ►  October (3)
    • ►  November (2)
  • ►  2023 (14)
    • ►  February (1)
    • ►  April (5)
    • ►  May (2)
    • ►  June (1)
    • ►  September (1)
    • ►  October (1)
    • ►  December (3)
  • ►  2024 (5)
    • ►  January (2)
    • ►  March (3)
  • ▼  2025 (6)
    • ►  March (1)
    • ►  April (3)
    • ▼  May (2)
      • Oracle 23ai : The all new Hybrid Read-Only for plu...
      • Oracle Active Data Guard Features and Benefits

Popular Posts

  • ORA-29283: invalid file operation: unexpected "LFI" error (1509)[29437]
    I was trying to export the schema in my windows PC, it got stuck with below error    C:\Users\shaik\Videos\technodba exp>expdp userid...
  • PostgreSQL : How to get data directory location for PostgreSQL instance
    Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know th...
  • Oracle Dataguard Broker Configuration (DGMGRL)
    Data Guard Broker is a command-line interface that makes managing primary and standby databases easy. DBA can use a single command to switch...
  • ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
    In previous articles, we have learned about user creation and grants  in MySQL in detail, but there are a few privileges called global priv...
  • Oracle 23ai : Use of NOVALIDATE Constraints in IMPDP
    While performing impdp operations in the Oracle database, Oracle performs validation checks for every constraint on the imported table, that...

Labels

oracle Oracle 12c mysql PostgreSQL MongoDB oracle 19c Oracle23c oracle19c Orale PDB-CDB oracle12c python AWS Oracle ASM Virtualbox pluggable database storage engine

Pages

  • Disclaimer
  • Privacy Policy

Follow TechnoDBA

Copyright © Atikh's DBA blog | Powered by Blogger