Interview questions which are frequently asked for Oracle DBA::

Below questions is for reference for preparations of interview point.

Answer Links : Will be shared soon.

List Of Questions:-

*. Tell me your self and roles and responsibilities.

*. What is location of cluster log file.

*. How to Upgrade the opatch utility(IF it is not higher or equal to OUI version) How to apply patch.

*. What are requirement before proceed to 9c Upgrade.

*. How to start scan listener and single instance?

*. In node RAC one node got rebooted How to proceed to check and start instance?

*. How to clone the user if you have mirror id? Explain in detail each command

*. How to refresh/clone database using particular time or SCN?(till yesterday night

etc) from where you will get scn number,sequence etc

*. In cloning side db is in mount stage after restore controlfile What is db_name

How(production or dev name)?

*. How to add new database to OEM

*. How many databases your are support in your environment and sizes etc(backup etc)

*. In standby concept

*. Standby side no space left on disk but we didn't check and added datafile in primary, How to fix this type of issues?

*. In your whole carrier What are achievements you got from mgmt or anything work you did alone?

*. How to export table from source to target database(target side schema name diff

and table name also diff) explain in detail with commands

*. What is your password security policy? directly providing password to user or

anything?

*. What is top 5 events in RAC?

*. What is the highest database size?

*. How many databases supporting in your environment?

*. What is the default unDo retention?

*. What is the ORA 555 error? if you get What you will do?

*. What is the diff b/w incident and problem?

*. How to check DG is in sync or not and incremental backup dg sync Explain?

*. How to refresh database?

*. What is the use of catalog and nocatalog?

*. What is the use of unDo advisor?

*. knowledge on PT?

*. ticketing tool Explain?

*. Explain about change open process?

*. which role you are support?

*. tell me ur self and roles and responsibilities?

*. controlfile retention?

*. tell me yourself roles and responsibilities?

*. What is extent,segment etc?

*. Explain about physical standby and logical standby, How to fix gap using incremental backup Explain in detail

*. What is row chaining and row migration, How to fix this issues?

*. What is database,instance,physical and logical sturctures?

*. Explain about shared server architecture?

*. How to clone database using target,catalog Explain in detail?

*. How to configure standby database?

*. What is password file and parameter file mgmt? Explain in detail?

*. What is ASSM?

*. How to apply the patch?

*. Explain about startup process like stages?

*. What are the day to day activities and issues you face

*. Do u know about redaction,scrubbing,multi tenant

*. What are sga parameters

*. if sga_target and sga_max_size are both set to same valuewill there be impact

*. If app team wants to increase sga and u have to plan How will u plan?

*. Difference between g,c

*. Difference between g,c RAC

*. if my system team is upgrading kernel and there has been some issue and you are not able to up the asmlib and u have to Upgrade the asmlib

*. How Do u come to know if the standby is out of sync?

*. How Do u bring standby in sync if it is out of sync for some time with huge gap?

*. How can you perform switchover and switchback?

*. What is the use of fal_client and fal_server?

*. What is the use of ADG? How Do u setup ADG?

*. U have setup dg broker, How Do you use it?

*. Different protection modes used in Standby database

*. What are the prerequisites of RAC installation?

*. What is difference between SCAN VIP and VIP?

*. What parameter is used to failover connection from one node to another?

*. What is node eviction?

*. How Do you come to know that node has evicted?

*. What is location of crs log?

*. What is startup sequence in RAC?

*. What are the background processes in ASM?

*. What is rebalancing in ASM?

*. What are the levels of redundancy And which are used in ur organization?

*. why is ASM better than Filesystem?

*. How does the cluster comes up without the ASM instance been initialized,as most of the files needed for startup are present in ASM

*. Which bg process communicates with listener in RAC (ans LREG)

*. What are the minimum parameters to bring up an ASM instance?

*. What is cache fusion?

*. What is split brain?

*. What happens when you Do not define + in front of datafile while adding in ASM?

*. Have you done addition and deletion of nodes?

*. What are types of mirroring?

*. What kinds of Db refreshes have you done?

*. If currently an export is running and you Do not have handover of the export and user asks about it ,What would you tell them regarding the same?

*. when you use parallel in expdp What is the thing that you check before using it?

*. What is ORA-0555 error and How would you resolve it?

*. Have you done cloning of database?

*. Have you done upgradation?

*. How to Do manual Upgrade?

*. What are the prerequisites of upgradation?

*. What is in pre Upgradejar package for database upgradation?

*. How would u plan to migrate a schema from one database to another

*. How would u plan an update for 00GB table

*. What if your downtime window exceeds for a deployment,What steps will you take?

*. How will you create a node-standby database for an Oracle RAC -node database?

*. Addition, deletion of nodes in oracle RAC

*. OEM installation and monitoring

*. Types of unDo ?

*. What you would Do in case of block corruption

*. How is mirroring achieved in rac?

*. pre requisites of RAC installation

*. What is dirty buffer?

*. which is the setup file you run during grid installation

*. What is the first window of grid installation?

*. What is the startup sequence in RAC database

*. where is the location of initd

*. What if the disks are not available on second node after issuing the scandisk command

*. What is the process of switchover and switchback in oracle database

*. OEM installation

*. How is monitoring done?

*. What are the steps after identifying ORA-600 errors?

*. when does node eviction happens?How would you come to know?

*. What is use of cssd process?

*. What are the steps involved in cloning of database?

*. What are the performance related issues that u have faced and resolved?

*. How Do u consider which table to de frag ?

*. How Do u come to know if the index is needed to be rebuilt? How Do u identify the indexes to be rebuilt?

*. Patching in oracle rac environment

*. Sequence of events that happen once u start Patching in RAC environment

*. Switchover, Switchback steps in RAC

*. Upgrade utility which has been used in oracle 9c

*. Pre checks to Upgrade

*. Use of datapump in GG

*. check for DMLs in replicat process

*. How to check the structure of database through RMAN

*. How to check the backups of your control file through RMAN

*. What is global inventory,local inventory?

*. Have you done Upgrade?

*. What are the steps involved in manual Upgrade?

*. If my server crashes during Upgrade,will i be able to resume Upgrade from where i left?

*. Which process is involved in instance recovery?

*. What are the pre requisites for Patching on Windows environment?

*. Types of backups used in oracle?

*. How can you make expdp backup faster?

*. What is the significance of exclude=statistics in expdp?

*. What if i have deleted archives from production database and standby db is out of sync ,What to do?

*. How can u restore a RMAN backup on RAC database?

*. Have you worked on DGBROKER? What is fast start failover?

*. What is ASYNC,SYNC?

*. What are the types of shutdown options?

*. What is startup force?

*. What is high water mark? Any way to reduce it?

*. What is optimizer and its use?

*. What is cost based and rule based optimizer?

*. What are the ways in which u can improve the performance of a query?

*. What are the commands that you know to monitor server performance?

*. Why is sysaux tablespace used?

*. How can u overcome the growth of a sysaux tablespace?

*. Does dropping a table can put it in recycle bin?

*. How to Rename a datafile? Are there any pre requisites involved?

*. How can you rename a database?

*. Why Do we need to change the timezone after Upgrade?

*. Have you worked on PDB,CDB?

*. U have done Cloud certifications,have you worked on cloud?

*. Have you worked on RAC

*. What is the difference between OCR and voting disk

*. Difference between OCRCHECK , OCRCONFIG

*. How to create physical standby,parameters involved in physical standby

*. How to convert non-ASM to ASM ?

*. How to convert from single node to RAC?

*. Difference between g,c,8c?

*. Have you worked on AWS,Azure?

*. Have you worked on any other technology apart from Oracle?

*. How to login to PostgreSQL database?

*. How to take backup of PostgreSQL database?

*. What are the utilities used in PostgreSQL to take backup?

*. Difference between SCAN VIP and VIP?

*. why is table_exist_action used in Datapump?

*. Why is attach parameter used in datapump?

*. Which processes are used to handle workload in Oracle database?

*. Which are the background processes in Oracle RAC?

*. Overall architecture in RAC database?

*. What are the tasks you have done in Performance tuning?

*. From which view to find the sql text of a given SQL id?

*. Have you worked on Oracle Goldengate and Exadata?

*. If my client or Whatever application team needs to implement RAC without SCAN IPs in g How will you Do it?

*. Have you worked on RAC?

*. What happens when your OCR gets corrupted and How will you restore it?

*. Have you faced block corruptions? How have you handled them?

*. What is the role of standby file management in dataguard?if i keep it as manual are there any kind of entries made at Standby?

*. If given an AWR report What all are the things that you would check in that report

*. Have you worked on PostgreSQL installation ?

*. Have you done Upgrade? What are the steps involved?

*. You have worked in Bank Of India What are the RBI guidelines did you follow as it is mentioned here

*. How did you implement DGBROKER?

*. What is difference between instance and database?

*. Have you done any implementations related to Firewall in terms of Security with Oracle?

*. Have you done any implementations or worked on Load Balancer?

*. What are the editions that you have used?

*. Hace you worked on OCI platform?

*. What happens when my control file is lost?

*. What are the stages of startup, What happens in resetlogs?

*. Why does an ORA-0555 error comes? How can you resolve it?Anything apart from unDo retention and size increase?

*. When does Deadlock occur? How can you resolve deadlock from db end?

*. If Application team wants to connect through same TNS name from their application, after you have performed switchover and they Do not want to change anything in their application, What would you do?

*. If during an Upgrade, you have created a restore point in Production and started the Upgrade but now after some time the production has gone down,is there any way to Upgrade the standby and make it production?

*. Have you done scripting for queries? Like can you find out the top 0 tables according to their sizes?Can you write scripts for relevant things as and when the come from client?

*. If i Do not have a control file and log file of RMAN backup can i restore it to another database?Only on the basis of datafiles and archive log files?

*. Can I use duplicate command to restore a backup from a source where the mount point name was different and at destination the mount point name was different?

*. If a user has updated and committed data mistakenly , How will you restore the data back to that table prior to the update operation?

*. How will u sync a table in Goldengate if it has gone into lag, and table is in TBs ?

*. What kind of Performance Tuning have you been involved in?

*. Auditing has been mentioned in your resume ,What kind of auditing have you done?

*. Are you comfortable with database refreshes?

*. Have you done shell scripting and have automated any tasks?

*. Have you worked with other technologies?

*. Have you done silent intallation?

*. Have you Upgraded from c to 9c? Do you know about the preUpgradejar utility?

*. Have worked on OEM?

*. Performance issue faced by you and solution provided

*. Different wait events, there common cause and solution

*. Suppose random customer comes and tell you that there is issue in database (no particular issue provided)

*. What evidence will you give him to proof that the database is working fine

*. What are the common things which you check in AWR report

*. Basic parameters used in dataguard

*. protection mode in brief

*. why sync and async is used

*. why AFFIRM and NOAFFIRM is used

*. What issues you have faced during recovery and What steps you have used to solve the issue

*. have you face block corruption in the database ?

*. Suppose there are - DR databases of single primary database and you have backup of yesterday

*. If there the entire mount point gets corrupted which includes

*. 9-10 datafiles and customer want the issue to be resolved asap How will you approach

*. Have you migrate cross platform databases?

*. What are basic prechecks done during migration

*. Have you done Upgrade ? steps to performs Upgrade and prechecks

*. Have you face any performance issue post upgradation? if yes which are those?

*. What are the solution provided for those performance issue post upgradation?(optimizer_ related parameters need to be set)

*. have you face ora-600, ora-7445, and How Do you approach ?

*. Precheck for manual Patching (not autopatch)

*. have you faced ORA-055?

*. What is unDo segments and why are they used

*. What is PCTFREE and PCTUSED

*. Block Change Tracking

*. What is checkpoint?

*. What is cache fusion

*. What is node eviction?

*. What are components of RAC?

*. Have you configure OEM from Scratch? What all things you Do during configuring OEM?

*. Have you work on exadata?

*. Have you worked on goldengate?

*. Which file you will check if any process is in abedended state?

*. How you will check the cluster health ?

*. Have you perform switchover switchback ? What are the imp things you check before activity?

*. How you will know that my DR database is ready for switchover?

*. What is Cache Fusion

*. What is OLR?

*. What is node eviction?

*. Explain RAC startup process

*. Difference between Virtual IP and Private IP

*. What is value of intance_type for oracle home and grid home?

*. How many voting disks are needed in a -node RAC?

*. What is Scan Ip?

*. How many scan IPs are required

*. How to add node in Oracle RAC? (Mujhse steps likhvaaye paper pe)

*. Which backup is good? Rman or expdp?

*. What is Listener Poisoning?

*. How can you make a rman backup piece password protected?

*. How to perform DR drill through DG broker ?

*. Write down the steps to perform switchover and switchback(ye bhi paper pe likhvaaya)

*. What is ctsd and cssd in RAC?

*. What is cache fusion? Which processes are involved?

*. How to analyze AWR report?

*. Which are the mandatory background processes?

*. Types of TDE?

*. Use of TDE?

*. How have you converted physical standby to snapshot standby?

*. Which parameter is used prior to incase there is a switchover and there happens to be a block corruption in the now primary database

*. What is private interconnect?

*. Command to unlock a user

*. What is the shared pool made up of?

*. How many datafiles max can be added in database?

*. What is bigfile? What are it's advantages?

*. What are the requirements for setting up an Oracle RAC database?

*. What are the various components of SGA?

*. What is parsing? What happens in it?

*. What is transportable Tablespace in datapump?

*. Which background processes are mandatory in RAC?

*. How does the mirroring happen in ASM

*. What is RAID level?

*. How to add and remove disks from ASM disk group?

*. How to add, delete node from RAC database?

*. What is ntpd service in RAC and How is ctsd service better?

*. Why is the default port 65 in RAC?

*. Have you done migration? What are the pre requisites?

*. Can u Upgrade a database from c to 8c? Can u Upgrade from c to 9c?

*. Steps for Manual Upgrade

*. Types of partitioned tables/partitioning?

*. Have u implemented tde?

*. What is the startup sequence in RAC?

*. How many daemons are started up when you start the RAC cluster?

*. Have you worked on performance tuning?

*. How Do u analyze a query and it's Explain plan?

*. Types of dataguard protection modes

*. Types of dataguard?

*. What happens when your reDo log or datafile becomes corrupt?

*. Command to add a datafile

*. What is sga? Pga?

*. What is archival process?

*. What is control file?

*. How many users can connect at a time in the database?

*. What is server process?

*. How Do you track the address of users connected to your database?

*. How Do you purge listener logs?

*. What is sqlnetora?

*. How to take rman full backup?

*. What is differential backup?

*. How to configure DR?Which parameters are used for it?

*. Which Oracle versions have u worked on?

*. Best practice before installing database?

*. How u used to take 5TB backup in yesbank?

*. Best way to clone database?

*. Have u worked on RAC?

*. How many scan IPs are involved?total no Of IPs?

*. What is node eviction?

*. What is split brain?

*. How to setup ASM?

*. Have you installed Goldengate?

*. How to analyze slowness in RAC database?

*. Have u configured dataguard?

*. What are the modes in dataguard?

*. Difference between Maximum protection and Maximum performance?

*. have you done database refresh activity?

*. What is flexasm?

*. In case of Block corruption How Will you open the database?

*. If the rfs process has stopped How will u resolve?

*. In case the hostname of the RAC server has changed What all changes will you have to perform?

*. Have u worked on RAC?

*. What can u use to display the no Of nodes in a cluster?

*. How is select statement processed in Oracle?

*. In parsing ,How many execution plans are generated by the optimizer?

*. How have u used sqlldr in database?

*. Have u used instaclient on Oracle client?

*. What is the startup sequence in RAC?

*. Which tools Do you use to identify alert log errors?

*. Have you done upgradation/migration?

*. How can you create a partitioned table in database with respect to day wise partition?

*. Have you heard of interval in partitioned tables?

*. Which parameter of RAC is used in Expdp

*. How much time does it take for the backup of a production database on ur previous client?

*. How Do u Do switchover switchback in c through dgmrl?

*. What if your datafile was taken offlineWhat is the impact and How will the datafile come online?

*. What is shared pool?

*. What shell scripting have you done so far?

*. What is database cloning?

*. After that they have given one system and they said check load on server,What are values at load average ?

*. They asked me to open test database,that db having issue

*. What is FRA?

*. Is having inactive sessions good or bad?

*. What views are used in active sessions and inactive sessions?

*. We have 50% Oracle and 50% mongodb,postgre,MySQL databases ,will u be open to work on them?

*. Which clients have you worked on?

*. When a user issues a command What happens?(in depth parsing steps)

*. When does the lgwr write the reDo log buffers to the reDo log files?(all events)

*. When does checkpoint happen?

*. What if a redolog file is deleted and I don't have the database in archived log mode or the database doesn't have a rman backupWhat would u do?(ye scenario pe 7-8 mins liya)

*. What is split brain? How will u address it? What will happen in this scenario? How will you or oracle internally handle it?(6 mins)

*. What is the process of adding node in a RAC?

*. I want a backup that should be consistentlike point in time kind of a backupis it possible in Expdp?

*. Is it possible to recover a table that is dropped from a rman backup taken?

*. Below topics which are covered to interview point of view only ,french based banking client ::

*. Explain plan

*. RAC wait events

*. Force_match = true

*. Monitoring template

*. Scripting

*. RAC Startup Sequence

*. RAC Installation

*. RAC node RMAN Restoration

*. RAC Patching (Manual)

*. RAC Node Eviction Types (NHB/DHB)

*. Adding node in RAC

*. GPnP Profile

*. LMD/LMS/LMON/GRD

*. Cache Fusion

*. Working of SCAN

*. VOTING Disk/OCR (Role/Min/Max)

*. Add Service in RAC / Checking whether added successfully?

*. Table Partitioning (HASH/Split)

*. Flashback DB/ Restore Point /Guarantee RP

*. ORA - 0000 Process Parameter Exceeds

*. ORACLE EVENTS - Index Fast full Scan

*. Big-Long Update is running What impact will be on Database (DBBC LRU Mechanism)

*. INITRANS/MAXTRANS/PCTFREE/PCTUSED

*. AMM / ASMM

*. Loss of all controlfile

*. c to g EXPDp/IMPDP (VERSION=)

*. FAL Client/FAL Server

*. DR Sync with Incremental NO REDo Recovery Method

*. Member KILL / Node KILL

*. Remote_listener Scenario (5 Node RAC connections only routing to rd Node only)

*. Access and Filter in Explain plan

*. Unique Index Scan / Index scan by RowID

*. Does datafile Contains Uncommitted Data? yes(for following conditions)

*. Flush buffer cache

*. Before a logfile gets overwritten

*. Manual checkpoint

*. To free buffers

*. Tablespace offline

*. Tablespace read only

*. Tablespace begin backup

*. Troublesshooting Node Eviction (Check /var/log/messages)

*. non rac to rac migration

*. Non asm to asm migration of database

*. manual database creation in RAC

*. creating scan listener

*. How scan Works, changing scan port,

*. Use of Interconnect in RAC

*. Use of OCR, Voting Disk

*. OCR Scenarios

*. - add/delete/replace OCR location

*. - OCR Corruption issues (one or all)

*. - ocrconfig, ocrcheck, ocrdump

*. - What if one of the OCR is corrupted

*. Voting Disk Scenarios

*. - add voting disk location

*. - deleting voting disk location

*. - restore voting disk scenarios

*. - Taking a backup of VD

*. - disk redundacy for Voting Disk

*. RAC processes

*. - lck0,lmsn,lmd0,lmon,ACMS,

*. RAC startup sequence

*. OHASD

*. oraagent,orarootagent,cssdagent,cssdmonitor

*. oraasm,gnsd,gpnpd,evmd,evmlogger,mdnsd

*. ctssd, crsd,diskmon,acfsdrivers

*. (crsd) oraagent,orarootagent

*. ASM,oradg,ons, listener, scan listener,db, ons,eons,gsd, services

*. scanvip,nodevip,gnsvip,gns

*. Cache Fusion, GRD

*. TAF,FAN implementation

*. RAC restoration, creation of Clone in RAC

*. RAC Node Pinning & Unpinning Concept

*. Installation steps , cleanup failed installation

*. How to detect master Node

*. Types of Master Node (Cluster Master,OCR Master,Resource Master)

*. How to add/delete resource

*. How to add/delete Node Instance

*. Patching Rac Setup, One offs, PSU

*. 19c New Features in RAC

*. Rac Node Node

*. Modiying Private IP/Virtual IP for RAC

*. Briefly Explain about architecture

*. What is pmon, smon, dbwr,lgwr,ckpt and archiver

*. Select and update query internal process

*. What is a keep and row cache

*. What is a base tables

*. What is a parsing

*. Difference b/w soft and hard parsing

*. Difference b/w pga and sga?

*. Difference b/w ASSM ans AMM?

*. Difference sg_target and memory_target?

*. Difference b/w sga_max_size and memory_max_target?

*. Difference b/w pfile and spfile?

*. What is alertlog file?

*. What is a 0RA-555 error

*. What is a ORA-00600 error and How you will fix

*. What is a ORA-404 error and How you will fix

*. Yesterday query is good today it's taking time

*. Database yesterday is good but today it's taking time

*. What is AWR

*. What is ASH

*. What is ADDM

*. What is sqlprofile

*. What is sqlbaseline

*. What is average active sessions

*. Difference b/w sqlprofile and sqlbaseline

*. Difference b/w AWR, ASH and ADDM

*. How you will generate AWR and exactly What you will check and briefly Explain it?

*. What is statistics, How will check table and index statistics

*. What is index, How will check index statistics

*. How will check table and index fragmentation

*. What is Explain plan and How you will generate?Table space and user management :

*. What is a tablespace

*. Difference b/w tablespace and table

*. What is a undotablespace and it's advantage

*. What is a temporaray tablespace and it's advantage

*. Difference b/w expired and unexpired blocks in undotablespace

*. How to enforce user requirement

*. User wants its same password How

*. What is a password file and How you will check password assign to user

*. How you will assign tablespace from another tablespace, What happen object inside the tablespace

*. UnDo database having 4hours unDo retention without guarntee clause What happen

*. UnDo database having 4hours unDo retention with guarntee clause What happen

*. Difference between exp and expdp or traditional and datapump or traditional and expdp

*. How you will improve performance of export and import

*. Mandatory things to create schema refreshment

*. How you will perform schema refreshment and table refreshment

*. How you will take only metadata of database

*. What is a table_exists_action

*. How you will rename user

*. User wrongly inserted the date What you will do?

*. Export or import failures happen What is the reason, How you will fix

*. How you will check the dump file job time before export

*. How you will check the dump file size before export

*. How you will export or import the data from unDo table space using traditional and datapump methods

*. Difference b/w cold backup and Hot backup

*. Difference b/w expired backup and obsolete backup

*. Difference b/w image copy and backupset

*. Difference b/w incremental and cummulative backups and differential backups

*. What is a backup startegy or backup policy

*. What is difference b/w full backup and Level 0 backup

*. What is a FRA and How you will enable and manage the FRA

*. Archive destination reaches 90% What you will do

*. Archive destination full What you will do

*. Pfile deleted, control file deleted(Backup and multiplexing not available), datafile deleted, log file corrupted or log file deleted, blocks corrupted and How will Do restore and recovery

*. Table dropped How will restore

*. Database crash at particular time How will restore

*. Disaster recovery(Pfile, spfile,control file, datafiles, logfiles entired deleted) How you will restore

*. Datafile deleted backup not available What you will do

*. How you will perform cloning briefy Explain

*. What is a snapshot controlfile

*. What is a encryption

*. What is a optimazation

*. What is a retention policy and recovery window

*. What is a Crosscheck command

*. Difference b/w no catalog database and recovery catalog database

*. What are the C and 9C featuers Data Guard

*. What is a data guard and How many types and What are there

*. Importance of dataguard

*. Difference b/w physical and logical standby

*. What are the top most services in dataguard

*. Difference b/w active and snapshot dataguard

*. How you wll create standy database

*. What are the mandatory things to create standby database

*. Difference b/w switchover and failover

*. How you will perform switchover and failover

*. Top 5 mandatory things to Do switchover activities

*. How you will patch on data guard environment and Upgrade

*. Difference b/w fall client and fall server

*. What is a standby management

*. Standby database not syn with primary from longtime or huge gap How you will fix

*. Less gap exists How you will fix

*. Archive shipping is very slow or archive shipping not's happening What is your findings and How you will fix

*. log applying is very slow or log applying not's happening What is your findings and How you will fix

*. Primary not available What you will do

*. What is a force logging

*. Tell me few reasons why primary not syn with standby database

*. Nologging operation happen at primary What happen and How you will recover standby

*. Somebody changes sys password at primary What happen and How you will fix

*. Difference between performance mode, protection mode and maximum availability mode

*. Difference between syn and asyn

*. Difference between noaffirm and Affirm

*. How you will perform incremental recovery in standby

*. What is the backup command to create standby database

*. Standby out of syn with primary then How archive backup and deletes

*. What are the C and 9C features in dataguard

*. What is a ASM and it's importance?

*. How you will add asm disk to diskgroup?

*. What is a asm array?

*. What is redundancy and How many type?

*. How you will create tablespace using ASM diskgroup?

*. How you will create external and normal redundancy?

*. How to move external redundancy to normal redundancy entire database?

*. What is asm_power_limit?

*. What is asm background process?

*. What is a power in ASM?Interview questions on RAC

*. What is a cluster and it's advantage

*. Difference b/w standalone instance and RAC instance

*. Difference b/w crash recovery and crash instance recovery

*. What is a ocr file, How you will find location and also backup location

*. How you will backup ocr file and restore

*. What is a olr file and it's importance and What is the location

*. What is a voting file and How you will find it's location

*. Why we need to use odd number of voting file

*. What is a master node and it's location

*. What is split brain syndrome

*. What is a node eviction and it's reason and How you will findout

*. Explain briefly about rac architecture

*. What is the cache fusion, cache coherence, resource affinity

*. Which background process is managing cache fusion

*. What are the rac background process

*. What are the cluster background process

*. What is the cluster sequence process

*. How you will stop and start cluster

*. How you wii stop particular instance

*. How you will check cluster status in entire instance and particular instance

*. How you wiil generate AWR report in rac

*. For node and node How many ip's require

*. What are the private, public and VIP'S

*. What is the SCAN and it's importance

*. What is the difference b/w local listener and scan listener

*. What is a scan ip

*. What is a service and it's properties and you will configure services

*. How you will configure service for particular instance

*. How data will move in rac environment

*. Rac installation

*. How you will psu patch in rac environment

*. How you will Upgrade rac database

*. How you will unlock grid home while applying patches and it's importance

*. What is you daily activities in a rac

*. Any issue faces in rac

*. Difference b/w cpu and psu patch

*. How you will check optach version and installed patches

*. How you will check central inventory

*. Central inventory corrupted How you wiil do

*. Difference b/w local inventory and central inventory

*. Local inventory corrupted then How

*. How you will download patch from oracle support

*. How you will apply psu patch in standalone and rac environment

*. How you will check conflicts, if conflicts happen then What you will do

*. How you will patch at database level

*. Difference b/w g and C patches

*. What is a Upgrade and it's importance

*. How you will Upgrade from g to c

*. How you will Upgrade from c to 9c

*. How you will Upgrade from 0 to 9c

*. Any issues faced while applying patches and upgradation

*. Describe Oracle database architecture?

*. What is SGA?

*. What is the logical and physical structure of the database?

*. What is pfile and spfile?

*. Can we create pfile from spfile or vise versa when the database is down?

*. What is the oracle background process?

*. What is the difference between pmon and smon?

*. What is instance recovery?

*. What is rollforward and rollback?

*. Describe all the database startup options? mount no-mount etc

*. What is Extents? How to calculate table, schema and database size? Give the SQL for each item?

*. What is an oracle tablespace? Can a datafile be a part of two tablespaces?

*. How many tablespaces can be created in a single database? What is the thumb rule?

*. What is unDo and temp tablespace?

*. What is checkpointing?

*. What is checkpoint global?

*. Why Do we need checkpointing?

*. Tell me about oracle memory buffers and parameters?

*. Why Do we need a large pool?

*. List all the mandatory background process names?

*. Can we run multiple databases from a single Oracle database instance?

*. Can we create a Database from scripts without using DBCA?

*. Without an oracle listener can we connect to the database?

*. How to connect to the oracle database from a remote server?

*. Can we connect to the oracle database from remote server odbcini?

*. While oracle database software installation why Do we need Linux packages or Linux?

*. What is the importance of RPM in oracle database software installation?

*. What is the environment variable file?

*. Can we connect to a database without sourcing the environment variable file? Explain both the ways with env File and without?

*. What is the semaphore? Why Do we need it?

*. How to limit the oracle instance memory utilization to a fixed size in the Linux server?

*. What is the difference between schema and user?

*. Why Do we need an index?

*. What is the global index?

*. How to hide the index so that SQL execution should not pick it? What is a cost-based optimizer? How does it work? How you can get an Explain plan for SQL? What is the profiler? How to create a profile for a SQL? What are the Histogram and buckets? What are the Hits? How to sHow only 000 rows of a SQL output from a hint and What hit you will use?

*. What is partitioned index and tables?

*. Can we convert a normal table to a partitioned table?

*. How to track the table row insert, update and delete? Suggest away or auditing tool?

*. What is oracle Grid or an enterprise manager?

*. How to automate daily repeated tasks? give any server-side scripting language name and example of automation?

*. Can we Do automation or trigger shell/python script from Oracle Grid?

*. What is the monitoring profile in the oracle gird

*. How to take a backup of a table or data?

*. What is the Data Pump? How is it more powerful than traditional export-import?

*. Can we export only data without metadata from expdp?

*. If the database size is 4tb and want to take the backup from expdp What is the imp parameter we should use to speed up the process and create the dump file in multiple pieces?

*. How to ignore the errors in impdp?

*. How to kill an expdp job?

*. Can we pause an expdp job?How to determine the dump size in advance without running actual expdp?

My queries

Hi all Hope definitely below queries is very useful to health check the database.

1. To check sync status of the database

select name,instance_name,open_mode,database_role from v$database,v$instance;

select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;

select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select distinct(checkpoint_time) from v$datafile_header;

02. To check active and inactive session details with sqlid, sid and machine.....

#active_sessions_details

Active :

col status for a9

col event for a30

col username for a10

col module for a25

col machine for a15

col osuser for a10

set lines 200 pages 50000

select username,inst_id, sid, command,serial#, sql_id,osuser, row_wait_obj#,machine ,event, module,

round(last_call_et) LCE from gv$session s where username is not null

and status='ACTIVE'

order by inst_id, event, sql_id

Inactive :

col status for a9

col event for a30

col username for a10

col module for a25

col machine for a15

col osuser for a10

set lines 200 pages 50000

select inst_id, sid,username, command,serial#, sql_id,osuser, row_wait_obj#,machine ,event, module,

round(last_call_et) LCE from gv$session s where username is not null

and status='INACTIVE'

order by inst_id, event, sql_id

03. To check Depencies of the objects

col owner format a20

col NAME format a20

col REFERENCED_OWNER format a30

col REFERENCED_NAME format a30

col REFERENCED_LINK_NAME format a30

set lines 200 pages 200

select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where owner='SCHEMA_NAME';

04. To check oracle_home path ,instead of checking oratab in cat /etc/oratab

cd /var/opt/oracle/
cat oratab

05. To check query is forwarding or not in database.

Select SID,BLOCK_GETS,CONSISTENT_GETS,PHYSICAL_READS,BLOCK_CHANGES, OPTIMIZED_PHYSICAL_READS from gv$sess_io where SID='143';

06. To check temp tablespace how much space is consumed.

select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;

07. To refresh mview (materialized view)

select name,database_role,log_mode,open_mode from v$database;

execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');

08. To check when last mv is refresh happens

alter session set nls_date_format='DD-MON-YYYY HH2:MI:SS';
select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');

09. To take ORACLE HOME backup

tar -cvf ORACLE_HOME_BKP1.tar /orahome_slm/app/oracle/product/11.1.0.2/dbhome_3

10. To check oracle NO process related in os level

ps -ef|grep LOCAL=NO|grep -i DB_NAME

11. Delete archives without asking yes/no option (like prompt)

run

{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}

12. Delete archives with prompt asking yes/no option

delete archivelog until time 'sysdate-2';

13. To delete archives for force fully.

delete force archivelog completed before 'sysdate-1';

14. To check particular archives available

list archivelog sequence ( no ) -------check archive exits or not

15. Connect DC to DR or DR to DC,using with service name

checkt the fal server name

show parameter fal_server

conn sys/password@targetservicename as sysdba

16. To Create user profile and assign to profile

SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;

Profile created.

SQL> alter user SCHEMA_NAME profile temp_profile;

17. Mrp start and stop command

Start:

alter database recover managed standby database disconnect from session;

Stop:

alter database recover managed standby database disconnect cancel;

18. To connect pl/sql developer software without tns name

Database -- scan_name:scan_port/service_name

Database -- servicename or ip:port_number/dbname

19. To check Ports numbers

Note : This is based on environment ,below ports is just exmaples :

show parameter remote_listener

srvctl config scan --> to find scan port and scan IP

srvctl config nodeapps -a --> to find virtual port and IP

FOR SCAN IP :1651

FOR Physical IP and Virtual IP : 1621

20. To check port open or not

telnet IP port_number --> command

which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )

for vip and pysical is 1621 ( Note :Based on environment )

srvctl config scan --> to check scan name and ip

srvctl config scan_listener --> to check scan port

srvctl config nodeapps -a ---> to check vip name and IP

srvctl config listener or show parameter remote_listener --> to check vip port

\

21. To deleting the trace files,trim files and audti files in backup ground

nohup find . -name '*.trc' -mtime +1 -exec rm -r {} \; &

nohup find . -name '*.trm' -mtime +4 -exec rm -r {} \; &

nohup find . -name '*.aud' -mtime +3 -exec rm -r {} \; &

22. kill at os level related to database related

Note : don't use without proper knowing the output

ps -ef|grep LOCAL=NO|grep -i DBNAME|awk '{print "kill -9 "$2}' > kill.sh

23. To kill particular session in database level

Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;

Ex:
alter system kill session '324,7879,@2' immediate;

24. To find stale stats and last analyzed for particular schema

stale --> yes means we have to run stats gather,no means no need.

select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;

25. To display (Dynamic query) grants from user to user for tables

SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';

26. To check unix mount point it will shows top consumed in top.

$du -sm *|sort -nr

27. To Take export meta data backup

expdp directory=EXP_SCHEMA_date dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y

28. To view first ten files

ls -al|head 11

29. ASM disk space check

set lines 150 pages 200

select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;

30. To check Tablescape space in database level.

col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000
SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;

31. To check default temp tablespace

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'

32. To check top 20 consumed tablespace detail

SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));

33. To check datafile for particular tablespace releated

set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;

34. To Rebuild index using below command.

alter index schemaname.index_name rebuild online;

35. Rebuild index parallel (online) and no parallel (offline)

alter index USERNAME.INDEX_NAME rebuild parallel 8;

alter index USERNAME.INDEX_NAME rebuild online noparallel;

36. To compile invalid objects

Ex:

alter materialized view schemaname.MV_Name compile;

37. To Check RMAN Running backup in database

rman bkp status

Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;

38. To check the list of Privileges granted to a User (Grantee) :

Note : just for understanding :grantee --- means privilege taken and grantor privilege given

col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';

39. To check the list of Roles granted to a User (Grantee) :

col Granted_role for a12

col Grantee for a12

set line 120

select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';

40: To check the System Privileges granted to a particular Role:

col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';

41. To check the Object Privileges granted to a particular Role:

col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';

42. To create DB link (DATABASE LINK)

conn Schema_name/Schema_name_pwd

create database link <dblink_name> connect to user identified by <user password> using '<database name>';

conn Schema_name/Schema_name#143

create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';

43. To check index ,related to particular schema and particular table.

select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='' and table_owner='USERNAME') group by owner,segment_type,segment_name

order by owner,segment_type,segment_name;

44. To Creating new user/schema

CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;

GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;

45. To check owner of object

select object_name,object_type,owner from dba_objects where object_name=' ';

46. Fragmentation check query

set lines 9999
set pages 2000
select owner,table_name,
round(((blocks*8)/1024),2) "object size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/

47. check holding and waiting session :

set lines 200
col sess for a25
select inst_id,decode(request,0,'holding_slm: ','waiting_slm: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;

48. How to run schema stats gather

EXEC DBMS_STATS.gather_schema_stats('SCHEMA_NAME',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

49. To run Table stats gather

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

50. To check Particular running Job Details

col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');

Part2:Below details is useful very most :

1. To check sync status of the database

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;
select distinct(checkpoint_time) from v$datafile_header;

02. To check active and inactive session details with sqlid, sid and machine.....
@sessions

Active :
col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='ACTIVE'
order by inst_id, event, sql_id
/

Inactive :

col status for a9
col event for a30
col username for a10
col module for a25
col machine for a15
col osuser for a10
set lines 200 pages 50000
select inst_id, sid, serial#, sql_id,osuser, username, row_wait_obj#, event, module,machine,
round(last_call_et) LCE from gv$session s where username is not null
and status='INACTIVE'
order by inst_id, event, sql_id
/

03. To Check Long running session details in Database
@long_running_sessions

set lines 200 pages 8000
col serial# for 9999999
col machine for a20
col progress_pct for 99999999.00
col elapsed for a20
col remaining for a20
col opname for a30

SELECT s.inst_id,sl.opname,
s.sid,
s.serial#,s.status,
s.machine,sl.opname,
trunc(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
trunc(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
and sl.time_remaining>0
order by inst_id;

04. To check Particular session with query details,how running it's running the query.
@querydetails_check

set echo off
set serveroutput on size 50000
set verify off

set feedback off
accept inst_id prompt 'Enter instance id: '
accept SID prompt 'Enter sid: '
DECLARE
v_sid number;
vs_cnt number;
s sys.gv_$session%ROWTYPE;
p sys.gv_$process%ROWTYPE;
cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
dbms_output.put_line('=====================================================================');
select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
open cur_c1;
LOOP
FETCH cur_c1 INTO v_sid;
EXIT WHEN (cur_c1%NOTFOUND);
select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
dbms_output.put_line('INST_ID : '|| s.inst_id);
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Details : '|| s.action||' - '||s.module);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select /*+ RULE */ decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.gv_$lock l, sys.obj$ o
where sid = s.sid
and inst_id='&inst_id'
and l.id1 = o.obj#(+) )
loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END LOOP;

close cur_c1;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
dbms_output.put_line('=====================================================================');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
END;
/
undef SID
set heading on
set verify on
set feedback on

05. To check holder and waiter session in database.
@holder_waiter_session

set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;

06. TO check Blocking session in database .
@blocking_session_details

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

07. To Check Object locking details

@lock_details
set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

08. To check tablespace details with pct size wise.

REM TABLESPACE DETAILS
col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000

SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;

9. To check how much time take query will time to complete.
*sofar*

col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;

10. To connect pl/sql developer software without tns name

Database -- scan_name:scan_port/service_name

Database -- servicename or ip:port_number//

11. To check Ports numbers

Note : This is based on environment ,below ports is just exmaples :

show parameter remote_listener
srvctl config scan --> to find scan port and scan IP
srvctl config nodeapps -a --> to find virtual port and IP

FOR SCAN IP :1651

FOR Physical IP and Virtual IP : 1621

12. To check port open or not

telnet IP port_number --> command

which port we have to give scan port or vip port -------for SCAN ip give 1651 ( Note :Based on environment )

for vip and pysical is 1621 ( Note :Based on environment )

srvctl config scan --> to check scan name and ip

srvctl config scan_listener --> to check scan port

srvctl config nodeapps -a ---> to check vip name and IP

srvctl config listener or show parameter remote_listener --> to check vip port
\

13. To deleting the trace files,trim files and audti files in backup ground

nohup find . -name '*.trc' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.trm' -mtime +3 -exec rm -r {} \; &
nohup find . -name '*.aud' -mtime +2 -exec rm -r {} \; &

For Super Cluster server recomemded by ACS :

Current user below command update 10Feb20 -->
nohup find . -name '*.trm' -type f -print0 -mtime +2 | xargs -0 -n1 rm &
nohup find . -name '*.trc' -type f -print0 -mtime +2 | xargs -0 -n1 rm &

14. kill at os level related to database related

Note : don't use without proper knowing the output

ps -ef|grep LOCAL=NO|grep -i INVEXP|awk '{print "kill -9 "$2}' > kill.sh

15. To Take export meta data backup

expdp directory=EXP_MCLR_23jan19 dumpfile=EXP_schema_metadata_date_%U.dmp logfile=EXP_schema_metadata_date.log schemas=Schemaname content=metadata_only compression=all cluster=n metrics=y

16. To get ddl information of the Objects(procedure,package,dblink,table,materialized view etc...)

for proper format setting ---
set long 999999999 longchunksize 20000 pagesize 0 linesize 1000 --> this will give proper output
column ddl format a1000
set heading on or off

To get ddl information of the procedure:

set long 999999999
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','SCHEMA_NAME') from dual;

To get ddl metarailed view :
set long 999999999
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_NAME','SCAHEMA_NAME') from dual;

To get ddl information of the package and package body :

set long 999999999
select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCAHEMA_NAME') from dual;

SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','SCAHEMA_NAME') FROM DUAL;

To get ddl information of the TRIGGER :

set long 999999999
SELECT DBMS_METADATA.GET_DDL('TRIGGER','TRIGGER_NAME','SCAHEMA_NAME') FROM DUAL;

To get ddl information of the DB link:

set long 999999999
SELECT DBMS_METADATA.GET_DDL('DB_LINK','DBLINK_NAME','SCAHEMA_NAME') FROM DUAL;

To get ddl information of the dba scheduler job data:

set long 999999999
select dbms_metadata.get_ddl('PROCOBJ','SCHEDULER_NAME','SCAHEMA_NAME') from dual;

17. To run .sh script in nohup

nohup sh /export/home/oracle/gatherstatics//_schemastats.sh &

18. To run .sql file script in nohup
nohup sqlplus /nolog < script_file_name.sql > script_nohup.log &

19. To run .rcv file in nohup
nohup rman target / <rman_script_file_name.rcv> rman_script_nohup.log &

20. To check nohup commands related output will display every 50 seconds

while true;do jobs;sleep 50;done

21. To Display limited number of files

ls -lrt log_[1-60]*.xml

22. connect to rman ( From primary to standy )

rman target sys/oracle@DRSERVICENAME

23. To kill particular session in database level

Syntax : alter system kill session 'SID,SERIAL#,@INSTANCENUMBER' immediate;

Ex:
alter system kill session '324,7879,@2' immediate;

24. To find stale stats and last analyzed for particular schema

stale --> yes means we have to run stats gather,no means no need.

select
table_name,
stale_stats,
last_analyzed
from
dba_tab_statistics
where
owner ='schema_name'
order by
last_analyzed desc, table_name asc;

25. To display (Dynamic query) grants from user to user for tables

SQL> select 'grant '||' '||'select'||' '||'on '||' '||'FROMUSER'||'.'||table_name ||' '||'to TOUSER;' from dba_tables where owner='FROMUSER';

26. To Copy command thread in asm mount point in grid user,inside the asmcmd

cp --port 1621 +DATA_R/CBSNGEOY/BACKUPSET/2016_10_14/nnndf0_TAG20161014T182103_0.962.925237267 sys/oracle@serverip.+ASM1:+DATA_P/BACKUP/2016_10_14/nnndf0_TAG20161014T182103_0

--for DBNAME--------

cp --port 1621 +DATA_DG/DBNAME/BACKUPSET/2016_10_20/annnf0_TAG20161020T163553_0.442.925749361 sys/oracle@serverip.+ASM1:+DATA_P/STAGE_RMAN/annnf0_TAG20161020T163553_0

27. To check unix mount point it will shows top consumed in top.

$du -sm *|sort -nr

28. To Set time in database level

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

29. To kill inactive,active with particular module session

select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and username='CARDS';

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where status='INACTIVE' and username in ('');

select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Module='PL/SQL Developer';

select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where status='INACTIVE' and Osuser='Logi Info .Net v4.0' and Module='w3wp.exe';

30. To kill particular with sql_id both nodes

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from gv$session where SQL_ID='274ksuz6zf2xr';

select 'alter system kill session ''' || sid || ',' || serial# ||',@'||inst_id||''' immediate;' from gv$session where sql_id='274ksuz6zf2xr';

31. To kill particular session related with db and os level ( example : select list query table)

SELECT distinct S.SID, S.SERIAL#, Q.SQL_TEXT
FROM V$SESSION S, V$SQL Qf
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and username <> 'SYS';

select distinct '!kill -9 '||p.spid
||chr(10)||
'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'
from v$session s, v$process p, V$SQL Q
where s.username <> 'SYS' and s.username is not null and s.SQL_ID IS NOT NULL
AND Q.SQL_ID = s.SQL_ID
AND Q.SQL_TEXT like ('SELECT%listquerytable%')
and s.paddr=p.addr
/

32. To view first ten files

ls -al|head 11

33. ASM disk space check

set lines 150 pages 200
select name,type,total_mb/1024,free_mb/1024,USABLE_FILE_MB/1024 from v$asm_diskgroup;

34. To check Tablescape space in database level.

col TABLESPACE_NAME for a30
REM Tablespace & freespace
set linesize 150
set pages 10000

SELECT tablespace_name,ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name
order by 5;

35. To check TEMP tablespace size and monitor purpose

TEMP Monitoring :

select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;

TEMP SPACE--------------------

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024,BLOCKS,STATUS,MAXBYTES/1024/1024/1024,MAXBLOCKS/1024/1024/1024 from dba_temp_files;

TEMP FREE SPACE ----------------------

select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024,ALLOCATED_SPACE/1024/1024/1024,FREE_SPACE/1024/1024/1024 from dba_temp_free_space;

TABLESPACE FREE SPACE

select FILE_NAME,BYTES/1024/1024/1024,STATUS from dba_data_files where tablespace_name='

select b.Total_MB,b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,round(used_blocks*8/1024) Current_Used_MB,round(max_used_blocks*8/1024) Max_used_MB from v$sort_segment a,(select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

36. To check default temp tablespace

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'

37. To check top 20 consumed tablespace detail

SELECT grantee, privilege FROM dba_sys_privs where privilege like '%UNLIMITED%TABLE%' and grantee in (select username from dba_users where account_status='OPEN'and username not in ('SYS','SYSTEM','SYSMAN','GGS','DBSNMP','DBMON','DBSPI','OUTLN'));

38. To check datafile for particular tablespace releated

set line 160
set pagesize 500
col name for a70
col TABLESPACE_NAME for a25
select df.NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from v$datafile_header df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE#=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME like upper('&Tablespace')
order by CREATION_TIME,free_space;

39. To Rebuild index using below command.

alter index schemaname.index_name rebuild online;

40. Rebuild index parallel (online) and no parallel (offline)

alter index CXPSADM.SYS_C0097820 rebuild parallel 8;
alter index CXPSADM.SYS_C0097820 rebuild online noparallel;

41. To compile invalid objects

Ex:
alter materialized view schemaname.MV_Name compile;

42. To Check database size

Physical size :

select sum(bytes/1024/1024/1024) Physical_size_gb from dba_data_files;

Actual size(logical size) :

select sum(bytes/1024/1024/1024) Actual_size_gb from dba_segments;

43. To Check RMAN Running backup in database

rman bkp status

Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;

44. To check the list of Privileges granted to a User (Grantee) :

Note : just for understanding :grantee --- means privilege taken and grantor privilege given

col Grantor for a12
col Grantee for a12
col Table_name for a12
set line 120
select GRANTEE, OWNER "OBJECT_OWNER", GRANTOR, TABLE_NAME, PRIVILEGE from DBA_TAB_PRIVS where GRANTEE='Schema_name';

45. To check the list of Roles granted to a User (Grantee) :

col Granted_role for a12
col Grantee for a12
set line 120
select GRANTEE, GRANTED_ROLE, ADMIN_OPTION from DBA_ROLE_PRIVS where GRANTEE=' ';

46: To check the System Privileges granted to a particular Role:

col Role for a12
set line 80
select ROLE, PRIVILEGE, ADMIN_OPTION from ROLE_SYS_PRIVS where ROLE=' ';

47. To check the Object Privileges granted to a particular Role:

col Role for a12
col Owner for a12
col Table_name for a12
col Column_name for a12
set line 120
select ROLE, OWNER "OBJECT_OWNER", TABLE_NAME, COLUMN_NAME, PRIVILEGE, GRANTABLE from ROLE_TAB_PRIVS where ROLE=' ';

48. To create DB link (DATABASE LINK)

conn Schema_name/Schema_name_pwd

create database link <dblink_name> connect to user identified by <user password> using '<database name>';

conn Schema_name/Schema_name#123
create database link DB_LINK_NAME connect to SCHEMA_NAME identified by pwd using 'service_name';

49. To check index ,related to particular schema and particular table.

select owner,segment_type,segment_name,sum(bytes)/1024/1024/1024 from dba_segments where segment_name in (select index_name from dba_indexes where table_name='FT_CORE' and table_owner='CXPSADM') group by owner,segment_type,segment_name
order by owner,segment_type,segment_name;

50. To Check How much RMAN RESTORE STATUS.

set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10

select s.inst_id, o.sid, CLIENT_INFO ch, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM gv$session_longops o, gv$session s
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND o.sid=s.sid
AND totalwork != 0
AND sofar <> totalwork;

Restore Status :

select inst_id, sid, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds
from gv$session where program like '%rman%' and
wait_time = 0 and
not action is null;

51. To Creating new user/schema

CREATE USER Schema_name IDENTIFIED BY passpwd DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE TEMP;

GRANT connect TO Schema_name ;
GRANT RESOURCE TO Schema_name ;

52. To check owner of object

select object_name,object_type,owner from dba_objects where object_name=' ';

53. To check how much query remaining to execute in database level.

@sofar
col target for a30
col opname for a30
select username,sid,opname,target,sofar,totalwork,(sofar*100)/totalwork from v$session_longops where sofar<totalwork;

54. Fragmentation check query

set lines 9999
set pages 2000

select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner = '&OWNER'
order by 1,4 desc
/

55. To check OEM Agent status (Oracle Entireprice Manager)

1-- ps -ef | grep oem
2--cd /u01/app/oracle/product/oem13c/agent13c/
3--cd agent_inst
4--cd bin
pwd----/u01/app/oracle/product/oem13c/agent13c/agent_inst/bin

run---- ./emctl status agent

./emctl pingOMS

56. To check running,holder session in database

running active sessions check:

ses.sql

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

check holder waiter session :
hw.sql

set lines 200
col sess for a25
select inst_id,decode(request,0,'Holder: ','Waiter: ')|| sid sess,id1,id2,lmode, request, type from gv$lock
where (id1,id2,type)in(select id1,id2,type from gv$lock where request>0)
order by id1,request;

57. Error solution suggeted oraclesupport

[cssd(8020)]CRS-1603:CSSD error

through grid

crsctl stat res -t

ps -ef | grep d.bin

crsctl stat res -t -init

58. To check Running RMAN backup in database.

set linesize 150
set pagesize 300
col start_time for a20
col end_time for a20
col time_taken_display for a9
col output_bytes_display for a12

select input_type,
command_id,
status,
to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,
-- to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
output_bytes_display,
time_taken_display
from v$rman_backup_job_details
--Where status = 'RUNNING'
--where COMMAND_ID like 'FULL%'
--where INPUT_TYPE like'DB%'
order by session_key desc;

58.1 rman backup check:

Set line 2000
col end_time for a20
col start_time for a20
col time_taken_display for a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yyyy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yyyy hh24:mi') end_time,time_taken_display,
input_bytes/1024/1024/1024,
output_bytes/1024/1024/1024
from V$RMAN_BACKUP_JOB_DETAILS
order by start_time;

59. To check backup is running on tape or device

set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a20
select distinct i.instance_name,i.host_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where /*r.START_TIME >= SYSDATE-7 and */
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;

60. To check Running backup is incremental level 0 or 1 in tape

set lines 200
set pages 2000
col TIME_TAKEN_DISPLAY for a10
col start_time for a20
col end_time for a20
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
col HOST_NAME for a10
select distinct i.instance_name,r.SESSION_KEY,b.DEVICE_TYPE, r.STATUS,r.START_TIME,r.END_TIME,r.time_taken_display,r.OUTPUT_BYTES/1024/1024 "SIZE_MB",
r.OUTPUT_BYTES/1024/1024/1024 "SIZE_GB",b.INCREMENTAL_LEVEL "INCR_LEV" from V$RMAN_BACKUP_JOB_DETAILS r,V$BACKUP_SET_DETAILS b,v$instance i where --r.START_TIME >= SYSDATE-7 and
r.SESSION_KEY=b.SESSION_KEY and b.INCREMENTAL_LEVEL is not null order by r.SESSION_KEY;

61. To check audit table backup aud$

select count(*) from sys.aud$;

col owner format a20
col segment_name format a20
select sum(bytes/1024/1024),segment_name,owner from dba_segments where segment_name='AUD$' group by segment_name,owner;

62. To check locking table details in database

select object_name,s.sid,s.serial#,p.spid from v$locked_object i,dba_objects o,gv$session s,gv$process p where i.object_id=o.object_id and i.session_id=s.sid and s.paddr=p.addr;

63. To check locking object in particular table

set lines 200 pages 8000
col OBJECT_NAME for a30
SELECT a.INST_ID,/*+rule*/ a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from gv$session a, dba_objects b, gv$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

64. How to run schema stats gather

spool schema_stats.log
set time on timing on echo on feedback on
EXEC DBMS_STATS.gather_schema_stats('schemaname_1',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_2',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats('schemaname_3',degree=>4,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;

65. To Run schema stats gather in .sh file

cat /u02/slm/gatherstats/DBNAME_schemastats.sh
export ORACLE_HOME=/ora_home/app/product/12.1.0/dbhome_1
export ORACLE_SID=DBNAME2
export PATH=$PATH:$ORACLE_HOME/bin
TODAY=`date '+%d%m%Y_%H%M'`
$ORACLE_HOME/bin/sqlplus "/as sysdba" @/u02/slm/gatherstats/DBNAME_schemastats.sql
cp /u02/slm/gatherstats/log/DBNAME_schemastats.log /u02/slm/gatherstats/log/DBNAME_schemastats_"${TODAY}".log
exit;

cat /u02/slm/gatherstats/DBNAME_schemastats.sql
set time on
set timing on
set echo on
set feedback on
set lines 200
set pages 2000
spool /u02/slm/gatherstats/log/DBNAME_schemastats.log
exec DBMS_STATS.GATHER_schema_stats(OWNNAME => 'SCHEMANAME',degree=> 6, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
exit;

To run:
nohup sh /u02/slm/gatherstats/DBNAME_schemastats.sh &

66. To run Table stats gather

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHEMANAME',TABNAME =>'TABLE_NAME',degree=> 8, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

67. To unlock the object while doing stats gather

exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');

exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'TABLE_NAME');

68. To Run dictionary&object stats of the database.

set echo off
set heading on
set feedback off
set termout off
set lines 400 pages 1000
conn / as sysdba
spool gather_stats_dic_object.log
exec dbms_stats.gather_fixed_objects_stats();
EXEC DBMS_STATS.gather_dictionary_stats;
spool off;
exit

69. To run .sql files in nohup mode in two ways givn in below.

1. connect without as sysdba :
nohup sqlplus /nolog < fixed_and_dict_stats.sql > fixed_and_dict_stats_nohup.log &

2. connect with as sysdba :
nohup sqlplus / as sysdba @scprod_procedure_16nov19.sql > scprod_procedure_16nov19_nohup_16nov19.log &

70. To divert the online services from one node to another node using below command.

srvctl relocate service -d HDPPRD -s HDPDB<service_name> -i HDPPRD1<current service instance_name> -t HDPPRD2<target instance_name>

71. To check gg log (golden gate)

gg check log

view report replicate_name

72. To set the defer and enable the log archive dest

Defer :
alter system set LOG_ARCHIVE_DEST_state_2='DEFER' sid='*';

Enable:
alter system set LOG_ARCHIVE_DEST_state_2='ENABLE' sid='*';

73. To check how much resotre is completed.

select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';

74. To check scheduler job in db

col LAST_START_DATE format a30
col JOB_NAME format a15
col NEXT_RUN_DATE format a35
col LAST_RUN_DURATION format a35
select JOB_NAME,NEXT_RUN_DATE,ENABLED,LAST_START_DATE,LAST_RUN_DURATION,FAILURE_COUNT from dba_scheduler_jobs where job_name in ('JOB_NAME')

select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME');

set lines 200 pages 200
col job_name format a30
col stauts format a10
col log_date format a45
col RUN_DURATION format a15
select
log_date,
job_name,
status,
run_duration,
ERROR#
from
dba_scheduler_job_run_details
where job_name in ('JOB_NAME')
order by log_date asc;

75. To check Particular running Job Details

col owner format a10
col job_name format a30
col START_DATE format a35
set lines 200 pages 200
select owner,JOB_NAME,START_DATE,NEXT_RUN_DATE from all_scheduler_jobs where job_name in ('JOB_NAME'');

76. To check error related to job_scheduler

select
log_id,
to_char(log_date,'dd/mm/yyyy hh24:mi:ss') log_date,
substr(job_name, 1,20) job_name,
substr(status, 1,10) status,
additional_info operation
from
dba_scheduler_job_log
where
job_name='JOB_NAME'
order by
log_date ;

77. To check disks using kfod command

kfod disks=all dscvgroup=true status=true

78. To Check PSU patch in db(at os level)

oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i desc
ARU platform description:: Solaris Operating System (SPARC 64-bit)
Patch description: "OCW PATCH SET UPDATE 12.1.0.2.181016 (28259914)"
Patch description: "Database Bundle Patch : 12.1.0.2.181016 (28259867)"
Patch description: "Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017)"
oracle@hostname:…product/12.1.0/dbhome_1/OPatch$ ./opatch lsinventory| grep -i applied
Patch 21065289 : applied on Thu Nov 22 18:00:39 IST 2018
Patch 20898351 : applied on Thu Nov 22 17:58:02 IST 2018
Patch 28259914 : applied on Thu Nov 22 16:43:36 IST 2018
Patch 28259867 : applied on Thu Nov 22 16:41:34 IST 2018
Patch 19339555 : applied on Tue Apr 11 15:54:29 IST 2017
Patch 24917972 : applied on Tue Mar 21 14:43:35 IST 2017

79. To Check how many archive's are generated hour base details

SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'DD-MON-YY' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY INSTANCE ,
log_date
ORDER BY INSTANCE ,
to_date(log_date,'DD-MON-YY');

80. Dynamic Resize tablespace query (it will shows which tablespace we can resize )

set lines 168 pages 5000
SELECT
'/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' Freeable MB */ ' ||'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' DF_RESIZE FROM DBA_DATA_FILES f,
SYS.TS$ t,
(SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC;

81. As per audit based on environment below lister should be on.

ADMIN_RESTRICTIONS_LISTENER = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN1 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN2 = ON
ADMIN_RESTRICTIONS_LISTENER_SCAN3 = ON

82. To Check failed Login details ( wrong password hits while connecting to schema)

select USERID, userhost, decode(returncode,01017,'Login Error','Acount Locked') "ISSUE", spare1,
TO_CHAR ( CAST(
( FROM_TZ(
CAST(
TO_DATE(
TO_CHAR( ntimestamp# , 'DD/MM/YYYY HH:MI PM'),
'DD/MM/YYYY HH:MI PM'
)
AS TIMESTAMP
) ,
'GMT'
) AT LOCAL
)
AS TIMESTAMP)
, 'DD/MM/YYYY HH:MI PM') "Time",
sqltext,
comment$text from SYS.aud$
where ( returncode=1017 OR returncode=28000 )
and userid in 'SCHEMA_NAME'
order by ntimestamp# desc ;

83. Dynamic query to give all select privilege to all tables (from user to user)

spool /u02/slm/grants_<date>.sql
select 'grant '||' '||'select'||' '||'on '||' '||'FROM_USERNAME'||'.'||table_name ||' '||'to TO_USERNAME;'
from dba_tables where owner='FROM_USERNAME';
spool off

84. To Give only table,view grants from user to user

select 'grant select on '||owner||'.'||object_name||' to TO_USERNAME;' from dba_objects where owner='FROM_USERNAME' and object_type in ('TABLE','VIEW');

85. To give sys level privileges to User or grant resorce.

grant CREATE TRIGGER to <USERNAME>;
grant CREATE SEQUENCE to <USERNAME>;
grant CREATE TYPE to <USERNAME>;
grant CREATE PROCEDURE to <USERNAME>;
grant CREATE CLUSTER to <USERNAME>;
grant CREATE OPERATOR to <USERNAME>;
grant CREATE INDEXTYPE to <USERNAME>;
grant CREATE TABLE to <USERNAME>;
grant create session to <USERNAME>;

or

grant resource to Username;

86. To create logfile using rman (while taking rman prompt it will record like spool use of below command)

rman target / log=logfilename.txt

87. To Flush Shared Pool with particluar SQL ID:

Note : Don't use ,if we dont knw exact use of this command in production

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '9yc%';

ADDRESS HASH_VALUE
---------------- ----------
000000095FD77CF0 908321886

SQL> exec DBMS_SHARED_POOL.Schame_name1 ('000000095FD77CF0, 908321886', 'C');

88. As per oracle docs difference between dirrentail and cumulative backup.

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0

A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

89. To check how much restoration status

set lines 168 pages 5000
select SID,MBYTES_PROCESSED/1024 "MBYTES_PROCESSED",STATUS,OPERATION,INPUT_BYTES/1024/1024/1024 "INPUT_BYTES GB",OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES GB" from V$RMAN_STATUS where status='RUNNING';

90. To enable the audit( after using below command,we got updated details in locked account details.

SQL> audit session whenever not successful ;

Audit succeeded.

91. To revoke and grant the user

revoke select, update, insert on emp from slm;
revoke select, update, insert on emp from slm;
grant select, update, insert on emp to slm;

9. Export backup ,dump file storing different location in mount points.

[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full_cron.sh

[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/expdp_DBNAME_full.sh
#!/bin/sh
export ORACLE_BASE=/oracle/app/product
export ORACLE_HOME=/oracle/app/product/11.2.0.4/db_home1
export ORACLE_SID=DBNAME
export DT=`date '+%d%m%Y_%H%M%S'`
export EXPFILE=expdp_DBNAME_`date '+%d%m%Y_%H%M%S'`
/oracle/app/product/11.2.0.4/db_home1/bin/expdp parfile=/orabkp_slm/EXPBKP/script/details.par dumpfile=EXP_BKP:${EXPFILE}_DIR1_%U.dmp,EXP_BKP1:${EXPFILE}_DIR2_%U.dmp,EXP_BKP2:${EXPFILE}_DIR3_%U.dmp,EXP_BKP3:${EXPFILE}_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_$DT.log FULL=Y compression=all parallel=16
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP1/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP2/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP3/ -name 'expdp_DBNAME_*.dmp*' -mtime +1 -exec /bin/rm {} \;
/usr/bin/find /orabkp_slm/EXPBKP/ -name 'expdp_DBNAME_*.log' -mtime +15 -exec /bin/rm {} \;
##cd /orabkp_slm/EXPBKP/
##gzip expdp_DBNAME_*.dmp

93. To check crs log file location

example :
/asm_home/app/grid_base/diag/crs/Hostname/crs/trace

94. desc sys.user$ details

NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon

95. To check object is locked or not

SELECT B.Owner, B.Object_Name, A.Oracle_Usernam e, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;

96. To check whic object user is accessing

select * from gv$access where object='';

97. wallet not open ( this is related to environment issue,)while converting snapshot to physical ,

Note :simply set the database environment and check

take directly down.

Before taking up fire below command :

srvctl setenv database -d DBNAME -T ORACLE_UNQNAME=DBNAME

Take Db up and check status

On sql prompt :

set lines 168 pages 5000
col WRL_PARAMETER for a40
select * from gv$encryption_wallet order by 1;

98. explan plan for query

syntax : explain plan for "QUERYL_DETAILS";

select * from table (dbms_xplan.display);

99. Don't Ask any comment, not display any comment in query

SET DEFINE OFF

100. To check how many object present in schema

col owner format a30
select count(1),owner,status,object_type from dba_objects where owner='SCHEMA_NAME' GROUP BY owner,status,object_type;

101. To check expdp status (attach file in expdp)

Example :
[oracle@Hostname EXP_SCHEMA_NAME_schema 02:08:48]$ expdp attach=SYS_EXPORT_SCHEMA_02

Export: Release 12.1.0.2.0 - Production on Mon Oct 7 14:08:54 2019

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

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Job: SYS_EXPORT_SCHEMA_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 944D134E49C9751DE0532C99510AEC28
Start Time: Monday, 07 October, 2019 11:52:29
Mode: SCHEMA
Instance: SLM
Max Parallelism: 8
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** AS SYSDBA directory=expdp dumpfile=Exp_SLM_SCHEMA_NAME_schema_Date_%U.dmp logfile=Exp_SLM_SCHEMA_NAME_schema_Date.log schemas=SCHEMA_NAME compression=all parallel=8 cluster=n
COMPRESSION ALL
State: EXECUTING
Bytes Processed: 31,675,147,640
Percent Done: 84
Current Parallelism: 8
Job Error Count: 0
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_%u.dmp
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_01.dmp
bytes written: 4,096
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_02.dmp
bytes written: 5,163,536,384
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_03.dmp
bytes written: 5,696,098,304
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_04.dmp
bytes written: 5,348,511,744
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_05.dmp
bytes written: 4,792,729,600
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_06.dmp
bytes written: 5,654,609,920
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_07.dmp
bytes written: 5,028,229,120
Dump File: /orabkp/EXP_SCHEMA_NAME_schema/Exp_SLM_SCHEMA_NAME_schema_Date_08.dmp
bytes written: 37,781,504

Worker 1 Status:
Instance ID: 1
Instance name: SLM
Host name: Hostname
Process Name: DW00
State: WORK WAITING

102. TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

The possible values have the following effects:

SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

APPEND loads rows from the source and leaves existing rows unchanged.

TRUNCATE deletes existing rows and then loads rows from the source.

REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

103. In full expdp backup,exlude two schemas

expdp system/<password> directory=DATA_PUMP_DIR dumpfile=EXPDAT%U.DMP FULL=y exclude=schema:\"IN \(\'SCOTT\',\'SYSTEM\')\"

[oracle@Hostname]$ cat /orabkp_slm/EXPBKP/script/details.par_bkp_01102019
userid=' / as sysdba'
EXCLUDE=SCHEMA:"IN ('Schame_name1','Schame_name2')"

parfile=/orabkp_slm/EXPBKP/script/details.par_bkp_01102019 dumpfile=EXP_BKP:EXPDP_DIR1_%U.dmp,EXP_BKP1:EXPDP_DIR2_%U.dmp,EXP_BKP2:EXPDP_DIR3_%U.dmp,EXP_BKP3:EXPDP_DIR4_%U.dmp directory=EXP_BKP logfile=expdp_DBNAME_.log FULL=Y compression=all parallel=16

104. server to server scp command

nohup scp * oracle@10.0.0.143:/slm1/BKP_DETAILS/ &

105. To check db link details

col owner format a15
col db_link format a20
col username format a15
col host format a35
set lines 200 pages 200
select * from dba_db_links where owner in ('schemaname');

106. To find fragmentation of the tables

list of the tables to find fragmented tables :

select owner,table_name,
round(((blocks*8)/1024),2) "table size mb",
round((num_rows*avg_row_len/1024/1024),2) "actual data in table mb",
round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2) "wasted space mb",
((round(((blocks*8)/1024),2)- round((num_rows*avg_row_len/1024/1024),2)) / (round(((blocks*8)/1024),2)) ) *100 "percent fragmented"
from dba_tables
where blocks != 0
and owner in ('');

top listed tables and tablespace details :

select t.owner,t.table_name, s.tablespace_name,round(s.bytes/1024/1024) actual_MB,round(((t.num_rows+1) * t.avg_row_len)/1024/1024) optimal_MB,round(s.bytes/1024/1024) -round(((t.num_rows+1) * t.avg_row_len)/1024/1024) CLAIMABLE_MB from dba_tables t, dba_segments s where t.owner = s.owner and t.table_name = s.segment_name and round(s.bytes/1024/1024) - round(((t.num_rows+1) * t.avg_row_len)/1024/1024) > 50 order by CLAIMABLE_MB desc;

107. Details autentication service

#none --> db able to connect
#nts --> application able to connect

108. bin directory actual permssion

Executable Directory
# chmod 750 $ORACLE _HOME/bin ## -rwxr-x---

109. TO check modification of the table count like insert,update,delete etc

SELECT TABLE_OWNER,
TABLE_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP AS `LAST_CHANGE`
FROM DBA_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;`

110. Normal DB Health check

select name,open_mode,database_role from v$database;

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
select startup_time from v$instance;

select distinct(checkpoint_time) from v$datafile_header;
select thread#,max(sequence#) from v$archived_log a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;

select thread#,max(sequence#) from v$log_history a,v$database b where a.resetlogs_change#=b.resetlogs_change# group by thread# order by thread#;

Check tablespace,asm level and mount point size.

111. How to take level 0 Backup

oracle@Hostname:/rmanbkp/SLM_BKP$ cat rman_L0_jan.rcv
CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET;
run
{
crosscheck archivelog all;
BACKUP incremental level 0 as compressed backupset section size 20G DATABASE FORMAT '/rmanbkp/SLM_BKP/DBNAMEFULL_%U_%D_%M_%Y';
BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/rmanbkp/SLM_BKP/control_%d_%u_%s_%T';
}
oracle@Hostname:/rmanbkp/SLM_BKP$

112. re-org dynamic query for tablespace (not checked )

select '/* '||bytes/1024/1024/1024||' */ alter '||segment_type||' '||owner||'.'||segment_name||' move tablespace AD_TBS_TMP parallel 16;'
||chr(10)||
'alter '||segment_type||' '||owner||'.'||segment_name||' noparallel;'
from dba_segments where tablespace_name='AD_TBS'
order by bytes/1024/1024/1024 desc
/

113. To check Depencies of the objects

col owner format a20
col NAME format a20
col REFERENCED_OWNER format a30
col REFERENCED_NAME format a30
col REFERENCED_LINK_NAME format a30
set lines 200 pages 200
select owner,name,type,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from dba_dependencies where owner='SCHEMA_NAME';

114. To check oracle_home path ,instead of checking oratab

cd /var/opt/oracle/
cat oratab

115. To check session is forwarding or not ,based on physical read

SQL> desc v$sess_io
Name Null? Type
------------------------------------- -------- ----------
SID NUMBER
BLOCK_GETS NUMBER
CONSISTENT_GETS NUMBER
PHYSICAL_READS NUMBER
BLOCK_CHANGES NUMBER
CONSISTENT_CHANGES NUMBER
OPTIMIZED_PHYSICAL_READS NUMBER
CON_ID NUMBER

SQL> select SID,BLOCK_GETS,CONSISTENT_GETS,PHYSICAL_READS,BLOCK_CHANGES,CONSISTENT_CHANGES,OPTIMIZED_PHYSICAL_READS from gv$sess_io where SID='1452';

116. To check temp tablespace how much consumed

select name,db_unique_name,TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024/1024 as tbs_size,FREE_SPACE/1024/1024/1024 as tbs_free,
round(((TABLESPACE_SIZE/1024/1024/1024)-(FREE_SPACE/1024/1024/1024))/(TABLESPACE_SIZE/1024/1024/1024),2)*100 as pct_full
from dba_temp_free_space a,v$instance,v$database;

117. To refresh mview (materialized view)

conn / as sysdba
set echo on
set time on
set timing on
set feedback on
spool spool_name.log
show user
select name,database_role,log_mode,open_mode from v$database;
execute DBMS_MVIEW.REFRESH('SCHEMA_NAME.MV_NAME','C');
spool off;
exit;

118. To check when last mv is refresh

select OWNER,MVIEW_NAME,last_refresh_date from all_mviews where owner='' and mview_name in ('');

119. To unlock and lock the object while doing stats gather

stats gather:
exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SCHEMA_NAME',TABNAME =>'Table_name',degree=> 20, CASCADE=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

unlock object:
exec dbms_stats.unlock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');

lock object:
exec dbms_stats.lock_table_stats(OWNNAME => 'SCHEMA_NAME',TABNAME =>'Table_name');

120. To take ORACLE HOME backup

tar -cvf ORACLE_HOME_BKP1.tar /orahome12c/app/oracle/product/12.1.0.2/dbhome_1

121. To check oracle NO process related in os level

ps -ef|grep LOCAL=NO|grep -i DB_NAME

122. Delete archives without asking yes/no option (like prompt)

run
{
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-2';
delete force noprompt expired archivelog all;
}

123. Delete archives with prompt asking yes/no option

delete archivelog until time 'sysdate-2';

124. To delete archives for force fully.

delete force archivelog completed before 'sysdate-1';

125. To check particular archives available

list archivelog sequence ( no ) -------check archive exits or not

126. Connect DC to DR or DR to DC,using with service name

checkt the fal server name
show parameter fal_server

conn sys/password@targetservicename as sysdba

127. To Create user profile and assign to profile

SQL> create profile temp_profile limit password_reuse_max unlimited password_reuse_time unlimited;

Profile created.

SQL> alter user SCHEMA_NAME profile temp_profile;

;
128. Mrp start and stop command

Start:
alter database recover managed standby database disconnect from session;

Stop:
alter database recover managed standby database disconnect cancel;

**********************Thank You ***********************************

Thank You