EXTERNAL TABLE

INTRODUCTION:

In this blog, we are going to create the external table

DESIGNATION:

This is easy to make and easy to insert the data quickly.
This is much faster than SQL * LOADER.
In this type of table, we cannot use a DML statement.
Bulk data can be easily inserted into the table.

OVERALL STEPS:

Step 1: Go to make a directory at the OS level

Step 2: After making the directory

Go to the directory, we have to create a text file and add the necessary values.

Step 3: We have to notify the location of the directory.

Step 4: Go to database, conn sys user

We have to permit for creation of a directory in that user by using the sys

Step 5: Create the directory and mention the location of the OS.

Step 6: Conn sys user,

We have to permit reading and writing to create a directory by using sys.

Step 7: Create the table and give the external format

Step 8: View the table

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

Below error i got while taking RMAN backup:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/12/2021 03:29:07
RMAN-06183: datafile or datafile copy +ORA_DATA/wwibetest_bosqeumcsdb/datafile/undotbs1.779.1077535587 (file number 3) larger than MAXSETSIZE

Finding and solution:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 30 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/wwi/wwdb/db/oracle/product/11.2.0.4/db_1/dbs/snapcf_WWIBEMCS.f’; # default

RMAN> CONFIGURE MAXSETSIZE TO 35 G;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 35 G;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 35 G;

Now I ran the RMAN backup it went successful.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

RMAN Database Restore ASM

RMAN Database Restore ASM

Description:
In this blog, we are going to see take RMAN backup in asm storage and restore the database to another server.

RMAN Restore ASM:
Take the backup using RMAN in source host copy the backup into destination host, using that backup files to restore the database.

Overall Steps:

Source server:
1. Backup Database using RMAN
2. Create pfile from spfile
3. Copy the backup files to the destination server

Destination server:
1. Edit pfile
2. Create Required Folders in destination server
3. Add the database entry in oratab
4. Startup using pfile Nomount stage
5. Restore control file
6. Mount database
7. Catalog backup pieces
8. Restore and recover the database
9. Change dbname/DBID uisng NID
10. verify the database name and id.

Source Server:

Step 1: Take backup using RMAN:

Create backup directory:

Backup script:
run {
allocate channel t1 type disk;
allocate channel t1 type disk;
allocate channel t1 type disk;
backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
release channel t1;
}
sql ‘alter system archive log current’;
run {
allocate channel a1 type disk;
backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
release channel c1;
}
exit

Connect RMAN and execute the script:

[oracle@asm ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:01:54 2022
Version 19.3.0.0.0

connected to target database: ORAASM (DBID=1977555372)

RMAN> @backup.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> allocate channel t3 type disk;
5> backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
6> release channel t1;
7> release channel t2;
8> release channel t3;
9> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=78 device type=DISK

allocated channel: t2
channel t2: SID=89 device type=DISK

allocated channel: t3
channel t3: SID=88 device type=DISK

Starting backup at 21-JAN-22
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORAASM/DATAFILE/system.257.1092813819
channel t1: starting piece 1 at 21-JAN-22
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORAASM/DATAFILE/sysaux.258.1092813853
input datafile file number=00014 name=+DATA/ORAASM/DATAFILE/tblspace.281.1093044531
input datafile file number=00007 name=+DATA/ORAASM/DATAFILE/users.260.1092813869
channel t2: starting piece 1 at 21-JAN-22
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869
input datafile file number=00015 name=+DATA/ORAASM/DATAFILE/test.282.1094538795
input datafile file number=00013 name=+DATA/ORAASM/DATAFILE/tblspace.280.1093044407
channel t3: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_080jqqo1_8 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:16
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375
input datafile file number=00011 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_060jqqns_6 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:01:10
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_070jqqns_7 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:01:13
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375
input datafile file number=00012 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459
channel t2: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_090jqqop_9 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:49
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0a0jqqq2_10 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:12
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0d0jqqqg_13 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0b0jqqq9_11 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0c0jqqqf_12 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:26
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544235.283.1094544237 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: t1

released channel: t2

released channel: t3

RMAN> sql ‘alter system archive log current’;
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=78 device type=DISK

Starting backup at 21-JAN-22
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=1094544242
input archived log thread=1 sequence=17 RECID=2 STAMP=1094544243
channel a1: starting piece 1 at 21-JAN-22
channel a1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/arch_ORAASM_0f0jqqrk_15 tag=TAG20220121T080403 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544247.289.1094544249 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=78 device type=DISK

Starting backup at 21-JAN-22
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-JAN-22
channel c1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/Control_ORAASM_0h0jqqrs_17 tag=TAG20220121T080412 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544256.290.1094544257 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: c1

RMAN> exit

Recovery Manager complete.

Check the backup file location:

Step 2: Create pfile from spfile

create pfile=’/home/oracle/initasmora.ora’ from spfile;
Step 3: Copy the backup file into destination server

[oracle@asm backup]$ scp * [email protected]:/u01/backup/

Destination server:

Step 1: Edit pfile
Change DB_UNIQUE_NAME in the pfile:

*.audit_file_dest=’/u01/app/oracle/admin/oraasm/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’+DATA/ASMORA/CONTROLFILE/current.262.1092813933′,’+DATA/ASMORA/CONTROLFILE/current.261.1092813933′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_name=’oraasm’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=12732m
*.db_unique_name=’asmora’ —–>change db unique name
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraasmXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_ORAASM’
*.open_cursors=300
*.pga_aggregate_target=629m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1886m
*.undo_tablespace=’UNDOTBS1′
[oracle@asm dbs]$

Step 2: Create Required Folders

Create required folders in the destination server
[oracle@asmnew ~]$ mkdir -p /u01/app/oracle/admin/ASMORA/adump/

Step 3: Add the Database entry in oratab file.

ASMORA:/u01/app/oracle/product/19.0.0/dbhome_1:N

Step 4: startup database using pfile nomount stage

[oracle@asmnew asmora]$ export ORACLE_SID=asmora
[oracle@asmnew asmora]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jan 21 08:18:06 2022
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/asmora/initasmora.ora’;
ORACLE instance started.

Total System Global Area 1979709696 bytes
Fixed Size 9136384 bytes
Variable Size 452984832 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7639040 bytes

Step 5: restore control file from backup location

[oracle@asmnew asmora]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:22:04 2022
Version 19.3.0.0.0

connected to target database: ORAASM (not mounted)

RMAN> restore controlfile from ‘/u01/share/backup/Control_ORAASM_0h0jqqrs_17’;

Starting restore at 21-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/ORAASM/CONTROLFILE/current.262.1092813933
output file name=+DATA/ORAASM/CONTROLFILE/current.261.1092813933
Finished restore at 21-JAN-22

RMAN>

Step 6: Mount the database.

SQL> alter database mount;

Database altered.

SQL> select name, open_mode, db_unique_name from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME

ORAASM MOUNTED asmora

Step 7: Catalog backup files

CATALOG BACKUPPIECE ‘/u01/asmora/arch_ORAASM_0f0jqqrk_15’;
CATALOG BACKUPPIECE ‘/u01/asmora/Control_ORAASM_0h0jqqrs_17’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_030jqo9c_3’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_060jqqns_6’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_070jqqns_7’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_080jqqo1_8’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_090jqqop_9’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0a0jqqq2_10’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0b0jqqq9_11’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0c0jqqqf_12’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0d0jqqqg_13’;

Step 8: Restore and recover database

List backup archivelogs

RMAN> list backup of archivelog all;

recover database using rman

run
{
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
ALLOCATE CHANNEL d2 DEVICE TYPE disk;
set newname for datafile 1 to ‘+DATA’;
set newname for datafile 2 to ‘+DATA’;
set newname for datafile 3 to ‘+DATA’;
set newname for datafile 4 to ‘+DATA’;
set newname for datafile 5 to ‘+DATA’;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_3.268.1092813965”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_2.266.1092813951”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_1.265.1092813949”
to ”+DATA”” ;
SET UNTIL SEQUENCE 12; <— 11+1
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

check logfile and database role

Step 9:change database name and ID

modify pfile
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

add this controlfile entry to pfile

SQL> startup nomount pfile='initasmora.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount;

Database altered.

SQL>

set db name and id:

[oracle@asm asmora]$ . oraenv
ORACLE_SID = [asmora] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@asm asmora]$ nid target=sys dbname=asmora

DBNEWID: Release 19.0.0.0.0 – Production on Fri Jan 21 09:09:50 2022

Password:
Connected to database ORAASM (DBID=1977555372)

Connected to server version 19.3.0

Control Files in database:
+DATA/ASMORA/CONTROLFILE/current.262.1092813933
+DATA/ASMORA/CONTROLFILE/current.261.1092813933

Change database ID and database name ORAASM to ASMORA? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1977555372 to 65466533
Changing database name from ORAASM to ASMORA
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – modified
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – modified
Datafile +DATA/ORAASM/DATAFILE/system.257.109281381 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/sysaux.258.109281385 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/undotbs1.259.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.109281441 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/users.260.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.109281545 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.280.109304440 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.281.109304453 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/test.282.109453879 – dbid changed, wrote new name
Datafile +DATA/ORAASM/TEMPFILE/temp.269.109281404 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CB418C2B91B66E053867EA8C0C5A0/TEMPFILE/temp.273.109281445 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/TEMPFILE/temp.278.109281541 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – dbid changed, wrote new name
Instance shut down

Database name changed to ASMORA.
Modify parameter file and generate a new password file before restarting.
Database ID for database ASMORA changed to 65466533.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

[oracle@asm asmora]$

Step 10: check the DB name and id

SQL> alter database open resetlogs;

Database altered.

SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME DBID

ASMORA READ WRITE asmora 65466533

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Oracle Database Restore Point

ORACLE DATABASE RESTORE POINT

Description : In this blog, we are going to see the Oracle database restore point and its types with the demo. Normal Restore Point:

A normal restore point enables you to flash the database back to a restore point within the time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
A normal restore point can be dropped explicitly.
The control files stores name of the restore point and the SCN.

Guaranteed restore point:

A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
Guaranteed restore point must be dropped explicitly by the user using the DROP RESTORE POINT command.
Guaranteed restore point never ages out.

Normal Restore Point Demo: Must DB in archive log mode. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Check available restore point: SQL> select name from v$restore_point; no rows selected Create a normal restore point: SQL> create restore point res_test; Restore point created. View the created restore point: SQL> select name from v$restore_point; NAME ——————– RES_TEST After the creation of the restore point creates a table. SQL> create table test as select * from dba_users; Table created. Check the RVWR process status. In normal restore point no background process are started. [oracle@primary ~]$ ps -ef |grep -i rvwr oracle 16948 14963 0 09:40 pts/2 00:00:00 grep –color=auto -i rvwr Flashback the restore point: SQL> flashback database to restore point res_test; Flashback complete. Guaranteed Restore Point Demo: Create guarantee restore point: SQL> create restore point res_test1 guarantee flashback database; Restore point created. RVWR back ground process can be started automatically. [oracle@primary ~]$ ps -ef |grep -i rvwr oracle 17014 1 0 09:41 ? 00:00:00 ora_rvwr_orcl oracle 17060 14963 0 09:42 pts/2 00:00:00 grep –color=auto -i rvwr [oracle@primary ~]$ Check the flashback status: SQL> select flashback_on from v$database; FLASHBACK_ON —————— RESTORE POINT ONLY Automatically create flashback logs: [oracle@primary ORCL]$ cd flashback/ [oracle@primary flashback]$ ls o1_mf_jtotn2c3_.flb o1_mf_jtotn8d4_.flb Drop restore point the logs will be removed. SQL> drop restore point res_test1; Restore point dropped. [oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL/flashback [oracle@primary flashback]$ ls [oracle@primary flashback]$ Bounce database and Flashback database to restore point: SQL> flashback database to restore point res_test2; Flashback complete. Reset the logs: SQL> alter database open resetlogs; Database altered. SQL> select count(*) from test1; COUNT(*) ———- 45 Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Wait Events And Their Solutions

Oracle Wait Events And Their Solutions.

Buffer Busy Wait:

This wait event happens when a session tries to access a block in the buffer cache but it can't because the buffer is busy, that is another session is modifying the block and the contents of the block are in flux.

Buffer Busy Wait:

SQL> SELECT s.sql_hash_value, sw.p1 file#, sw.p2 block#, sw.p3 reason
FROM v$session_wait sw, v$session s
WHERE sw.event = 'buffer busy waits'
AND sw.sid = s.sid; 

no rows selected

SQL> SELECT  owner , segment_name , segment_type
FROM  dba_extents
WHERE  file_id = &FileNumber
AND  &BlockNumber BETWEEN block_id AND block_id + blocks -1;  2    3    4
Enter value for filenumber: 1
old   3: WHERE  file_id = &FileNumber
new   3: WHERE  file_id = 1
Enter value for blocknumber: 2
old   4: AND  &BlockNumber BETWEEN block_id AND block_id + blocks -1
new   4: AND  2 BETWEEN block_id AND block_id + blocks -1

no rows selected

Another query that can be very useful is finding the objects in the entire Oracle database that are suffering from "buffer busy waits". The following query gives the top 10 segments:

SQL> SELECT * FROM (
   SELECT owner, object_name, subobject_name, object_type,
          tablespace_name, value
   FROM v$segment_statistics
   WHERE statistic_name='buffer busy waits' and owner not like '%SYS%'
   ORDER BY value DESC)
WHERE ROWNUM <=10;

OWNER                OBJECT_NAME                    SUBOBJECT_NAME                 OBJECT_TYPE        TABLESPACE_NAME                  VALUE
-------------------- ------------------------------ ------------------------------ ------------------ ------------------------------ ----------
GSMADMIN_INTERNAL    DDLID$                                                        TABLE              SYSAUX                               0
XDB                  XDB$ROOT_INFO                                                 TABLE              SYSAUX                               0
XDB                  XDB$SCHEMA_URL                                                INDEX              SYSAUX                               0

DB File Sequential Read

The db file sequential read wait event has three parameters:
file#, first block#, and block count.
In Oracle Database 11g, this wait event falls under the User I/O wait class. 


The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.

The two important numbers to look for are the TIME_WAITED and AVERAGE_WAIT by individual sessions.

Significant db file sequential read wait time is most likely an application issue.


This event occurs when a user tries to perform a Physical I/O while waiting for sequential reads from the Buffer cache. This type of situation usually occurs when the data on the table is accessed by using index, not full table scan, as a result of single block reading.
If this event occurs,  possible reasons are wrong index usage, index fragmentation, excessive I/O traffic on specific disks. To Solve this problem, Query should use Right index and fragmented indexes should be defragmented with Rebuild Index operation.
When you encounter this wait event, which appears very frequently in AWR and ADDM reports, we cannot always say that there is a problem. However, if this wait event takes place, if the database have ‘Enqueue’ and Latch Free and they are spending too much time, then database should be monitored.

DB File Scattered Read

This wait event occurs getting multiblock of physical blocks that are not physically close to each other (neighbors) into buffer cache Scattered, or during a full scan to the buffer cache. So Db file scattered read is to read multiple blocks I/O during the fast full scan.

A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan. The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other.

Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'.

Direct path Read

 This event occurs when Oracle Instance query data from the Datafiles asynchronously and puts this data into PGA  instead of Buffer Cache in SGA.
This type of event usually occurs during the use of Temporary ( Temp ) Tablespace in the Sorting operations, during the creation of Lob segments, and when multiple sessions Full table scan in parallel.
In order to solve this problem, the memory should be increased, parallel operations should not be done unless required, and pay attention to Lob segments reads.

DB CPU

This event represents the total time spent of the users’ queries on the CPU.  Oracle’s Background processes (SMON, PMON ..) are not included in this total time.
If this value is high, it means that the Oracle instance spends most of the time on the CPU. To reduce this wait event, the SQLs in the SQL ordered by CPU section in the AWR report must be TUNE.

Logfile sync

 This event is known as the time lost as a result of the LGWR process waiting while users initiate a Transaction Commit or Rollback.
If this wait event is available continuously, I/O performance of the LGWR process is probably poor, or Commit is coming too often by the application. The solution to this problem is not to commit too much, if necessary, and to examine the I/O performance of the disk on which the Redo log files are located, and to use a high performance disk such as an SSD disk if necessary.

Enq: TX – row lock contention

 row lock contention:  This type of event occurs when a user session is trying to update or delete a row held by another session, which is an application design problem. Normally, when a transaction is finished, commit or rollback must be executed to release related rows.
The solution to this problem is that if the session that holds the row is active, then execute commit statement, if it is not active, kill the session or execute rollback the session.

ARCH wait on SENDREQ

This wait event is the total time taken by the Archiver Processes to archive the Standby in the Dataguard and to write these archives to the local disks.
The main reason why this value is high is that the archives sent to the Standby side arrive late due to the network. To solve this problem, it is necessary to optimize the Network and set the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file to an optimized value (32767).

Gc current block busy

 This wait event occurs between the nodes of the Cluster database ( Real Application Cluster ). When a transaction requests a block, that request sent to the master instance. Normally, this request is performed by a cache fusion.
However, in some cases, this block transfer is delayed because the corresponding instance is held by the other instance or because the corresponding transaction records cannot be written to the redo logs immediately, in which case this wait event is triggered.
This can be solved by tune the wait event Log Writer process or Solving network problem between Cluster nodes.

Gc cr block busy-wait

ifference is that while the above event is running in current mode, this wait event runs in CR mode. This can be solved by tune the wait event Log Writer process.

Read by Other Session

When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known, there are several alternative methods for solving the issue.
When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

Finding the contentions :
When a session is waiting on the "read by other session" event, an entry will be seen in the v$session_wait system view, which will give more information on the blocks being waited for:
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'read by other session';
If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object. The following query will give the name and type of the object:
SELECT relative_fno, owner, segment_name, segment_type
 FROM dba_extents
 WHERE file_id = &file
 AND &block BETWEEN block_id AND block_id + blocks - 1;

Eliminating contentions:
Depending on the Oracle database environment and specific performance situation the following variety of methods can be used to eliminate contention:
Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information you can obtain from the operating system.
Redistribute data from the hot blocks - Deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:
SELECT data_object_id
 FROM dba_objects
 WHERE owner='&owner' AND object_name='&object';
SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid
 FROM dual;
--rowid for the first row in the block
 SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid FROM dual;
--rowid for the 500th row in the block
 SELECT <column_list>
 FROM <owner>.<segment_name>
 WHERE rowid BETWEEN <start_rowid> AND <end_rowid>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

RUNNING GATHERSTATS

Description:
In this blog, we are going to see what has gathered stats and various levels of gather stats.

What is GATHERSTATS:
To gather stats in oracle we require to use the DBMS_STATS package. It will collect the statistics in parallel with collecting the global statistics for partitioned objects. The DBMS_STATS package is specially used only for optimizer statistics. As I explained in the first paragraph the dbms_stats is very vital for good SQL performance. We require to gather the stats before adjusting or setting up any optimizer parameters in oracle.

The less the query cost the execution time of the query is fast. We must have to gather the statistics on regular basis for database objects to give the best information to the Oracle optimizer to run queries in the best possible time. Using the analysis statement is the traditional way of checking the cost of the query. But nowadays to gather stats in oracle we need to use the DBMS_STATS package.

Gather STATS:

CASCADE => TRUE: Gather statistics on the indexes as well. If not used 
Oracle will determine whether to collect it or not.
DEGREE => 4: Degree of parallelism.
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set 
the sample size % for skew(distinct) values (accurate and faster than 
setting a manual sample size).
METHOD_OPT=> : For gathering Histograms:
FOR COLUMNS SIZE AUTO: You can specify one column between “” 
instead of all columns.
FOR ALL COLUMNS SIZE REPEAT: Prevent deletion of histograms and collect
it only for columns that already have histograms.
FOR ALL COLUMNS: Collect histograms on all columns.
FOR ALL COLUMNS SIZE SKEWONLY: Collect histograms for columns that have 
skewed values should test skewness first
FOR ALL INDEXED COLUMNS: Collect histograms for columns that 
have indexes only.

DATABASE Level:

      Gathering statistics for all objects in the database, the cascade 
will include indexes  

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from 
DBA_TAB_STATISTICS where STALE_STATS='YES';

SQL> exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR 
ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SCHEMA level:

      Gathering statistics for all objects in a schema, the cascade
will include indexes.If not used Oracle will determine whether to 
collect it or not.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS 
where STALE_STATS='YES' and OWNER='&owner;

PL/SQL procedure successfully completed.

SQL> set timing on

SQL> exec dbms_stats.gather_schema_stats(ownname=>'&schema_name', 
CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree =>4);

Enter value for schema_name: vbt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.19

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');

Enter value for schema_name: vbt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

TABLE Level:

          The CASCADE parameter determines whether or not statistics are
 gathered for the indexes on a table.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name');

Enter value for schema_name: vbt

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

SQL> SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from 
DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

Enter value for tname: agent

old   1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from 
DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME'

new   1: SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from 
DBA_TAB_STATISTICSWHERE TABLE_NAME='agent'

no rows selected

Index Statistics:

SQL> CREATE TABLE sam AS SELECT * FROM dba_tables ORDER BY table_name;

Table created.

SQL> CREATE INDEX idsam ON sam(table_name, num_rows);

Index created.


SQL> EXEC dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'sam');

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows,
 i.clustering_factorFROM dba_tables t, dba_indexes i
WHERE t.table_name = i.table_name AND i.index_name='idsam';
TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR
------------------ ------------------- ---------- ----------------- 
sam idsam 46 1705 46

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',
indname =>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

DATABASE INCARNATION USING RMAN

ORACLE DATABASE INCARNATION USING RMAN

Description:-
In this blog, we are going to see the Oracle database incarnation using RMAN backup with the demo.

INCARNATION:-
The current online redo logs are archived, the log sequence number is reset to 1, a new database incarnation is created, and the online redo logs are given a new timestamp and SCN.

Database incarnation falls into the following category:-
Current, Parent, Ancestor, and Sibling:
i) Current Incarnation: The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation: The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation: The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation: Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

Overall steps:-

Backup full database using RMAN
After backup create a table in the database note the SCN number.
Delete and Drop table take SCN number.
Do log switching and check incarnation status.
Bounce the database started in the mount stage.
Connect RMAN and recover the database mention the sequence number
where the table record was deleted.
After the recovery is completed perform resetlogs to open the database.
Connect a database to check incarnation also check the table can be retrieved.
Shut the database and start the mount stage again.
Reset the database incarnation 2 to retrieve the table records mention the
scn where the table was created.
Check record can be retrieved.

Step 1: Backup full database:-

[oracle@test ~]$ . livedb.env
[oracle@test ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043)
RMAN> backup database;
Starting backup at 15-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_system_jmz2qqys_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_undotbs1_jmz2snrh_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_sysaux_jmz2rvhs_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: starting piece 1 at 15-SEP-21
channel ORA_DISK_1: finished piece 1 at 15-SEP-21
piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/
o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp tag=TAG20210915T035943 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 15-SEP-21

Starting Control File and SPFILE Autobackup at 15-SEP-21
piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_15/
o1_mf_s_1083297650_jn28ntoh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-SEP-21
RMAN> exit

Step 2: After backup create a table in the database note SCN number :-

[oracle@test ~]$ . livedb.env
[oracle@vtest ~]$ sqlplus / as sysdba
SQL> create table sample as select * from all_objects;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
———–———–
2146558

Step 3: Delete and Drop table take SCN number:-
Delete the rows in the sample table:
SQL> delete from the sample;
71297 rows deleted.

Get the scn number also,
SQL> select current_scn from v$database;
CURRENT_SCN
———-———–
2146578

Drop the table structure:
SQL> drop table sample;
Table dropped.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
——————–
2146601

Step 4: Do log switching and check incarnation status:-

SQL> alter system switch logfile;
System altered.
Check the incarnation using v$database_incarnation:
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
———— —————–
1 1
2 1920977

Step 5: Bounce the database start in mount stage:-

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size                       8897312 bytes
Variable Size               402653184 bytes
Database Buffers        1275068416 bytes
Redo Buffers                  7876608 bytes
Database mounted.

Step 6: Connect RMAN and recover the database mention the sequence number where the table record was deleted:-

[oracle@test ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043, not open)
RMAN> run{
set until scn=2146578;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 15-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/
backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp 
tag=TAG20210915T035943

channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 15-SEP-21

Starting recover at 15-SEP-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15-SEP-21

Step 7: After the recovery is completed perform resetlogs to open the database:-

RMAN> alter database open resetlogs;
Statement processed
RMAN> exit

Step 8 : Connect database to check incarnation also check the table can be retrieved:-

[oracle@test ~]$ sqlplus / as sysdba
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
————          —————–
               1                        1
               2              1920977
               3              2146579
The table structure is retrieved but the record can be deleted from the scn number stage.
SQL> select * from the sample;
no rows selected

Step 9: Shut the database and start the mount stage again:-

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 10: Reset the database incarnation 2 to retrieve the table records mention the scn where table created:-

RMAN> reset database to incarnation 2;
using target database control file instead of recovery catalog
database reset to incarnation 2

RMAN> run{
set until scn=2146558;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 15-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp 
tag=TAG20210915T035943
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 15-SEP-21

Starting recover at 15-SEP-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file 
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/
o1_mf_1_18_jn28r8l8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/
2021_09_15/o1_mf_1_18_jn28r8l8_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-SEP-21

Perform reset logs method:

RMAN> alter database open resetlogs;
Statement processed
RMAN> exit

Step 11: Check the record can be retrieved from the table:-

[oracle@test ~]$ sqlplus / as sysdba

SQL> select count(1) from sample;

COUNT(1)
———-
71297
SQL>

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

GENARATE AWR REPORT

AWR REPORT:

The Automatic Workload Repository (AWR) collects and maintains statistics of the 
database.
We can generate awr report for a particular time frame in the past using the script 
awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)
script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

step – 1

[oratest@oracle ~]$ export ORACLE_SID=test
[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 11:06:50 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ ONLY

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show user;
USER is "SYS"

step – 2

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:<strong> <span style="color:#ed0911" <br />class="has-inline-color">html</span></strong>
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) <br />report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) <br />report_type from dual

Type Specified:  html

old   1: select '&&report_type' report_type_def from dual
new   1: select 'html' report_type_def from dual



old   1: select '&&view_loc' view_loc_def from dual
new   1: select 'AWR_PDB' view_loc_def from dual



Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 2378581000     TEST                        1 test           test








Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
* 2378581000     1      TEST         test         oracle.local

Using 2378581000 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

test         TEST               646  29 Sep 2021 00:30    1
                                647  29 Sep 2021 01:30    1
                                648  29 Sep 2021 02:30    1
                                649  29 Sep 2021 03:30    1
                                650  29 Sep 2021 04:30    1
                                651  29 Sep 2021 05:30    1
                                652  29 Sep 2021 06:30    1
                                653  30 Sep 2021 00:14    1
                                654  30 Sep 2021 01:30    1
                                655  30 Sep 2021 03:46    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:<strong><span style="color:#a3000d" <br />class="has-inline-color"> </span><span style="color:#ec0b1e" <br />class="has-inline-color">650</span></strong>
Begin Snapshot Id specified: 650

Enter value for end_snap: <strong><span style="color:#e90c17" <br />class="has-inline-color">651</span></strong>
</pre>
<!-- /wp:preformatted -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_650_651.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_1_07_10_2021.html

Using the report name awrrpt_1_07_10_2021.html

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

Analysis Period
---------------
AWR snapshot range from 650 to 651.
Time period starts at 29-SEP-21 04.30.25 AM
Time period ends at 29-SEP-21 05.30.33 AM

Analysis Target
---------------
Database &apos;TEST&apos; with DB ID 2378581000.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance test, numbered 1 and hosted at
oracle.localdomain.

Activity During the Analysis Period
-----------------------------------
Total database time was 0 seconds.
The average number of active sessions was 0.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.

</pre>
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
End of Report
</body></html>
Report written to awrrpt_1_07_10_2021.html

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

PACKAGES REQUIRED FOR AWR

AWR REPORT:

AWR collects database statistics every 60 minutes (on the hour) out of the box 
(this is configurable), and this data is maintained for a week and then purged.
ü The Oracle database uses AWR for problem detection and analysis as well as for 
self-tuning.A number of different statistics are collected by the AWR, 
including wait events, time model statistics, active session history statistics, 
various system- and session-level statistics, object usage statistics, and 
information on the most resource-intensive SQL statements.
ü To properly collect database statistics, set the initialization parameter 
STATISTICS_LEVEL to TYPICAL (the default) or ALL.
ü The AWR consists of a number of tables owned by the SYS schema and typically stored 
in the SYSAUX tablespace (currently no method exists that I know of to move these
 objects to another tablespace).
Recommendations before getting an AWR Report :

Collect Multiple AWR Reports

It’s always good to have two AWR Reports, one for good time (when database was 
performing well), second when performance is poor. This way Remote DBA can easily 
compare good and bad report to find out the culprit.

Stick to Particular Time

“Database is performing slow” will not help anymore to resolve performace issues. 
We have to have a specific time like Database was slow yesterday at 1 Pm and 
continue till 4Pm. Here, DBA will get a report for these three hours.

Split Large AWR Report into Smaller Reports

Instead of having one report for long time like one report for 4hrs. it’s
 better to have four reports each for one hour. This will help to isolate the problem.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Database link

Database link:

A database links allows an Oracle client to access two or more databases as one 
logical database via a connection between the database servers. 
Database links are actually entries in a data dictionary table that define 
a communication path from one Oracle database to another remote database. 
In order to access the database link a user must be connected to the 
database that contains the data dictionary table that defines the database link

Types Of Database Links

Private and Public

Private Link

To create a private database link, you must have the CREATE DATABASE LINK 
system privilege.
To create a public database link, you must have the CREATBE PUBLIC DATAASE LINK 
system privilege.
Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Private database link to a user in a remote database

SQL> CREATE DATABASE LINK orcl_remote
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'orcl'; 

Database link created.

Private database link to a user in a remote database, with the full connection string

SQL> CREATE DATABASE LINK scott_remote
   CONNECT TO apple IDENTIFIED BY apple
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=orcl))
          )';

Database link created.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: duplicate target database to testdb backup location

INTRODUCTION:

DESIGNATION:

OVERALL STEPS:

ORACLE DATABASE RESTORE POINT

Oracle Wait Events And Their Solutions.

RUNNING GATHERSTATS

ORACLE DATABASE INCARNATION USING RMAN

AWR REPORT:

step – 1

step – 2

AWR REPORT:

Collect Multiple AWR Reports

Stick to Particular Time

Split Large AWR Report into Smaller Reports

Database link:

Types Of Database Links

Private and Public

Private Link

Private database link to a user in a remote database

Private database link to a user in a remote database, with the full connection string