Cloning with Direct NFS

Clonedb is a new Direct NFS (DNFS) feature introduced in the 11.2.0.2.  clonedb uses dNFS technology to instantly fire up a clone using an existing backup of a database as the data store. The clone uses copy-on-write technology, so only changed blocks need to be stored locally, while the unchanged data is referenced directly from the backup files. This drastically increases the speed of cloning a system and means that several separate clones can function against a single set of backup datafiles, thus saving considerable amounts of space.

Configuration, Setup

• NFS Server : viviana (ES Linux)
• Primary DB server : Chopin
• Clone DB server : Wolfgang

Create an file with the following attributes for each NFS server to be accessed using Direct NFSserver:

viviana path: 192.168.1.3 export: /u01/nfs/backup mount: C:\backup

Oracle Database uses an ODM library, , to enable Direct NFS. To replace the standard ODM library, , with the ODM NFS library,complete the following steps:

Shutdown database C:\> copy oraodm11.dll oraodm11.dll.stub C:\> copy /Y oranfsodm11.dll oraodm11.dll On the NFS server, Export the directory as an NFS share by adding the following lines to the "/etc/exports" file /u01/nfs/backup  *(rw,wdelay,insecure,no_root_squash,no_subtree_check)

Make sure the NFS service is available after reboot and restart the NFS service.

# chkconfig nfs on # service nfs restart

Take a image backup of the production database:

run {    set nocfau;    backup as copy database format '/host/backups/prod/%U' ; }

Use the following views for Direct NFS management:

• v$dnfs_servers: Shows a table of servers accessed using Direct NFS.
  
• v$dnfs_files: Shows a table of files currently open using Direct NFS.
• v$dnfs_channels: Shows a table of open network paths (or channels) to servers for which Direct NFS is providing files.
  
• v$dnfs_stats: Shows a table of performance statistics for Direct NFS

Oracle Data Guard Broker DGMGRL Configuration

Step-by-step instructions on how to set up  DG broker

1. Set up init parameters on primary to enable broker
   
   

   ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; ALTER SYSTEM SET DB_UNIQUE_NAME='apple1'; ALTER SYSTEM SET DB_DOMAIN='db_domain';

2. Set up init parameters on standby
   
   

   ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; ALTER SYSTEM SET DB_UNIQUE_NAME='apple2'; ALTER SYSTEM SET DB_DOMAIN='db_domain';

3. GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>> in listener.ora on all instances of both primary and standby.
   
   
   

   SID_LIST_LISTENER =   (SID_LIST =  (SID_DESC =         (GLOBAL_DBNAME = apple1_dgmgrl)         (ORACLE_HOME = c:\oracle\1020)         (SID_NAME = apple1)         ) )

4. TNSNAMES.ora need to change to the correct service_names as the listener.ora
   
   This is important otherwise you'll have TNS-12154 error during switchover operation.
5. Create the configuration
   
   
   

   CREATE CONFIGURATION 'AppleDR' AS > PRIMARY DATABASE IS 'apple1' > CONNECT IDENTIFIER IS 'apple1'ADD DATABASE 'apple2' AS > CONNECT IDENTIFIER IS 'apple2' maintained as physical;

6. Enable the configuration
   

   DGMGRL> ENABLE CONFIGURATION Enabled. DGMGRL> SHOW CONFIGURATION > show database verbose apple1

   
   
7. Troubleshooting
    Let us see some sample issues and their fix
   
   Issue
   
   

   DGMGRL> CONNECT sys/sys  ORA-16525: the Data Guard broker is not yet available  Fix  Set dg_broker_start=true

   
   
   
   Issue
    After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
   Warning: ORA-16608: one or more sites have warnings
   
   Fix
    To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.
8. Monitoring the Data Guard Broker Configuration
   
   If we receive any error or warnings we can obtain more information about the same by running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.
   
   
   

   DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport'; DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus'; DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties'; DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps'; DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport'; DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus'; DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties'; DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

   
   
   ;
    

Installing Oracle Enterprise Manager Grid Control 11g Release 1 (11.1.0.1.0) Linux x86

http://st-curriculum.oracle.com/obe/em/emgc1110/installemgc11101linux/emgc11101install.htm

Install and Configure Oracle Enterprise Linux 5

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r1/prod/install/oel5gainst/oel5gainst.htm

Move Database from Linux to Windows

There are two basic methods to achieve this.

Method 1
========
Steps 1:
Check the ENDIAN format of the platforms. Both Windows and linux should have the same format.
In our case we are moving from Linux 32-bit to Windows 32-bit:

col PLATFORM_NAME format a40
select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Microsoft Windows IA (64-bit) Little

Step 2:
Check if the database can be transported. We need to use DBMS_TDB.CHECK_DB, to check if our database can be
transported to the target OS, in the way it is currently.
Need to start the database in READ ONLY mode:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> set serveroutput on
SQL> declare
2 check_db boolean;
3 begin
4 check_db:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

As we see no errors or message, so our database is ready to be transported to Windows 32-bit.

Step 3:
Check if there are any external files associated with the database, they will not be transported using RMAN.
SQL> set serveroutput on
SQL> declare
2 chk boolean;
3 begin
4 chk:=dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.FOR_HR, SYS.BLOB_TEST, SYS.IDR_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,
SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR,
SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.
The above directories exists in the database, we will need to recreate them with new locations once we complete the move.

Step 4:
Now we need to use RMAN convert database command to convert the source database to windows 32-bit.
The source database must be in read only mode.

RMAN> convert database new database 'ORCL'
2> transport script '/home/oracle/transport1.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/home/oracle/product/oradata/test' '/home/oracle/con_dbf';

Starting conversion at source at 23-MAR-09
using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.FOR_HR found in the database
Directory SYS.BLOB_TEST found in the database
Directory SYS.IDR_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.AUDIT_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/home/oracle/product/oradata/test/users01.dbf
converted datafile=/home/oracle/con_dbf/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:21
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/home/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/home/oracle/con_dbf/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/home/oracle/product/oradata/test/system01.dbf
converted datafile=/home/oracle/con_dbf/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/home/oracle/product/oradata/test/example01.dbf
converted datafile=/home/oracle/con_dbf/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/home/oracle/product/oradata/test/undotbs01.dbf
converted datafile=/home/oracle/con_dbf/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Edit init.ora file /home/oracle/product/11g/db/dbs/init_00kal9vi_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/transport1.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-MAR-09

RMAN>

Below are our converted database files:

oracle@oracle:~/con_dbf$ pwd
/home/oracle/con_dbf
oracle@oracle:~/con_dbf$ ls -lrt
total 7267080
-rw-r----- 1 oracle oracle 5608382464 2009-03-23 16:17 users01.dbf
-rw-r----- 1 oracle oracle 882057216 2009-03-23 16:18 sysaux01.dbf
-rw-r----- 1 oracle oracle 744497152 2009-03-23 16:19 system01.dbf
-rw-r----- 1 oracle oracle 104865792 2009-03-23 16:20 example01.dbf
-rw-r----- 1 oracle oracle 94380032 2009-03-23 16:20 undotbs01.dbf
-rw-r--r-- 1 oracle oracle 1710 2009-03-23 17:01 init_00kal9vi_1_0.ora
-rw-r--r-- 1 oracle oracle 2698 2009-03-23 17:01 transport1.sql
Screen shot of the transport1.sql file:

Step 5:
FTP the files to the windows server. I have used filezilla to copy the files to the windows server.

Step 6:
Use oradim to create the service:
C:\Documents and Settings\Administrator>oradim -new -sid orcl -intpwd oracle -s
artmode manual -pfile C:\oracle\11g\product\11.1.0\db_1\dbs\init_orcl.ora
Instance created.

Use the init file (init_00kal9vi_1_0.ora) to startup nomount the db on the windows machine:
Make required changes to the paths in the init ora file.
Screen shot of the init.ora file:

C:\Documents and Settings\Administrator>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 23 17:31:25 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1333620 bytes
Variable Size 310380172 bytes
Database Buffers 104857600 bytes
Redo Buffers 6098944 bytes
SQL>

Step 7:
Create the control file:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\oradata\ORCL\log1.rdo' SIZE 50M,
9 GROUP 2 'C:\oracle\oradata\ORCL\log2.rdo' SIZE 50M,
10 GROUP 3 'C:\oracle\oradata\ORCL\log3.rdo' SIZE 50M
11 DATAFILE
12 'C:\oracle\oradata\ORCL\system01.dbf',
13 'C:\oracle\oradata\ORCL\sysaux01.dbf',
14 'C:\oracle\oradata\ORCL\undotbs01.dbf',
15 'C:\oracle\oradata\ORCL\users01.dbf',
16 'C:\oracle\oradata\ORCL\example01.dbf'
17 CHARACTER SET AL32UTF8;

Control file created.

Step 8:
Open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> select name from v$database;
ERROR:
ORA-03114: not connected to ORACLE

Meanwhile and checked if the redo log files were created, Saw that they were created. Logged in again and:

SQL> select name from v$database;
NAME
---------
ORCL

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

Step 9:
Add the tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\ORCL\temp01.dbf' SIZE
54525952 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Step 10:
Do a sanity check of the database.
Run $ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-03-24 14:43:54
PL/SQL procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2009-03-24 14:43:56
PL/SQL procedure successfully completed.
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
ORCL READ WRITE

Database moved from linux to windows machine!!!!

Method 2
========
There another way to do the datafile conversion, we can convert the datafiles on the destination server also.
The rman script changes a little in that case.
RMAN> convert database on target platform
2> convert script '/home/oracle/convert.sql'
3> transport script '/home/oracle/transport.sql'
4> new database 'orcl'
5> format '/home/oracle/%U_%d';

The datafiles create by the above rman command should be copied to a temp directory on the destination server.
This will create a transport script to create the database instance on the destination server.
It will also create a rman script to convert the datafiles on the destination server.
It will look like:
run {
CONVERT DATAFILE 'C:\oracle\oradata\ORCL\SYSTEM.DBF'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT 'c:\temp\SYSTEM.DBF';
} ;
In the above case the files copied from the linux server were kept in c:\temp and then they would be converted to
C:\oracle\oradata\ORCL location.
After the conversion, run the transport.sql script after making the required changes.
After you have opened the database the rest of the steps are the same.

Flashback Database

Flashback Database

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.



If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.




-- initialization

SQL >alter system set db_recovery_file_dest_size = 20G; SQL >alter system set db_recovery_file_dest = '/home/oracle/FRA';  SQL > alter database archivelog SQL > alter database flashback on;

-- create restore point

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

-- check

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT

check usage

SQL> select file_type, space_used*percent_space_used/100/1024/1024 used, space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable, frau.number_of_files from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

-- mount database and flash back

SQL> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’; SQL> ALTER DATABASE OPEN RESETLOGS;

-- drop flashback

SQL> drop restore point before_upgrade;

Flashback database - the RMAN way

1. create restore point
   
   

   RMAN> create restore point before_upgrade guarantee flashback database; Statement processed

2. list restore point
   
   

   RMAN> list restore point all; SCN              RSP Time  Type       Time      Name ---------------- --------- ---------- --------- ---- 2019135                    GUARANTEED 21-MAR-17 BEFORE_UPGRADE

3. flashback database to restore point
   
   

   RMAN> flashback database to restore point before_upgrade; Starting flashback at 21-MAR-17 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:07 Finished flashback at 21-MAR-17

4. drop restore point
   
   

   RMAN> drop restore point before_upgrade; Statement processed

CRSCTL Commands CheatSheet

You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

 

Relocate services

#srvctl status service -s opera -s slors

#srvctl relocate service -d opera -s slors -i opera3 -t opera1

 

Start Oracle Clusterware

#crsctl start crs

 

Stop Oracle Clusterware

#crsctl stop crs

 

Enable Oracle Clusterware

#crsctl enable crs

 

It enables automatic startup of Clusterware daemons

 

Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

 

Checking Voting disk Location

$crsctl query css votedisk

0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6
Located 3 voting disk(s).

Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

Add Voting disk

#crsctl add css votedisk path

 

Remove Voting disk

#crsctl delete css votedisk path

 

Check CRS Status

$crsctl check crs

Cluster Synchronization Services appears healthy

Cluster Ready Services appears healthy

Event Manager appears healthy

You can also see particular daemon status

$crsctl check cssd

Cluster Synchronization Services appears healthy

$crsctl check crsd

Cluster Ready Services appears healthy

$crsctl check evmd

Event Manager appears healthy

You can also check Clusterware status on both the nodes using

$crsctl check cluster

prod01 ONLINE

prod02 ONLINE

 

Checking Oracle Clusterware Version

To determine software version (binary version of the software on a particular cluster node) use

$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.

There are other options for CRSCTL too which can be seen using

$crsctl

Or

$crsctl help

SQLServer commands

Command	Purpose	Sample Usage
sp_helpdb	This gives you information about all databases in the instance or specific information about one database.	sp_helpdb sp_helpdb databasename
fn_virtualfilestats	This command will show you the number of read and writes to a data file. Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.	SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid) SELECT * FROM :: fn_virtualfilestats(1, 1)
fn_get_sql()	Returns the text of the SQL statement for the specified SQL handle. This is similar to using DBCC INPUTBUFFER, but this command will show you additional information. This can also be embedded in a process easier then using the DBCC commandMSSQLTips additional info	DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 SELECT * FROM ::fn_get_sql(@Handle)
sp_lock	This command shows you all of the locks that the system is currently tracking This is similar to information you can see in Enterprise Manager.	sp_lock sp_lock spid sp_lock spid1, spid2
sp_help	This command gives you information about the objects within a database. The command without an objectname will give you a list of all objects within the database.	sp_help sp_help objectname
sp_who2	Gives you process information similar to what you see when using Enterprise Manager.	sp_who2 sp_who2 spid
sp_helpindex	Gives you information about the indexes on a table as well as the columns used for the index.MSSQLTips additional info	sp_helpindex objectname
sp_spaceused	This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.	sp_spaceused sp_spaceused objectname
DBCC CACHESTATS	Displays information about the objects currently in the buffer cache.	DBCC CACHESTATS
DBCC CHECKDB	This will check the allocation of all pages in the database as well as check for any integrity issues.	DBCC CHECKDB
DBCC CHECKTABLE	This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.	DBCC CHECKTABLE (‘tableName’)
DBCC DBREINDEX	This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.MSSQLTips additional info	DBCC DBREINDEX (tablename, indexname, fillfactor) DBCC DBREINDEX (authors, '', 70) DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
DBCC PROCCACHE	This command will show you information about the procedure cache and how much is being used. Spotlight will also show you this same information.	DBCC PROCCACHE
DBCC MEMORYSTATUS	Displays how the SQL Server buffer cache is divided up, including buffer activity.	DBCC MEMORYSTATUS
DBCC SHOWCONTIG	This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.	DBCC SHOWCONTIG DBCC SHOWCONTIG WITH ALL_INDEXES DBCC SHOWCONTIG tablename
DBCC SHOW_STATISTICS	This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.	DBCC SHOW_STATISTICS (tablename, indexname)
DBCC SHRINKFILE	This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.MSSQLTips additional info	DBCC SHRINKFILE (filename, size in MB) DBCC SHRINKFILE (DataFile, 1000)
DBCC SQLPERF	This command will show you much of the transaction logs are being used.	DBCC SQLPERF(LOGSPACE)
DBCC TRACEON	This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.	DBCC TRACEON(traceflag)
DBCC TRACEOFF	This command turns off a trace flag.

Oracle incremental update using image copy

Explains the RMAN Image copy feature and incrementally updated backups New in Oracle 10G.

• Rman is configured with Recovery Catalog.
• Recovery Catalog Database Name-Orcl
• Database Name test is taken for demo
• Database Test is Running in no archive log mode
• Configured Backup location is 'D:\ORACLE\RMAN\ORA10G'
• During the first and second run of the backup scripts I made some changes in the Database. Also during the second and the third run I made some changes in the Database.

Expanded Image Copying Features: A standard RMAN backup set contains one or more backup pieces, and each of these pieces consists of the data blocks for a particular datafile stored in a special compressed format. When a datafile needs to be restored, therefore, the entire datafile essentially needs to be recreated from the blocks present in the backup piece.

An image copy of a datafile, on the other hand, is much faster to restore because the physical structure of the datafile already exists. Oracle 10g now permit image copies to be created at the database, tablespace, or datafile level through the new RMAN directive BACKUP AS COPY. For example, here is a command script to create image copies for all datafiles in the entire database:
Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN> switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

 

DBMS SCHEDULER

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g. The older job scheduler, DBMS_JOB, is still available, is easier to use in simple cases
Make sure the OracleJobScheduler Service is started.
Create a job
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
   job_name => 'myjob',
   job_type => 'EXECUTABLE',
   job_action => 'd:\oracle\script\vng.bat',
   repeat_interval => 'FREQ=MINUTELY',
   enabled => TRUE );
END;
Remove a job
EXEC DBMS_SCHEDULER.DROP_JOB('myjob');
Change job attributes
Examples:
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');
BEGIN
   DBMS_SCHEDULER.SET_ATTRIBUTE ('WEEKNIGHT_WINDOW',    'repeat_interval', 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');END;/
Enable
/
Disable a job
BEGIN
   DBMS_SCHEDULER.ENABLE('myjob');
END;
BEGIN
   DBMS_SCHEDULER.DISABLE('myjob');
END;
Monitoring jobs
SELECT * FROM dba_scheduler_jobs WHERE job_name = 'myjob';
SELECT * FROM dba_scheduler_job_log WHERE job_name = 'myjob';
Use user_scheduler_jobs and user_scheduler_job_log to only see jobs that belong to your user (current schema).

Oracle image copy

With Oracle 10g R2 we can recover datafile copies like we recover the real datafiles.This gives us the oportunity to recover the entire database without having to restore it from backup first.Which of course saves very valuable time in case of a disaster.

Backup Procedure
Step 1 : Create a image copy of full database
RUN {
ALLOCATE CHANNEL dbkp1 DEVICE TYPE DISK FORMAT 'D:\oracle\rman\ora10G\U%';
BACKUP AS COPY DATABASE; }

Step 2 : Backup incrementally and restore to the image copy
RUN {
# Roll forward any available changes to image copy files
# From the previous set of incremental Level 1 backups
RECOVER COPY OF DATABASE WITH TAG 'cool';
# Create incremental level 1 backup of all datafiles in the database
# For roll-forward application against image copies
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'cool' DATABASE; }

Restore/recovery procedure
In case of a disaster you can now tell rman to just change the locations of the datafiles in the controlfile to the image copies by issuing the following:
RMAN> alter database mount;
RMAN>switch database to copy;

If you want to restore the database
RMAN > restore database;
RMAN > recover database;

Investigating high redo archive generation

If you have ASH, you can use it to find the sessions and queries that waited the most for “log file sync” event. I found that this has some correlation with the worse redo generators.view sourceprint?

1. find the sessions and queries causing most redo and when it happened

select SESSION_ID,user_id,sql_id,round(sample_time,'hh'),count(*) from V$ACTIVE_SESSION_HISTORY where event like 'log file sync' AND SESSION_ID=506 group by SESSION_ID,user_id,sql_id,round(sample_time,'hh') order by count(*) desc

 

1. you can look the the SQL itself by: 

select * from DBA_HIST_SQLTEXT where sql_id='dwbbdanhf7p4a' Another ways from Oracle metalink is by checking block_changes SELECT s.sid, s.serial#, s.username, s.program, i.block_changes FROM v$session s, v$sess_io i WHERE s.sid = i.sid ORDER BY 5 , 1, 2, 3, 4;

you can also check using this script if you are using 10g and above

SELECT dhso.object_name, sum(db_block_changes_delta) FROM dba_hist_seg_stat dhss, dba_hist_seg_stat_obj dhso, dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id AND dhs.instance_number = dhss.instance_number AND dhss.obj# = dhso.obj# AND dhss.dataobj# = dhso.dataobj# AND begin_interval_time BETWEEN to_date(’2012_01_19 18',’YYYY_MM_DD HH24') AND to_date(’2012_01_19 19',’YYYY_MM_DD HH24') GROUP BY dhso.object_name order by sum(db_block_changes_delta) desc SELECT distinct dbms_lob.substr(sql_text,4000,1) FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst WHERE upper(dhst.sql_text) LIKE ‘%INT_UPLOAD_STATUS%’ AND dhss.snap_id=dhs.snap_id AND dhss.instance_Number=dhs.instance_number AND dhss.sql_id = dhst.sql_id and rownum<2;

How to create SQL Server 2005 Clustering with Virtual Server 2005

How to create SQL Server 2005 Clustering with Virtual Server 2005 (Part 1) - Balmukund - Site Home - MSDN Blogs

 

 

VirtualBox Commands

Using VirtualBox you can run multiple Virtual Machines (VMs) on a single server, allowing you to run both RAC nodes on a single machine. Here are some of the useful command to create a virtual SAN storage.

To set a UUID of a hard drive run this
> VBoxManage internalcommands setvdiuuid disk2.vdi

Create the disks and associate them with VirtualBox as virtual media.
> VBoxManage createhd --filename asm1.vdi --size 5120 --format VDI --variant Fixed
Connect them to the VM.
> VBoxManage storageattach ol6-112-rac1 --storagectl "SATA" --port 1 --device 0 --type hdd \
    --medium asm1.vdi --mtype shareable

To manually clone a virtual disk
> VBoxManage clonehd /u01/VirtualBox/ol6-112-rac1/ol6-112-rac1.vdi /u03/VirtualBox/ol6-112-rac2/ol6-112-rac2.vdi

Restoring a 32Bit RMAN backup on 64Bit Operating System

Restoring a 32Bit RMAN backup on 64Bit Operating System

It is possible to restore RMAN backups taken on 32 bit operating system (with 32 bit oracle binaries) to a 64 bit operating system (with 64 bit oracle binaries). But it is preferable to keep the same bit version, but as long as the same operating system platform is maintained the common consensus is this will work.

If you will be running the 64-bit database against the 32-bit binary files or vice versa, after the recovery has ended the database bit version must be converted using utlirp.sql. The utlirp.sql script recompiles existing PL/SQL modules in the format required by the new database. If the version does not include a call to
utlrp, then you must manually run utlrp.sql to recompile invalid objects.

See this note for details on switching between bit sizes:

Note 62290.1 Changing between 32-bit and 64-bit Word Sizes

Example :
======
Things to do on 32 bit server :
+ Take a full backup of database including archivelogs on 32 bit server

RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; backup database plus archivelog; backup current controlfile; }

Things to do on 64 bit server :

+ Make the backups available on 64 bit server
+ Create the pfile or copy the pfile from 32 bit server
+ Startup nomount the instance on the 64 bit server.

+ connect to rman and set the dbid

$ rman target / nocatalog RMAN> set dbid=<dbid from 32 server>; + Restore the controlfile RMAN> restore controlfile from '/disk1/backup/backup_piece_name'; RMAN> alter database mount; RMAN> restore database;

+ Identify the archivelog till you want to do the recovery and check whether you have all the backups.

RMAN> run { set until sequence xxxx; recover database; } RMAN> exit + Migrating to 64 bit SQL> recover database until cancel using backup controlfile; cancel SQL> alter database open resetlogs migrate; SQL> @ ?/rdbms/admin/utlirp.sql Now you must restart the database and explicitly invoke utlrp.sql to recompile invalid objects. SQL> shutdown immediate; SQL> startup; SQL> @ ?/rdbms/admin/utlrp.sql SQL> shutdown immediate; SQL> startup

The database is now opened to use.

Snapshot standby in Oracle 10g

Snapshot standby allow the standby database to open for testing, reporting prupose. You can revert back the transactions to continue in standby mode. You can also use snapshot standby in Oracle 10g.  11g reduces these steps making this a feature they call the “SNAPSHOT DATABASE”.
Steps:1.Set Flash Recovery Area parameters
2.Create a guranteed restore point
3.Activate standby database
4.Perform read/write testing
5.Revert to original standby
1.Set Flash Recovery Area parameters

SQL> alter system set db_recovery_file_dest_size=2G; SQL> alter system set db_recovery_file_dest='D:\oracle\product\10.2.0\fra';

2.Create a guranteed restore pointto revert to when you need to return to the original state.

SQL> create restore point pre_activ_snap guarantee flashback database; SQL> select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point; NAME TIME STORAGE_SIZE GUA -------------------------------------------------- -------------------------------- ------------ --- PRE_ACTIV_SNAP 20-MAR-10 07.59.35.000000000 PM 8192000 YES

3.Activate the standby database

SQL> alter database activate standby database; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL MOUNTED CURRENT SQL> alter database open; SQL> select name, open_mode, controlfile_type from V$database; NAME OPEN_MODE CONTROL -------------------------------------------------- -------------------- ------- ORCL READ WRITE CURRENT

Now the standby database is in READ/WRITE mode after the activation.

PRMY> ALTER SYSTEM ARCHIVE LOG CURRENT; PRMY> ALTER SYSTEM SET DG_BROKER_START=FALSE; PRMY> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

NOW you can use the physical standby safely for testing purposes, it is a fully functional database with data the same as production as of a certain point in time, no new data will be added…remember your Recovery Time Objective.

When testing is over, Revert….back into a physical standby.

STBY> STARTUP MOUNT FORCE; STBY> FLASHBACK DATABASE TO RESTORE POINT HOTFIX1; STBY> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; STBY> STARTUP MOUNT FORCE; STBY> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

(*using REAL-TIME APPLY)

Some useful SQL commands

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; select * from v$flash_recovery_area_usage;

SimpleCheck - Oracle diag checking utilties

This program provide a convenient way to check for the status of Oracle database. You can easily find out which datafiles is OFFLINE, which datafiles need recoveries, what many invalid objects in the database, etc. You do not need to remember complex SQL statements to retrieve those information.

This program like all our previous program, it does not requires to be install. If you are running on windows 2003 server, you have all the necessary DDL to run it.

Features

• Show Hard disk space
• Show Oracle status "mount, open", version
• SHow the location of alert.log, archive log
• Show datafiles which needs recoveries, offline
• Show how many objects are invalid
• Show the amount of tablespace free space.

Updates

• Display a list of tables, last analyzed date, number of rows in a schema
• Display what privileges a schema owner has

Show a current status of database, Hard disk space, alert_log location.

 

List the tablespace space

 

 

List schema information such as tables, privileges, last analyzed date, etc

Download

http://download.cnet.com/Simpleback/3000-2242_4-10908765.html
or
https://app.box.com/s/ezysz0tgkdj4pdas0aat

 

How to Run a Batch File Before Logging on to Your Computer

Windows server can be configured to run a startup script that is a batch file, VB script. The AutoExnt utility is included in the Resource Kit so that this functionality is available.

The AutoExNT service permits an administrator to configure a Windows 2000-based computer to run a custom batch file when first starting the computer. Also, a user or administrator is not required to be logged on at the time this custom batch file runs.

http://support.microsoft.com/kb/q243486/

Installing the AutoExNT Service

1. Using a text editor (such as Notepad), create a batch file named Autoexnt.bat and include the commands you want to run at startup in this file.
2. Copy the Autoexnt.bat file you just created, in addition to the Autoexnt.exe, Servmess.dll, and Instexnt.exe files located in the Resource Kit CD-ROM to the %SystemRoot%\System32 folder on your computer.
3. At a command prompt, type instexnt install, and then press ENTER.

simpleBack - GUI Oracle Rman Backup tool

Update ** VRMAN backup tools
New version of oracle rman backup. Support compressed backup, controlfile backup and archivelog backup.



SimpleBack is a GUI for Oracle Rman backup.

Tradtionally backing up Oracle database is difficult as you either require to know Rman command or purchase 3rd party software. You can use 3rd party software like Veritas or Arcserve bu they are not cheap.

SimpleBack change it all. Just select the option from the radio button and click run. It will generate rman scripts and backup your oracle database. It's that easy! This is a free tool for all to share. Tested on Oracle 10g. (should work on Oracle 9i)

Option

• Backup Full : Backup the whole database
• List Backup : List all the backups (including datbase, archivelog and controlfile)
• Delete Backup : Delete all the obsolete backup , based on the retention policy
• Restore Full : Perform a complete recovery.

FAQ

• Where does the backup location set?
  Default location of Oracle 10g is on flash_recovery_area. You can manually set it using Rman command
• Why can't I do a backup?
  Did you set your database in archivelog mode? Backup are perform using online backup, thus you need to set it to archivelog mode.
• How do I change the retention policy?
  You have to change it using the rman command. By default it is set to 1 day.

download
https://app.box.com/s/1mdt3shdga43cks7gbtl

simpleBack - GUI Oracle Rman Backup tool

SimpleBack is a GUI for Oracle Rman backup. Just select the option from the radio button and click run. It will generate rman scripts and backup your oracle database. It's that easy! This is a free tool for all to share. Tested on Oracle 10g. (should work on Oracle 9i)

Oracle Application Server Troublshooting

Oracle Application Server 10g offers a comprehensive solution for developing, integrating, and deploying your enterprise's applications, portals, and Web services.

Each application server can support up to 70 concurrent users.

Oracle10gappR2AScontrol
This service is used to give access to remote administration of the app server via a web browser interface on port 1810. While this service is running, you can point a browser to the URL http://[appserver]:1810 and you will be asked to log in. The default login and password for that connection are ias_admin‟

Oracle10gappR2ProcessManager
This is the master service that controls all the other services needed by the application server. This service is more of a shell for the other services such as the HTTP_Listener, the OC4J servers, etc. To monitor what this service is actually running, you use the „opmnctl‟ utility from the command line.

OPMNCTL Utility
The opmnctl utility is located on the application server in the \oracle\10gappr2\opmn\bin directory. This utility can be used to view what processes are currently being managed by the Oracle10gappr2ProcessManager service.

Report Queue Manager
Print jobs processed from thin-client sessions can be managed using the Oracle Report Queue Manager utility. From any workstation access the URL - http://[appserver]/reports/rwservlet/showjobs?server=rep[appserver]orcl -

Other user commands

> rwdiag -findall
> rwserver server=rep[servername]

Boot and run Linux from a USB flash memory stick

Boot and run Linux from a USB flash memory stick

Carry a portable Linux version with you on a USB flash pen drive. Easily bring your portable desktop with you wherever you go. I have managed to do so, so can you.

USB Feather Linux Prerequisites:

• 128MB or larger USB pen drive
• Feather Linux
• HP USB tool (for formatting the stick)
• Syslinux

The Feather USB Installation Process:

1. Download the HP USB tool and format your stick with the tool using a FAT file system.
2. Download the Feather Linux zip file.
3. Extract the files from feather-0.7.4-usb.zip to your USB stick.
4. Download and extract syslinux.zip to a directory on your computer.
5. Run the command prompt in windows (start/run/cmd) and CD to the syslinux directory. Once there simply type syslinux.exe F: (F: being the example drive letter of the USB stick in this system)
   Reboot your PC, go into your system BIOS and set your boot order to boot from any selectable USB device. (example USB_ZIP or USB_HDD)
   Save your BIOS settings. On the next reboot, you should have a sucessful launch of Feather Linux from USB