How to recreate EM repository for Oracle 10g R2 on Windows

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Windows 2003 server SE SP2, Oracle 10g standard edition 10.2.0.4.0, Windows is using domain account to authenticate.
Problem: Due to some reason, we cannot login as sys/system/sysman/dbsnmp, we can only use oracle login Windows server and use os authentication(sqlpuls / as sysdba) to check database. Also, Enterprise Manager is not working. We need to drop/recreate EM repository to get EM up so that we can monitor database through EM

Objective: change password for sys/system/sysman/dbsnmp, recreate EM repository for getting EM working, use the latest version of sqldeveloper(3.0) so that we can use menu 'view-DBA' function to manage database also.

Question: For sql developer 3.0, why cannot we use os authentication?
I have successfully tried to use sql developer 2.0 for os authentication, please refer to
Refer to http://linuxtechres.blogspot.com/2011/07/how-to-use-sqldeveloper-with-os.html, but 3.0 doesn't work this way. So we cannot use DBA view function which is very good feature in version 3.0.


Steps:

1. change sys/system/sysman/dbsnmp password for database first.
login Windows, use os authentication
sqlplus / as sysdba
sql> alter user sys identified by "yourpassword";
sql> alter user system identified by "yourpassword";
sql> alter user sysman identified by "yourpassword";
sql> alter user dbsnmp identified by "yourpassword";

make user all above users are open, not expiried and locked.
Your might want to refer to another article at http://linuxtechres.blogspot.com/2010/09/jephe-wu-httplinuxtechres.html
and How to Change DBSNMP Password in Database 10g and 11g Monitored by DB Control [ID 259387.1] in Oracle support.

2. drop/recreate the existing EM repository.

Make sure c:\windows\system32\drivers\etc\hosts contains a line like this even though you can ping FQDN with correct ip without this following line.
1.2.3.4   FQDN  hostname

otherwise, you will encouter error message below when cretae EM repository:
May 17, 2006 7:34:39 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfig
Exception: Error instantiating EM configuration files at oracle.sysman.emcp.EMAgentConfig.updateAgentConfigFiles(EMAgentConfig.java:2560)
 at oracle.sysman.emcp.EMAgentConfig.performConfiguration(EMAgentConfig.java:1166)
 at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:207)
 at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:185)
 at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:146)
 at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:479)
 at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1123)
 at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:463)
 at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:412)

Refer to EMCA Fails With Error "SEVERE: Error instantiating EM configuration files" [ID 370207.1] in Oracle support.

Sometimes, under Linux, without the correct entry in /etc/hosts with the correct ip address, you might encounter error for creating EM repository below:
SEVERE: Failed to allocate port(s) in the specified range(s) for the following process(es): JMS [5540-5559],RMI [5520-5539],Database Control [5500-5519],EM Agent [3938] | [1830-1849]

If you encounter error message like this:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one upper, one lower and
one special character

that's due to internal BUG 4195090. check REPMANAGER AND EMCA FAILS WITH PASSWORD VERIFICATIONFUNCTION [ID 779098.1] from Oracle support
=> change 

ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null; 

then put back later after done emca. 

c:> set ORACLE_SID=xxxx  (important, otherwise, you cannot secure dbconsole after creating repository)
c:> emca -deconfig dbcontrol db -repos drop
type in sid, listener port, passwords etc

c:> emca -config dbcontrol db -repos create
type in sid, listener port, passwords etc.
You will get successfully created EM repository message, and secured dbcontrol successfully, but failed to start up dbcontrol, wait for 15 mins.
it will fail and give the following errors:

Refer to the log file at D:\oracle\10.2.0\db_1\cfgtoollogs\emca\xxxx\emca_2011-08-26_10-32-25-AM.log for more details.
26/08/2011 10:49:38 oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error starting Database Control
    at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:646)
    at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:224)
    at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:193)
    at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:184)
    at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)
    at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)
    at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)
    at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)

According to Enterprise Manager Database Control Configuration - Recovering From Errors Due to CA Expiry on Oracle Database 10.2.0.4 or 10.2.0.5 [Video] [ID 1222603.1]
and ATTENTION - Enterprise Manager Database Control 10.2.0.4 Or 10.2.0.5 - Patch Required from 31-Dec-2010 onwards [ID 1217493.1]

According to above docs, you can either apply Patch 8350262, or disable https for EM as follows:

You can use
emca -config dbcontrol db -repos recreate
 to drop and create

3. disable https for EM (Starting from 10.2.0.4 onwards DBConsole uses HTTPS in its URL by Default [ID 747770.1] in Oracle support)

c:> emctl unsecure dbconsole
c:> emctl start dbconsole
access EM at http://FDQN:5501/em/console/

4. use Sqldeveloper 3.0 and EM to monitor/manage database.

-->

How to setup MS SQL server logshipping and failover

Jephe Wu - http://linuxtechres.blogspot.com

Objective: setup a logshipping between primary and secondary. and failover it.
Environment: sql server 2005/2008.


Steps:


Setup requirments:
1. Installing both primary and standby SQL servers, configure primary database first
2. setup shared folder on primary or another network share(third server)

You must have a shared folder to copy the transaction log backups to.
The SQL Server Agent service account of the primary server must have read/write access either to the shared folder or to the local NTFS folder. The SQL Server Agent account of the standby server must have read access to the shared folder for applying transaction log
3. primary server must be in full or bulk-loged mode for generating transaction logs

You can configure log shipping with SQL Server Agent services stopped, but the process does not run until the agent is started.

Note: refer to step-by-step configuring logshipping at
http://www.mssqltips.com/tipprint.asp?tip=2301

How to failover - http://msdn.microsoft.com/en-us/library/ms191233.aspx
1. copy any uncopied backup files from backup share to file copy folder on each secondary server

You might want to generate tail log backup from primary if primary server is still online and running.

a. if primary db is online and you plan to perform transaction log apply for secondary server, before starting that, consider to  backup tail of the primary server transaction log with command below, so that you will have the latest data from primary db:

BACKUP LOG <dbname> TO <backup_device> WITH NORECOVERY;
go

note: This leaves the primary database in the restoring state, and eventually you will be able to roll this database forward by applying transaction log backups from the original secondary server.

b. if db is offline and cannot start.
As no transaction can occur this time, using 'with norecovery' is optional, if db is damaged, use 'with continue_after_error'

as follows:

backup log <dbname> to <backup_device> with continue_after_error

Use CONTINUE_AFTER_ERROR only if you are backing up the tail log for a damaged database.

c. if db is damaged and inaccessible, however, transaction log is undamaged and accessbible:

backup log <dbname> to <backup_device> with no_truncate;
go

note:
a.no_truncate=copy_only+continue_after_error
b. How to: Back Up the Tail of the Transaction Log (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/dd297499
How to: Back Up the Transaction Log When the Database Is Damaged (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms189606
c. For details about tail-log backup, see http://msdn.microsoft.com/en-us/library/ms179314

2. apply any unapplied transaction log backups in sequence to each secondary database
How to: Recover a Database from a Backup Without Restoring Data (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms176039.aspx
How to: Restore a Transaction Log Backup (SQL Server Management Studio) - http://msdn.microsoft.com/en-us/library/ms177446.aspx

If you are  restoring the last log backup, you can do the following:
restore log <dbname> from <backup_device> with recovery;  bring database online from Read-only mode.
go

otherwise, do
restore log <dbname> from <backup_device> with norecovery;
restore database <dbname> with recovery; bring database online from Read-only mode.
go

note:
a.always explicitly specify either with norecovery or with recovery.
b. <backup_device> is the name of the device that contains the log backup being restored.

How to recover a database from a backup without restoring data - http://msdn.microsoft.com/en-us/library/ms176039.aspx
restore database <dbname> with recovery

3. after you have recovered secondary db, you can reconfigure it to act as a primary database for other secondary db, and you can redirect client to this server instance.

Before this, you might need to transfer login ids from primary to seconday server:
How to set up and perform a log shipping role change (Transact-SQL) - http://msdn.microsoft.com/en-us/library/aa215392(v=sql.80).aspx



PS: how to restore ms sql server from the previous full backup plus transaction logs, other than the most recent backup:

-----------
# make if offline then online first before restore
use master
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE test SET ONLINE  WITH ROLLBACK IMMEDIATE

# restore full backup and all transaction logs, after x.bak, actually, we have done another full bakcup x2.bak, but we lost that, let's skip it.

restore database test from disk='i:\download\x.bak' with norecovery
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162700_8205296.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162704_9113476.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162737_0047650.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162801_1445920.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162830_7010021.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162900_8320271.trn' WITH NORECOVERY
RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_162950_0283644.trn' WITH NORECOVERY

RESTORE LOG test FROM DISK = 'i:\download\test_backup_2013_06_04_163001_1705924.trn' WITH RECOVERY
---------------------

References and Commands:
a. alter database <dbname> set recovery full
b. how to restore database with norecovery
restore database <dbname> from <device_name> with norecovery;
go
c. how to apply transaction log backup (T-SQL) - http://msdn.microsoft.com/en-us/library/ms187607.aspx
d. High Availability Solutions Overview -  http://msdn.microsoft.com/en-us/library/ms190202%28v=SQL.105%29.aspx 
e.  SQL 2005 logshipping tutorial video - http://www.dbsnaps.com/category/sql-server/high-availability-sql-server/