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?
https://asrblogger.com/solution-of-oracle-database-19c-practical-assignment/