Saturday, July 26, 2014

Beauty of command line - OEM 12c

Why all software should have a command line and automation plugin ? Answer is simple - if you have to repeat number of operation for different targets - scripts can help you save your precious time.

I really enjoy a fact that Oracle added a command line to Oracle Enterprise Manager line, and now you can script lot of boring tasks like adding new administrator to list of users who can access Named Credentials.

To add new admin (przepiorom) it's enough to run the following script
 
add_privs.sh przepiorom

This is first draft of this script (no error handling but it's doing his work)
#!bin/bash

NEW_ADMIN=$1

TPID=$$
PRIV_LIST=`emcli list_named_credentials | awk '{ print $1; }' | grep -v Credential  > /tmp/priv_$TPID`


while read LINE ; do
        echo $LINE
        emcli grant_privs -name="${NEW_ADMIN}" -privilege="FULL_CREDENTIAL;CRED_NAME=${LINE}:CRED_OWNER=sysman"
done > /tmp/priv_$TPID

rm /tmp/priv_$PPID


The next example is an another script which is refreshing a Weblogic domain components.
When a new version of application is deployed a previous one are still registered as a targets and you will see it as down in your OEM.



There is a domain refresh command in OEM menu but if you have more systems going through all of those is not what you want. Using a command line and configuration file you can be done with one line.
emcli login -username=sysman -password=xxxxxxx -force
emcli refresh_wls -input_file=domain_refresh_file:/home/oracle/bin/domain_refresh_file.csv –debug

Content of domain_refresh_file.csv looks like this:

/xxx_soa_mot_domain_soa/soa,R
/xxx_soa_mot_domain_soa/soa,E

There is a one line per target split into two parts.

First part of line is a target name and domain name, ex. /xxx_soa_mot_domain_soa/soa Second part is operation: 
R - remove target which doesn't exist in domain anymore 
E - enable refresh of domain (aka. add monitoring targets)


regards,
Marcin

Wednesday, July 16, 2014

Crossplatform transportable tablespaces - part 1

There is couple of way to do heterogeneous migration of Oracle databases but staring with 12c there is whole set of new RMAN commands to transport data across different platforms.
I was looking for a best method of move tablespaces from HP-UX to Linux and after some research I found this presentation by Martin Bach from Enkitec (you can watch it online here). Martin describing Oracle Perl script (MOS ID 1389592.1) which allows you to convert tablespaces on 11g database including Exadata. At first sigh it looks like a solution for me but to use that script I need to create a new backup of all tablespaces I want to move. That could be an option but I already had a daily updated copy of all data files in FRA. So in next step I started to investigate who script is working and how to convert a backupset from HPUX to Linux and apply incremental backup into files not registered into database. Well solution was easy to predict - use PL/SQL RMAN interface - DBMS_BACKUP_RECOVERY package. 

It was not a first time when I was looking into it and it remembered me a Oracle 8i database with corrupted control file without RMAN catalog which had to be recovered. For those who forget RMAN in Oracle 8i had not a catalog functionality so you had to treat all control files or RMAN catalog with extra care. But using knowledge about files and backup names, PL/SQL and DBMS_BACKUP_RECOVERY it was possible to restore everything manually.

But let's come back to current problem - I was keen to use PL/SQL but before that I decided to check what new Oracle introduced in 12c and nice surprise - now all operations described used by Oracle perl script are possible from RMAN interface. So in next step I decided to do a little test with existing copies of data files from smallest tablespace called 'USERS'.
This small test was successful and now I need to document and describe all steps and this is material for a next post. It's working a little bit better than Oracle script as there is no need to convert backupset - recover can apply and do conversion on the fly

New syntax to learn in investigate more:
RMAN> recover from platform 'HP-UX IA (64-bit)' foreign datafilecopy 'C:\TEMP\USERS01.DBF' from backupset 'c:\temp\inc15_2.bkp';

Starting restore at 15-JUL-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file C:\TEMP\USERS01.DBF
channel ORA_DISK_1: reading from backup piece c:\temp\inc15_2.bkp
channel ORA_DISK_1: foreign piece handle=C:\TEMP\INC15_2.BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-JUL-14


regards
Marcin

Tuesday, July 1, 2014

Don't delete your flashback logs manually

What happen when someone will delete Oracle flashback logs ? You probably don't notice it until you will try to flashback database or bounce instance. 
There is no hope for flashback database without flashback files but there is still way to start your database again without recovery or data loss.

Here is a scenario:
[oracle@dev-6 alert]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> select status from v$instance;

STATUS
------------------------------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

OK open database doesn't work. So what happen when I disable a flashback logging
SQL> alter database flashback off;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Well still doesn't work - but what is a flashback state ?
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY

SQL> select * from v$restore_point;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 33 seq 476 thread 1:
"/u01/app/oracle/fast_recovery_area/DEV/flashback/o1_mf_9nq1wbon_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
OK at least there is some information about root cause - it is looking for missing flashback files. Information about flashback database is keep inside control file so let's try to recreate control file using trace file
SQL> alter database backup controlfile to trace as '/tmp/control.ctl';

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1336176640 bytes
Fixed Size                  2253024 bytes
Variable Size             822087456 bytes
Database Buffers          503316480 bytes
Redo Buffers                8519680 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
Backup of control file has been created in trace file and edited as follow
[oracle@dev-6 tmp]$ vi control.ctl
CREATE CONTROLFILE REUSE DATABASE "DEV" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/dev/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/dev/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/dev/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/dev/system01.dbf',
  '/u01/app/oracle/oradata/dev/sysaux01.dbf',
  '/u01/app/oracle/oradata/dev/undotbs01.dbf',
  '/u01/app/oracle/oradata/dev/users01.dbf',
  '/u01/app/oracle/oradata/dev/USER.dbf',
  '/u01/app/oracle/oradata/dev/DATA.dbf',
  '/u01/app/oracle/oradata/dev/DATA_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE.dbf',
  '/u01/app/oracle/oradata/dev/REFERENCE_INDEX.dbf',
  '/u01/app/oracle/oradata/dev/ARCHIVE.dbf',
  '/u01/app/oracle/oradata/dev/apex01.dbf'
CHARACTER SET AL32UTF8
;

VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/nim_backup/backup/oracle/db/dev/%F''');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');

RECOVER DATABASE;

-- Block change tracking was enabled, so re-enable it now.
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/app/oracle/oradata/dev/bct_01.log' REUSE;

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dev/temp01.dbf' REUSE;
Let's try to create new control files and open database
[oracle@dev-6 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:43:45 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> @control.ctl

Control file created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Media recovery complete.

Database altered.


System altered.


Database altered.


Tablespace altered.


SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------------------------------------------------------------
YES

SQL>
Database has been started and opened - all done. 

I blogged about that cause I have to solve this problem several time when due to space restriction flashback logs has been deleted by other DBA manually instead of disabling and enabling flashback on database. Just keep in mind if you need to release space in FRA don't delete flashback logs manually but turn off flashback on the database 

regards, 
Marcin