Block change Tracking (BCT) in Oracle 19c – CTWR

Description:-

In this article we are going to see use of Oracle Block Change Tracking concept in 19c database.

  • Oracle 10g introduced the Block change tracking (BCT) feature which is used to Speed up Rman Incremental Backup.
  • After Enabling this feature records the modified since last backup and stores the log of it in a block change tracking file using the CTWR (Change Tracking Writer) process.
  • Records the modified since last backup and stores the log of it in a block change tracking file.
  • Block change tracking feature is not reading the entire database blocks it just directly reads only changed blocks results in block change tracking file.
  • During backups Rman uses the log file to identify the specific blocks that must be backed up.
  • It improves the Rman performance as it is not scan whole datafiles instead of read the changed blocks and take backups .

Step 1: Enable Block Change Tracking and Check the status.

SQL> alter database enable block change tracking using file ‘/u01/clone/datafile/blockfile.log’;

SQL> Select filename,status from V$block_change_tracking;

Step 2: To check the change tracking writer(ctwr) process started or not.

SQL> select sid,program,status from v$session where program like ‘%CTWR%’;

Step 3: Disable block change Tracking.

SQL> alter database disable block change tracking;

Database altered.

SQL> select sid,program,status from v$session where program like ‘%CTWR%’;

no rows selected

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

Resolve ORA-00210,ORA-00202,ORA-27041 and Restore A Loss Of Controlfile Using Autobackup in Oracle 19c

Description

In this article we are going to see how to restore a loss of controlfile using autobackup.

Step 1:- Check the RMAN configuration and controlfile autobackup feature is ON.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 12:56:58 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name INBAA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/19.0.0/dbs/snapcf_inbaa.f’; # default

RMAN>

Step 2:- Simulate a failure to remove the controlfile when the database is running.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 12:55:02 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> select open_mode,name from v$database;

OPEN_MODE NAME

READ WRITE INBAA

SQL> select name from v$controlfile;

SQL> !
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/INBAA/
[oracle@localhost INBAA]$ ls
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@localhost INBAA]$ rm -rf control01.ctl
[oracle@localhost INBAA]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/recovery_area/INBAA/
[oracle@localhost INBAA]$ ls
archivelog autobackup backupset control02.ctl onlinelog
[oracle@localhost INBAA]$ rm -rf control02.ctl

[oracle@localhost INBAA]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 13:09:30 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.10.0.0.0

SQL>
SQL> select tablespace_name from dba_data_files;
select tablespace_name from dba_data_files
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select status from v$instance;

STATUS

OPEN
SQL>
SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL>

Step 3:- Keep the database in NOMOUNT stage and restore the controlfile

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
SQL>
Step 4:- Since we are not using a RMAN catalog we need to set the DBID

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 13:16:07 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (not mounted)

RMAN> set dbid=936024966;

executing command: SET DBID

RMAN> restore controlfile from autobackup;

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

recovery area destination: /u01/app/oracle/recovery_area
database name (or database unique name) used for search: INBAA
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210131
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210130
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210129
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210128
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/INBAA/control01.ctl
output file name=/u01/app/oracle/recovery_area/INBAA/control02.ctl
Finished restore at 31-JAN-21
RMAN>

Step 5:- Mount and recover the database

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed
RMAN> 
RMAN> recover database;

Starting recover at 31-JAN-21
Starting implicit crosscheck backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 31-JAN-21
Starting implicit crosscheck copy at 31-JAN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-JAN-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/INBAA/redo01.log
archived log file name=/u01/app/oracle/oradata/INBAA/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JAN-21
RMAN>

Step 7:- Open the database using resetlogs option

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> startup nomount;
ORACLE instance started.
Total System Global Area            3019895280 bytes
Fixed Size                                       8901104 bytes
Variable Size                                 738197504 bytes
Database Buffers                         2264924160 bytes
Redo Buffers                                 7872512 bytes
SQL> select open_mode,name from v$database;

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

Recover a loss of all online redo log files using RMAN in Oracle 19c

Description:-

In this blog I will explain how to recover a loss of all online redo log files using RMAN

Step 1:- Check the member and status of redo logfiles

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Jan 28 19:17:52 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode                        Archive Mode
Automatic archival Enabled
Archive destination                       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        1
Next log sequence to archive     3
Current log sequence                  3

SQL> select member from v$logfile;

SQL> select * from v$log;

Step 2: We can simulate this scenario by deleting all the online redo log files at the OS level.

Step 2 : Shutdown and mount the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.

Step 3: Connect to RMAN and recover until the available sequence.

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 20:21:27 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run 
{
set until sequence 3;
restore database;
recover database;
alter database open resetlogs;
}2> 3> 4> 5> 6> 7>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp tag=TAG20210128T200722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JAN-21
Statement processed
RMAN>

SQL> select name,open_mode from v$database;

Since we have done an incomplete recover with open resetlogs, we should take a fresh
complete backup of the database.

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

Performing point in time recovery using RMAN in 19c

Description:-

  • RMAN database point-in-time recovery (DBPITR) restores the database from RMAN backups.
  • RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time.
  • Point in time recovery may be incomplete recovery because it does not use all the available archive logs files or completely recover all changes to your database.
  • If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.

Prerequisites :-

  • Database must be running in archivelog mode.
  • You must have all the datafile backups available prior to target time to recover.

STEP 1:Check archive log list for the current log sequence number.

SQL> archive log list
Database log mode                           Archive Mode
Automatic archival                            Enabled
Archive destination                           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence             2
Next log sequence to archive          4
Current log sequence                       4

Step 2: Create user for testing  and table data for the recovery purpose.

SQL> create user inba identified by inba default tablespace users temporary tablespace temp profile default account unlock;

User created.

SQL> grant connect,resource to inba;

Grant succeeded.
SQL> grant all privileges to inba;

Grant succeeded.
SQL> conn inba/inba
Connected.

SQL> create table product(pro_id number,pro_name varchar(10));

Table created.

SQL> insert into product values(11,’laptop’);

1 row created.

SQL> select * from product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

SQL> select count(*) from product;

COUNT(*)

5

STEP 3 : Delete all the records in the table and Check the log sequence with specified time in the v$log.

SQL> archive log list
Database log mode                           Archive Mode
Automatic archival                            Enabled
Archive destination                           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence             2
Next log sequence to archive          4
Current log sequence                       4

SQL> conn inba/inba
Connected.
SQL> delete product;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> select sequence#,first_change#, to_char(first_time,’HH24:MI:SS’) from v$log order by 3;

SEQUENCE# FIRST_CHANGE# TO_CHAR(

2 2341033 19:19:21
3 2341053 19:19:55
4 2341065 19:20:14

Here you can find the time of log sequence 3 19:19:55 that is the time the data would be available in the database.so we need to go back that time and recover the database using the RMAN backups.

Step 4 : Startup the database at mount stage and connect the rman  to recover  the database using the log sequence number 3.

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL>

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 19:28:33 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run
{
set until sequence=3;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp tag=TAG20210128T143632
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15gx9vj_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-JAN-21

RMAN>

STEP 4: Once the recovery has completed,open the database with resetlogs option and check the data in the table.

SQL> alter database open resetlogs;

Database altered.

SQL>

Step 5:- Check the table data now that the point in time recovery has worked.

SQL> select * from inba.product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

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 Database 19c Installation On Oracle Linux 7 (OEL7)

Description:
This article describes the installation of Oracle Database 19c 64-bit on Oracle Linux 7 (OL7) 64-bit. The article is based on a server installation with a minimum of 2G swap and secure Linux set to permissive.

Step1 : Unzip the 19c software and execute runInstaller :

Step2 : Click create and configure a single instance database

Step 3 : Click Server class

Step 4 : Select Enterprise Edition

Step 5 :Choose location for ORACLE_BASE

Step 6 : Select configuration type as General Purpose / Transaction Processing.

Step 7: Enter Database name

Step 8 : Enter database file location

Step 9 : Enable Archivelog mode option for database

Step 10 : Enter SYS and SYSTEM user password

Step 11 : Check OS groups for installation

Step 12 : Check Prerequisite checks for 19c database installation

Step 13 : Check Summary

Step 14: Run root.sh and Orainventory scripts

Step 15 : Check the  Environment and database status

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

SQLFILE Parameter in Oracle19c Database.

DESCRIPTION:

In this article we are going to see the Oracle 19c Datapump- Sqlfile

Sqlfile option will show you the DDL in the text format.Sometimes customer provides us data pump export file, by that time we will use SQLFILE parameter and find out DDL’s (Schema names) and perform data pump import accordingly.

SYNTAX:

impdp directory=DATA_PUMP_DIR dumpfile=PRODDB_Full.dmp sqlfile=FULL_DDL.sql

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01' sqlfile=tab.sql

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:30:30 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

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

Master table "DATA"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded

Starting "DATA"."SYS_SQL_FILE_TABLE_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01 sqlfile=tab.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_SQL_FILE_TABLE_01" successfully completed at Mon Jan 25 09:30:36 2021 elapsed 0 00:00:01




[oracle@oracle19c ~]$ cd /u01/app/oracle/oradata/

[oracle@oracle19c oradata]$ ls

compressed1.dmp  con01.dmp  con.log        CONTINUE.dmp  My_Dir        schema.dmp  table01.dmp       tablespace.log  tabs.log

compressed2.dmp  con01.log  CONTINUE1.dmp  CONTINUE.log  ORACLE19C     schema.log  table01.log       tabs00.log      tab.sql

compressed.dmp   con.dmp    CONTINUE1.log  export.log    ORACLE19CCON  tab00.dmp   tablespace01.log  tabs.dmp

sqlfile activity completed successfully and ‘tab.sql‘ file has created in the specified dump directory location.

[oracle@oracle19c oradata]$ vi tab.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:
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

Apply Patching On Oracle 19c Database Release Update- January 2021

DESCRIPTION

This article we are going to see steps to apply the latest Oracle 19c Database Release Update Patch 32218454

Sep 1 : DOWNLOAD THE PATCH FROM ORACLE SUPPORT

Sep 2 : CHECK THE CURRENT OPTACH VERSION IN THE DATABASE

[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.
[oracle@localhost ~]$

Sep 3 : MOVE THE PATCH TO ORACLE HOME LOCATION

[oracle@localhost ~]$ cd Desktop/
[oracle@localhost Desktop]$ ls
LINUX.X64_193000_db_home.zip p32218454_190000_Linux-x86-64.zip VMwareTools-10.0.6-3595377.tar.gz
[oracle@localhost Desktop]$ mv p32218454_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/

NOTE:We need to Upgrade Opatch from “12.2.0.1.17 to 12.2.0.1.23” for applying Patch “32218454”.

Sep 4 : UNZIP THE DOWNLOADED OPATCH

oracle@localhost Desktop]$ mv p6880880_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/
[oracle@localhost Desktop]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ unzip p6880880_190000_Linux-x86-64.zip

Archive: p6880880_190000_LINUX.zip

replace OPatch/emdpatch.pl? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: OPatch/emdpatch.pl
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
inflating: OPatch/oplan/oplan
inflating: OPatch/datapatch

Continues

inflating: OPatch/modules/thirdparty/jackson-databind-2.10.2.jar
inflating: OPatch/modules/thirdparty/jackson-annotations-2.10.2.jar

[oracle@localhost 19.0.0]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost 19.0.0]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost 19.0.0]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@localhost 19.0.0]$

Sep 5 : BEFORE APPLYING THE PATCH CHECK THE STATUS

SQL> col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 6 : SHUTDOWN DATABASE AND LISTENER

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/product/19.0.0
[oracle@localhost 19.0.0]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 18:14:07

Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost 19.0.0]$
The command completed successfully

Sep 7 : APPLY RU PATCH ON ORACLE_HOME 19C

[oracle@localhost 19.0.0]$ cd 32218454/
[oracle@localhost 32218454]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2021, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/19.0.0
Central Inventory : /u01/app/oracle/product/19.0.0/oraInventory
from : /u01/app/oracle/product/19.0.0//oraInst.loc
OPatch version : 12.2.0.1.23
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32218454
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/19.0.0’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Patching component oracle.network.client, 19.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0…
Patching component oracle.precomp.common, 19.0.0.0.0…
Patching component oracle.precomp.lang, 19.0.0.0.0…
Patching component oracle.jdk, 1.8.0.201.0…
Patch 32218454 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32218454].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/cfgtoollogs/opatch/opatch2021-01-27_18-46-39PM_1.log
OPatch succeeded.

Sep 8 : STARTUP THE DATABASE AND LISTENER

[oracle@localhost ~]$ cd scripts/
[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 27-JAN-2021 19:12:28
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 27-JAN-2021 19:12:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 620756992 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL>

[oracle@localhost OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.10.0.0.0 Production on Wed Jan 27 19:47:50 2021
Copyright (c) 2012, 2020, Oracle. All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_18910_2021_01_27_19_47_50/sqlpatch_invocation.log

Connecting to database…OK
Gathering database info…done
Bootstrapping registry and package to current versions…done
Determining current state…done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.10.0.0.0 Release_Update 210108185017: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 27-JAN-21 05.00.33.668766 PM
Adding patches to installation queue and performing prereq checks…done
Installation queue:
No interim patches need to be rolled back
Patch 32218454 (Database Release Update : 19.10.0.0.210119 (32218454)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.10.0.0.0 Release_Update 210108185017
No interim patches need to be applied
Installing patches…

Sep 9 : AFTER APPLYING RU PATCH,CHECK THE DBA_REGISTRY_SQLPATCH

SQL>col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;

Sep 9 : USING BELOW QUERY, WE CAN VERIFY DESCRIPTION,STATUS OF APPLIED PATCH
COLUMN action_time FORMAT A10
COLUMN status FORMAT A30
COLUMN description FORMAT A30
SELECT action_time,action,status,description,patch_id from dba_registry_sqlpatch;

Sep 10 : CHECK OPATCH LSINVENTORY AND LIST OF PATCHES APPLIED.

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/
[oracle@localhost ~]$ export PATH=/u01/app/oracle/product/19.0.0/OPatch:$PATH
[oracle@localhost ~]$ opatch lspatches
32218454;Database Release Update : 19.10.0.0.210119 (32218454)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@localhost ~]$

Sep 11 : AFTER THE EXECUTION OF DATAPATCH SHOULD RUN UTLRP.SQL TO CLEAR INVALID OBJECT.

SQL> @?/rdbms/admin/utlrp.sql

CHECK THE ACTION & PATCHID IN DBA_REGISTRY_SQLPATCH

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 RAC Administration and Monitoring Scripts

Oracle RAC Administration and Monitoring Scripts -1

To check clusterware status, execute following command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1
[root@racdb ~]$ crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
To check cluster status, execute following command.
[root@racdb ~]$ crsctl check cluster 
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@racdb ~]$
To check all cluster resource status, execute following command.
[root@Prodecidbrac01 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details 
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATAC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.DBFS_DG.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.RECOC1.dg
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.asm
ONLINE ONLINE Prodecidbrac01 Started,STABLE
ONLINE ONLINE Prodecidbrac02 Started,STABLE
ora.net1.network
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
ora.ons
ONLINE ONLINE Prodecidbrac01 STABLE
ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE Prodecidbrac01 169.254.94.159,STABL
E
ora.cvu
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.dbm01.db
1 ONLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.ebys.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.ebytest.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.mgmtdb
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
ora.oc4j
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.repdb.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.scan1.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
ora.scan2.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.scan3.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 ONLINE OFFLINE Instance Shutdown,ST
ABLE
ora.test.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.Prodeci.db
1 ONLINE ONLINE Prodecidbrac01 Open,STABLE
2 ONLINE ONLINE Prodecidbrac02 Open,STABLE
ora.Prodecidbrac01.vip
1 ONLINE ONLINE Prodecidbrac01 STABLE
ora.Prodecidbrac02.vip
1 ONLINE ONLINE Prodecidbrac02 STABLE
--------------------------------------------------------------------------------
[root@Prodecidbrac01 ~]#

RAC Cluster Command ( Scripts )

To stop Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl stop crs

 

To start Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb1 ~]$ . oraenv
ORACLE_SID = [root] ? +ASM1

[root@racdb ~]$ crsctl start crs
To disable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb ~]$ crsctl disable crs
To enable Clusterware on specific node, execute following command. Set ASM profile before executing crsctl command.
[root@racdb ~]$ crsctl enable crs
To Query Voting disk location, execute following command. Set ASM profile before executing crsctl command.
[oracle@racdb ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE bd81ab8e27a64fbebf7b6b326c5f614d (/Prod/mapper/HDD_E0_S03_1418288932p1) [DATA]
2. ONLINE 450684e276874ff9bff52d481befa809 (/Prod/mapper/HDD_E0_S15_1420185788p1) [DATA]
3. ONLINE fd1efd6de5204f6bbfe9793a480c517f (/Prod/mapper/HDD_E0_S06_1423843312p1) [DATA]
Located 3 voting disk(s).
To find OCR files location, execute following command.
[root@racdb ~]# cat /etc/oracle/ocr.loc
#Prodice/file +DATA getting replaced by Prodice +DATA/Prodeci-c/OCRFILE/registry.255.923150247
ocrconfig_loc=+DATA/Prodeci-c/OCRFILE/registry.255.923150247
local_only=false
[root@racdb ~]#
To check the status of the Oracle Cluster registry , execute following command.
[root@racdb ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 2412
Available space (kbytes) : 407156
ID : 2022573354
Prodice/File Name : +DATA
Prodice/File integrity check succeeded
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Prodice/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@racdb ~]#
To add voting disk, execute following command. Set ASM profile before executing crsctl command.
crsctl add css votedisk new_vote_disk_path
To Delete voting disk, execute following command. Set ASM profile before executing crsctl command.
crsctl delete css votedisk new_vote_disk_path
To Add voting disk to ASM Disk, execute following command.
crsctl add votedisk asm_disk_group
To Replace or Migrate voting disk, execute following command.
crsctl replace votedisk asm_diskgroup / vote_disk_path
To find OCR backup location, execute following command. Set ASM profile before executing crsctl command.
ocrconfig –showbackup auto
To change OCR Backup location, execute following command.
ocrconfig –backuploc Shared_PATH
To Add OCR files location, execute following command.
ocrconfig –add Location_PATH
To Replace or Change OCR files location, execute following command.
ocrconfig –replace New_PATH
To Repair OCR files location, execute following command. Cluster or Oracle RAC Services should be power off.
ocrconfig –repair –add +DATAC1
There are lots of options used with SRVCTL command, i described them below.
-d Database Name
-i Instance Name
-s Service Name
-n Node Name
-r Preferred list
-a Available list
-p TAF (Transparent application failover policy)
-v Verbose

Click this link for srvctl commands here.

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

TABLESPACE LEVEL PARAMETER

DESCRIPTION:

In this article, we are going to see  how to export and Import table space using Datapump.

Tablespaces are the logical storage units which are used by the database to store separate objects, such as tables, types, PL/SQL code, and so on. Typically, related objects are grouped together and stored in the same tablespace.

Using expdp export utility of data pump we can export tablespaces. Exporting tablespace is also a way of taking logical backup of the tablespace of your database. Exporting tablespace means only the tables contained in a specified set of tablespace are unloaded along with its dependent objects.

Exporting tablespace means

  • Only the tables contained in a specified set of tablespace are unloaded
  • If a table is unloaded, then its dependent objects are also unloaded
  • Tablespace export unloads both object metadata and Data.

DIRECTORY.

Create a directory anywhere in your system or on your network where expdp export utility can save the exported files such as dump files and log files.

TABLE SPACE:

Using this TABLESPACES parameter we can specify the list of tablespace names which you want to export. For example here I have specified USERS.

[oracle@oracle19c ~]$ expdp directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 09:22:37 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

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

Starting "DATA"."SYS_EXPORT_TABLESPACE_01":  data/******** directory=My_Dir tablespaces=users dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/COMMENT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

. . exported "DATA"."TABLE01"                            5.515 KB       5 rows

Master table "DATA"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for DATA.SYS_EXPORT_TABLESPACE_01 is:

  /u01/app/oracle/oradata/tab00.dmp

Job "DATA"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jan 25 09:22:51 2021 elapsed 0 00:00:09

Export activity completed successfully.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:23:37 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> conn data/pump

Connected.

SQL> select * from table01;

ID NAME

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

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee
SQL> drop table table01;

Table dropped.

SQL> select * from Table01;

select * from Table01

              *

ERROR at line 1:

ORA-00942: table or view does not exist


SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Import the tablespaces using the Dumpfile

[oracle@oracle19c ~]$ impdp directory=My_Dir  dumpfile=tab00.dmp  logfile=tabs00.log

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:28 2021

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Username: data

Password:

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

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=tab00.dmp logfile=tabs00.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 09:24:36 2021 elapsed 0 00:00:03

Import Activity successfully completed.Now we can access the tablespaces.

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 09:24:51 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> conn data/pump

Connected.


SQL> select * from table01;

ID NAME

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

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

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