Recovery scripts and examples

RMAN recover database

connect target /
RUN {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

connect target /
RUN {
shutdown immediate;
startup nomount;
set controlfile autobackup format for device type disk to '/db1/orabackup/%F';
restore controlfile from autobackup;
mount database;
restore database;
recover database;
alter database open;
}

RMAN recover tablespace

connect target /
RUN {
sql "alter tablespace sysaux offline";
RESTORE TABLESPACE sysaux;;
RECOVER TABLESPACE sysaux;
SQL "alter tablespace sysaux online";
}

RMAN recover data file

connect target /
RUN {
sql "alter tablespace sysaux offline";
restore datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
recover datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
SQL "alter tablespace sysaux online";
}

RMAN Point-In-Time-Recovery database (incomplete recovery)

Until SCN:

connect target /
RUN
{
RESTORE DATABASE;
RECOVER DATABASE UNTIL SCN 1000; # recovers through SCN 999 or
ALTER DATABASE OPEN RESETLOGS;
}

Until Time:

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

connect target /
RUN
{
set until time '28-JUL-05 06:00:00';
Restore database;
Recover database;
sql "alter database open reset logs";
}

Until Log Seq:

This example assumes that log sequence 1234 was lost due to a disk failure and the database needs to be recovered by using available archived redo logs.

RUN
{
SET UNTIL SEQUENCE 1234 THREAD 1;
RESTORE CONTROLFILE TO '?/oradata/cf.tmp';
RESTORE CONTROLFILE FROM '?/oradata/cf.tmp'; # restores to all CONTROL_FILES locations
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE; # recovers through log 1233
ALTER DATABASE OPEN RESETLOGS;
# you must add new tempfiles to locally-managed temporary tablespaces after restoring
# a backup control file
SQL "ALTER TABLESPACE temp ADD TEMPFILE ''?/oradata/trgt/temp01.dbf'' REUSE";
}

RMAN restore control file

See also here
1. Shut down the database and try to start it up. The instance will start and try to mount the database, but when it doesn’t find the control files, the database fails to mount:

RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP
Oracle instance started
RMAN-00571:
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of startup command at 07/11/2005 17:18:05
ORA-00205: error in identifying controlfile, check alert log for more info

You can avoid the preceding error messages by using the alternative command STARTUP NOMOUNT:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;

2. Issue the RESTORE CONTROLFILE command so RMAN can copy the control file backups to their default locations specified in the init.ora file:

RMAN> RESTORE CONTROLFILE;

3. After the restore is over, mount the database:

RMAN> ALTER DATABASE MOUNT;

4. Recover the database:

RMAN> RECOVER DATABASE;

5. Because RMAN restores the control files from its backups, you have to open the database with the RESETLOGS option:

RMAN> ALTER DATABASE OPEN RESETLOGS;

RMAN tablespace Point-In-Time-Recovery (TSPITR)

THIS PROCEDURE WILL RECOVER THE TABLESPACE IN AUX INSTANCE FIRST AND TRANSFER DATA TO TARGET IN ONE RMAN STEP
DON'T USE THIS PROCEDURE, IF YOU DON'T WANT TO OVERWRITE TARGET TABLESPACE DATA!!!!

Recover the tablespaces from the database (the target database) by first performing the PITR in a temporary instance called the auxiliary database, which is created solely to serve as the staging area for the recovery of the tablespaces.

Here’s how to use RMAN to perform a TSPITR:

1. Create the auxiliary database. Use a skeleton initialization parameter file for the auxiliary instance along the lines of the following:
db_name=help (this is the target database_name)
db_file_name_convert=('/oraclehome/oradata/target/', '/tmp/')
/* Lets you convert the target database data files to a different name */
log_file_name_convert=('/oraclehome/oradata/target/redo', '/tmp/redo')
/* Lets you convert the target database redo log files to a different name. */
instance_name=aux
control_files=/tmp/control1.ctl
compatible=10.0.2
db_block_size=8192

2. Start up the auxiliary database in the nomount mode:

$ sqlplus /nolog
SQL> CONNECT sys/oracle@aux AS sysdba
SQL> STARTUP NOMOUNT PFILE = /tmp/initaux.ora

3. Generate some archived redo logs and back up the target database. You can use the ALTER SYSTEM SWITCH LOGFILE command to produce the archived redo log files.

4. Connect to all three databases—the catalog, target, and auxiliary databases—as follows:

$ rman target sys/sys_passwd@targetdb catalog rman/rman@rmandb auxiliary system/oracle@aux

5. Perform a TSPITR. If you want to recover until a certain time, for example, you can use the following statement (assuming your NLS_DATE format uses the following format mask: Mon DD YYYY HH24:MI:SS):

RMAN> RECOVER TABLESPACE users UNTIL TIME ('JUN 30 2005 12:00:00');

This is a deceptively simple step, but RMAN performs a number of tasks in this step. It restores the data files in the users tablespace to the auxiliary database and recovers them to the time you specified. It then exports the metadata about the objects in the tablespaces from the auxiliary to the target database. RMAN also uses the SWITCH command to point the control file to the newly recovered data files.

6. Once the recovery is complete, bring the user tablespace online:

$ rman target sys/sys_passwd@targetdb
RMAN> SQL "alter tablespace users online";
RMAN> Exit;

7. Shut down the auxiliary instance and remove all the control files, redo log files, and data files pertaining to the auxiliary database.

RMAN restore and recover a non-archivelog database from a full (cold) backup

Source: http://www.idevelopment.info/

In this case study, (running in no-archivelog mode), any user error or media failure would require a complete database recovery. You can, however, use the SET UNTIL command to recover to different points in time when incrementals are taken. (Keep in mind that in our example, we did not make use of incremental backups!)

NOTE: Because redo logs are not archived, only full and incremental backups (if you were taking incremental backups) are available for restore and recovery.

It is assumed that you have all the configuration files like:

    * Server parameter file (spfile - equivalent of init.ora in 9i)
    * tnsnames.ora
    * listener.ora
    * sqlnet.ora (optional)

are all in their appropriate places. It is also assumed that you can startup the Oracle instance in NOMOUNT MODE and connect from RMAN to the target instance.

The steps are:

   1. If not using a recovery catalog, or if the database name is ambiguous in, you need to start RMAN and set the DBID before restoring the controlfile from autobackup.
   2. Startup database in NOMOUNT mode. (You should have restored the initialization file for database, and listener files [only if connecting over SQLNET].)
   3. Restore controlfile.
   4. Mount the database.
   5. Restore all database files.
   6. Apply all incrementals. (In this example, we are not taking incremental backups, so this step is not required.)
   7. Open database with RESETLOGS mode to re-create the online log files.
   8. You will need to manually add any tempfiles back to the database after recovering the database.

set dbid 2528050866;

connect target /;
startup nomount;

run {
# -----------------------------------------------------------
# Uncomment the SET UNTIL command to restore database to the
# incremental backup taken two days ago.
# SET UNTIL TIME 'SYSDATE-2';
# -----------------------------------------------------------
set controlfile autobackup format for device type disk to '/orabackup1/rman/TARGDB/%F';
restore controlfile from autobackup;
alter database mount;
restore database;
recover database noredo;
alter database open resetlogs;
sql "alter tablespace temp add tempfile ''/u06/app/oradata/TARGDB/temp01.dbf''
size 500m autoextend on next 500m maxsize 1500m";
}

exit

NOTE: Tempfiles are automatically excluded from RMAN backups. This requires them to be re-added at recovery time.

HP-UX Commands Cheat Sheet

HP-UX Commands Cheat Sheet
- Reference
- Hardware
- OS Information
- Network
- User
- Disk
- Storage
- Software
- Operation
- Service
- Note
- ICAP
- Replacement for Linux Command

Reference

HPUX 10.20 - man pages http://ods.com.ua/win/eng/unix/usail/man/hpux/

Hardware

Operation	Command	supplement
Check Hardware (CPU, Memory, Firm, OS)	machinfo machinfo \|grep -i memory
Check model number	model	Example: superdome, Integrity rx2800
reboot	shutdown -r 0 shutdown -r now reboot
halt	shutdown -h 0

# shutdown -r 0

SHUTDOWN PROGRAM
05/23/15 11:40:00 JST
Do you want to send your own message? (You must respond with 'y' or 'n'.): n

Broadcast Message from user01 (pts/0) Sat May 23 11:40:15...
PLEASE LOG OFF NOW ! ! !
        System maintenance about to begin.
All processes will be terminated in 0 second

# shutdown -r 0

SHUTDOWN PROGRAM
05/23/15 11:42:53 JST
Do you want to send your own message? (You must respond with 'y' or 'n'.): y
Type your message.
End with a new-line and EOF character ([CTRL]-[D]).                                        <- Ctrl - D

# reboot
Shutdown at 16:44 (in 0 minutes)

        *** FINAL System shutdown message from user@test-hpux-1 ***

System going down IMMEDIATELY

System shutdown time has arrived
reboot: redirecting error messages to /dev/console

OS Information

Operation	Command	supplement
Check OS Information	osinfo	Host, Version, LastBootTime, SystemUpTime
Check hostname	hostname grep -i hostname /etc/rc.config.d/netconf
Check runlevel	who -r grep initdefault /etc/inittab
Check release date	uname -a uname -r
Check kernel configuration date	kconfig -w
Check kernel parameter	kctune ← all parameter kctune -S ← not default parameter kctune PARAMETER
Setting kernel parameter	kctune PARAMETER=VALUE kctune PARAMETER= ← Change Default kctune PARAMETER=Default ← Change Default kctune -h PARAMETER=VALUE -h : Changes will be held until next boot Example kctune filecache_max=4% kctune semmns=“(semmni*2)”
	dmesg

Network

/etc/rc.config.d/netconf

Operation	Command	supplement
Check NIC	nwmgr	Is the NIC Down or Up?
Check NIC	lanscan	Is the NIC Down or Up?
List All NIC's IP	netstat -in ← no DNS netstat -i ← Use DNS
Check IP	ifconfig NICNAME ifconfig lan0
Check routing	netstat -rn
Check NIC Speed	lanadmin -x PPANUMBER lanadmin -x 0
Change NIC Down	ifconfig NICNAME down
Change NIC Up	ifconfig NICNAME up

User

Operation	Command	supplement
add a new group to the system	groupadd -g GID GROUP groupadd -g 1000 dev
add a new user login to the system	useradd -m USER useradd -g PRIMARY_GROUP -G GROUP -u UID -s /usr/local/bin/bash -m USER -m : Create the home directory for the new login if it does not exist.
delete a user login from the system	userdel USER userdel -r USER ← delete home directory
change login password	passwd passwd USER
password expires's day	passwd -x 90 USER ← 90days
Check Locked User?	userstat -u USER
unlock user	userdbset -d -u USER auth_failures
password rule	/usr/sbin/userdbget -u USER ← check /usr/sbin/userdbset -u USER MIN_PASSWORD_LENGTH=8 PASSWORD_HISTORY_DEPTH=1 AUTH_MAXTRIES=10 PASSWORD_MIN_LOWER_CASE_CHARS=1 PASSWORD_MIN_UPPER_CASE_CHARS=1 PASSWORD_MIN_DIGIT_CHARS=1 PASSWORD_MIN_SPECIAL_CHARS=0
modify a user login on the system	usermod -u UID -g PRIMARY_GROUP -G GROUP USER

Disk

Operation	Command	supplement
utilization ratio	bdf
summarize disk usage	du -sk DIRECTORY
check mount	mount mount -v
mount volume	mount -F vxfs -o largefiles,log,nodatainlog /dev/vg01/lvol1 /u02 largefiles : using over 2GB file.
umount volume	umount /u02
check mount process	fuser -u /u02
	pvdisplay /dev/disk/disk3_p2
list vg (volume group)	vgdisplay vgdisplay -v vgdisplay -v vg00 \|grep "PV Name"	check activated or not
VG activate / deactivate	vghcnage -a n /dev/vgxxx ← deactivate vgchange -a y /dev/vgxxx ← activate
list lv (logical volume)	lvdisplay /dev/vg/lvol

Associated Site

How to use LVM on HP-UX

Storage

Operation	Command	supplement
Check LUN of Storage	ioscan -N -fnu -C disk ioscan -fnkC disk ioscan -m dsf ioscan -m lun
Check HP XP Storage	xpinfo -i ← reduction xpinfo ← detail xpinfo -v ← check xpinfo version
Scan Storage	ioscan ioscan -f ← Detail

Software

Operation	Command	supplement
Listing Software	swlist
HP-UX Version	swlist \|grep HPUX11i
HP-UX pach version	swlist -l bundle QPKBASE swlist -l bundle \| grep QPK
Installing Software	swinstall -s ./SOURCE.depot swinstall -s ./SOURCE.depot SOFTWARE swinstall -s ./SOURCE.depot "*"	Installed /usr/local You need correct hosts and DNS.
Removing Software	swremove ← GUI swremove SOFTWARE ← CUI
	show_patches

Operation

Operation	Command	supplement
ls	ls -l ← Byte Unit
sudo	visudo
Performance Information right now	top
Performance Information right now	glance
Memory Information	swapinfo
Load Average	w
login information right now	w
login information backward	last

Service

Operation	Command	supplement
stop service	/sbin/init.d/SERVICE stop	secsh ← ssh net ← network
start service	/sbin/init.d/SERVICE start

restart

/sbin/init.d/secsh stop; /sbin/init.d/secsh start

Note

Operation	Command	supplement
Japanese LANG	export LANG=ja_JP.SJIS

ICAP

Operation	Command	supplement
	icapstatus
	parstatus

Replacement for Linux Command

Linux Operation	HP-UX Command	supplement
find --maxdepth=1	cd /etc ; find . -name "hosts" -type f ! -path './/*'
compress tar z option	tar -cvf - test.d \| gzip > /tmp/test.tar.gz tar -cvf - /etc/test.d \| gzip > /tmp/test.tar.gz
uncompress tar z option	gzip -dc test.tar.gz \| tar xvf - bzip2 -dc test.tar.bz2 \| tar xvf -

Information Technology

Friday, September 18, 2020

Oracle Quick Reference