Following are the simple steps for migrating the SQL Server database from one server to the other along with all the user logins with their same login passwords,
1. On Source database server identify the location of the MDF and LDF files for the database.
2. Right click the database and go to all task, detach database.
3. After the database is successfully detached, copy the database files MDF and LDF to the destination server and attach the MDF file and automatically the database will be attached to the target.
To transfer logins and passwords between these SQL Server databases, follow the below steps:
1. Run the below script on the source SQL Server, this will create two procedures, sp_help_revlogin & sp_hexadecimal.
2. Execute the sp_help_revlogin procedure from Query Analyzer on the source server. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password.
3. Save the above step's output, and then paste and run it in Query Analyzer on the destination SQL Server like ‘EXEC master..sp_help_revlogin’. This will create/migrate all the logins along with their original SID and password to the target SQL Server database.
Script to create the sp_help_revlogin stored procedure. (This is written by Microsoft consultants so just create these procedures as is without chaning the code)
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
ECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
references:
http://support.microsoft.com/kb/246133
http://support.microsoft.com/kb/918992
Thursday, December 27, 2012
Saturday, November 3, 2012
Non-Dataguard Standby for RAC primary using RMAN
The following are the steps to create a single instance manual standby database for RAC Primary using RMAN without dataguard setup.
on Primary RAC Servers (ed-olraclin1 & ed-olraclin2)
On one of the RAC node take a full RMAN backup of the database on /u01/BACKUP folder, also create the same folder on target dred-olraclin server in same drive and copy the backup set to the DR database server. We will later use this backup set to restore the database to initial build the standby database.
[oracle@ed-olraclin2 ~]$ RMAN TARGET /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> RUN
{
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '/u01/BACKUP/%d_%T_%s.bak';
BACKUP DATABASE;
BACKUP ARCHIVELOG ALL;
}
RMAN> DELETE NOPROMPT OBSOLETE;
Since the autobackup of controlfile is on ASM disk +TESTDB_FRA, used the new feature of 11.2.0.2 that can copy the files from ASM to local disk using the ASMCMD utility CP copy command as given below,
[oracle@ed-olraclin2 ~]$ export ORACLE_HOME=/u01/oracle/11.2.0/grid
[oracle@ed-olraclin2 ~]$ export ORACLE_SID=+asm2
[oracle@racdb2 ~]$ asmcmd
ASMCMD> pwd
+TESTDB_FRA/testdb/AUTOBACKUP/2012_10_26
ASMCMD> ls
s_790046163.3443.790046167
ASMCMD> CP s_790046163.3443.790046167 /u01/BACKUP/s_790046163.3443.790046167
Copy all the files to dred-olraclin standby database server,
The below copies the directory BACKUP to /u01 on dred-olraclin server,
[oracle@ed-olraclin2 ~] scp -r /u01/BACKUP oracle@dred-olraclin:/u01
or use the below to copy all files from BACKUP directory to /u01/BACKUP directory on dr-olraclin server,
[oracle@ed-olraclin2 ~] scp /u01/BACKUP/* oracle@dred-olraclin:/u01/BACKUP
On Target Single Instance server (dred-olraclin)
Install database software on DR server with same version that of Primary. Or you can use the below article to clone the database home,
http://samiora.blogspot.com/2012/10/cloning-oracle-database-home.html
Create the Listener using 'netca' and database service using 'oradim' utility.
Create the initTEST.ora file and update the parameters accordingly.
SQL> STARTUP NOMOUNT;
[oracle@dred-olraclin ~] RMAN TARGET /
RMAN> RESTORE CONTROLFILE FROM '/u01/BACKUP/s_790046163.3443.790046167';
SQL> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
Automate archive log apply from primary to standby site
Create .sh scripts and through crontab, automate the process of archive log backup, robocopy the archive backupset to DR server, catalog the archive backups to DR control files and then apply the archives to the DR database using RMAN.
archives_backup.sh
/u01/oracle/11.2.0/bin/rman target / log archive_backup.log @archive_level_0.sql
archive_level_0.sql
run
{
backup format '/u01/archive_backup/%d_%I_%u_%T_%e.rman_arch' archivelog all;
}
copyarchives.sh
scp /u01/archive_backup/* oracle@dred-olraclin:/u01/archive_backup
rman_recovery.sh
rman target / log recovery.log @rman_dr_recovery.sql
rman_dr_recovery.sql
CATALOG START WITH '/u01/archive_backup/' NOPROMPT;
recover database;
Note that 'CATALOG START WITH' statement catalogs all the archives from /u01/archive_backup folder and will skip those files that were already cataloged earlier.
Through crontab, schedule the above scripts archive_backup.sh and copyarchives.sh on any one node of primary RAC server while schedule rman_recovery.sh on DR standby single instance database server.
Verify archive apply and data
You can verify the standby database data by opening the database in read only mode as follows,
SQL> ALTER DATABASE OPEN READ ONLY;
Here verify the data and then restart the database in mount state so that the recovery process continues.
SQL> STARTUP FORCE MOUNT;
To check the last archives been applied run the below query on primary and standby databases,
SQL> SELECT thread#,max(SEQUENCE#) FROM V$ARCHIVED_LOG group by thread#;
THREAD# MAX(SEQUENCE#)
------------------------------------
1 10701
2 13430
on Primary RAC Servers (ed-olraclin1 & ed-olraclin2)
On one of the RAC node take a full RMAN backup of the database on /u01/BACKUP folder, also create the same folder on target dred-olraclin server in same drive and copy the backup set to the DR database server. We will later use this backup set to restore the database to initial build the standby database.
[oracle@ed-olraclin2 ~]$ RMAN TARGET /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> RUN
{
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '/u01/BACKUP/%d_%T_%s.bak';
BACKUP DATABASE;
BACKUP ARCHIVELOG ALL;
}
RMAN> DELETE NOPROMPT OBSOLETE;
Since the autobackup of controlfile is on ASM disk +TESTDB_FRA, used the new feature of 11.2.0.2 that can copy the files from ASM to local disk using the ASMCMD utility CP copy command as given below,
[oracle@ed-olraclin2 ~]$ export ORACLE_HOME=/u01/oracle/11.2.0/grid
[oracle@ed-olraclin2 ~]$ export ORACLE_SID=+asm2
[oracle@racdb2 ~]$ asmcmd
ASMCMD> pwd
+TESTDB_FRA/testdb/AUTOBACKUP/2012_10_26
ASMCMD> ls
s_790046163.3443.790046167
ASMCMD> CP s_790046163.3443.790046167 /u01/BACKUP/s_790046163.3443.790046167
Copy all the files to dred-olraclin standby database server,
The below copies the directory BACKUP to /u01 on dred-olraclin server,
[oracle@ed-olraclin2 ~] scp -r /u01/BACKUP oracle@dred-olraclin:/u01
or use the below to copy all files from BACKUP directory to /u01/BACKUP directory on dr-olraclin server,
[oracle@ed-olraclin2 ~] scp /u01/BACKUP/* oracle@dred-olraclin:/u01/BACKUP
On Target Single Instance server (dred-olraclin)
Install database software on DR server with same version that of Primary. Or you can use the below article to clone the database home,
http://samiora.blogspot.com/2012/10/cloning-oracle-database-home.html
Create the Listener using 'netca' and database service using 'oradim' utility.
Create the initTEST.ora file and update the parameters accordingly.
SQL> STARTUP NOMOUNT;
[oracle@dred-olraclin ~] RMAN TARGET /
RMAN> RESTORE CONTROLFILE FROM '/u01/BACKUP/s_790046163.3443.790046167';
SQL> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
Automate archive log apply from primary to standby site
Create .sh scripts and through crontab, automate the process of archive log backup, robocopy the archive backupset to DR server, catalog the archive backups to DR control files and then apply the archives to the DR database using RMAN.
archives_backup.sh
/u01/oracle/11.2.0/bin/rman target / log archive_backup.log @archive_level_0.sql
archive_level_0.sql
run
{
backup format '/u01/archive_backup/%d_%I_%u_%T_%e.rman_arch' archivelog all;
}
copyarchives.sh
scp /u01/archive_backup/* oracle@dred-olraclin:/u01/archive_backup
rman_recovery.sh
rman target / log recovery.log @rman_dr_recovery.sql
rman_dr_recovery.sql
CATALOG START WITH '/u01/archive_backup/' NOPROMPT;
recover database;
Note that 'CATALOG START WITH' statement catalogs all the archives from /u01/archive_backup folder and will skip those files that were already cataloged earlier.
Through crontab, schedule the above scripts archive_backup.sh and copyarchives.sh on any one node of primary RAC server while schedule rman_recovery.sh on DR standby single instance database server.
Verify archive apply and data
You can verify the standby database data by opening the database in read only mode as follows,
SQL> ALTER DATABASE OPEN READ ONLY;
Here verify the data and then restart the database in mount state so that the recovery process continues.
SQL> STARTUP FORCE MOUNT;
To check the last archives been applied run the below query on primary and standby databases,
SQL> SELECT thread#,max(SEQUENCE#) FROM V$ARCHIVED_LOG group by thread#;
THREAD# MAX(SEQUENCE#)
------------------------------------
1 10701
2 13430
For any further questions please don't hesitate to email me samiora@gmail.com
Create 11g Clone or Standby Database using RMAN
Following are the steps of Creating a Standby or a Clone Database using Rman Duplicate From Active Database for 11.2.0.2 database.
The following are the steps to create a standby or a clone of oracle database using RMAN,
1) Create a parameter file for the clone or standby
2) Add the following three parameters to the standby or clone database parameter file, even if the paths are identical on both the source and standby or clone servers:
*._compression_compatibility='11.2.0'
*.db_file_name_convert=('target path','auxilary path'....)
*.log_file_name_convert=('target path','auxilary path'....)
3) Copy the passwordfile from the source database to the clone or standby Oracle_Home/database for windows folder or $Oracle_Home/dbs for Linux.
4) Configure the network on the clone or standby server so that you can connect to the database
5) Startup nomount the clone or standby database
6) Add a tnsnames.ora entry to the clone or standby on your source database server
7) Check that you can connect to the clone or standby database as sysdba
8) On the Primary server connect to the source database and clone or standby database (auxiliary) using RMAN
D:\> RMAN TARGET / AUXILIARY sys/password@prodclon
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 2 9:02:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=7119395273)
Connected to auxiliary database: PRODCLON (not mounted)
9) Use the following syntax to duplicate to a clone (use as many channels as CPU’s you may dedicate):
run
{
allocate channel c1 type disk;
allocate auxiliary channel cr1 type disk;
duplicate target database to 'prodclon' from active database nofilenamecheck;
}
10) Use the following syntax to duplicate for standby (use as many channels as CPU’s you may dedicate):
run
{
allocate channel c1 type disk;
allocate auxiliary channel cr1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
If you have any questions regarding the above steps then please don't hesitate to email me samiora@gmail.com
The following are the steps to create a standby or a clone of oracle database using RMAN,
1) Create a parameter file for the clone or standby
2) Add the following three parameters to the standby or clone database parameter file, even if the paths are identical on both the source and standby or clone servers:
*._compression_compatibility='11.2.0'
*.db_file_name_convert=('target path','auxilary path'....)
*.log_file_name_convert=('target path','auxilary path'....)
3) Copy the passwordfile from the source database to the clone or standby Oracle_Home/database for windows folder or $Oracle_Home/dbs for Linux.
4) Configure the network on the clone or standby server so that you can connect to the database
5) Startup nomount the clone or standby database
6) Add a tnsnames.ora entry to the clone or standby on your source database server
7) Check that you can connect to the clone or standby database as sysdba
8) On the Primary server connect to the source database and clone or standby database (auxiliary) using RMAN
D:\> RMAN TARGET / AUXILIARY sys/password@prodclon
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Nov 2 9:02:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=7119395273)
Connected to auxiliary database: PRODCLON (not mounted)
9) Use the following syntax to duplicate to a clone (use as many channels as CPU’s you may dedicate):
run
{
allocate channel c1 type disk;
allocate auxiliary channel cr1 type disk;
duplicate target database to 'prodclon' from active database nofilenamecheck;
}
10) Use the following syntax to duplicate for standby (use as many channels as CPU’s you may dedicate):
run
{
allocate channel c1 type disk;
allocate auxiliary channel cr1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
If you have any questions regarding the above steps then please don't hesitate to email me samiora@gmail.com
Tuesday, October 23, 2012
Cloning Oracle Database Home
Following are the step by step method of cloning an Oracle 11g 11.2.0.2 Database Home from source server(ProdA) to a target server(TestA).
1. Check the patches applied on source ProdA server and the size of the oracle home directory,
[oracle@proda dbhome_1]$ opatch lsinv
[oracle@proda dbhome_1]$ du -h . tail
2. On TestA target server create directory for oracle home, change ownership to oracle user and then on source ProdA server tar oracle home and copy to destination TestA server,
[root@testa ~]# mkdir -p /oracle/app/product/11.2.0/dbhome_1
[root@testa ~]# chown -R oracle:dba /oracle
[oracle@proda ~]$ cd /oracle/app/product/11.2.0/dbhome_1
[oracle@proda dbhome_1]$ tar cvf - .
[oracle@proda dbhome_1]$ scp *.tar testa:/oracle/app/product/11.2.0/dbhome_1
[oracle@testa ~]$ cd /oracle/app/product/11.2.0/dbhome_1
[oracle@proda dbhome_1]$ tar xvf *.tar
3. On TestA target server detach the oracle home (This step is required only if the server itself is a clone/image of the source server or if oracle home was already registered in the inventory)
[oracle@testa ~]$ cd /oracle/app/product/11.2.0/dbhome_1/oui/bin
[oracle@testa bin]$ ./runInstaller -detachHome ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1"
4. Now do the cloning of oracle home,
[oracle@testa bin]$ ./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="dbhome_11gR2" ORACLE_BASE="/oracle/app" OSDBA_GROUP=dba OSOPER_GROUP=dba
Once the above step is successful then execute root.sh script as root,
[root@testa ~]# /oracle/app/product/11.2.0/dbhome_1/root.sh
Finally you can check the cloned home and crosscheck the patchset level from the result seen in step 1, both will be same.
[oracle@testa ~]$ opatch lsinv
5. Post cloning steps is to accordingly update the files in $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin folders.
On Windows:
After doing step 1 and 2 as above then from step 3 do the following,
SET PATH=I:\r11i\ebstestdb\10.2.0\bin;I:\r11i\ebstestdb\10.2.0\jdk\jre\bin;%PATH%
cd I:\r11i\ebstestdb\10.2.0\oui\bin
# setup.exe -clone -silent -ignorePreReq ORACLE_HOME="I:\r11i\ebstestdb\10.2.0" ORACLE_HOME_NAME="dbhome_11107" ORACLE_BASE="I:\r11i"
Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM. Please wait ... Oracle Universal Installer, Version 10.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.
You can find a log of this install session at:
C:\Program Files\Oracle\Inventory\logs\cloneActions2013-02-10_12-15-02PM.log
....................................................................................................Dll to load is C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstal
l2013-02-10_12-15-02PM\Win64RunProcesses.dll
Loading psapi.dll from C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM\psapi.dll
100% Done.
Installation in progress (Sunday, February 10, 2013 12:15:52 PM GST)
.........................................................................
73% Done.
Install successful
Setup in progress (Sunday, February 10, 2013 12:16:07 PM GST)
Problem in getting file info: No such file or directory toLoad is C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM\win64ocxReg.dll
ERROR: Cannot add user to application access ACL.
No mapping between account names and security IDs was done.
(534)
ERROR: Cannot add user to application launch ACL.
No mapping between account names and security IDs was done.
(534)
Setup successful
End of install phases.(Sunday, February 10, 2013 12:16:27 PM GST)
The cloning of dbhome_11107 was successful.
Please check 'C:\Program Files\Oracle\Inventory\logs\cloneActions2013-02-10_12-15-02PM.log' for more details.
Please press Enter to exit...
For more details you can refer to following MOS documents and don't hesitate to contact me as well samiora@gmail.com:
Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) (Doc ID 1154613.1)
Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI (Doc ID 1221705.1)
FAQs on RDBMS Oracle Home Cloning Using OUI (Doc ID 565009.1)
1. Check the patches applied on source ProdA server and the size of the oracle home directory,
[oracle@proda dbhome_1]$ opatch lsinv
[oracle@proda dbhome_1]$ du -h . tail
2. On TestA target server create directory for oracle home, change ownership to oracle user and then on source ProdA server tar oracle home and copy to destination TestA server,
[root@testa ~]# mkdir -p /oracle/app/product/11.2.0/dbhome_1
[root@testa ~]# chown -R oracle:dba /oracle
[oracle@proda ~]$ cd /oracle/app/product/11.2.0/dbhome_1
[oracle@proda dbhome_1]$ tar cvf - .
[oracle@proda dbhome_1]$ scp *.tar testa:/oracle/app/product/11.2.0/dbhome_1
[oracle@testa ~]$ cd /oracle/app/product/11.2.0/dbhome_1
[oracle@proda dbhome_1]$ tar xvf *.tar
3. On TestA target server detach the oracle home (This step is required only if the server itself is a clone/image of the source server or if oracle home was already registered in the inventory)
[oracle@testa ~]$ cd /oracle/app/product/11.2.0/dbhome_1/oui/bin
[oracle@testa bin]$ ./runInstaller -detachHome ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1"
4. Now do the cloning of oracle home,
[oracle@testa bin]$ ./runInstaller -clone -silent -ignorePreReq ORACLE_HOME="/oracle/app/product/11.2.0/dbhome_1" ORACLE_HOME_NAME="dbhome_11gR2" ORACLE_BASE="/oracle/app" OSDBA_GROUP=dba OSOPER_GROUP=dba
Once the above step is successful then execute root.sh script as root,
[root@testa ~]# /oracle/app/product/11.2.0/dbhome_1/root.sh
Finally you can check the cloned home and crosscheck the patchset level from the result seen in step 1, both will be same.
[oracle@testa ~]$ opatch lsinv
5. Post cloning steps is to accordingly update the files in $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin folders.
On Windows:
After doing step 1 and 2 as above then from step 3 do the following,
SET PATH=I:\r11i\ebstestdb\10.2.0\bin;I:\r11i\ebstestdb\10.2.0\jdk\jre\bin;%PATH%
cd I:\r11i\ebstestdb\10.2.0\oui\bin
# setup.exe -clone -silent -ignorePreReq ORACLE_HOME="I:\r11i\ebstestdb\10.2.0" ORACLE_HOME_NAME="dbhome_11107" ORACLE_BASE="I:\r11i"
Starting Oracle Universal Installer...
No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM. Please wait ... Oracle Universal Installer, Version 10.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.
You can find a log of this install session at:
C:\Program Files\Oracle\Inventory\logs\cloneActions2013-02-10_12-15-02PM.log
....................................................................................................Dll to load is C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstal
l2013-02-10_12-15-02PM\Win64RunProcesses.dll
Loading psapi.dll from C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM\psapi.dll
100% Done.
Installation in progress (Sunday, February 10, 2013 12:15:52 PM GST)
.........................................................................
73% Done.
Install successful
Setup in progress (Sunday, February 10, 2013 12:16:07 PM GST)
Problem in getting file info: No such file or directory toLoad is C:\DOCUME~1\sami\LOCALS~1\Temp\1\OraInstall2013-02-10_12-15-02PM\win64ocxReg.dll
ERROR: Cannot add user to application access ACL.
No mapping between account names and security IDs was done.
(534)
ERROR: Cannot add user to application launch ACL.
No mapping between account names and security IDs was done.
(534)
Setup successful
End of install phases.(Sunday, February 10, 2013 12:16:27 PM GST)
The cloning of dbhome_11107 was successful.
Please check 'C:\Program Files\Oracle\Inventory\logs\cloneActions2013-02-10_12-15-02PM.log' for more details.
Please press Enter to exit...
For more details you can refer to following MOS documents and don't hesitate to contact me as well samiora@gmail.com:
Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) (Doc ID 1154613.1)
Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI (Doc ID 1221705.1)
FAQs on RDBMS Oracle Home Cloning Using OUI (Doc ID 565009.1)
Thursday, September 6, 2012
Oralce Linux 6 OS & Oracle 11g DB Installation
Linux 6 installation followed by oracle 11g Release 2 database installation on a machine with 8GB RAM.
1. Boot from the Oracle Linux 6 DVD. At the boot screen select "Install or upgrade an existing system" and press "Enter".
2. The next screen test the media DVD before the installation starts. This will take long time so use the TAB key and when the focus is on SKIP button press ENTER.
3. On the "Welcome" screen, click the "Next" button.
4.Select the appropriate language, then click the "Next" button.
5.Select the relevant keyboard setting, then click the "Next" button.
6.Select the storage option necessary for the installation, then click the "Next" button.
7.Click the "Re-initialize all" button on the disk initialization warning dialog.8.Enter a fully qualified host name, then click the "Configure Network" button.
10.Check the "Connect automatically" checkbox. If you are not using DHCP, click on the "IPv4 Settings" tab, set the method to "Manual", click the "Add" button and enter the appropriate network details. When you are happy with the details, click the "Apply" and "Close" buttons to return to the host name screen, then click the "Forward" button.
12.Enter a root password for the server, then click the "Next" button to proceed.
13.Check the partitioning type you require. If you want to amend the default partitioning layout, check the "Review and modify partitioning layout" option. Click the "Next" button.
14.The installer will list the default partitioning scheme for your size disk. Amend them as required and click the "Next" button, then the "Format" and "Write changes to disk" buttons on the following dialogs.
15.Accept the boot loader settings by clicking the "Next" button.
16.Accept the "Basic Server" installation and check the "Customize now" option, then click the "Next" button.
17.The "Package Group Selection" screen allows you to select the required package groups, and individual packages within the details section. When you've made your selection, click the "Next" button. If you want the server to have a regular gnome desktop you need to include the following package groups from the "Desktops" section:
◦Desktops
◦Desktop
◦Desktop Platform
◦Fonts
◦General Purpose Desktop
◦Graphical Administration Tools
◦X Windows System
18. Now you can wait and relax for the installation to complete.
20.On the "Welcome" screen, click the "Forward" button.
22.Pick the desired ULN Registration option, then click the "Forward" button. In this case we will pick the register later option.
23.Click the "No thanks, I'll connect later." button.
24.Finish the software updates setup by clicking the "Forward" button.
25.Enter the details for system user, then click the "Forward" button.
26.Adjust the Date and Time settings if necessary, and click the "Forward" Button.
28.Next, you are presented with the login screen. Log in by clicking on the user on the login screen.
29.Once logged in, you are ready to use the desktop.
30. Now turn off the Firewall,
>Open the "Firewall Configuration" dialog (System > Administration > Firewall).
>Click the "Close" button on the startup dialog and type in the "root" password if requested.
>On the resulting dialog, click the "Disable" button, followed by the "Apply" button on the toolbar.
>Click the "Yes" button on the confirmation dialog.
>Quit the "Firewall Configuration" dialog.
31. Finally, Disable SELinux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.
Now the following is about Installing Oracle database 11g on the above installed Oracle Linux 6.
Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6
The previous post is about Oracle Linux 6 installation and the following article describes the installation of Oracle Database 11g Release 2 (11.2) (64-bit) on Oracle Linux 6 (64-bit). The article is based on a server installation with a minimum of 2G swap, with both SELinux and the firewall disabled. The following package groups were included for this installation.
Note. Oracle currently only support 11gR2 on Oracle Linux 6 using the Unbreakable Linux Kernel (UEK).
For example.
Create the new groups and users.
Create the directories in which the Oracle software will be installed.
1.Configure Security Updates
2.Select Install Option
3.System Class
4.Node Selection
5.Select Install Type
6.Typical Install Configuration
7.Create Inventory
8.Perform Prerequisite Checks
9.Summary
10.Install Product
11.Database Configuration Assistant
12.Database Configuration Assistant 2
13.Execute Configuration Scripts
14.Finish
1. Boot from the Oracle Linux 6 DVD. At the boot screen select "Install or upgrade an existing system" and press "Enter".
2. The next screen test the media DVD before the installation starts. This will take long time so use the TAB key and when the focus is on SKIP button press ENTER.
9.Highlight the relevant connection and click the "Edit" button.
11.Select the relevant time zone by clicking on your nearest city on the map. Click on the "Next" button to proceed.
◦Desktops
◦Desktop
◦Desktop Platform
◦Fonts
◦General Purpose Desktop
◦Graphical Administration Tools
◦X Windows System
19.Click the "Reboot" button to complete the installation.
21.Accept the license agreement and click the "Forward" button.
27.Accept the default setting on the Kdump screen by clicking the "Finish" button.
>Open the "Firewall Configuration" dialog (System > Administration > Firewall).
>Click the "Close" button on the startup dialog and type in the "root" password if requested.
>On the resulting dialog, click the "Disable" button, followed by the "Apply" button on the toolbar.
>Click the "Yes" button on the confirmation dialog.
>Quit the "Firewall Configuration" dialog.
Once the change is complete, restart the server.SELINUX=disabled
Now the following is about Installing Oracle database 11g on the above installed Oracle Linux 6.
Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6
The previous post is about Oracle Linux 6 installation and the following article describes the installation of Oracle Database 11g Release 2 (11.2) (64-bit) on Oracle Linux 6 (64-bit). The article is based on a server installation with a minimum of 2G swap, with both SELinux and the firewall disabled. The following package groups were included for this installation.
- Base System > Base
- Base System > Client management tools
- Base System > Compatibility libraries
- Base System > Hardware monitoring utilities
- Base System > Large Systems Performance
- Base System > Network file system client
- Base System > Performance Tools
- Base System > Perl Support
- Servers > Server Platform
- Servers > System administration tools
- Desktops > Desktop
- Desktops > Desktop Platform
- Desktops > Fonts
- Desktops > General Purpose Desktop
- Desktops > Graphical Administration Tools
- Desktops > Input Methods
- Desktops > X Window System
- Development > Additional Development
- Development > Development Tools
- Applications > Internet Browser
Note. Oracle currently only support 11gR2 on Oracle Linux 6 using the Unbreakable Linux Kernel (UEK).
- Download Software
- Unpack Files
- Hosts File
- Automatic Setup
- Manual Setup
- Additional Setup
- Installation
- Post Installation
Download Software
Download the Oracle software from OTN or MOS-Metalink Oracle Support depending on your support status.- OTN: Oracle Database 11g Release 2 (11.2.0.1) Software (64-bit) - For unsupported installation.
- MOS: Oracle Database 11g Release 2 (11.2.0.3) Software (64-bit) - For supported installation.
Unpack Files
Unzip the files.You should now have a single directory called "database" containing installation files.# 11.2.0.1 unzip linux.x64_11gR2_database_1of2.zip unzip linux.x64_11gR2_database_2of2.zip #11.2.0.2 unzip p10098816_112020_Linux-x86-64_1of7.zip unzip p10098816_112020_Linux-x86-64_2of7.zip #11.2.0.3 unzip p10404530_112030_Linux-x86-64_1of7.zip unzip p10404530_112030_Linux-x86-64_2of7.zip
Hosts File
The "/etc/hosts" file must contain a fully qualified name for the server.For example.
127.0.0.1 localhost.localdomain localhost 192.168.0.181 ol6-112.localdomain ol6-112
Automatic Setup
If you plan to use the "oracle-rdbms-server-11gR2-preinstall" package to perform all your prerequisite setup, follow the instructions at http://public-yum.oracle.com to setup the yum repository for OL, then perform the following command.All necessary prerequisites will be performed automatically.# yum install oracle-rdbms-server-11gR2-preinstall
Manual Setup
Oracle recommend the following minimum parameter settings.The current values can be tested using the following command.fs.suid_dumpable = 1 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586
Add or amend the following lines in the "/etc/sysctl.conf" file./sbin/sysctl -a | grep
Run the following command to change the current kernel parameters.fs.suid_dumpable = 1 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 # semaphores: semmsl, semmns, semopm, semmni kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default=4194304 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=1048586
Add the following lines to the "/etc/security/limits.conf" file./sbin/sysctl -p
Install the following packages if they are not already present.oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 4096 oracle hard nofile 65536 oracle soft stack 10240
Note. This will install all the necessary 32-bit packages for 11.2.0.1. From 11.2.0.2 onwards many of these are unnecessary, but having them present does not cause a problem.# From Oracle Linux 6 DVD cd /media/cdrom/Server/Packages rpm -Uvh binutils-2*x86_64* rpm -Uvh glibc-2*x86_64* nss-softokn-freebl-3*x86_64* rpm -Uvh glibc-2*i686* nss-softokn-freebl-3*i686* rpm -Uvh compat-libstdc++-33*x86_64* rpm -Uvh glibc-common-2*x86_64* rpm -Uvh glibc-devel-2*x86_64* rpm -Uvh glibc-devel-2*i686* rpm -Uvh glibc-headers-2*x86_64* rpm -Uvh elfutils-libelf-0*x86_64* rpm -Uvh elfutils-libelf-devel-0*x86_64* rpm -Uvh gcc-4*x86_64* rpm -Uvh gcc-c++-4*x86_64* rpm -Uvh ksh-*x86_64* rpm -Uvh libaio-0*x86_64* rpm -Uvh libaio-devel-0*x86_64* rpm -Uvh libaio-0*i686* rpm -Uvh libaio-devel-0*i686* rpm -Uvh libgcc-4*x86_64* rpm -Uvh libgcc-4*i686* rpm -Uvh libstdc++-4*x86_64* rpm -Uvh libstdc++-4*i686* rpm -Uvh libstdc++-devel-4*x86_64* rpm -Uvh make-3.81*x86_64* rpm -Uvh numactl-devel-2*x86_64* rpm -Uvh sysstat-9*x86_64* rpm -Uvh compat-libstdc++-33*i686* rpm -Uvh compat-libcap* cd / eject
Create the new groups and users.
Note. We are not going to use the "asm" groups, since this installation will not use ASM.groupadd -g 501 oinstall groupadd -g 502 dba groupadd -g 503 oper groupadd -g 504 asmadmin groupadd -g 506 asmdba groupadd -g 505 asmoper useradd -u 502 -g oinstall -G dba,asmdba,oper oracle passwd oracle
Additional Setup
Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.Once the change is complete, restart the server.SELINUX=disabled
Create the directories in which the Oracle software will be installed.
Login as root and issue the following command.mkdir -p /u01/app/oracle/product/11.2.0/db_1 chown -R oracle:oinstall /u01 chmod -R 775 /u01
Login as the oracle user and add the following lines at the end of the ".bash_profile" file.xhost +
# Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=ol6-112.localdomain; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=DB11G; export ORACLE_SID PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
Installation
Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.DISPLAY=:0.0; export DISPLAY
Proceed with the installation of your choice. The prerequisites checks will fail for the following version-dependent reasons:./runInstaller
- 11.2.0.1: The installer shows multiple "missing package" failures because it does not recognize several of the newer version packages that were installed. These "missing package" failures can be ignored as the packages are present. The failure for the "pdksh" package can be ignored because we installed the "ksh" package in its place.
- 11.2.0.2: The installer should only show a single "missing package" failure for the "pdksh" package. It can be ignored because we installed the "ksh" package in its place.
- 11.2.0.3: The installer shows no failures and continues normally.
1.Configure Security Updates
2.Select Install Option
3.System Class
4.Node Selection
5.Select Install Type
6.Typical Install Configuration
7.Create Inventory
8.Perform Prerequisite Checks
9.Summary
10.Install Product
11.Database Configuration Assistant
12.Database Configuration Assistant 2
13.Execute Configuration Scripts
14.Finish
Post Installation
Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
If you have any questions then don't hesitate to email me. samiappsdba@gmail.comDB11G:/u01/app/oracle/product/11.2.0/db_1:Y
Wednesday, August 1, 2012
Migrating autoconfig to database tier
If you don't have appsutil on the database tier then follow the below steps to configure appsutil to migrate autoconfig to database tier.
1. Copy AutoConfig to the RDBMS ORACLE_HOME as the APPLMGR user:
- source the environment file on application tier:
. /APPS_MI/visappl/APPSORA.env
- On the application tier, Create appsutil.zip file
[applmgr@apps ~]$ perl $AD_TOP/bin/admkappsutil.pl
- Now Copy the appsutil.zip to the Database Tier (as the ORACLE user) and unzip it:
[applmgr@dbhome ~] cd /APPS_DB_R12/db/tech_st/10.2.0
[applmgr@dbhome ~] cd unzip -o appsutil.zip
2. Now generate Database Context File
cd $ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=APPS
If the listener.ora is not created in /APPS_DB_R12/db/tech_st/10.2.0/network/admin/VIS_db this will generates an error which can be ignored.
The above adbldxml.pl script will generate the following context file:
/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
3. This is an optional step but you can review and Resolve Manual Configuration Changes by following script,
cd $ORACLE_HOME/appsutil/bin
./adchkcfg.sh contextfile=/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
The following report will be generated: /APPS_DB_R12/db/tech_st/10.2.0/appsutil/out/VIS_db/05072108/cfgcheck.html
This report provide information about all file changes, profile option changes and other important database updates that will be done during the next normal execution of AutoConfig
4. Generate and Apply AutoConfig Configuration files
cd $ORACLE_HOME/appsutil/bin
./adconfig.sh contextfile=/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
This command creates /APPS_DB_R12/db/tech_st/10.2.0/appsutil/scripts/VIS_db/adautocfg.sh and run it.
/APPS_DB_R12/db/tech_st/10.2.0/network/admin/VIS_db/listener.ora will be created as well.
For any further queries please don't hesitate to contact me samiora@gmail.com
1. Copy AutoConfig to the RDBMS ORACLE_HOME as the APPLMGR user:
- source the environment file on application tier:
. /APPS_MI/visappl/APPSORA.env
- On the application tier, Create appsutil.zip file
[applmgr@apps ~]$ perl $AD_TOP/bin/admkappsutil.pl
- Now Copy the appsutil.zip to the Database Tier (as the ORACLE user) and unzip it:
[applmgr@dbhome ~] cd /APPS_DB_R12/db/tech_st/10.2.0
[applmgr@dbhome ~] cd unzip -o appsutil.zip
2. Now generate Database Context File
cd $ORACLE_HOME/appsutil/bin
perl adbldxml.pl tier=db appsuser=APPS
If the listener.ora is not created in /APPS_DB_R12/db/tech_st/10.2.0/network/admin/VIS_db this will generates an error which can be ignored.
The above adbldxml.pl script will generate the following context file:
/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
3. This is an optional step but you can review and Resolve Manual Configuration Changes by following script,
cd $ORACLE_HOME/appsutil/bin
./adchkcfg.sh contextfile=/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
The following report will be generated: /APPS_DB_R12/db/tech_st/10.2.0/appsutil/out/VIS_db/05072108/cfgcheck.html
This report provide information about all file changes, profile option changes and other important database updates that will be done during the next normal execution of AutoConfig
4. Generate and Apply AutoConfig Configuration files
cd $ORACLE_HOME/appsutil/bin
./adconfig.sh contextfile=/APPS_DB_R12/db/tech_st/10.2.0/appsutil/VIS_db.xml
This command creates /APPS_DB_R12/db/tech_st/10.2.0/appsutil/scripts/VIS_db/adautocfg.sh and run it.
/APPS_DB_R12/db/tech_st/10.2.0/network/admin/VIS_db/listener.ora will be created as well.
For any further queries please don't hesitate to contact me samiora@gmail.com
Friday, June 1, 2012
Manually Clone Database using RMAN
rman target /
backup database;
2) Move the following files to the NODE 2:
- backup pieces
- controlfile backup piece
- the parameter file i.e init.ora file to the default location i.e $ORACLE_HOME/dbs
3) Modify PFILE accordingly, invoke Rman on the node 2 after setting the Oracle environment variables and start the database in nomount mode:
export ORACLE_HOME, ORACLE_SID, PATH
$rman target /
RMAN> startup nomount;
4) Restore the controlfile from the backup piece.
RMAN> restore controlfile from ‘/node2/database/backup/c-3932056136-20070213-02′;
5) Mount the database
RMAN > alter database mount
6) Now catalog the backup pieces that was moved from NODE 1
RMAN> catalog backuppiece ‘/node2/database/backup/o1_mf_annn_TAG20070213T002925_2×21m6ty_.bkp’;
7) Now restore the datafiles
RMAN> run
{
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}
also refer Note:419137.1
Subscribe to:
Posts (Atom)