PARAMETER & PASSWORDFILE:
* Create pfile from spfile to location '/home/oracle/init<sid.ora>'
* Create password file with sys password as oracle1
* Create parameterfile in different location /u01 & start the DB to up and running condition.
* How to check all parameters associated with database?
* How to change specific parameters with Scope=both/spfile/memory?
* How to check which parameter files required DB bounce for the changes to get reflected?
* How to check which parameter files which can be change dynamically ?
* How to check which parameter files cannot be changed?
PROCESS & SESSION:
* How to check total/active/inactive session?
* How to identify and kill inactive session?
* How to kill multiple inactive session via script?
* Commands to check blocking session?
* How to increase process parameters?
ARCHIVELOG:
* How to set recovery file destination to '/u01/app/oracle/FRA'
* How to set recovery file destination Size to 8900MB.
* How to enable archivelog?
* How to disable archivelog?
* How to Check size of archive generation per day ?
* How to check FRA Usage?
DB CREATE/DROP & MULTIPLEXING:
* Manual Database Creation
* Drop database manually
* Multiplexing of Controlfile to location '/u02/app/control03.ctl'
* Multiplexing of Group & Member of Redologfile to different location.
* How to resize relogfile (say from 50MB to 100MB)
Startup & Shutdown Stages:
* How to start DB in restricted mode.
* How to bring DB to no restricted mode.
Listener & TNSnames:
* Create listener with port 1721 via netmgr.
* Create TNSnames file with manual method.
* Perform DB connectivity using service name:
-> When Listener is down.
-> When Listener is up.
* List out some ORA error if user is not able to do connectivity using service name due to listener issues?
* How to stop, start the Listener Process?
* How to check status of listener?
* What command you will use to reload listener?
* How to check listener status from OS prompt?
* How to kill listener process(PID) from OS prompt?
* How to check listener logs?
Tablespace and datafiles
* Create a tablespace sha_tb01.
* Create a datafile named as shadb01.dbf in '/opt/stage/shadb01.dbf' with size 2GB
* How to resize datafile shadb01.dbf to 4GB
* How to check list of datafiles and tablespaces in database.
* Command to check tablespace utilization (%free,%used)
* How will you check the number of datafile & its size which belongs to spcific tablespace.
* How to Drop non default datafile?
TEMP TABLESPACE:
* Create temp datafile sha_tmp with 200M size.
* Check number of temp datafiles with size.
* Command to check temp tablespace Utilization (%free,%used)
* How to Drop temp (Default) datafile?
UNDO TABLESPACE:
* How to add undo datafile with size 1GB?
* How to check undo retention parameter at Database level?
* How to increase undo retention polity for 1Day?
* Commands to check undo tablespace utilization.
* How to Drop undo datafile?
USER Administration:
* Create a user named as SHAUSR
* How to check number of user and their account status at DB level?
* How to lock user account?
* How to unlock User account?
* How to identify the default tablespace allocated to particular user?
* How to drop a user normally and with cascade option?
* How to check system privilege assinged to a user?
* How to check Object privilege assinged to a user?
* How to check what roles is assinged to a user?
* Create a script to Kill all session of a specific user?
PRIVILEGES:
* Grant system privs(Create session, create tablespace) to user SHAUSR.
* Grant system privs(Create session, create tablespace) to user SHAUSR1 with admin option.
* How to check which user has admin privs?
* Grant object privs(Select , Insert) to user SHAUSR to access and insert HR.Employees tables.
* Grant object privs(Select , Update) to user SHAUSR1 to access and append HR.Employees tables with Grant option.
* Create a read user "SHA_READ" and grant Select privs to access all HR Schemas database objects. (With Script)
* Revoke system privs(Create session, create tablespace) from user SHAUSR.
* Revoke object privs(Select , Insert) from user SHAUSR to restrict access HR.Employees tables.
ROLE:
* Create a role names as SHAR1.
* Grant Create session, create tablespace privs to role SHAR1.
* Grant role SHAR1 to user SHAUSR.
* Revoke role SHAR1 to user SHAUSR.
* How to check what privs is present in specific roles?
* How to how many roles are craeted at DB level?
PROFILE:
* How to check what is the default profile associated with Database?
* How to create profile SHA_PR?
* Create a profile with password limits: PASSWORD_GRACE_TIME 10
* Create a profile with resource limits: SESSIONS_PER_USER UNLIMITED
* How to assinged profile SHA_PR to user SHAUSR
NETWORKING:
* How to make IP Static and perform connectivity with putty.
* Create Database Link named as 'SHALINK'
* How to check the list of datalinks?
* How to change hostname for a server?
Locking & Latching:
* Perform a Deadlock Scenerio.
* Try to perform shared lock.
* Commands to check Deadlock.
CRONTAB:
* Automate RMAN Full Backup using Shell Script in Crontab.
* Automate Script in Crontab to delete .trc,.trm,.aud files keeping 1 day logs.
* Automate Script in Crontab to check tablespace & FRA usage if it cross beyond threshold value ie,(80%)
BACKUP:
* Permorm Hot backup.
* Perform Cold backup.
* How to configure retention policy to redundancy 2?
* How to configure controlfile autobackup ON?
* How to delete obsolete and Expired backup via RMAN?
* How to take Incremental 1 level (Cumulative & Differential) & 0 level incremental backup?
* How to Configure RMAN Default Channels ?
* How to taking Compressed RMAN Backups in RMAN?
* Command to check backup status ,its completion & running status?
* How to delete archivelog keeping 2 days logs?
* How to kill/disconnect RMAN Backup session?
* How to backup controlfile to trace?
RESTORE POINT:
* How to create Guranteed restore point?
* How to check if Guranteed restore point is created or not?
* How to Drop Guranteed restore point?
FLASHBACK:
* How to enable Flashback?
* How to check flashback is enabled or disabled?
* How to flashback a dropped table from recyclebin?
* How to purge recycle bin?
* How to Flashback Database to SCN & Timestamp ?
RECOVERY:
* How to recover System Datafile by RMAN ?
* How to recover Syasaux Datafile by RMAN ?
* How to recover Users Datafile by RMAN ?
* How to create recovery catalog?
* How to recover corrupted block?
* How to Recover dropped tablespace with RMAN ?
* Recovery of Drop Table with purge condition using Current SCN?
* How to Recover drop table from Recyclebin using Flashback?
* Restore control file when it lost without any backup?
EXPDP/IMPDP:
* Commands to check expdp jobs status?
* How to start & stop expdp jobs?
* How to kill expdp/impdp jobs?
* How to export a schema from 12c and import in 19c?
* How to take expdp of schemas & tables ?
* How to take backup of data & metadata of specific schema?
* How to take backup of only metadata of specific schema?
* Perform Remap schema <HR:SHA>
* Perform Remap TABLE <EMPLOYEE:EMPP>
* How to run export/import backup via NOHUP?
PERFORMANCE TUNING
* Generate AWR Report
* Generate ASH Report
* Generate ADDM Report
* CREATE INDEX (B TREE)
* How to find Stale object and last analyzed for tables/indexes.
* How to check indexes name in specific Tables?
* How to find on which column index is created?
* How to rebuild index?
* How to perform stats gather (For table/index)?
* How to generate SQL Tuning Advisory Report?
* Commands to identify I/O & Estimate execution time for specific SQL Query?
* How to Pin a Plan with SQL Profile?
* How to check growth of the table?
* How to Find waitevent in database?
===
ORATOP Utility
TOP Command
EXTRAS:
* Commands to check DB Instance startup time?
* How to check when was server rebooted?
* How to check the OS Type?
* How to identify memory and CPU of server?
* How to check hugepages value?
* What contents to delete if filesystem utilization reached beyond 80% threshold?
* Commands to check Database size?
* Commands to flush CURSOR?
TEST ASSINGMENTS:
* Create and configure Duplicate Database named as SHADB?
* How to perform RMAN Refresh?
