Unlock/restore users/passwords after database migration based on template with data

Unlock/restore users/passwords after database migration

In one of my experiments with migration using database templates with data I ended in situation that all users in restored database are locked and password are expired. This is normal because of security but not very useful after migration J. After spend many hours on searching for the solution I finally got one.

Here is the script:

Run SQL to unlock all user accounts (you can choose exception like SYS, SYSTEM…accounts which do not need to be unlocked):

SELECT 'ALTER USER '|| name ||' ACCOUNT UNLOCK;' FROM sys.user$ WHERE name IN (SELECT USERNAME FROM DBA_USERS where USERNAME NOT IN ('OUTLN',

'DIP',

'ORACLE_OCM',

'WMSYS',

'EXFSYS',

'XDB',

'ANONYMOUS',

'APPQOSSYS',

'XS$NULL',

'SYS',

'SYSTEM',

'DBSNMP',

'SYSMAN') AND account_status != 'OPEN' )

As a result, I got three sql stamenets for three users in my test:

ALTER USER TESTONE ACCOUNT UNLOCK;

ALTER USER HR ACCOUNT UNLOCK;

ALTER USER TESTTWO ACCOUNT UNLOCK;

Run those scripts and accounts should be unlocked.

Then, to get SQL which “restore” the passwords run this script:

SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| password ||''';' FROM sys.user$ WHERE name IN (SELECT USERNAME FROM DBA_USERS where USERNAME NOT IN ('OUTLN',

'DIP',

'ORACLE_OCM',

'WMSYS',

'EXFSYS',

'XDB',

'ANONYMOUS',

'APPQOSSYS',

'XS$NULL',

'SYS',

'SYSTEM',

'DBSNMP',

'SYSMAN') AND account_status != 'OPEN' )

…and I got:

ALTER USER TESTONE IDENTIFIED BY VALUES 'E851CC2EB21F4DB7';

ALTER USER TESTTWO IDENTIFIED BY VALUES '6AD44FBD54FC6A0F';

ALTER USER HR IDENTIFIED BY VALUES '4C6D73C3E8B0F0DA';

I executed those sql statements and users got their old passwords back.

How did I migrate Oracle database from one server to another one?

How did I migrate oracle database from one server to another one?

I had many assignments about migration of Oracle databases. The last I got was to migrate databases version 11.2.0.4 from one Windows server to another Windows server. Well, I was thinking about two approaches:

1.      Involve template-migration with data files

2.      Use the good old RMAN.

If you thinking about the data location structure – good point there! To make less trouble about migration I decided to setup absolute the same data structure on the new server like on the old one. It means that the new server must to have C disc like the old one, D disc like the old one, E disc like the old one…and so on.

Although will be easier with Templates it have one very bad consequence: It locks all users and set theirs password expiredL.

I have tried with it and yes, all users’ accounts were locked after with expired password. HOWEVER, i had one solution to it:

Get their hash password, unlock user and renew password with the one from hash. Yes, it worked. You can find what I did in other post 

(Unlock/restore users/passwords after database migration)

This process is a little bit tricky and I decided to use RMAN and try option number two.

Preparation (old server):

·         Make a template of database (structure only) based on database you want to copy.

·         Write down info about NLS parameters

·         Write down DBID (SELECT DBID FROM V$DATABASE;)

·         Write down info about TEMP files:

·         select file_name from dba_temp_files;

·         Write down sizes of those files, location, name and tablespace name which files belong to.

·         Write down location where redologs files are placed (SELECT * FROM V$LOGFILE;)

Preparation (new server):

·         Install database software on the new server (same version, same location!) using template you have created before.

·         Install database with the same name as old database and pay attention on:

·         Install database with NLS parameters the same as on old server,

·         Location of FRA and DIAG folders – they have to be created manually if the installation process will not install them (same location as on the old server!).

·         Pay attention on firewall (the new server should not be visible on the production network yet)

OLD SERVER: 

Start CMD window and run:

SET ORACLE_SID=TEST    (TEST is the name of the database)

RMAN NOCATALOG

CONNECT TARGET /

Configure autobackup of controlfile and location where will be backup placed:

 RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\TEST\BACKUP_TEST_MIG\%U';

 RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'K:\TEST\BACKUP_TEST_MIG\%F';

Now, action:

Usually I made some control data I one test table in database which will I use against users which will complain that they lost some data (this is just not possible).

In one table of test-schema I made text like “Test migration “plus date/time”.

RMAN>shutdown immediate;

RMAN>startup mount;

RMAN>BACKUP AS BACKUPSET full database;

RMAN>alter database open; this is just to check that original database is OK again (this is not reuired to do it but I’m very careful about this) and stop database again!

RMAN>shutdown immediate;

·         Stop listener on original server

·         Copy the backup to the Server B (controlfile and backupset file)

·         Copy pfile, passwordfile, spfile from $ORACLE_HOME/database to the new server on the same location (overwrite!)

New server:

CMD

SET ORACLE_SID=TEST

·         check changes on local TNSNAME.ORA so it can accept connections

Start RMAN:

rman nocatalog

connect target /

RMAN> shutdown abort;

RMAN> startup nomount;

Restore control files

RMAN> SET DBID 1234567890; (DBID you got earlier from the old database)

RMAN> restore controlfile from 'path/backup_controlfile'

Delete redolog files

Outside of RMAN with WindowsExplorer delete all redo logs files (from location you wrote earlier) to get rid of ORA-01186 fail about file-verification.

Restore datafiles

RMAN>alter database mount;

 RMAN>CROSSCHECK ARCHIVELOG ALL;

 RMAN>CROSSCHECK BACKUP;

 RMAN>CATALOG START WITH 'full_path_to_folder_where_you _just_copied_backup' NOPROMPT;

    RMAN>restore database;

 RMAN>recover database; (this is not necessary if database is in ARCHIVELOG mode and will fail if database you are copying is in archivelog mode but can’t do nothing wrong anyway)

  

 RMAN>ALTER DATABASE OPEN RESETLOGS;

RMAN>exit

Regenerate TEMP files

Now, new database is not ready yet as we need to regenerate temp files (you wrote earlier info about this):

Suppose we had TEMP and TEMP1 temporary tablespaces with files TEMP01.DBF and TEMP02.DBF sizes 100Mb and 200Mb. Location of TEMP01.DBF was T:\TEMP\TEMP01.DBF and same location for the other one.

First must we drop both files:

CMD

SET ORCALE_SID=TEST

sqlplus “/as sysdba”

  

alter database tempfile ‘T:\TEMP\TEMP01.DBF' drop;

alter database tempfile ‘T:\TEMP\TEMP02.DBF' drop;

and add the new ones:

alter tablespace TEMP add tempfile ‘T:\TEMP\TEMP01.DBF'  SIZE 100M REUSE;

alter tablespace TEMP1 add tempfile ‘T:\TEMP\TEMP02.DBF'  SIZE 200M REUSE;

You can check for invalid objects (just in case J )

And that’s it!

I’ve used this method for seven databases I migrated lately and it worked perfect. Hope this can be helpful to someone J.

How to enable ssh root login in Solaris 10

How to enable ssh root login in Solaris 10

From the terminal window and with the VI-editor change the following file /etc/ssh/sshd_config and replace the:

 "PermitRootLogin no"

to

"PermitRootLogin yes"

and save the file.

After we need to restart the ssh service:

svcadm restart svc:/network/ssh:default

…and that's it!

 

Install SUDO on Solaris and setup for the EM 12c agent deployment

Install SUDO on Solaris and setup for the EM 12c agent deployment

When you try to add a new host to be monitored in Oracle EM Cloud Control the very first thing you are getting is the error about oracle is not in sudoers file. Well, in order to solve this we must install SUDO on Solaris and add the oracle user to sudoers file on the target sever.

First, install SUDO:

SUDO demands some libraries to be installer before: 

libiconv-1.9.2-sol10-x86-local.gz, 

libgcc-3.4.6-sol10-x86-local.gz, 

and libintl-3.4.0-sol10-x86-local.gz

As root, download those files and install them with pkgadd –d command.

Last, but not least, download and install SUDO:

sudo-1.8.3p2-sol10-x86-local.gz

Usually, SUDO is located under /usr/local/… so we need to link the important files to /usr/… :

ln -s /usr/local/bin/sudo /bin/sudo

ln -s /usr/local/etc/sudoers /etc/sudoers

ln -s /usr/local/bin/sudoedit /bin/sudoedit

Now, edit /etc/sudoers file and do the following changes (marked as red):

##

## User privilege specification

##

root ALL=(ALL) ALL

oracle ALL=(ALL) ALL

Defaults visiblepw

Explanation for the last entry is that sudo cannot run if the user must enter a password but it is not possible to disable echo on the terminal. When visiblepw set, sudo will prompt for a password even when it would be visible on the screen. This makes it possible to run things like “ssh somehost sudo ls” since by default, ssh(1) does not allocate a tty when running a command.  This is the last demand from Agent deployment process.

After this, you may successfully deploy EM Cloud Control 12c agent to the target Solaris host: