How to find current voting disk location, How to Backup Voting Disk

Below commands tested on Oracle Enterprise Linux 5.1 – 10gR2 2-node RAC cluster.

[oracle@linux1 ~]$ crsctl query css votedisk

0. 0 /u02/oradata/ractest/CSSFile

1. 0 /u02/oradata/ractest/CSSFile_mirror1

2. 0 /u02/oradata/ractest/CSSFile_mirror2

located 3 votedisk(s).

when we back up the voting disks:

– After RAC installation
– After adding nodes to or deleting nodes from the RAC
– After performing voting disk add or delete operations

To make a backup of the voting disk:

– I use the Linux dd command,
voting_disk_name is the name of the active voting disk.
backup_file_name is the name of the file to which you want to back up the voting disk contents.

dd if=voting_disk_name of=backup_file_name

Perform this operation on every voting disk as needed.
– If the voting disk is stored on a raw device, you can use the device name in place of voting_disk_name.

Example:
[oracle@linux1 ~]$ dd if=/u02/oradata/ractest/CSSFile of=CSSFile_bck

20000+0 records in

20000+0 records out

10240000 bytes (10 MB) copied, 1.13288 seconds, 9.0 MB/s

“the backup can be performed while the Cluster Ready Services (CRS) process is active”.

Best Regards

Referances:
Oracle® Database 2 Day + Real Application Clusters Guide 10g Release 2 (10.2) Part Number B28759-03
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2)

Some useful 10g RAC cluster commands(SRVCTL)

in this post, i will share some useful RAC commands with you. Below commands tested on Oracle Enterprise Linux 5.1 – 10gR2 2-node RAC cluster.

To list all configured databases

[oracle@linux1 ~]$ srvctl config database
ractest

To display all services for the specified cluster database

[oracle@linux1 ~]$ srvctl config service -d ractest

taf_srvc PREF: ractest1 ractest2 AVAIL:

To display configuration for the RAC database

[oracle@linux1 ~]$ srvctl config database -d ractest
linux1 ractest1 /u01/app/oracle/product/10.2.0/db_1
linux2 ractest2 /u01/app/oracle/product/10.2.0/db_1

To display status of all instances and services

[oracle@linux1 ~]$ srvctl status database -d ractest
Instance ractest1 is running on node linux1
Instance ractest2 is not running on node linux2

To display status of a single instance

[oracle@linux1 ~]$ srvctl status instance -d ractest -i ractest1
Instance ractest1 is running on node linux1

To display status of a named service globally across the database

[oracle@linux1 ~]$ srvctl status service -d ractest -s taf_srvc
Service taf_srvc is running on instance(s) ractest1, ractest2

To display status of node applications on a particular node

[oracle@linux1 ~]$ srvctl status nodeapps -n linux1
VIP is running on node: linux1
GSD is running on node: linux1
Listener is running on node: linux1
ONS daemon is running on node: linux1

To display all running instances in the cluster using SQL command

SQL> SELECT
inst_id
, instance_name
, status
, database_status db_status
, active_state state
, host_name host
FROM gv$instance
ORDER BY inst_id;

INST_ID INSTANCE_NAME PAR STATUS DB_STATUS STATE HOST ——————————————————————————–
1 ractest1 YES OPEN ACTIVE NORMAL linux1
2 ractest2 YES OPEN ACTIVE NORMAL linux2

SQL>

References
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2)

Saving Current Schema Statistics and Gather New Statistics-Import Schema Statistics From User Statistics Table

In this article, i will gather new statistics on the YASAR schema, but first i will save the original schema statistics in a user statistics table: YASAR.STATS_TAB1. This example are based on a Oracle 10g installation.

begin
Dbms_stats.CREATE_STAT_TABLE(ownname=> ‘YASAR’ ,stattab=> ‘STATS_TAB1′);/*This operation creates user statistics table */
end;

SQL> select * from yasar.stats_tab1;
no rows selected

now yasar.stats_tab1 is empty.

SQL>SELECT * FROM DBA_OPTSTAT_OPERATIONS WHERE TARGET = ‘YASAR’;
OPERATIONTARGETSTART_TIMEEND_TIME
gather_schema_statsYASAR18-01-009 11:20:3618-01-009 11:20:37 /*Current statistics of yasar schema

History of statistics operations performed.

dbms_stats.gather_schema_stats(
ownname=> ‘YASAR’ ,
cascade=> TRUE,
degree=> DBMS_STATS.AUTO_DEGREE,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> ‘AUTO’,
method_opt=> ‘FOR ALL COLUMNS SIZE AUTO’,
options=> ‘GATHER’,
stattab=> ‘STATS_TAB1′,
statid => ‘DENEME1′
);
end;

This operation gathers new statistics on the yasar schema, but first saves the original statistics in a user statistics table: yasar.stats_tab1.

SQL> select STATID,D1 from yasar.stats_tab1;
STATIDD1
DENEME11.18.2009 11:20:36
DENEME11.18.2009 11:20:36
DENEME11.18.2009 11:20:37
………………………………..

now table isn’t empty because there are original statistics in yasar.stats_tab1.

SQL>SELECT * FROM DBA_OPTSTAT_OPERATIONS WHERE TARGET = ‘YASAR’;
OPERATIONTARGETSTART_TIMEEND_TIME
gather_schema_statsYASAR18-01-009 11:20:3618-01-009 11:20:37 –this statistics are in the yasar.stats_tab1 now.
gather_schema_statsYASAR19-01-009 12:20:3619-01-009 12:20:37 –after executing dbms_stats.gather_schema_stats.

If the new statistics are causing the optimizer to generate poor plans, then the original statistics can be restored as follows:

BEGIN
DBMS_STATS.DELETE_SCHEMA_STATS (ownname=>’YASAR’);
end;

This operation deletes schema statistics from the dictionary.

begin
DBMS_STATS.IMPORT_SCHEMA_STATS (‘YASAR’,stattab => ‘STATS_TAB1′,statid => ‘DENEME1′);
END;

This operation imports statistics from STATS_TAB1 to dictionary.

Moving Or Renaming Datafile

In this article i will move and rename one datafile. This example are based on a Oracle 10g installation on Windows, but this method is the same for different versions of Oracle on any platform.

The current location of the datafiles can be queried from the v$datafile.

SQL> SELECT file#,name, status FROM v$datafile;

FILE# NAME STATUS
—– ——————————————————- ———-
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF ONLINE
4 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF ONLINE
5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF ONLINE

USERS01.DBF will be moved. For this , database will be shutdown.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

USERS01.DBF physical file is moved to new location. OS commands are used for this operation. D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF is moved and renamed to C:\orcl\oradata\moved_USERS01.DBF.

Database is started up in mount mode.
SQL> startup mount
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1296500 bytes
Variable Size 146802572 bytes
Database Buffers 159383552 bytes
Redo Buffers 7090176 bytes
Database mounted.

SQL> alter database rename file ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF’ to ‘C:\orcl\oradata\moved_USERS01.DBF’;

Database altered.
Data dictionary has been updated.

SQL> alter database open;

Database altered.

initial query shows that the the datafile has been renamed in the data dictionary.
SQL> SELECT file#,name, status FROM v$datafile;

FILE# NAME STATUS
—– ——————————————————- ———-
1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
2 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF ONLINE
3 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF ONLINE
4 C:\ORCL\ORADATA\MOVED_USERS01.DBF ONLINE
5 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF ONLINE

Also you can do this when the database is open, by offlining the corresponding tablespace. But before do this when database is open, make sure you have valid backup of database and archivelog.

Best Regards,

References:
1 – Oracle® Database SQL Reference 10g Release 2 (10.2)
2 – Metalink
3 – Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 2 (10.2)

Moving Or Renaming Controlfile

This article about a brief explanation of how Oracle controlfiles can be moved or renamed to a new location.The examples are based on a Oracle 10g installation on Windows, but this method is the same for different versions of Oracle on any platform.

The current location of the controlfiles can be queried from the V$CONTROLFILE.

SQL> select name from v$controlfile;

NAME
——————————————————————————–
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

The current control_files instance parameter can be shown below.

SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL,
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL,
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

Alter the control_files parameter using the ALTER SYSTEM command. For instances using a pfile replace the spfile manipulation steps with an amendment of the parameter in the init.ora file.

SQL> ALTER SYSTEM SET
control_files=’C:\orcl\oradata\cfile\CONTROL01.CTL’,
‘D:\oracle\product\10.2.0\oradata\orcl\CONTROL02.CTL’,
‘E:\orcl\oradata\cfile\CONTROL03.CTL’ SCOPE=SPFILE;

System altered.

Shutdown the database.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

Move or Rename the physical file on the OS.

SQL> HOST MOVE D:\oracle\product\10.2.0\oradata\orcl\CONTROL01.CTL C:\orcl\oradata\cfile\CONTROL01.CTL

SQL> HOST MOVE D:\oracle\product\10.2.0\oradata\orcl\CONTROL03.CTL E:\orcl\oradata\cfile\CONTROL03.CTL

Startup database.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 1296500 bytes
Variable Size 146802572 bytes
Database Buffers 159383552 bytes
Redo Buffers 7090176 bytes
Database mounted.
Database opened.

This query shows that the controlfile has been moved in the data dictionary.
SQL> select name from v$controlfile;

NAME
——————————————————————————–
C:\ORCL\ORADATA\CFILE\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
E:\ORCL\ORADATA\CFILE\CONTROL03.CTL

Best Regards,

References:
1 – Oracle® Database SQL Reference 10g Release 2 (10.2)
2 – Metalink
3 – Oracle® Database Backup and Recovery Advanced User’s Guide 10g Release 2 (10.2)