ORACLE TIPS

ORACLE TIPS

Instance Info

Get production info:

SELECT * FROM product_component_version;

SELECT * FROM v$version

Initial passwirds:

SYS: CHANGE_ON_INSTALL
SYSTEM: MANAGER

Running Oracle processes:

ps -ef | grep "ora_" | grep -v grep | grep $ORACLE_SID | awk '{print $2}'

Oracle Home:

cat /etc/oratab | grep ^$ORACLE_SID | cut -f 2 -d ':'

Files

SELECT name FROM sys.v_$datafile; SELECT member FROM sys.v_$logfile; SELECT name FROM sys.v_$controlfile;

PFile and SPFile

Location: $ORACLE_HOME\dbs

CREATE spfile FROM pfile

Sessions

Locked Session

SELECT
   c.owner,
   c.object_name,
   c.object_type,   
   b.sid,
   b.serial#,
   b.STATUS,
   b.osuser,
   b.machine
FROM
   v$locked_object a,
   v$session b,
   dba_objects c
WHERE
   b.sid = a.session_id
AND
   a.object_id = c.object_id;

Archive Log Mode

shutdown database:

SQL> shutdown immediate;

startup database in mount mode:

SQL> startup mount;

Change DB log mode:

SQL> ALTER DATABASE archivelog;

Open DB for normal use

SQL> ALTER DATABASE open;

check archivelog status

SQL> archive log list

Default Archive log destination.

10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
If your not using FRA then all files created in $ORACLE_HOME/database

We can check database archivelog mode through below query:

SQL> SELECT log_mode FROM v$database;
-- or
SQL> archive log list

Simple RMAN Backup

Configure RMAN for compression, auto backup control files, then backup:

rman target=/
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
backup database plus archivelog delete input;

Shell Wrapper Sample

Serious Shell Scripts:

#!/bin/sh
sqlplus / as sysdba << EOF
select * from v\$instance;
EOF

One liner:

echo -e 'select count(*) from v$instance;\n describe v$instance; ' | sqlplus / as sysdba

SQL Plus Formatting

set recsep off
set linesize 100
set newpage 0
set pagesize 999
col XXX format a32

export

exp bcis/bcis file=bcis.dmp log=bcis.log rows=no indexes=no

Instance info

select instance_name, version, host_name, status, database_status, to_char(startup_time,'DD-MON-YYYY HH:MI:SS') from v$instance

Import & Export

To export:

exp scott/tiger file=emp.dmp log=emp.log TABLES=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp TABLES=(emp,dept)

To import:

imp scott/tiger file=emp.dmp FULL=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott TABLES=dept

Backup & Restore

User Managed

offline, datafile

To get a list of all files that needs to be backed up:

SELECT name FROM sys.v_$datafile;
SELECT member FROM sys.v_$logfile;
SELECT name FROM sys.v_$controlfile;

Sometimes Oracle takes forever to shutdown with the “immediate” option. As workaround to this problem, shutdown using these commands:

ALTER system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;

Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.

RAC Management

Starting / Stopping the Oracle RAC Cluster

At this point, we've installed and configured Oracle RAC 10g entirely and have a fully functional clustered database.

After all the work done up to this point, you may well ask, “OK, so how do I start and stop services?” If you have followed the instructions in this guide, all services—including Oracle Clusterware, all Oracle instances, Enterprise Manager Database Console, and so on—should start automatically on each reboot of the Linux nodes.

There are times, however, when you might want to shut down a node and manually start it back up. Or you may find that Enterprise Manager is not running and need to start it. This section provides the commands (using SRVCTL) responsible for starting and stopping the cluster environment.

Ensure that you are logged in as the oracle UNIX user. We will runn all commands in this section from linux1:

# su - oracle
$ hostname
linux1

Stopping the Oracle RAC 10g Environment

The first step is to stop the Oracle instance. When the instance (and related services) is down, then bring down the ASM instance. Finally, shut down the node applications (Virtual IP, GSD, TNS Listener, and ONS).

$ export ORACLE_SID=orcl1
$ emctl stop dbconsole
$ srvctl stop instance -d orcl -i orcl1
$ srvctl stop asm -n linux1
$ srvctl stop nodeapps -n linux1

Starting the Oracle RAC 10g Environment

The first step is to start the node applications (Virtual IP, GSD, TNS Listener, and ONS). When the node applications are successfully started, then bring up the ASM instance. Finally, bring up the Oracle instance (and related services) and the Enterprise Manager Database console.

$ export ORACLE_SID=orcl1
$ srvctl start nodeapps -n linux1
$ srvctl start asm -n linux1
$ srvctl start instance -d orcl -i orcl1
$ emctl start dbconsole

Start/Stop All Instances with SRVCTL

Start/stop all the instances and their enabled services. I have included this step just for fun as a way to bring down all instances!

$ srvctl start database -d orcl

$ srvctl stop database -d orcl

While sometimes TNS Listeners won't start… just start them manually

lsnrctl start

RAC Management Commands

The commands we will use are listed below. Remember that this document is a quick reference, and not an exhaustive list of all commands for managing your RAC environment.

Cluster Related Commands

crs_stat -t Shows HA resource status (hard to read)
crsstat Ouptut of crs_stat -t formatted nicely
ps -ef |grep d.bin crsd.bin evmd.bin ocssd.bin
crsctl check crs CSS,CRS,EVM appears healthy
crsctl stop crs Stop crs and all other services
crsctl disable crs* Prevents CRS from starting on reboot
crsctl enable crs* Enables CRS start on reboot
crs_stop -all Stops all registered resources
crs_start -all Starts all registered resources

These commands update the file /etc/oracle/scls_scr/<node>/root/crsstart which contains the string “enable” or “disable” as appropriate.

Database Related Commands

srvctl start instance -d <db_name> -i <inst_name> Starts an instance
srvctl start database -d <db_name> Starts all instances
srvctl stop database -d <db_name> Stops all instances, closes database
srvctl stop instance -d <db_name> -i <inst_name> Stops an instance
srvctl start service -d <db_name> -s <service_name> Starts a service
srvctl stop service -d <db_name> -s <service_name> Stops a service
srvctl status service -d <db_name> Checks status of a service
srvctl status instance -d <db_name> -i <inst_name> Checks an individual instance
srvctl status database -d <db_name> Checks status of all instances
srvctl start nodeapps -n <node_name> Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n <node_name> Stops gsd, vip and listener

Keep in mind that some resources will not start unless other resources are already online. We will now look at the general dependency list in greater detail.

There are three main background processes you can see when doing a ps –ef|grep d.bin. They are normally started by init during the operating system boot process. They can be started and stopped manually by issuing the command:

/etc/init.d/init.crs {start|stop|enable|disable}

/etc/rc.d/init.d/init.evmd
/etc/rc.d/init.d/init.cssd
/etc/rc.d/init.d/init.crsd

Once the above processes are running, they will automatically start the following services in the following order if they are enabled. This list assumes you are using ASM and have a service set up for TAF/load balancing.

The nodeapps (gsd, VIP, ons, listener) are brought online.
The ASM instances are brought online.
The database instances are brought online.
Any defined services are brought online.

Extend Tablespace

ALTER tablespace TBBASINFO ADD datafile '/dbdata/cisdb/bas/tbbasinfo04.dbf' size 16000M;

select n to m row

SELECT * FROM
(SELECT FIRM_INFO.*, ROW_NUMBER() OVER (ORDER BY KYRQ) R FROM FIRM_INFO)
WHERE R BETWEEN n AND m

oracle, db, unix

WIKI

Subscribe

To Wiki

To Blog

Powered by Google

Tag Cloud

Word Cloud

Recent Page

Wiki