RMAN> backup current controlfile;
Starting backup at 11-AUG-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-AUG-21
channel ORA_DISK_1: finished piece 1 at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0m069eu0_1_1
tag=TAG20210811T232448 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-AUG-21
Starting Control File and SPFILE Autobackup at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1608118455-20210811-09
comment=NONE
Finished Control File and SPFILE Autobackup at 11-AUG-21
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> backup spfile;
Starting backup at 11-AUG-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 11-AUG-21
channel ORA_DISK_1: finished piece 1 at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0o069ev4_1_1
tag=TAG20210811T232524 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-AUG-21
Starting Control File and SPFILE Autobackup at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1608118455-20210811-0a
comment=NONE
Finished at 11-AUG-21
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:
[oratest@oracle ~]$ impdp help=y
Import: Release 19.0.0.0.0 - Production on Mon Aug 9 22:52:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.
ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH,
EXTERNAL_TABLE, and INSERT_AS_SELECT.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].
CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT, ENABLE_NETWORK_COMPRESSION,
REJECT_ROWS_WITH_REPL_CHAR, SKIP_CONSTRAINT_ERRORS, CONTINUE_LOAD_ON_FORMAT_ERROR,
TRUST_EXISTING_TABLE_PARTITIONS and VALIDATE_TABLE_DATA.
DIRECTORY
Directory object to be used for dump, log and SQL files.
DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo is suppressed while standard input is read.
ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Import everything from source [YES].
HELP
Display help messages [NO].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of import job to create.
KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].
LOGFILE
Log file name [import.log].
LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
MASTER_ONLY
Import just the master table and then stop the job [NO].
METRICS
Report additional job information to the import log file [NO].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [NO].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file.
PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REMAP_DATAFILE
Redefine data file references in all DDL statements.
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].
SCHEMAS
List of schemas to import.
SERVICE_NAME
Name of an active service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION
Edition to be used for extracting metadata.
SQLFILE
Write all the SQL DDL to a specified file.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to import.
TARGET_EDITION
Edition to be used for loading metadata.
TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION,
STORAGE, and TABLE_COMPRESSION_CLAUSE.
TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.
TRANSPORT_TABLESPACES
List of tablespaces from which metadata is loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.
VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name is appended
to the view name.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.
STOP_WORKER
Stops a hung or stuck worker.
TRACE
Set trace/debug flags for the current job.
SQL> create or replace directory dump_dir as '/home/oracle/backup';
Directory created.
[oracle@oracle ~]$ impdp directory=dump_dir dumpfile=india.dmp logfile=india.log
tables=india.apple
Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: test
Password:
The ATTACH parameter is used to attach the interrupted job and status of data pump operation.
[oracle@oracle ~]$ impdp system/oracle attach=SYS_EXPORT_SCHEMA_01
Export: Release 19.0.0.0.0 - Production on Mon Aug 20 22:19:02 2018
Version 19.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM
Remap Table
[oracle@oracle ~]$ impdp DIRECTORY=dumpdir DUMPFILE=india_table.dmp logfile=india_table1.log TABLES=india.apple REMAP_TABLE=india.apple:mango
Username: system
Password:
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "INDIA"."MANGO" 5.609 KB 8 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "INDIA"."SYS_IMPORT_TABLE_01" successfully completed at Wed Aug 4 21:49:48 2021 elapsed 0 00:00:19
Remap_schema
[oracle@oracle ~]$ impdp directory=dumpdir dumpfile=india_owner.dmp logfile=india_owner1.log remap_schema=tamil:english
Username: system
Password:
. . imported "ENGLISH"."T1" 5.539 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:24:10 2021 elapsed 0 00:00:17
Remap_Tablespace
[oracle@oracle backup]$ impdp directory=dumpdir dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Username: system
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dumpdir dumpfile=test_tablespace.dmp logfile=test_tablespace1.log remap_tablespace=test:orcl
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Aug 4 22:56:25 2021 elapsed 0 00:00:02
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@sample ~]$ expdp help=y
Export: Release 19.0.0.0.0 - Production on Thu Aug 5 21:33:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:
Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
------------------------------------------------------------------------------
The available keywords and their descriptions follow. Default values are listed within square brackets.
ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.
ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.
ATTACH
Attach to an existing job.
For example, ATTACH=job_name.
CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].
COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.
CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS
Data layer option flags.
Valid keyword values are: GROUP_PARTITION_TABLE_DATA, VERIFY_STREAM_FORMAT and XML_CLOBS.
DIRECTORY
Directory object to be used for dump and log files.
DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FILESIZE
Specify the size of each dump file in units of bytes.
FLASHBACK_SCN
SCN used to reset session snapshot.
FLASHBACK_TIME
Time used to find the closest corresponding SCN value.
FULL
Export entire database [NO].
HELP
Display Help messages [NO].
INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.
JOB_NAME
Name of export job to create.
KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].
LOGFILE
Specify log file name [export.log].
LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
METRICS
Report additional job information to the export log file [NO].
NETWORK_LINK
Name of remote database link to the source system.
NOLOGFILE
Do not write log file [NO].
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file name.
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].
SAMPLE
Percentage of data to be exported.
SCHEMAS
List of schemas to export [login schema].
SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.
SOURCE_EDITION
Edition to be used for extracting metadata.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES
Identifies a list of tablespaces to export.
TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].
TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.
VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
------------------------------------------------------------------------------
The following commands are valid while in interactive mode.
Note: abbreviations are allowed.
ADD_FILE
Add dumpfile to dumpfile set.
CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT
Quit client session and leave job running.
FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.
HELP
Summarize interactive commands.
KILL_JOB
Detach and delete job.
PARALLEL
Change the number of active workers for current job.
REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].
START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.
STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.
STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.
STOP_WORKER
Stops a hung or stuck worker.
TRACE
Set trace/debug flags for the current job.
Full Database Export
[oracle@oracle backup]$ expdp system/password directory=dumpdir dumpfile=full.dmp logfile=full.log full=y
Master table "INDIA"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INDIA.SYS_EXPORT_FULL_01 is:
/home/oracle/backup/full.dmp
Job "INDIA"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 6 00:19:43 2021 elapsed 0 00:02:41
SQL> create or replace directory dump_dir as '/home/oracle/backup';
Directory created.
[oracle@oracle ~]$ expdp directory=dump_dir dumpfile=india.dmp logfile=india.log tables=india.apple
Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: test
Password:
The ATTACH parameter is used to attach the interrupted job and status of data pump operation.
[oracle@oracle ~]$ expdp system/oracle attach=SYS_EXPORT_SCHEMA_01
Export: Release 19.0.0.0.0 - Production on Mon Aug 6 22:19:02 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Job: SYS_EXPORT_SCHEMA_01
Owner: SYSTEM
Directory
Directory Parameter is used for Dump file and Log file.
Directory Parameter is used for Dump file and Log file.
Sql > create or replace directory dumpdir as ‘/backup/export’;
Directory created.
Sql > grant read,write on directory dumpdir to apple;
Grant succeeded.
Sql > exit
[oracle@oracle ~]$ expdp directory=dumpdir dumpfile=full.dmp logfile=full.log tables=emp
Transport_Tablespaces
Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace names for which object metadata will be exported from the source database into the target database.
Keep INDIA Tablespace in Read only mode.
SQL> alter tablespace INDIA read only;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/india01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
[oracle@oracle]$ scp /u01/app/oracle/oradata/proddb/india01.dbf [email protected]/u01/app/oracle/oradata/TEST/datafile
[oracle@oracle export]$ expdp DUMPFILE=india.dmp LOGFILE=india.logDIRECTORY=dumpdir TRANSPORT_TABLESPACES=india
Export: Release 19.0.0.0.0 - Production on Thu Aug 6 23:07:59 2018
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
******************************************************************************
Datafiles required for transportable tablespace INDIA:
/u01/app/oracle/oradata/proddb/india01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Aug 6 23:08:48 2021
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:
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and
p.addr=s.paddr ;
2. To list inactive Sessions respective username
SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS=’INACTIVE’
group by username
order by num_inv_sess DESC;
SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS=’INACTIVE’;
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS=’INACTIVE’;
3. To find session-id with a set of SPIDs
select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);
4. To find SQL Text given SQLHASH & SQLADDR
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece;
select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;
5.Checking for Active Transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;
6. Session details from Session longops
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;
7. Session details with SPID
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’)
from v$session where paddr in (select addr from v$process where spid = ‘&spid’)
/
8. To find Undo Generated For a given session
select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid=’&sid’;
9. To list the count of connections from other machines
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;
10. To get the total count of sessions and processes
select count(*) from v$session;
select count(*) from v$process;
select (select count() from v$session) sessions, (select count() from v$process) processes from dual;
11. To find SQL text through SQL address
select sql_address from v$session where sid=1999;
select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;
12. To find SQL text for different SQL hash value
select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)
13. Session details associated with SID and Event waiting for
set pages 50000 lines 32767
col EVENT for a40
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;
14. Active Sessions running for more than 1 hour
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”,
ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS=’ACTIVE’
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;
15.SQLs Running from longtime
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’;
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from v$session_longops;
16. Last/Latest Running SQL
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc;
17. Current Running SQLs
set pages 50000 lines 32767
col program format a20
col sql_text format a50
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id;
18. Current Running SQLs
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,’DD-MON-RR HH24:MI’) login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
— and S.status=’ACTIVE’
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;
Please find out all of our articles send us the Invitation or Follow us:
Export(exp) and import(imp) are Logical backup and Recovery. When exporting the database objects are dumped to a binary file which can then be imported into another Oracle database.
The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. First, let us see export in detail.
Difference between exp/imp and expdp/impdp
Exp/imp is a Traditional export import,expdp/impdp is a Datapump export
import.
Traditional export import utility is starting with exp/imp,Datapump utility
is starting with expdp/impdp.
Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
exp/imp (Traditional) is using conventional path , expdp /impdb (Datapump) is using Direct path.
Exp (Traditional) is byte mode ,Datapump is block mode.
Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.
Datapump utility we can stop and Restart the Jobs.
Features of Datapump utility
Job Estimation can be Done in Datapump.
Data Remapping can be done using REMAPDATA parameter.
EXCLUDE and INCLUDE parameter allows the fine-grained object selection.
Failed export/import Jobs can be Restarted.
Export and import can be taken over the network using database links even without Generating the dump file using NETWORK_LINK parameter.
CONTENT parameter gives the freedom for what to export with options METADATA ONLY, DATA, BOTH.
You don’t need to specify the BUFFER size in datapump
Job estimated completion time can be monitored from v$session_longops view.
Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you have to compress the dumps using OS utilities.
Data encryption can be done in datapump.
DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.
REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.
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:
Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.
Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
SQL> select * from v$log;
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 91
2 INACTIVE 89
3 INACTIVE 90
SQL> select * from v$logfile;
Active (Current) and Inactive Redo Log Files
Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file.
Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents. If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.
Add New redo log file
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/TESTDB/redo04.dbf' size 100m;
Database altered.
Add Member to an existing group
SQL> alter database add logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log' to group 4;
Database altered.
Drop member
SQL> alter database drop logfile member '/u01/app/oracle/oradata/TESTDB/redo04b.log';
Database altered.
Drop Logfile Group
SQL> alter database drop logfile group 4;
Database altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
Adding Two member
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/TESTDB/redo04a.log', '/u01/app/oracle/oradata/TESTDB/redo04C.log') size 100m;
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ---------------------------
4 /u01/app/oracle/oradata/TESTDB/redo04a.log
4 /u01/app/oracle/oradata/TESTDB/redo04C.log
Log Switches and Log Sequence Numbers
A log switch is a point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.
Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.
Each online or archived redo log file is uniquely identified by its log sequence number. During a crash, instance, or media recovery, the database properly applies to redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.
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:
In this blog, we will see how to upgrade Oracle Database 12c to 19c using AutoUpgrade Tool
What is the AutoUpgrade?
The Oracle Database AutoUpgrade utility is a small command-line tool that allows you to upgrade your databases very easily with very little interaction
The new AutoUpgrade utility in Oracle 19¢ performs almost 99% of the task by itself, we just have to provide inputs during the initial phase
So it performs Prechecks against multiple databases, upgrades multiple databases in one go
Also, it does Post upgrade, object recompilation, and time zone up-gradation
The only thing you need to provide is a contig file in text format
Which database releases are supported?
As a source, the minimum version is Oracle Database 11.2.0.4. onwards
Download the latest auto-upgrade jar file
Auto upgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory of Oracle 19c Home
Oracle strongly recommends downloading the latest AutoUpgrade version before doing the upgrade
You download the most recent version from MOS Note: 2485487.1 AutoUpgrade Tool
Once you download this jar file transfer it to the Server and create a new directory and place this file in that directory
prddb_config.cfg file should have following entry which specifies source and target database home location and DB name and log locations and other information’s:
Auto upgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.
Execute AutoUpgrade in analyze mode with the below syntax:
Once the upgrade process is started consider monitoring the logs and database alert logs to see the progress of the upgrade. Auto upgrade logs are available under
/home/oracle/auto_upgrade_19c/upg_logs/
Once the upgrade finishes crosscheck the below.
SELECT VERSION FROM V$TIMEZONE_FILE;
select name, open_mode, version, status from v$database, v$instance;
Post-upgrade task
Once the upgrade is successful and all testing is done, drop the restore point.
Drop the Guaranteed restore point
select name from v$restore_point;
drop restore point restorepoint_name;
Change the compatible parameter
After the upgrade, the database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.
show parameter compatible;
alter system set compatible=’19.0.0′ scope=spfile;
shutdown immediate:
startup;
show parameter compatible;
Hope this blog was useful…
Please find out all of our articles send us the Invitation or Follow us:
How to find the bind variable values of the SQL statement using SQLID?
SELECT sql_id, b.name BIND_NAME, b.value_string BIND_STRING from gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL
AND sql_id ='&SQLID';
Additional script which may be helpful at a later point in time:
To know the last executed query in the oracle and its hash value,sqlid
SELECT * FROM v$SQLTEXT_WITH_NEWLINES WHERE address =
(SELECT prev_sql_addr FROM v$session WHERE audsid = userenv('SESSIONID'))
ORDER BY piece;
To know the SQL plan history of a sql using the sqlid:
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
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:
