tesdbacademy

Tuesday, September 19, 2023

SQL LABS - Views, Sequence, Synonyms

Views, Sequence, Synonyms

views and sequence

1. create a view whose deptno=10 and 20;

2. create a view without a table then create a table and compile the view;

3. create the read-only view for the emp table deptno=30;

4)create a table from Scott.emp3 from emp add a column sno,

if data is inserted into the table the sno column should be increment by 1;

synonyms

create user u1 and u2

-> Create a table emp and dept in u1.

-> From u1 create synonyms for table emp and dept tables

->grant only select privilleges to u2 from u1 on emp table.

->grant update and select privileges to u2 from u1 on the dept table.

-> From u2, call the table by using the synonym name

SQL LABS - Privileges

Privileges

1)Create Two Users:

-> Create Duplicate table of emp1 from emp;
-> User1 should insert ,select,delete from scott.emp1
-> User2 should insert ,select,delete from scott.emp1;

2) check the system and object privilages for all users(scott, user1,user2);

3) from user1 if i give select * from emp1 data should come from scott.emp1
this emp1 user1 should be used all other users;

SQL LABS - Joins

JOINS

Tasks based in scott user

1)select name,job,sal from emp whose dept,dname is sales?

2)list out the names who hiredate on apr and belongs dallas(location);
3)list out the sal  and comm by dept wise

4)create a duplicate table emp1 from emp and add a column name Bonus then update the bonus with  adding   of the salary

new york 10% update
eg.dallas 20 % update
chicago 30% update
boston 40% update

5.create a duplicate table emp2 from emp and give me below commands.
->insert into emp2
->select * from emp;
->delete  all the duplicate records
->print nth and salary
->print name and location of the person who has 2nd max salary
->list out the ename and job and deptname whose the top 5 salary

6.which emp has the highest age?
7.print the job and location whose job greater than 5000
8.print the smith data two times

SQL LABS - Functions

FUNCTIONS

SQL> create table students(COURSE_ID varchar2(20), COURSE_NAME varchar2(20), TRAINING_TYPE varchar2(20), LOCATION varchar2(20), FEES number(10), DURATION int);

select * from students;

COURSE_ID COURSE_NAME	  TRAINING_TYPE     LOCATION 	FEES	 DURATION 
-------------------- -------------------- -------------------- ---------- 
c001	 sql_plsql	  online	     chennai	10000	   20     
c002	 oracle_dba 	  online	     chennai	30000	   180    
c003	 linux	 	  online	     chennai	5000 	   10

c004	 rac		  online	     mumbai	15000      20	  
c005	 dataguard	  online	     banglore 	7000       20	  
c006	 golden_gate	  online	     bangalore	15000      90	  
c007	 aws		  online	     chennai	15000	   30     
c008	 postgresql_dba	  online	     chennai	25000	   30     
c009	 oracle_developer online	     bangalore	15000      20	  
c010	 sql		  classroom          chennai	5000	   20	  
c011	 plsql		  classroom          chennai	10000	   30	  
c012	 oracle_dba 	  classroom          bangalore	30000      200	  
c013	 linux		  classroom          mumbai	5000       10	  
c014	 rac		  classroom          mumbai	15000	   20     
c015	 dataguard	  classroom          bangalore	7000       20	  
c016	 goldengate 	  classroom          bangalore	15000      20	  
c017	 aws		  classroom	     chennai	15000	   30     
c018	 postgresql_dba	  classroom          chennai	25000      30	  
c019	 oracle_developer classroom          mumbai	15000      20	  

19 rows selected.



1. Which course has maximum fees.


2. List out the courses that is taken in Chennai.


3. List out the courses that is taken online training from Bangalore.


4. What is the fees for online oracle dba training in chennai.


5. List out the course name from c001 to c006


6. Print the total fees that which is taken for all ONLINE training course.

7. Print the Characters starting with 2 , 5 from training type.\
Eg. for ONLINE -> NLIN, for CLASSROOOM -> LASS


8. Print the fees total with respect to location
eg .
CHENNAI 135000
MUMBAI 55000
BANGALORE 84000


9. Print out the Location that has 2 'A's.
eg. BANGALORE


10. Print out the courses that starts with 'O'


11. Print out the courses that has "_".

12. Replace 'ONLINE' in TRAINING_TYPE as 'ONLION'


13. Which course has second highest duration.


14. What are the course names that has location ends with 'I'
 

15. Print the courses for which fees is between 10000 and 20000


16. Which course has maximum character and its count.
eg. ORACLE_DEVELOPER 15


17. Remove 'C' from COURSE_ID and find the highest number from it.


18. Print the record that has C012 two times


19. Find the Highest average of Classroom training Fees respective to location.


20. Find the sum of salaray with respective to training_type and location.

21.print the sysdate in '24/07/2010' format;

22.print the sysdate in '24-07-2010:23:00:00' format;

23.Print sysdate twice
ex.
04-MAY-2021
04-MAY-2021

Monday, September 18, 2023

TesDBAcademy

TesDBAcademy - Oracle Training

Linux:

Linux commands

SQL LABS - Constraints

1. Constraints

create a table with the below specification:
Table name: Accounts
columns names :
account_id number(5), ( no duplicates or null values allowed)
account_name varchar(20)
account_type varchar(20) ,It can have only 2 types(SAVINGS) , create a check constraint name as chk_act_type to do that
join_date date , sysdate should be automatically inserted if not specified( use a constraint)
inital_deposit number(10), mininum of rs.10000 should be deposited,( use a constraint) constraint name as chk_intdepo
address varchar(50),
contactno number(10),
location varchar(20))
After creating the table, do the following

2. Add a not null constraint to contactno.

3. Add a check constraint to location and the data inserted should be (NORTH,EAST,WEST,SOUTH).

4. Modify the Account_type to have two options (SAVINGS and CURRENT).

5. insert 5 records with the below values.

1,CHERRY,SAVINGS,10000,CHENNAI,9980499232,SOUTH
2,SAKTHI,CURRENT,12000,BANGALORE,9980493232,SOUTH
3,NEHA,SAVINGS,15000,PUNE,9980499242,NORTH
4,SHIVANI,CURRENT,10000,PUNE,9985499232,SOUTH
5,TEJA,SAVINGS,14000,ASSAM,9980499232,NORTH
6,KAVIYA,SAVINGS,20000,CHENNAI,9980439232,EAST
7,HASINI,CURRENT,17000,CHENNAI,9980239232,WEST

6. Check how many constraints are there in ACCOUNTS table. Also, specify its type.

7. Check which columns have constraints

8. How many indexes are there for this table.

9. Create a table TXN with following columns

column name :
account_id number(5)
account_name varchar2(20)
txn_date date, without null
txn_type varchar2(10)(DEPOSIT/WITHDRAWL) -> use check constraint
amount number(10)
after creating the table.
Add a foreign key for the TXN table referencing accounts(account_id)

10. Check how many constraints are there in ACCOUNTS/TXN table. Also, specify its type.

11. Check which columns have constraints on both tables

12. How many indexes are there for this table on both tables

13. insert below records. If you get any errors, specify the reason why you were not able to do it and fix it.

1,CHERRY,sysdate,DEPOSIT,10000
8,SAI,sysdate,DEPOSIT,20000
4,SHIVANI,sysdate,WITHDRAWL,30000
4,SHIVANI,sysdate,DEPOSIT,40000
2,SAKTHI,sysdate,WITHDRAWL,2000

14. delete a record from accounts with accountid=1

15. delete account_id=2 from accounts , and make sure that the records from TXN also deletes automatically. (use on delete cascade)

16. Disable all the constraints for accounts and txn( create a alter script for that)

17. insert below records

4,SHIVANI,sysdate,WITHDRAWL,10000
11,SHIVANI,sysdate,DEPOSIT,40000
10,SAKTHI,sysdate,WITHDRAWL,2000

18. Enable all the constraints for accounts and txn (without validating it)( create a alter script for that)

19. Check how many constraints are there in ACCOUNTs/TXN table. Also, specify its type

20. Check which columns have constraints on both tables

SQL LABS – Sql Practice Questions

1. what is the difference between delete, drop and truncate?

2. What is the varchar,varchar2 and char?

3. How will you check the table size and index size?

4. How will you check when the object is created?

5. What is difference between object and segment? Give examples.

6. Where will you check if my view is valid/invalid?

7. How will you compile a view?

8. Is it possible to create a view without table?

9. What is the size of view if the table size is 1gb?

10. What is public and private synonym?

11. What is sequence?

12. what is outer join?

13. List out the commands that does sorting?

14. print the sysdate in ’24/07/2010′ format;

15. print the sysdate in ’24-07-2010:23:00:00′ format;

16. create a table One as give below.

create table one (tdate date);
insert into one values(sysdate);
insert into one values(sysdate);
insert into one values(sysdate);
insert into one values(sysdate);
commit;
select * from one where tdate=sysdate;

Why there are no records fetched?

Hint : select * from one where trunc(tdate)=trunc(sysdate);

17. Disable all primary and foreign keys keys, expain the steps;

18. How will you clean the schema?

19. Scenario:

We have table “CHILD” of 10M records. It has reference to another table “PARENT”. It has foreign key.
now we need to upload 10M records to CHILD, we are sure that all the new records are compatible with PARENT.
How will you load the data quickly ?
HINT: disable constraint., Load the data, enable constraint with no validate – if you dont have time for validate,
When time permits validate the constraint.
Explain all the steps.

20. Difference between UNIONand UNION ALL?

21. When will you use MINUS & INTERSECT?

22. Print sysdate twice using single query.

ex.
04-MAY-2021
04-MAY-2021

23. Types of views?= Read only, simple, complex view. Explain and give example.

24. What are object and system privileges, where will you check that.

25. Explain with grant option and with admin option.

26. How will you delete duplicate records?

27. What are pseudo columns. Name them.

28. How will increment sequence?

29. Create a table two;

create table two(a varchar2(10)); [no data inserted.], If the below sql executed you should get 1 as answer.
select func(a) from two;
———————
1
what will be the func(a);
Hint : NVL, MAX

30. What is correlated subquery? Give example

Linux Lab – Linux Shell Scripts Assessments

Linux Shell Scripts Assessments

1) Write a shell script to print the following :

========================================================================================
				  	 CHOOSE ANY ONE
========================================================================================
					 1 .  CPU information
					 2 .  RAM information
					 3 .  Kernel  information
					 4 .  OS  version
					 5 .  Hostname
					 6 .  Ipaddress
					 7 .  Exit
=========================================================================================

Enter your Choice :

2) create a folder like /home/oracle/sfile/

Write a shell script to check the file(excel_19112020.txt) exists.

The checking should continue for every 2 secs with additional “.” suffixing the checking word.
ex. Like checking………..

If the file exists , then print “File received”.

3) Create a shell script to greet by given time

like
$ ./greetme.sh 11

If the input is between 0 and 12 , then should print “Good Morning!!!”
if between 13 and 18 , then should print “Good Afternoon”
if between 19 and 24 , then should print “Good Night”

4) Write a shell script to create a database where the inputs ae passed as arguments

./CreateDB.sh {dbname} {path}
ex : ./CreateDB.sh sdbt /u01/app/oracle/oradata/sdbt

Tablespace information:
System 500m
sysaux 500m
UNDOTBS 100m
TEMP 100m

Pfile :
dbname
sga_target=800m
undo_management=AUTO
undo_tablespace=UNDOTBS

Also, Make sure, catalog and catproc were executed.

5) Write a shell script to monitor alertlog.

6) Write a shell script to do log rotate , remove logs < sysdate-2

7) Write a shell script to multiplex the controlfile.

./Dupcontrol.sh DBname 3

where 3 specifies no of controlfiles to be multipluxed.

8) Write a shell script to start the database.

9) Write a shell script to stop the database.

10) Write a shell script to start the listener, if not started.

11) Write a shell script to stop the listener.

12) Write a shell script to check for fragmentation

13) Write a shell script to defragment all the tables.

14) Write a shell script to rebuild only the unusable indexes.

15) Write a shell script to check the mount point limits. If exceeds 85% should send a mail to [email protected].

16) Write a schell script to check how many oracle databases are running in the server.

17) Write a shell script check the database state like NOMOUNT|MOUNT|OPEN for a given database, where database name is passed as first argument.

18) Write a schell script to analyze the database|schema|table where the component is passwd as argument.

ex. ./DBanalyze.sh {full} {dbname} ( will analyze the full database)
Hint : use dbms_stats.gather_database_stats;

./DBanalyze.sh {fix} {dbname} (will analyzed fixed stats )
Hint use: dbms_stats.gather_fixed_objects_stats;

./DBanalyze.sh {dict} {dbname} (will analyzed dict stats )
Hint use: dbms_stats.gather_dictionary_stats;

./DBanalyze.sh {schema} {table} {dbname} (will analyzed table stats )
Hint use: dbms_stats.gather_table_stats (‘SDBT_USER,’SAKTHIDB_TABLE’);

19) Write a shell script to cleanup given schema( cleaning schema meas dropping all the objects from that schema)

ex. Cleanschema.sh

20) rite a shell script to the below activity.This is also one type of cleaning activity.

Disable all the constraints.
Truncate the tables
Enable all the constraint.

Task – 1

1) Print the Following :

Server Name
Kernal Version
OS Version
IP Address
System architecture

2) When was your system or server start ?

3) How will you find your groups & id ?

4) How will you find previous commands ?

5) Print how many CPUs & MEM info ?

6) Create directory by Following structure ?

7) create sdbt directory and sub directory’s above the structure.

8) copy files to sql in oracle (/sdbt/database/oracle/sql )

9) rename the copied files

10) copy oracle directory and send to oel.

11) create duplicate files in oel

12) oel files copy to python.

13) Rename oel

14) Create compress the python and copy to windows.

15) And gzip all the dumps.

16) Check entire size of sdbt.

17) Create 2 users(user1 and user2 ) and change the owner user1 to oracle:group 1 and user2 to group2.

18) user1 and user2 access python directory.

19) what is the location of ls.

20) create soft link windows to oracle

21) delete file which is older then 10 days.

22) how will find the previous command is executed or not.

23) how will find cpu utilization without using top command.

24) how will zip directory.

25)create file with date and time.

Oracle Assignments - BACKUP and RECOVERY

BACKUP and RECOVERY

USER MANAGED BACKUP - UMB

Take full cold backup of the database(db_name=test)

Restore the backup with different DB name(prod)
make sure you store datafile and logfile in different mount point.

Take hot backup of dev database and clone it to different name(qadb)

1. nid (DBNEWID Utility)
2. recreate control file set newname

Recovery:

Corrupt the following and recover it.

- take backup of control file in txt format. 

spfile : Hint [use strings] , if no spfile/pfile , check alert log and get the parameters.

controlfile 
. multiplexed
. having only one control file

logfile:
inactive log file [ easy]
current logfile (try using undocumented parameter discussed in the class)

Tablespace: (try recovering online, and report the steps how you recovered)
sysaux - datafile
system
user_data
undo 
temp

drop a table and recover the same using PITTR

Very important : Explain the concepts behind "alter tablespace .. begin backup"

TOPIC 2. RMAN (Any doubts discuss with your trainer)

Take full backup
take a level - 0 full backup
take a differential backup - L1 (twice to know the difference)
take a cummulative backup-L0
using RMAN clone a database using duplicate command

Corrupt the following and recover it.
spfile : Hint [use strings] , if no spfile/pfile , check alert log and get the parameters.

controlfile 
. multiplexed
. having only one controlfile

logfile:
inactive log file [ easy]
current logfile (try using undocumented parameter discussed in the class)

Tablespace: (try recovering online, and report the steps how you recovered)
sysaux - datafile
system
user_data
undo
temp

drop a table and recover the same using PITR
remove archive logs from physical level. Check and clear the information in repository 
regarding archive logs from RMAN
Restore the backup taken in dev database to prod database. (Hint : catalog the backup files)
======================================================================================
TOPIC 3- IMPORT AND EXPORT ( get the tax.dmp from out shared drive) 
======================================================================================
perform a schema refresh from tax user to tax1 user
perform a schema refresh from tax1 user to tax2 and tax3(single command)
mention the pre - requisites of schema refresh and post schema refresh steps

Export schema with only structure and no data
create a table Ao in tax user from all_objects , insert the data from all_objects till the table reaches the size of 500mb.
check number of extents,  export the created table and import the table to other user using compress option.
check number of extents

export the tables from tax user but the dump file size should be 100mb.

export table AO but using sys user

import the table data only without index into another user with below parameter
Commit, buffer

import index alone

using transportable tablespace option complete import and export of dev database

How to read the contents of dump

import the tax dump without data into  user u1.
import the data separately into the user u1
import the index separately into user u1.
======================================================================================
TOPIC 4- DATAPUMP ( more real time scenarios will be given on Zoom meeting)
======================================================================================

schema refresh - tax
abort the session when taking export, restart the export from the interruption add parallel option
export all tables from a user without records
create export and import without creating dump file (using network link)
take export of a single column from a table
create a table as a copy of all_objects without data. Import the data from dump of the all_objects
how will you increase performance of data pump and start the job, stop the job,kill the job , restart the job,check the
status and continue the job.
clone the TAX schema to tax1 using datapump
export the tax dump and import all the tables into user02 without data and without index
import the data separately into the user user02
import the index separately into user user02.
read the dump
 using transport tablespace option complete import and export of dev database
take a dump in 12c and import it into 19c database.

Assignments - Oracle Architecture

LAB - Exercise on ORACLE ARCHITECTURE and INSTALLATION

INSTALLATION

19c silent mode installation
tar ball installation with inventory addition
database creation using dbca in silent mode
xming installation into putty

DB CREATION

TASK -1

1)create a pfile with below parameters and start the database to nomount stage

sga_max_size=800m

Db_name=test

once the database is in nomount stage create a database test

- Which file will be executed when you give create database

- Check the BG process running from linux.

- Execute and explain the below packages

Catalog.sql
Catproc.sql
Utlrp.sql

Check all the packages ran successfully?

change the database to OFA(oracle flexible architecture)

Now change all files from OFA to ORADATA as below

/oradata/test/control/control1.ctl

/oradata/test/log/log1.log

/oradata/test/log/log2.log

/oradata/test/data/system.dbf

/oradata/test/data/sysaux.dbf

/oradata/test/data/undo01.dbf

/oradata/test/data/temp01.dbf

/oradata/test/data/data.dbf

/oradata/test/data/index.dbf

/oradata/archive/

Make sure Database is running in Spfile.

Multiplex the control file as shown below

/oradata/test/control/control1.ctl

/oradata/test/control/control2.ctl

/oradata/test/control/control3.ctl

Increase the logfile as 220Mb and create as shown below

Group1

/oradata/test/log/log11.log

/oradata/test/log/log12.log

/oradata/test/log/log13.log

Group2

/oradata/test/log/log21.log

/oradata/test/log/log22.log

/oradata/test/log/log23.log

Group3

/oradata/test/log/log31.log

/oradata/test/log/log32.log

/oradata/test/log/log33.log

===================================================================================

TASK 2 - TABLESPACE

===================================================================================

Create a Tablespace as USER_DATA having two Datafiles as

/oradata/test/data/user_data01.dbf

/oradata/test/data/user_data02.dbf

create a table(only structure) AO using scott user (using CTAS method from all_objects)

and table should be in tablespace USER_DATA.

insert data from all_objects but data should be available only at user_data02 and

should not store in to user_data01.dbf. Also prove that data is stored

only in user_Data02.dbf.

Rename the user_data02 to /oradata/test/data/user_data03.dbf

move the location of

/oradata/test/data/data_user01 to /home/oracle/data_user01.dbf(online mode)

insert data from all_objects into table, make sure data goes into both datafiles.

drop the datafile /oradata/test/data/data_user03.dbf

select all the data from the AO table.

verify the error by retrieving data from table.

drop the tablespace user_data.

move the datafile SYSAUX to another location(online)

move the datafile SYSTEM to another location (online)

===================================================================================

TASK 3 - UNDO TABLESPACE & TEMP Tablespace

===================================================================================

Demonstrate the ORA-0555 error

Rename the undo tablespace to undo01 tbs

Check if undo tablespace can be brought offline

rename the temp tablespace to temp01 tablespace

get the statistics of database(types – fixed table, table, schema,data dictionary)

===================================================================================

TASK 4 - FRAGMENTATION

===================================================================================

create a table like AO from all_objects on a newly created tablespace (eg. USER_DATA01)

alter the table and add an extra column as “aoid”

create an unique index and primary key on column “aoid”, check if possible and explain.

create composite index on (owner, object_id)

create a sequence to generate values for the column “aoid”

all other data to be inserted from all_objects(upto 1lak records), duplicate it with

sql> insert into ao select ... from ao;

check

table size

index size

number of extents

number of blocks of tables and indexes

delete from table where rownum < 50000

Again check

table size

index size

number of extents

number of blocks of tables and indexes

The size will remain the same, explain why?

check the fragmentation for table, index using our script.

Repeat above steps for each defragment method and print it in table format.

Defrag method ---- Object-type -- index status -- original size -- fragmented size- after defrag size

1. alter table .. move

2. alter table shrink

3. online redefinition

4. exp/imp

5. CTAS

===================================================================================

TASK 5 - FLASHBACK

===================================================================================

create table tab01 from all_objects and insert data, commit the same

demonstrate as of query

drop the table

restore the table

===================================================================================
TASK 6 - ROLES AND PROFILES
===================================================================================

create a user(without any role) with password tesdb123 and the user should belong

to user_data tablespace

connect to the database as user and create a table tab01 as select * from all_objects.

check and rectify the error

Try the below password limits:

create a user with a password expiration limit in 2 days

password should have one capital letter, number, special character

should not allow the old password to be reused and account to be locked after 3

wrong attempts.

password expiration, recreate password

===================================================================================

TOPIC 7- SCHEDULER

===================================================================================

create a job to gather the stat for schema - SCOTT every one hour

create a job to find the fragmentation every week saturday at 3:00 am.

More practical scenario questions in our repository.

ORACLE REALTIME INTERVIEW QUESTIONS -1

ORACLE REALTIME INTERVIEW QUESTIONS

1. Explain how RAC cluster starts.

Hint : (like HAS,CRS,NODEAPPS,ASM,DATABASE,SERVICES)

2. Explain the processes that involved in starting a RAC cluster.

3. What is cache fusion

4. what is difference between olr and ocr

5. What is gpnp profile

6. Where will be RAC logs stored

7. How ips are needed to configure 19c rac.

8. What is VIP? Why it is used.

9. what is different between srvctl and crsctl commands

10. How will you tune a sql?

11. How to find long running sql for a given time.

12. How to check current long running sql?

13. Difference between lock and deadlock

14. Difference between SPM and SQL PRofile.

15. Why we should use SQL profile. How to create a sql profile.

16. Is it possible take expdb from standby database?

17. How will you apply opatch on grid and database? What precheck you will do?

18. What will ./datapatch verbose do? What you will check on dba_registry_sqlpatch?

19. How to restore a given rman backup into QA?

20. How to port sql profile one DB to another DB?

21. What will you see in AWR?

22. Do you have experience on Data encryption?

23. How will you sync standby database if archive logs are lost?

24. If there is frequent interconnect disconnection, what will happen

25. How to do a database upgrade with minimal downtime?

26. What is the use of scan-ips

27. Steps to change maximum availability to maximum performance.

ORACLE DBA INTERVIEW QUESTIONS -1

Oracle DBA interview questions

1. List out 10 memory-level parameters

2. Which commands leads to sorting? What memory area is used for sorting ? How will you increase the performance of sorting ? Name the parameters?

3. what will happen if you give commit? What are the wait events that affect log buffer?

4. How will you increase the size of library cache? What is hard and soft parse?

5. What is checkpoint?How will reduce the checkpoint frequency?

6. Explain the steps that involved when bringing up the database? How to drop the database? how to change the mode of database?

7. Explain instance recovery? Who does instance recvoery? Where will you check whether the instance recovery happened or not? What files were involved in the Instance recovery?

8. Who read the data into memory? How will you increaase the performance of write from DBBC to datafiles?

9. What is inittrans, maxtrans. what is the default values for table and index?

10. what is logging ad nologging? What is use of pctfree/pctused and pct increase?

11. How will you find fragmentation? How will you rectify it?

12. What is row chain and row migration? How will you find it and rectify it?

13. Why we should analyze a table?How to check if the table/index is analyzed? What is the command to gather stats/analyze?

14. What is differnce between?

spfile/pfile

pinned buffer/dirty buffer

latch /enqueue

LMT/DMT

btree/bitmap

15. When and how to rebuild index?

16. What are the types of partitions? Explain composit partitons?

17. What is different between system/hash partition?

19. what is partiton PRUNING?

20. How will you check the following?

- Table/index/view size

- free space in database/tablespace

- used space in database/tablespace

- how many indexes present for a table? check indexes columns and its positon? on what function the index is created?

- How many constraints are there in the table and on what columns? What are the constraint types?

21. What are the types of grants ? Difference between GRANT/ADMIN Option?

22. what is password verification function? Steps to implement it.

23. Will discuss about profiles? Try to learn Password Resources

24. what is snapshot too old error?

25. what is different between lock and deadlock?

26. How will you multiplex controlfile and logfile? How to increase the size of logfiles and datafiles?

27. What is the use of UNDO tablespace? How to hange the name of undo tablespace and location?

28. What is the use of temp tablespcae? how to change the location of temp tablespace?

29. Steps to create dbms scheduler? How to check the failed job / status?

30. What is use of flashback query /version/transaction/table?

31. What is UMASK?

32. How will find available port ?

33. How to delete files that are older than 10 days?

34. Explain the logic for monitoring alert.log , filesystem check.

35. How will you pass arguments to a shell scrits? How will you call them inside the shell?ow to check how many number of arguments passed and list out them?

36. How to check the previos command that was execute is successful?

37. How to check the following?

ipaddress

servername

OS release

CPU count

Ram size

users groupname

CPU utilization

IO utilization

virtual memory utilization

38. Which command is used to find the process that was locking/using the file?

39. which command is used to find the user who is using the file?

40. How to change the ownership of a file?

41. Prerequisite to install oracle on Linux? Expain kernel parameters.

42. Steps to configure silent mode installation?

43. Expain PATH, ORACLE_HOME,ORACLE_BASE,DISPLAY variables

44. How to create database using dbca using putty with GUI termical.

45. What is OMF? How will you use it?

46. what is oratab? What is the use of it?

47. Steps to configure automatic start and shut options? What is the use of chkconfig command?

48. what will happen if you execute root.sh?

49. What is rpm? How will you install rpm? How to check the existance of rpm?

50. Explain all the linux compression commands?

51. Explain crontab and its parameters?

52. What is logical and physical backup? hot and cold backup?

53. Explain the internals of hot backup in UMB?

54. Explain the steps to take hot backup in UMB?

55. how to recover a system datafile/ non system datafile that is running in a Production env?

56. how to recover a UNDO datafile/ tempfile that is running in a Production env?

57. How to recover a corrupted logfile,controlfile,spfile

58. How to take a backup using rman and location should be in "/backup"

59. How to increase the performance of RMan backup and explain it advantages over UMB?

60. What is the use of crosscheck , obsolete, expired commands

61. Difference between differential and cumulative backup?

62. What is the use of BCT?

63. How to increase the performance of RMAN nackup?

64. How will recover a table sing RMAN? Explain the stepswith commands?

65. Whats is schema refresh?what are the post refresh activities after schema refresh?

66. Given a exp/expdp full dump (with out export logfile) how will you create a database with it?

67. What is the use of compress , consistent for exp?

68. What is teh performance parameters for exp/imp/expdp/impdp?

69. What are the performance parameters for SQL loader?

70. How will you take export of 20 tables? explain steps with commands?

71. Given a 100gb table dump, but having 5 gb of undo, how will you import the table?

72. On which oracle utility you will get snapshot too old error?

73. What is use of resumable_timeout parameter in DB as well as EXP/expdp?

74. How will you do schema refresh without removing grants from target schema?

75. How to export set of record from a table?

76. A datapump export is running with parllel=2 . Now you are asked to increase the parallel=4 from another terminal, how will you do it?

77. Having the DB size ad 600GB. How will you take a database dumpfiles and copy that to another server. Explain the steps

78. Is it possible to do a schema copy to another db with out dumpfile ?

79. Explain the steps to transport tablespace?

80. What is the advantage of datapump over exp/imp?

81. Need to export a database using exp from putty even we get a network issue.

82. Import a schema which is in tablespace USERS to another schema which is in tablespace USERS2.

83. how to goback to client mode from interactive mode in datapump?

84. How will you trouble shoot a client server connetion issue? Expplain all the possible issues?

85. If listerner is down, what will happen to the existeing connetion? what will happen to new connection?

86. How to check how many listeners are running on the server? How tio check their status?

87. Is it possible to change the listener and tnsnames location ? If so how to do that?

89. Expain the steps to recover a database using a backup from a location?

90. Explain teh steps Rman cloning and active cloning?

91. Can we have 2 listeners for a single database and 1 listener for multiple database? If so How?

92. How to register a database to a listener?

93. How to setup a recover catalog?explain with steps?

94. Find the user who is locking a table?

95. How to find if a query is using index?

96. What are the possible chances that a index is not used by a query?How to force a index to be used?

97. How to intrepret execution pan?

98. If a user reports that a query hangs , how willyou trouble shoot?

99. If a query run yesterday for 2 mins and it takes more tha 1 hr, what might be the possible issue?

100. How will you kill a session even after it marked for killing in DB?

101. How will you check how many plans exists for a query?

102. How to check the duration of current running SQLS?

103. How to get the execution plan for a Query?

104. How will you trace a session? explain the steps? What will you check in it?

105. What is AWR? When will you go for AWR? how to take it and what will you look in to it?

106. Name some wait events (atleast 10) and explain hot to resolve it?

107. What is ASH? Give the views related to ASH?

108. Explain joining methods with examples.

109. Name some hints (10 hints)and explain when that will be used?

110. How to make the query not to a the index.

111. What is the prerequisite to do upgrade / patching?

112. How to check the conflict of a patch?

113. How to install / rollback a patch?

114. How to list the patches installed? What does ./datapatch -verbose do?

115. What are the type of patches?

116. what is opatch and opatchauto?

117. How will you monitor while patching? Where the logs will be stored?

118. How to check how many users are connect to database from linux command?

119. How will remove kernal memory (shared/semaphores) from unix?

120. How will estimate the remaining time of running rman backup / gather stats.

Monitoring Commands

To find long running SQLS

with qduration as ( select ash.sql_exec_id,ash.sql_id,ash.module,ash.sql_plan_hash_value,max(cast(ash.sample_time as date)-cast(ash.sql_exec_start as date))*24*60 elapsed_time,(select username from dba_users

where user_id=ash.user_id) User_name, ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax ,

EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,

EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start))) MI ,

EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS,

ash.sql_opname

from dba_hist_active_sess_history ash

where ash.sample_time BETWEEN to_date('01-SEP-2022:21:00:28','DD-MON-YYYY:HH24:MI:SS')

and to_date('02-SEP-2022:12:58:28','DD-MON-YYYY:HH24:MI:SS')

and sql_id is not null

and sql_exec_start is not null

--and ash.user_id in (select user_id from dba_users where username in ('SCOTT'))

group by ash.sql_exec_id,ash.sql_id,ash.sql_plan_hash_value,ash.sql_exec_start,ash.sql_id,ash.module,ash.user_id,ash.sql_opname)

select qd.sql_id,qd.sql_plan_hash_value,qd.elapsed_time,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,qd.sql_opname,qd.user_name,qd.module,substr(dh.sql_text,1,4000) "SQLTEXT"

from qduration qd,dba_hist_sqltext dh

where qd.sql_id=dh.sql_id

and qd.sql_opname<>'PL/SQL EXECUTE'

order by 8 desc , 9 desc,10 desc;

To find the SQL_TEXT from history

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

select * from dba_hist_Sqltext where sql_id='sdbt';

To find the bind_values from history

select * from dba_hist_sqlbind where sql_id='sdbt' order by last_captured desc;

To find plan hash value for a sql_id

select user_id,sql_id, sql_exec_start,sql_plan_hash_value,max(cast(sample_time as date)-cast(sql_exec_start as date))*24*60 elapsed_time from

dba_hist_active_sess_history

where sql_id='5sv64cjnskwd4'

--and user_id in (select user_id from dba_users where username in ('SDBT'))

group by user_id,sql_id,sql_exec_start ,sql_plan_hash_value having max(cast(sample_time as date)-cast(sql_exec_start as date))*24*60 is not null

order by 3 desc;

To find the sequence of SQLS that were running for a particular time (history).

select sql_exec_id,snap_id,instance_number,sql_id,sql_opname,sql_exec_start,sample_time,event,session_id,session_serial#,session_state,blocking_session,blocking_Session_Serial#,

(select username from dba_users where user_id=dba_hist_Active_Sess_history.user_id) usernaeme ,sql_opname,sql_plan_hash_value,

qc_session_id,qc_session_serial#,p1text,p2text,p3text,wait_time,

program,module,machine

from dba_hist_Active_Sess_history where sample_time between

to_date('02-OCT-2022:20:08:57','DD-MON-YYYY:HH24:MI:SS')

and to_date('02-OCT-2022:23:08:57','DD-MON-YYYY:HH24:MI:SS')

--and sql_id='47vahb2s0m7t2'

--and program like '%P01F%'

--and user_id in (select user_id from dba_users where username in ('SDBT'))

and sql_exec_start is not null

order by sample_time desc;

To check the plans for a sql_id

select * from table(dbms_xplan.display_awr('sdbt'));

RAC Commands

[root@racn1 ~]# $ORACLE_HOME/bin/crsctl stop cluster -all

[root@racn1 ~]# systemctl stop oracle-ohasd

[root@racn2 ~]# systemctl stop oracle-ohasd

[grid@rac1 ~]$ srvctl status nodeapps

VIP 192.168.1.31 is enabled

VIP 192.168.1.31 is running on node: rac1

VIP 192.168.1.32 is enabled

VIP 192.168.1.32 is running on node: rac2

Network is enabled

Network is running on node: rac1

Network is running on node: rac2

ONS is enabled

ONS daemon is running on node: rac1

ONS daemon is running on node: rac2

[grid@rac1 ~]$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node rac2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node rac1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node rac1

[grid@rac1 ~]$ crsctl check cluster -all

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

rac1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

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

rac2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

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

[grid@rac1 ~]$ crsctl check ctss

CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[grid@rac1 ~]$ srvctl config all

Oracle Clusterware configuration details

========================================

Oracle Clusterware basic information

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

Operating system Linux

Name rac-cluster

Class STANDALONE

Cluster nodes rac1, rac2

Version 19.0.0.0.0

Groups SYSOPER:oinstall SYSASM:dba SYSRAC:dba SYSDBA:oinstall

OCR locations +DATA

Voting disk locations DATA

Voting disk file paths /dev/oracleasm/disks/VOL1

Cluster network configuration details

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

Interface name Type Subnet Classification

enp0s3 IPV4 192.168.1.0/24 PUBLIC

enp0s8 IPV4 192.168.10.0/24 PRIVATE, ASM

SCAN configuration details

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

SCAN "rac-scan" details

+++++++++++++++++++++++

Name rac-scan

IPv4 subnet 192.168.1.0/24

DHCP server type static

End points TCP:1521

SCAN listeners

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

Name VIP address

LISTENER_SCAN1 192.168.1.41

LISTENER_SCAN2 192.168.1.42

LISTENER_SCAN3 192.168.1.43

ASM configuration details

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

Mode remote

Password file +DATA

SPFILE +DATA

ASM disk group details

++++++++++++++++++++++

Name Redundancy

DATA EXTERN

Database configuration details

==============================

Database "ora.orcl.db" details

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

Name ora.orcl.db

Type RAC

Version 19.0.0.0.0

Role PRIMARY

Management AUTOMATIC

policy

SPFILE +DATA

Password file +DATA

Groups OSDBA:oinstall OSOPER:oper OSBACKUP:backupdba OSDG:dgdba

OSKM:kmdba OSRAC:racdba

Oracle home /u01/app/oracle/product/19.3

[grid@rac1 ~]$ srvctl status database -d orcl -verbose

Instance orcl1 is running on node rac1. Instance status: Open.

Instance orcl2 is not running on node rac2

[grid@rac1 ~]$ crsctl status resource -t

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

Name Target State Server State details

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

Local Resources

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

ora.LISTENER.lsnr

ONLINE ONLINE rac1 STABLE

ONLINE ONLINE rac2 STABLE

ora.chad

ONLINE ONLINE rac1 STABLE

ONLINE ONLINE rac2 STABLE

ora.net1.network

ONLINE ONLINE rac1 STABLE

ONLINE ONLINE rac2 STABLE

ora.ons

ONLINE ONLINE rac1 STABLE

ONLINE ONLINE rac2 STABLE

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

Cluster Resources

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

ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)

1 ONLINE ONLINE rac1 STABLE

2 ONLINE ONLINE rac2 STABLE

3 ONLINE OFFLINE STABLE

ora.DATA.dg(ora.asmgroup)

1 ONLINE ONLINE rac1 STABLE

2 ONLINE ONLINE rac2 STABLE

3 OFFLINE OFFLINE STABLE

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE rac2 STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE rac1 STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE rac1 STABLE

ora.asm(ora.asmgroup)

1 ONLINE ONLINE rac1 Started,STABLE

2 ONLINE ONLINE rac2 Started,STABLE

3 OFFLINE OFFLINE STABLE

ora.asmnet1.asmnetwork(ora.asmgroup)

1 ONLINE ONLINE rac1 STABLE

2 ONLINE ONLINE rac2 STABLE

3 OFFLINE OFFLINE STABLE

ora.cvu

1 ONLINE ONLINE rac1 STABLE

ora.orcl.db

1 ONLINE ONLINE rac1 Open,HOME=/u01/app/ o

racle/product/19.3, S

TABLE

2 OFFLINE OFFLINE Instance Shutdown,S T

ABLE

ora.qosmserver

1 ONLINE ONLINE rac2 STABLE

ora.rac1.vip

1 ONLINE ONLINE rac1 STABLE

ora.rac2.vip

1 ONLINE ONLINE rac2 STABLE

ora.scan1.vip

1 ONLINE ONLINE rac2 STABLE

ora.scan2.vip

1 ONLINE ONLINE rac1 STABLE

ora.scan3.vip

1 ONLINE ONLINE rac1 STABLE

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

srvctl stop listener -n rac1

[grid@rac1 ~]$ srvctl status nodeapps

VIP 192.168.1.31 is enabled

VIP 192.168.1.31 is running on node: rac1

VIP 192.168.1.32 is enabled

VIP 192.168.1.32 is running on node: rac2

Network is enabled

Network is running on node: rac1

Network is running on node: rac2

ONS is enabled

ONS daemon is running on node: rac1

ONS daemon is running on node: rac2

[grid@rac1 ~]$ srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is running on node rac1

SCAN VIP scan2 is enabled

SCAN VIP scan2 is running on node rac2

SCAN VIP scan3 is enabled

SCAN VIP scan3 is running on node rac2

[grid@rac1 ~]$ srvctl config scan

SCAN name: rac-scan, Network: 1

Subnet IPv4: 192.168.1.0/255.255.255.0/enp0s3, static

Subnet IPv6:

SCAN 1 IPv4 VIP: 192.168.1.41

SCAN VIP is enabled.

SCAN 2 IPv4 VIP: 192.168.1.42

SCAN VIP is enabled.

SCAN 3 IPv4 VIP: 192.168.1.43

SCAN VIP is enabled.

[grid@rac1 ~]$ srvctl status asm

ASM is running on rac1,rac2

nslookup imdb-scan.imdbworld.com

nslookup imdb02-vip.imdbworld.com

OCR Commands :

OCR manual backup

./ocrconfig -manualbackup

./ocrconfig -showbackup

./oifcfg getif

./crsctl query css votedisk

./crsctl enable has

./crsctl disable has

./crsctl stop has

./srvctl modify service -db orcl -service gracserv -failovertype SELECT

./srvctl modify service -db orcl -service gracserv -failovermethod BASIC

./srvctl modify service -db orcl -service gracserv -notification TRUE

./srvctl modify service -db orcl -service gracserv -failoverdelay 5

./srvctl modify service -db orcl -service gracserv -failoverretry 60

./srvctl modify service -db orcl -service gracserv -clbgoal SHORT

Use below commands to modify preferred and available instances

./srvctl modify service -db orcl -service gracserv -modifyconfig -preferred orcl 2 -available orcl 1

./srvctl modify service -db orcl -service gracserv -modifyconfig -preferred orcl 2,orcl 1

Start created service

./srvctl start service -db orcl -service gracserv

Relocate to next instance

./srvctl relocate service -db orcl -service gracserv -oldinst orcl 1 -newinst orcl 2

Show configuration for the service

./srvctl config service -db orcl -service gracserv

dev5088:+ASM2:/e2open/home/grid: 348>srvctl config all

Oracle Clusterware configuration details

========================================

Oracle Clusterware basic information

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

Operating system Linux

Name gopirac

Class STANDALONE

Cluster nodes dev5088

Version 19.0.0.0.0

Groups SYSOPER: SYSASM:asmadmin SYSRAC:asmadmin SYSDBA:asmdba

OCR locations +DATA01

Voting disk locations DATA01

Voting disk file paths /dev/sde1

Cluster network configuration details

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

Interface name Type Subnet Classification

eth0 IPV4 10.120.96.0/19 PUBLIC

eth1 IPV4 192.168.0.0/24 PRIVATE, ASM

SCAN configuration details

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

SCAN "gopirac-scan" details

+++++++++++++++++++++++++++

Name gopirac-scan

IPv4 subnet 10.120.96.0/19

DHCP server type static

End points TCP:1521

SCAN listeners

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

Name VIP address

LISTENER_SCAN1 10.120.115.205

LISTENER_SCAN2 10.120.115.204

LISTENER_SCAN3 10.120.115.203

ASM configuration details

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

Mode remote

Password file +DATA01

SPFILE +DATA01

ASM disk group details

++++++++++++++++++++++

Name Redundancy

ARCH01 EXTERN

DATA01 EXTERN

DATA02 EXTERN

REDO01 EXTERN

Database configuration details

==============================

Database "ora.gopirac.db" details

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

Name ora.gopirac.db

Type RAC

Version 19.0.0.0.0

Role PRIMARY

Management AUTOMATIC

policy

SPFILE +DATA01

Password file +DATA01

Groups OSDBA:dba OSOPER:oinstall OSBACKUP:dba OSDG:dba OSKM:dba

OSRAC:dba

Oracle home /oragopirac01/app/oracle/product/19.3.0

ip route

ip rule

1p addr

srvctl add service -d gopirac -s TEST -r "gopirac1,gopirac2"

srvctl relocate service -d gopirac -s gservice -oldinst gopirac2 -newinst gopirac1 -force -drain_timeout 30 -stopoptions IMMEDIATE

[root@rac1 ~]# /u01/app/grid/19.3/bin/crsctl stop cluster -all

CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'

CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'rac1'

CRS-2673: Attempting to stop 'ora.chad' on 'rac1'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac1'

CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'rac1'

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac1'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'rac2'

CRS-2673: Attempting to stop 'ora.cvu' on 'rac2'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac2'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac2'

CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac1'

CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'

CRS-2673: Attempting to stop 'ora.qosmserver' on 'rac2'

CRS-2673: Attempting to stop 'ora.chad' on 'rac2'

CRS-2677: Stop of 'ora.scan1.vip' on 'rac1' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac2'

CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac2'

CRS-2677: Stop of 'ora.scan3.vip' on 'rac2' succeeded

CRS-2677: Stop of 'ora.DATA.dg' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'rac1'

CRS-2677: Stop of 'ora.scan2.vip' on 'rac2' succeeded

CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac1'

CRS-2677: Stop of 'ora.cvu' on 'rac2' succeeded

CRS-2677: Stop of 'ora.qosmserver' on 'rac2' succeeded

CRS-2677: Stop of 'ora.chad' on 'rac2' succeeded

CRS-2677: Stop of 'ora.chad' on 'rac1' succeeded

CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'rac1'

CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'rac1' succeeded

CRS-33677: Stop of resource group 'ora.asmgroup' on server 'rac1' succeeded.

CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'rac2'

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac2'

CRS-2677: Stop of 'ora.DATA.dg' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'rac2'

CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac2'

CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'rac2'

CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'rac2' succeeded

CRS-33677: Stop of resource group 'ora.asmgroup' on server 'rac2' succeeded.

CRS-2673: Attempting to stop 'ora.ons' on 'rac2'

CRS-2677: Stop of 'ora.ons' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'

CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed

CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'

CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed

CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.storage' on 'rac2'

CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'

CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.storage' on 'rac1'

CRS-2677: Stop of 'ora.storage' on 'rac2' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'

CRS-2677: Stop of 'ora.storage' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'rac1'

CRS-2673: Attempting to stop 'ora.asm' on 'rac2'

CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded

CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'

CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded

CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'

CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded

CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac2'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac2' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'

CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded

[root@rac1 ~]# /u01/app/grid/19.3/bin/crsctl start cluster -all

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'

CRS-2672: Attempting to start 'ora.evmd' on 'rac2'

CRS-2672: Attempting to start 'ora.evmd' on 'rac1'

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'

CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rac1'

CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'

CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rac2'

CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'

CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded

CRS-2676: Start of 'ora.evmd' on 'rac2' succeeded

CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded

CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded

CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'

CRS-2672: Attempting to start 'ora.ctssd' on 'rac2'

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac2'

CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded

CRS-2676: Start of 'ora.ctssd' on 'rac2' succeeded

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac2' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rac2'

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rac1'

CRS-2676: Start of 'ora.asm' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rac1'

CRS-2676: Start of 'ora.asm' on 'rac2' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rac2'

CRS-2676: Start of 'ora.storage' on 'rac2' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'rac2'

CRS-2676: Start of 'ora.crsd' on 'rac2' succeeded - at this point you can query crsctl stat res -t

CRS-2676: Start of 'ora.storage' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.crsd' on 'rac1'

CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

Create a service :

[oracle@rac1 ~]$ srvctl add service -d orcl -s gs -r orcl1,orcl2 -P BASIC

[oracle@rac1 ~]$ srvctl status service -d orcl -s gs

Service gs is not running.

[oracle@rac1 ~]$ srvctl start service -d orcl

[oracle@rac1 ~]$ srvctl status service -d orcl

Service gs is running on instance(s) orcl1,orcl2