srvctl commands

1. Stop the database

- Shut down all services in the Oracle home on each node that might be accessing a database:
$ srvctl stop service -d pdb -s pdb_taf (stop pdb_taf only, not stop the instances)
$ srvctl stop service -d pdb (stop all services accessing pdb?, including pdb_taf)

In general:
$ srvctl stop service -d db_name [-s service_name_list [-i inst_name]]

- Shut down all Oracle RAC instances on all cluster nodes by entering the following command where db_name is the name of the database:
$ srvctl stop database -d pdb
(or $ srvctl stop db -d pdb )
$ srvctl stop database -d pdb -o immediate

In general:
$ srvctl stop database -d db_name

- Shut down Automatic Storage Management (ASM) instances on all cluster nodes by entering the following command (replace node_name with each node):

$ srvctl stop asm -n node_name

- To stop the listener running on a node, where the listener is running:

$ srvctl stop listener -n node_name [-l listenername]

- Stop all node applications (gsd, ons, listener, and vip) on all cluster nodes by entering the following command as the root user, where node_name is the name of the node where the applications are running:

# srvctl stop nodeapps -n node_name

- Shut down the Oracle Clusterware processes by entering the following command on all nodes as the root user:

# CRS_home/bin/crsctl stop crs

2. Start the database

- Start all instances (database)
$ srvctl start database -d db_name
- Start an instance
$ srvctl start instance -d db_name -i inst_name
- Start a services pdb_taf:
$ srvctl start service -d pdb -s pdb_taf
(If related instances are down, it will start all instances as well)

In general:
$ srvctl start service -d db_name -s service_name
$ srvctl start service -d pdb (start all services?)

- Start listener on each node of the cluster:
$ srvctl start listener -n node

3. A way to run RAC in a single instance:
- In instance PDB1 on PDB1P, issue
SQL> alter system cluster_database=false scope=spfile;

- Stop all services and instances:
pdb2p:$ srvctl stop service -d pdb
pdb2p:$ srvctl stop database -d pdb -o immediate

- Restart all instances and services:
pdb2p:$ srvctl start database -d pdb
pdb2p:$ srvctl start service -d pdb -s pdb_taf (only need this one?)

- Check the status:
pdb2p:$ srvctl status service -d pdb
Service pdb_taf is running on instance(s) pdb2
pdb2p:$ srvctl status db -d pdb
Instance pdb1 is not running on node pdb1p
Instance pdb2 is running on node pdb2p

- alter_pdb1.log shows the message:

Sun Feb 17 15:52:09 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Interface type 1 en3 192.168.10.0 configured from OCR for use as a cluster interconnect
Interface type 1 en0 10.1.0.0 configured from OCR for use as a public interface
Picked latch-free SCN scheme 3
WARNING: db_recovery_file_dest is same as db_create_online_log_dest_2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =121
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.3.0.
System parameters with non-default values:
processes = 1000
sessions = 1105
sga_max_size = 4294967296
__shared_pool_size = 570425344
__large_pool_size = 33554432
__java_pool_size = 16777216
__streams_pool_size = 0
spfile = +PDATA/pdb/spfilepdb.ora
sga_target = 4294967296
control_files = +PDATA/pdb/controlfile/current.384.636108915, +PFRA/pdb/controlfile/current.272.636108915
db_block_size = 8192
__db_cache_size = 3657433088
compatible = 10.2.0.3.0
log_archive_dest_1 = LOCATION=+PFRA
db_file_multiblock_read_count= 8
cluster_database = FALSE
cluster_database_instances= 1
db_create_file_dest = +PDATA
db_create_online_log_dest_1= +PDATA
db_create_online_log_dest_2= +PFRA
db_recovery_file_dest = +PFRA
db_recovery_file_dest_size= 605590388736
thread = 1
instance_number = 1
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=pdwaXDB)
remote_listener = LISTENERS_PDB
job_queue_processes = 10
parallel_max_servers = 40
parallel_execution_message_size= 16384
background_dump_dest = /u01/oracle/admin/pdb/bdump
user_dump_dest = /u01/oracle/admin/pdb/udump
core_dump_dest = /u01/oracle/admin/pdb/cdump
audit_file_dest = /u01/oracle/admin/pdb/adump
db_name = pdb
open_cursors = 300
pga_aggregate_target = 536870912
Cluster communication is configured to use the following interface(s) for this instance
192.168.10.101
Sun Feb 17 15:52:09 2008
cluster interconnect IPC version:Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
PMON started with pid=2, OS id=1200584
DIAG started with pid=3, OS id=831956
PSP0 started with pid=4, OS id=1409044
LMON started with pid=5, OS id=1179846
LMD0 started with pid=6, OS id=1290462
MMAN started with pid=7, OS id=1241146
DBW0 started with pid=8, OS id=1405370
LGWR started with pid=9, OS id=729280
CKPT started with pid=10, OS id=880810
SMON started with pid=11, OS id=926172
RECO started with pid=12, OS id=684094
CJQ0 started with pid=13, OS id=589840
MMON started with pid=14, OS id=1417592
Sun Feb 17 15:52:10 2008
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=15, OS id=917530
Sun Feb 17 15:52:10 2008
starting up 1 shared server(s) ...
Sun Feb 17 15:52:10 2008
lmon registered with NM - instance id 1 (internal mem no 0)
cluster_database_instances parameter mismatch!
Instance 1 = 2, mine = 1
This instance has not mounted yet. Exiting!
Sun Feb 17 15:52:11 2008
USER: terminating instance due to error 29707
Instance terminated by USER, pid = 1474652

Run RAC on one single node

Sometimes, we want to run RAC on a single node, instead. Here is what I did to make that happen.

-- Stop the TAF service
phdb2p:/localhome/oracle$srvctl stop service -d pdb
phdb2p:/localhome/oracle$srvctl status service -d pdb
Service pdb_taf is not running.

-- Stop the instances
phdb2p:/localhome/oracle$srvctl stop db -d pdb
phdb2p:/localhome/oracle$srvctl status db -d pdb
Instance pdb1 is not running on node phdb1p
Instance pdb2 is not running on node phdb2p

-- Make sure the neighor on the same box is not affected
phdb2p:/localhome/oracle$srvctl status service -d pdba
Service pdba_taf is running on instance(s) pdba2, pdba1

phdb2p:/localhome/oracle$srvctl status db -d pdba
Instance pdba1 is running on node phdb1p
Instance pdba2 is running on node phdb2p

phdb2p:/localhome/oracle$export ORACLE_SID=pdb2
phdb2p:/localhome/oracle$sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 18 09:19:07 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2078368 bytes
Variable Size 671091040 bytes
Database Buffers 3607101440 bytes
Redo Buffers 14696448 bytes
Database mounted.

SQL> alter system set CLUSTER_DATABASE=false scope=memory;
alter system set CLUSTER_DATABASE=false scope=memory
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> create pfile='/u01/oracle/admin/pdwa/pfile/pdb2Init.ora' from spfile;

File created.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

-- Edit the file by changing the line to "CLUSTER_DATABASE=false"
-- Then, start the instance. In this way, the instance will use the
-- original spfile for the next startup without any manual changes.
--
SQL> startup mount pfile=/u01/oracle/admin/pdb/pfile/pdb2Init.ora;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2078368 bytes
Variable Size 671091040 bytes
Database Buffers 3607101440 bytes
Redo Buffers 14696448 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +PFRA
Oldest online log sequence 26801
Current log sequence 26802

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
pdb2

SQL> select name from v$database;

NAME
---------
PDB

SQL>

-- After that, user needs to make sure the tnsnames.ora has an
-- entry for the instance pdb2:
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = phdb2p-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb)
(INSTANCE_NAME = pdb2)
)
)

CONTROL_FILE_RECORD_KEEP_TIME and RMAN

Our financial database has about 5 terabytes (TB) data. In the upgrade project, we maintein more than 5 instances (QA, Staging, Conversion, Development, Static, etc) running. That creates big challenge in backing up all instances.

During a period of days, the Veritas backup server ran very slow and took more than 24 hours to back up one instance. When we used one backup to refresh (restore) another instance, we kept geeting "missing files" or "expired files" from the backup set, such as:

MAN-00571: ==================================
RMAN-00569: == ERROR MESSAGE STACK FOLLOWS ==
RMAN-00571: =================================
RMAN-03002: failure of restore command at 01/24/2008 11:35:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1381 found to restore
RMAN-06023: no backup or copy of datafile 1382 found to restore

But, the Veritas log and command "RMAN> list backup of datafile 1381 ; " clearly indicate that the data file was included in a backup piece.

After I cheked the Verita log carefully, I found that some backupieces that were just created by the script were deleted at the end of same backup script.

The reason is that RMAN backup uses target control file instead of recovery catalog. The parameter "CONTROL_FILE_RECORD_KEEP_TIME = 1" in the init file makes RMAN delete all backuppieces that was backed up 24 hours (1 day) ago, even "RMAN retention policy is set to redundancy 3;" in the RMAN configuration.

Be careful when you use CONTROL_FILE_RECORD_KEEP_TIME!

Recovery area is full

Database was hung with error in alert log file of instance pdb1:

ORA-19815: WARNING: db_recovery_file_dest_size of 605590388736 bytes is 100.00% used, and has 0 remaining bytes available.

1. Re-mount the database pdb1

After I stopped the database, I had difficulties to mount it maybe because the ORA error, and so I had to work around to mount the database.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2078328 bytes
Variable Size 687868296 bytes
Database Buffers 3590324224 bytes
Redo Buffers 14696448 bytes

-- Temporarily to increase the size to make room for mounting the db
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 700g scope=memory;
System altered.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch

--
-- Note: It's a RAC. I had to shut down the instance pdb2 on another
-- node before I can mount this one because the parameter change.
--

SQL> alter database mount;
Database altered.

2. Run RMAN to back up recovery area after connect to the catalog and the target pdb1 where the regular RMAN backup job runs. Note that the database has to be in mount for the below RMAN command to run:

run {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
backup recovery area;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}

3. Check the database view to make sure that RMAN gets the space back as well:

SQL> SELECT substr(name, 1, 30) name, space_limit AS quota, space_used AS used, space_reclaimable AS reclaimable, number_of_files AS files
FROM v$recovery_file_dest;

NAME QUOTA USED RECLAIMABLE FILES
------ ---------- ---------- ----------- ----------
+FRA 7.5162E+11 6.0577E+11 6.0576E+11 88

4. Shut down the mounted instance:

$ srvctl status db -d pdb
Instance pdb1 is running on node db1s
Instance pdb2 is not running on node db2s

-- shut it down
SQL> shutdown immediate;

-- re-check
$ srvctl status db -d pdb
Instance pdb1 is not running on node db1s
Instance pdb2 is not running on node db2s

$ srvctl status service -d pdb
Service pdb_taf is not running.

5. Use srvctl to start the instances:

$ srvctl start service -d pdb -s "pdb_taf" -o open

$ srvctl status db -d pdb
Instance pdb1 is running on node db1s
Instance pdb2 is running on node db2s

$ srvctl status service -d pdb
Service pdb_taf is running on instance(s) pdb1, pdb2

6. Check the parameter in the database. It's far too big!

SQL> show parameter db_recovery_file

NAME TYPE VALUE
----------------------------- ----------- ------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 564G

Now, after the instances started, the alert log says:

db_recovery_file_dest_size of 577536 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup.

Metalink note 315098.1 says that this action will not delete the files from the FRA. The files would be deleted whenever any space is required for any new operation. RMAN may just mark those archive logs as deletion cadidates as they were backed up to the tape. It is the safest way to fix the issue.

Oracle Regular Expressions in 10G

I was requested to find all non-numeric values in the column ATTRIBUTE1 of table PA_BUDGET_LINES in 11i. Since the table has more than one million rows, I had to find a good query to do the task.

The new regular expressions helped me to get the job done quickly:

select * from pa_budget_lines
where attribute1 is not null
and (regexp_like(upper(attribute1), '*.[A-Z].*') or regexp_like(attribute1, '[^A-Z,0-9,.,-]'));
-- regexp_like(upper(attribute1), '[^0-9]') does not include the mix.

There are four functions available in both SQL and PL/SQL:

REGEXP_LIKE -- Determine whether pattern matches
REGEXP_SUBSTR -- Determine what string matches the pattern
REGEXP_INSTR -- Determine where the match occurred in the string
REGEXP_REPLACE -- Search and replace a pattern