SQL RENAME Statement

SQL RENAME TABLE syntax is used to change the name of a table. Sometimes, we choose non-meaningful name for the table. So it is required to be changed.

Some of the relational database management system (RDBMS) does not support this command, because this is not standardizing statement.

For example renaming a table through MS SQL Server you must use storage procedure SP_RENAME.

Syntax for SQL RENAME is:

RENAME TABLE {tbl_name} TO {new_tbl_name};

Where {tbl_name} table that exists in the current database, and {new_tbl_name} is new table name.

As for Oracle may also be use the following option:

ALTER TABLE {tbl_name} RENAME TO {new_tbl_name};For Example;create table states(id NUMBER (6),name VARCHAR (20));INSERT INTO STATES (id, name) values(1,'delhi');INSERT INTO STATES (id, name) values(2,'Punjab');INSERT INTO STATES (id, name) values(3,'Gurgaon');INSERT INTO STATES (id ,name) values (4,'Noida');SQL> select * from states;SELECT Output:        ID NAME---------- --------------------         1 delhi         2 Punjab         3 Gurgaon         4 NoidaNow Rename start---SQL> alter table states rename to cities;
Table altered.
SQL> select * from cities;SELECT Output:        ID NAME---------- --------------------         1 delhi         2 Punjab         3 Gurgaon         4 Noida


Crontab In Linux

Crontab – Quick Reference

1. What is crontab?

 Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

2.What is a cron job or cron schedule?

Cron job or cron schedule is a specific set of execution instructions specifying day, time and command to execute. crontab can have multiple execution statements.

3. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e    Edit crontab file, or create one if it doesn’t already exist.
crontab -l    crontab list of cronjobs , display crontab file contents.
crontab -r    Remove your crontab file.
crontab -v    Display the last time you edited your crontab file. (This option is only available on a few systems.)

4. Syntax of crontab

The syntax is:

1 2 3 4 5 /path/to/command arg1 arg2

OR

1 2 3 4 5 /root/backup.sh

Where,

• 1: Minute (0-59)
• 2: Hours (0-23)
• 3: Day (0-31)
• 4: Month (0-12 [12 == December])
• 5: Day of the week(0-7 [7 or 0 == sunday])
• /path/to/command – Script or command name to schedule

Easy to remember format:

* * * * * command to be executed
- - - - -
| | | | |
| | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)

5. Crontab Examples



1) To run /path/to/command five minutes after midnight, every day, enter:
5 0 * * * /path/to/command
2) Run /path/to/script.sh at 2:15pm on the first of every month, enter:
15 14 1 * * /path/to/script.sh
3) Run /scripts/phpscript.php at 10 pm on weekdays, enter:
0 22 * * 1-5 /scripts/phpscript.php
4) Run /root/scripts/perl/perlscript.pl at 23 minutes after midnight, 2am, 4am …, everyday, enter:
23 0-23/2 * * * /root/scripts/perl/perlscript.pl
5) Run /path/to/unixcommand at 5 after 4 every Sunday, enter:
5 4 * * sun /path/to/unixcommand

Oracle DBA Interview Questions and Answers -(Rac )Part 1

Oracle DBA Interview Questions and Answers -(Rac) -Part 1

Q What is RAC?

RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.

 Q What is RAC and how is it different from non RAC databases?

RAC stands for Real Application Cluster, you have n number of instances running in their own separate nodes and based on the shared storage. Cluster is the key component and is a collection of servers operations as one unit. RAC is the best solution for high performance and high availably. Non RAC databases has single point of failure in case of hardware failure or server crash.

Q- Where are the Clusterware files stored on a RAC environment? 
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file) 

Q. Where are the database software files stored on a RAC environment? 
The base software is installed on each node of the cluster and the 
database storage on the shared disks. 

Q. What kind of storage we can use for the shared Clusterware files? 
- OCFS (Release 1 or 2) 
- raw devices 
- third party cluster file system such as GPFS or Veritas 

Q- What kind of storage we can use for the RAC database storage? 
- OCFS (Release 1 or 2) 
- ASM 
- raw devices 
- third party cluster file system such as GPFS or Veritas 

Q. What is a CFS? 
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view. 

Q. What is an OCFS2? 
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster. 

Q. Which files can be placed on an Oracle Cluster File System? 
- Oracle Software installation (Windows only) 
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype) 
- Shared configuration files (spfile) 
- OCR and voting disk 
- Files created by Oracle during runtime 
Note: There are some platform specific limitations. 

Q. Do you know another Cluster Vendor? 
HP Tru64 Unix, Veritas, Microsoft 

Q. How is possible to install a RAC if we don’t have a CFS? 
This is possible by using a raw device. 

Q. What is a raw device? 
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks. 

Q. What is a raw partition? 
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition. 

Q. When to use CFS over raw? 
A CFS offers: 
- Simpler management 
- Use of Oracle Managed Files with RAC 
- Single Oracle Software installation 
- Autoextend enabled on Oracle datafiles 
- Uniform accessibility to archive logs in case of physical node failure 
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster. 
Note: This option is very dependent on the availability of a CFS on your platform. 

Q. When to use raw over CFS? 
- Always when CFS is not available or not supported by Oracle. 
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk. 
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed. 

.Q. What CRS is? 
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware. 

Q. What is VIP IP used for? 
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113. 

Q. Why we need to have configured SSH or RSH on the RAC nodes? 
SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account. 

Q. Do we have to have Oracle RDBMS on all nodes? 
Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk). 

Q. What is the Load Balancing Advisory? 
To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag. 

Q. How many nodes are supported in a RAC Database? 
With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63. 

Q. What is the Cluster Verification Utiltiy (cluvfy)? 
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment. 

Q What is hangcheck timer used for ? 
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically. 

There are 2 key parameters for this module: 

-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds. 

-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node. 

Q.Is the hangcheck timer still needed with Oracle RAC 10g? 
Yes. 

Q.. What files can I put on Linux OCFS2? 
For optimal performance, you should only put the following files on Linux OCFS2: 
- Datafiles 
- Control Files 
- Redo Logs 
- Archive Logs 
- Shared Configuration File (OCR) 
- Voting File 
- SPFILE 

Q. Is it possible to use ASM for the OCR and voting disk? 
No, the OCR and voting disk must be on raw or CFS (cluster file system). 

Q. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware? 
No, you must properly uninstall Oracle Clusterware and then re-install. 

Q. What the O2CB is? 
The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems). 

Q. What the OCR file is used for? 
OCR is a file that manages the cluster and RAC configuration. It contain resource information.

.Q. What the Voting Disk file is used for? 
The voting disk is nothing but a file that contains and manages information of all the node memberships.Voting disk  must be accessible for each nodes.

Q. What is the recommended method to make backups of a RAC environment? 
RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file. 

Q. What command would you use to check the availability of the RAC system? 
crs_stat -t -v (-t -v are optional) 

Q.. What is the minimum number of instances you need to have in order to create a RAC? 
You can create a RAC with just one server.  

Q..Can you have many database versions in the same RAC? 
Yes, but Clusterware version must be greater than the greater database version. 

Q. What was RAC previous name before it was called RAC? 
OPS: Oracle Parallel Server 

Q.. What RAC component is used for communication between instances? 
Private Interconnect. 

Q. What is the difference between normal views and RAC views? 
A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION 

Q.. Which command will we use to manage (stop, start) RAC services in command-line mode? 
srvctl 

Q.. How many alert logs exist in a RAC environment? 
One for each instance. 

Q.. What are Oracle Clusterware Components 
Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk. 

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster 

Q.. How do you backup voting disk ?
#dd if=voting_disk_name of=backup_file_name 

Q.. How do I identify the voting disk location ?
#crsctl query css votedisk 

Q.. How do I identify the OCR file location 
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform) 
or 
#ocrcheck 

Q.. How do you backup the OCR 
There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\ 
To display backups : 
#ocrconfig -showbackup 
To restore a backup : 
#ocrconfig -restore 
With Oracle RAC 10g Release 2 or later, you can also use the export command: 
#ocrconfig -export -s online, and use -import option to restore the contents back. 
With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command: 
# ocrconfig -manualbackup 

Q. What is SCAN? 
Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster. 

Q. What are Oracle Clusterware processes for 10g on Unix and Linux 
Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart. 

Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user 

Event manager daemon (evmd) —A background process that publishes events that crs creates. 

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms. 

RACG (racgmain, racgimon) —Extends clusterware to support Oracle- specific requirements and complex resources. Runs server callout scripts when FAN events occur. 

Q. What are Oracle database background processes specific to RAC 
•LMS—Global Cache Service Process 
•LMD—Global Enqueue Service Daemon 
•LMON—Global Enqueue Service Monitor 
•LCK0—Instance Enqueue Process 
To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances. 

Q. What is the purpose of Private Interconnect ? 

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol. 

Voting Disks in RAC

Voting Disks in RAC

Voting disks are important component of Oracle Clusterware. Voting disk is a file that manages information about node membership.It is located on the shared cluster system or a shared raw device file or ASM (11gr2 onwards) .Its primary purpose is to help in a situation where the private network communication fails. CSS (Cluster Synchronization Service) is the service that determines which nodes in the cluster are available via communication through a dedicated private network and with a voting disk used as a secondary communication mechanism. 

Important points about Voting disks in RAC

These files can be stored either in ASM or on shared storage.

1)If it is stored in ASM, no need to configure manually as the files will be created depending on the redundancy in ASM.

2) In shared storage system, we need to manually configure these files with redundancy setup for high availability.

a) We must have odd number of disks.

b)Oracle recommends minimum of 3 and maximum of 5. In 10g, Clusterware can supports 32 voting disks but in 11gR2 supports 15 voting disks.

c)A node must be able to access more than half of the voting disks at any time. For eg, if you have 5 voting disks, a node must be able access atleast 3 of the voting disks. If it cannot access the minimum of voting disks, then it is evicted/removed from the cluster.

d) All nodes in the RAC cluster register their heartbeat information in the voting disks/files. RAC heartbeat is the polling mechanism that is sent over the cluster interconnect to ensure all RAC nodes are available.

What is stored in voting disks in RAC?

Voting disks contain static and dynamic data.

Static data : Info about nodes in the cluster

Dynamic data : Disk heartbeat logging

It maintains and consists of important details about the cluster nodes membership, such as
– which node is part of the cluster,
– who (node) is joining the cluster, and
– who (node) is leaving the cluster.

Important Operation for Voting disks in RAC

a) To list currently configured voting disk
$ORA_CRS_HOME/bin/crsctl query css votedisk

b) Adding or deleting the Voting disks

crsctl add css votedisk

Run the following command as the root user to remove a voting disk:

crsctl delete css votedisk

c)  replacing Voting disk

crsctl replace votedisk

Voting disk backup  (In 10g)

dd if=<voting-disk-path> of=<backup/path>

Voting disk restore (In 10g)

dd  if=<backup/path>  of=<voting disk path>

In 11gR2, the voting files are backed up automatically as part of OCR.  Oracle recommends NOT used dd command to backup or restore as this can lead to loss of the voting disk.

Add/delete vote disk

crsctl add css votedisk <path> -adds a new voting disk

crsctl delete css votedisk <path> -- deletes the voting disk

IMPOTED Excel/Text File Data USING TOAD

IMPOTED Excel/Text File Data Into Oracle Table Using TOAD

Step 1 : Open TOAD.

Step 2 : Write the following query to create a test table: 

1. Create table TESTTABLE   
2. (  
3. NAME varchar2(20),  
4. Address varchar2(100),  
5. RollNo integer  
6. )  

Step 3 : Now my table is ready. It's time to create the data in an Excel file.


Step 4 : Go to Database -> Import -> Table Data. 

Step 5 : Select the table you want to import the Excel data to.



Step 6 : Click on show data. Now execute the data button to enable the use. Click on that.

Step 7 : After clicking on Execute Wizard the following screen will open:



Next ->



Step 8 : Now, browse to your Excel file - > Next ->



Next ->



Step 9 : Choose the sheet and click on AutoMap. The AutoMap button will automatically bind your Excel sheet column data with the Oracle table column. 

Next ->



Next ->



Here your Excel data has been bound with the table columns. Next ->



Step 10 : Click on Execute to finish the process. It may ask for sheet selection. Click on Okay with the desired sheet.



Here is your uploaded data:



Step 11 : Commit the Task by clicking on the commit button.