Our databases recently went through an audit and we were asked to supply a list of all database users and the date when the last password change occurred. My first reaction was that Oracle does not keep that information, but a little research proofed that I was wrong. The information is kept in sys.user$.ptime
The following query will show when last a password was changed:
select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4
/
Friday, November 13, 2009
Monday, April 6, 2009
DBMS_FILE_TRANSFER
Oracle recommends the use of RMAN to create a physical standby database. But in the manual and various MAA white papers they simply state that you should make the backups available to the target system and that the path to the backup files on the source and standby server must be the same.
Unfortunately they do not recommend how you should do this, especially when you have a FRA configured in ASM.
With the release of version 11 ASM Oracle introduced the ASMCMD cp command. I thought that solves the problem since the white paper (Oracle Database 11G Automatic Storage Management New Features Overview – published June 2007) states “The ASMCMD cp command allows you to copy files between ASM disk groups between 2 ASM servers”, but I could not get the command to copy my RMAN backupsets between ASM disk groups. I eventually logged SR 7173090.993 but even Oracle support was not sure and they wasted 2 days trying to make it work. Only after 2 days, did Oracle support say that the command does not work for copying backupsets between ASM disk groups (only data files).
OK what now?
I had a look around and decided that my only option is the cumbersome (but at least workable) DBMS_FILE_TRANSFER package.
First step is to locate the rman backupsets on the source server:
Now create the same directory structure on the remote server. On the standby server I first created an ASM Diskgroup with the same name as the FRA on production (+PCASDGF).
Use DBMS_FILE_TRANSFER to instantiate an ASM DataGuard database
DBMS_FILE_TRANSFER.GET_FILE contacts a remote database to read a remote file and then creates a copy of the file in the local file system. But before I can use this method I need a "stage" instance on the standby server. (I used DBCA to create a stage database - it took 20 minutes to create)
Prepare the newly created stage database to use DBMS_FILE_TRANSFER:
On Primary:
Get a list of the backupsets on the source server:
Use an editor to create a SQL command (one per backupset) which looks like this:
Note that the destination_file_name is not the same as the source file name. You will get an ORA-15046 if they are the same, this error is the result of the backupset being an Oracle-Managed file name.
So I execute these commands on the standby server from the stage instance. The progress of the command can be monitored using the V$SESSION_LONGOPS view. Once all the backupsets are copied over I was done with the stage database.
The next step is to create aliases for the new names of the backuppieces, so back on the source server issue:
Once done, I can query V$ASM_ALIAS and there are two entries per backuppiece. One for the backuppiece and one for the alias.
The aim of doing this was to create a Data Guard instance, so before I can restore the backup I first have to prepare a Data Guard instance:
Log onto the standby server
OK now I am ready to start the restore. First catalog the backuppieces in the source server/primary RMAN
Connect to RMAN on the primary server
Once done, I could list the backup in RMAN and see two entries, one for the backuppiece and one for the alias.
Eventually I am ready to use the RMAN backup to create a data guard instance.
Unfortunately they do not recommend how you should do this, especially when you have a FRA configured in ASM.
With the release of version 11 ASM Oracle introduced the ASMCMD cp command. I thought that solves the problem since the white paper (Oracle Database 11G Automatic Storage Management New Features Overview – published June 2007) states “The ASMCMD cp command allows you to copy files between ASM disk groups between 2 ASM servers”, but I could not get the command to copy my RMAN backupsets between ASM disk groups. I eventually logged SR 7173090.993 but even Oracle support was not sure and they wasted 2 days trying to make it work. Only after 2 days, did Oracle support say that the command does not work for copying backupsets between ASM disk groups (only data files).
OK what now?
I had a look around and decided that my only option is the cumbersome (but at least workable) DBMS_FILE_TRANSFER package.
First step is to locate the rman backupsets on the source server:
ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
Now create the same directory structure on the remote server. On the standby server I first created an ASM Diskgroup with the same name as the FRA on production (+PCASDGF).
ASMCMD> mkdir +PCASDGF/CASOB1
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET/2009_03_27
Use DBMS_FILE_TRANSFER to instantiate an ASM DataGuard database
DBMS_FILE_TRANSFER.GET_FILE contacts a remote database to read a remote file and then creates a copy of the file in the local file system. But before I can use this method I need a "stage" instance on the standby server. (I used DBCA to create a stage database - it took 20 minutes to create)
Prepare the newly created stage database to use DBMS_FILE_TRANSFER:
CONNECT sys/XXX
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27'
CREATE DATABASE LINK SOURCE_DB CONNECT TO system IDENTIFIED BY XXX USING 'CASOB1.VODACOM.CO.ZA';
On Primary:
CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27';
Get a list of the backupsets on the source server:
ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
ASMCMD> ls
annnf0_TAG20090327T115944_0.417.682603193
annnf0_TAG20090327T115944_0.418.682603193
...
Use an editor to create a SQL command (one per backupset) which looks like this:
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'ncsdf0_TAG20090406T092911_0.1
367.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP1',source_database => 'SOURCE_DB');
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'nnndf0_TAG20090406T092911_0.1
364.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP2',source_database => 'SOURCE_DB');
…
Note that the destination_file_name is not the same as the source file name. You will get an ORA-15046 if they are the same, this error is the result of the backupset being an Oracle-Managed file name.
So I execute these commands on the standby server from the stage instance. The progress of the command can be monitored using the V$SESSION_LONGOPS view. Once all the backupsets are copied over I was done with the stage database.
The next step is to create aliases for the new names of the backuppieces, so back on the source server issue:
ALTER DISKGROUP PCASDGF ADD ALIAS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP1' for '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/annnf0_TAG20090327T115944_0.417.682603193';
…
Once done, I can query V$ASM_ALIAS and there are two entries per backuppiece. One for the backuppiece and one for the alias.
The aim of doing this was to create a Data Guard instance, so before I can restore the backup I first have to prepare a Data Guard instance:
Log onto the standby server
# $export ORACLE_SID=casobc
# $orapwd=orapwcasobc password=XXX
sqlplus
SYS@casobc>create spfile from pfile; (I previously created a pfile from production with a few changes necessary for DG)
File created.
SYS@casobc>startup force nomount;
ORACLE instance started.
OK now I am ready to start the restore. First catalog the backuppieces in the source server/primary RMAN
Connect to RMAN on the primary server
RMAN> connect target sys/XXX@casob1.vodacom.co.za;
connected to target database: CASOB1 (DBID=1215042422)
RMAN> connect auxiliary /
connected to auxiliary database: CASOB1 (not mounted)
RMAN> catalog backuppiece '+PCASDGF/ CASOB1/BACKUPSET/2009_03_27/BACKUP1';
RMAN> catalog backuppiece '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP2';
…
Once done, I could list the backup in RMAN and see two entries, one for the backuppiece and one for the alias.
Eventually I am ready to use the RMAN backup to create a data guard instance.
RMAN> duplicate target database for standby ;
...
RMAN> exit
Monday, March 9, 2009
RMAN restore to new host
The mission:
Restore a production 10.2.0.4.0 database to a new host in order to test our recoveries. The production database is part of a Data Guard configuration in Maximum Availability mode and it runs on Unix RAW devices. The restore will be to ASM.
Notes by my colleague Ian Baugaard:
If you perform a test restore only, then do not connect to the recovery catalog when restoring the datafiles. Otherwise, RMAN records information about the restored datafiles to the recovery catalog. This intereferes with future attempts to restore and recover the primary database. If you must use a recovery catalog because the control file is not large enough to contain the RMAN repository data on all of the backups that you need to restore, then export the catalog and import it into a different schema or database and use the copied recovery catalog for the test restore. Otherwise, the catalog considers the restored database as the current target database.
Also confirm all of the following before commencing:
- Ensure that the /etc/oratab file is correctly configured
- Ensure that the following environment variable are set to the values shown
o NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
o NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
- Ensure that the $ORACLE_BASE/admin/$ORACLE_SID/bdump directory is empty
- Ensure that the job_queue_processes parameter is set to 0 when editing the parameter file at step 3
- That the location specified in the log_archive_dest parameter is valid, writable and empty
- Confirm whether or not the source database is using a change tracking file, and if so, try to ensure that the corresponding path exists on the node where the restore is being performed
- Ensure that the tnsnames.ora file does not contain any references whatsoever to a production system
1. Ensure your environment is correctly configured, i.e. ORACLE_SID is set correctly and that the Oracle software installation matches the environment of the source system. When ready, start RMAN and connect to the target without connecting to the recovery catalog.
rman target / NOCATALOG
2. Start the instance without mounting it, using the DBID obtained from the v$database view on the source database. RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file.
RMAN> set DBID dbid ;
RMAN> startup nomount ;
3. Restore and edit the server parameter file. NB: The values for NSR Server and NSR Client will have to be adjusted accordingly, and can be derived from the values of the production script
run {
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
shutdown abort;
}
4. From a second session, edit all appropriate parameters of the restored parameter file to cater for the environment you are restoring on, i.e. to reflect new directory structures, memory differences, etc. Once done, start the instance using the new file
RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
5. Proceed to restore the controlfile from an autobackup from your original session
run {
allocate channel c1 device type SBT PARMS=' ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore controlfile from autobackup;
alter database mount;
}
6. Query the database filenames recorded in the control file on the new host by running the following query in SQL*Plus
col name for a60
SELECT file# AS "File/Grp#", name FROM v$datafile
UNION
SELECT group#, member FROM v$logfile;
EXIT
7. Write the RMAN recovery script. The script must include the following steps:
a. For each datafile on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below
select
'set newname for datafile '|| file# ||' to '''|| replace( name, 'rabdg', 'rabbcv' ) ||''' ;'
from v$datafile
order by file#
b. For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below
select
'SQL "alter database rename file '''''|| member ||''''' to '''''|| replace ( member, 'rabdg', 'rabbcv' ) ||''''' " ;'
from v$logfile
order by member
c. Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.
d. Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
e. Restore and recover the database
NB: By default, RMAN does not restore read-only files when you issue the RESTORE DATABASE command.
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c2 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c3 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";
# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20080310 08:33:12','YYYYMMDD HH24:MI:SS')" ;
# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;
# RECOVER THE DATABASE
recover database;
}
EXIT
8. Now perform an OPEN RESETLOGS at the restored database.
RMAN> alter database open resetlogs ;
9. Issue a shutdown and start the restored database in mount mode, and correct the location of all tempfiles
10. If this was a test restore, and it was successful, then you can shut down the test database instance, and delete the test database with all of its files.
SQL> startup force mount restrict pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
SQL> drop database ;
rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora
rm log_archive_dest/*
Scripts and Logfiles
casprd1:/opt/apps/oracle/database/10.2.0.4/rdbms/admin> rman target / NOCATALOG
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 18 14:17:28 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> set dbid 2070848595;
executing command: SET DBID
RMAN> startup nomount ;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/apps/oracle/database/10.2.0.4/dbs/initcasprd1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2054608 bytes
Variable Size 67110448 bytes
Database Buffers 83886080 bytes
Redo Buffers 6332416 bytes
RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2)';
3> restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
4> shutdown abort;
5> }
allocated channel: c1
channel c1: sid=39 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0
Starting restore at 19-FEB-2009 12:40:16
Change init.ora file to:
*.audit_file_dest='/opt/apps/oracle/admin/casprd1/adump'
*.background_dump_dest='/opt/apps/oracle/admin/casprd1/bdump'
*.compatible='10.2.0'
*.control_file_record_keep_time=21
*.core_dump_dest='/opt/apps/oracle/admin/casprd1/cdump'
*.db_block_size=8192
*.db_cache_size=500000000
*.db_files=500
*.db_name='casprd'
*.db_writer_processes=4
*.instance_name='casprd1'
*.large_pool_size=50000000
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='casprd_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_archive_min_succeed_dest=1
*.log_buffer=16384000
*.os_authent_prefix='ops$'
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='casprd'
*.sga_target=2G
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDO'
*.user_dump_dest='/opt/apps/oracle/admin/casprd1/udump'
*.workarea_size_policy='auto'
*.db_create_file_dest='+TUNXDG_RESTORE'
*.db_recovery_file_dest='+TUNXDG_RESTORE'
*.DB_RECOVERY_FILE_DEST_SIZE=2G
*.PROCESSES=200
RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
Oracle instance started
Total System Global Area 2147483648 bytes
Fixed Size 2057496 bytes
Variable Size 503319272 bytes
Database Buffers 1610612736 bytes
Redo Buffers 31494144 bytes
RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2,NSR_DEBUG_FILE=/home/oracle/nsr_icap2.log)';
3> restore controlfile from autobackup;
4> alter database mount;
5> }
allocated channel: c1
channel c1: sid=33 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0
Starting restore at 19-FEB-2009 13:53:31
channel c1: looking for autobackup on day: 20090219
channel c1: autobackup found: c-2070848595-20090219-04
channel c1: control file restore from autobackup complete
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507
Finished restore at 19-FEB-2009 13:55:17
database mounted
released channel: c1
Edit init.ora file to reflect new controlfile settings:
*.control_files='+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505’,’+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507'
casprd1:/home/oracle/meyert> cat icap_restore.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_restore.log' cmdfile='/home/oracle/meyert/icap_restore.par'
casprd1:/home/oracle/meyert> cat icap_restore.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
…
set newname for datafile 321 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SA_T_CMD_PARAM_0422' ;
SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";
…
# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;
# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;
}
EXIT
casprd1:/home/oracle/meyert> cat icap_restore.log
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 5 15:44:38 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog
allocated channel: chnl1
channel chnl1: sid=26 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0
allocated channel: chnl2
channel chnl2: sid=33 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0
allocated channel: chnl3
channel chnl3: sid=11 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0
executing command: SET NEWNAME
executing command: SET NEWNAME
…
executing command: SET until clause
Starting restore at 05-MAR-2009 15:51:58
channel chnl1: starting datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_12
restoring datafile 00027 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0401
restoring datafile 00156 to +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_ssb
restoring datafile 00193 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0409
channel chnl1: reading from backup piece /full_CASPRD_t680405445_s84501_p1/
…
Finished restore at 07-MAR-2009 01:19:52
datafile 1 switched to datafile copy
input datafile copy recid=665 stamp=680836812 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_system01
datafile 2 switched to datafile copy
input datafile copy recid=666 stamp=680836813 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_undo01
…
datafile 321 switched to datafile copy
input datafile copy recid=985 stamp=680837070 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0422
Starting recover at 07-MAR-2009 01:25:06
channel chnl1: starting incremental datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0418
destination for restore of datafile 00035: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_cmd
destination for restore of datafile 00042: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_event
destination for restore of datafile 00192: +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_05
channel chnl1: reading from backup piece /level1_CASPRD_t680504446_s84621_p1/
…
channel chnl3: reading from backup piece /level1_CASPRD_t680506557_s84697_p1/
channel chnl3: restored backup piece 1
piece handle=/level1_CASPRD_t680506557_s84697_p1/ tag=TAG20090303T050038
channel chnl3: restore complete, elapsed time: 00:17:46
starting media recovery
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+TUNXDG_RESTORE/casprd/datafile/casprd_system01'
released channel: chnl1
released channel: chnl2
released channel: chnl3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2009 08:19:08
RMAN-06557: unable to restore archived log thread 1, sequence 12670
RMAN-06558: archived log size of 2043389 kb is bigger than available space of 2020336 kb
Recovery Manager complete.
(My RECOVERY_DEST_SIZE was too small, so I increased it and simply ran a recovery – since the restore was completed)
casprd1:/home/oracle/meyert> cat icap_recovery.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_recovery.log' cmdfile='/home/oracle/meyert/icap_recovery.par'
casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;
# RECOVER THE DATABASE
recover database;
}
EXIT
casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;
# RECOVER THE DATABASE
recover database;
}
EXIT
casprd1:/home/oracle/meyert> cat icap_recovery.log
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 9 07:33:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog
allocated channel: chnl1
channel chnl1: sid=33 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0
allocated channel: chnl2
channel chnl2: sid=12 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0
allocated channel: chnl3
channel chnl3: sid=27 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0
executing command: SET until clause
Starting recover at 09-MAR-2009 07:33:37
starting media recovery
channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12668
channel chnl1: restoring archive log
archive log thread=1 sequence=12669
channel chnl1: reading from backup piece /arch_CASPRD_t680507135_s84703_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680507135_s84703_p1/ tag=TAG20090303T054533
channel chnl1: restore complete, elapsed time: 00:01:17
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 thread=1 sequence=12668
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 recid=108554 stamp=681032266
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 thread=1 sequence=12669
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 recid=108553 stamp=681032231
channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12670
channel chnl1: reading from backup piece /arch_CASPRD_t680515326_s84705_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680515326_s84705_p1/ tag=TAG20090303T080205
channel chnl1: restore complete, elapsed time: 00:02:06
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 thread=1 sequence=12670
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 recid=108555 stamp=681033249
media recovery complete, elapsed time: 00:05:01
Finished recover at 09-MAR-2009 07:59:13
released channel: chnl1
released channel: chnl2
released channel: chnl3
Recovery Manager complete.
Startup fails with ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR
I found the solution on Stewart F's blog.
Metalink note 245731.1 this was caused by that fact that there was still some kind of reference in my Primary databases data dictionary: "The Primary Database has still the Protection Mode stored in the Data Dictionary. The Protection Mode requires a running connection to a Standby Database with LGWR SYNC as Log-Transportation Mode. Since the standby is not available anymore,LGWR of the primary terminates the instance."
Solution:
SYS@casprd1> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2057496 bytes
Variable Size 503319272 bytes
Database Buffers 1610612736 bytes
Redo Buffers 31494144 bytes
Database mounted.
SYS@casprd1> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database altered.
SYS@casprd1> shutdown immediate
SYS@casprd1> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2057496 bytes
Variable Size 503319272 bytes
Database Buffers 1610612736 bytes
Redo Buffers 31494144 bytes
Database mounted.
SYS@casprd1> select name from v$tempfile ;
NAME
-----------------------------------------------------------------------------------------------------------------------------
/dev/vx/rdsk/oradg/casprd_TEMP01
/dev/vx/rdsk/oradg/casprd_TEMP02
/dev/vx/rdsk/oradg/casprd_TEMP04
/dev/vx/rdsk/oradg1/casprd_TEMP05
/dev/vx/rdsk/oradg1/casprd_TEMP06
/dev/vx/rdsk/oradg1/casprd_TEMP07
/dev/vx/rdsk/oradg1/casprd_TEMP08
7 rows selected.
SYS@casprd1> alter database rename file '/dev/vx/rdsk/oradg/casprd_TEMP01' to '+TUNXDG_RESTORE/casprd/tempfile/casprd_TEMP01';
Database altered.
Etc for all 7 tempfiles.
SYS@casprd1> alter database open;
Database altered.
Friday, January 23, 2009
Data Guard Lag Time
We use the Broker to administer our Data Guard configurations and we also use OEM to monitor our databases, we have found that every time we change the state of the Physical Standby between ONLINE and READ-ONLY (and back to ONLINE again) we start getting OEM alarms for reaching our set limits for Lag Time (the time in seconds that the Physical Standby is behind the Primary database).This Lag Time can also be found in v$dataguard_stats (in fact that is where OEM gets it from).
What we have found was that we need to bounce the Physical Standby to force v$dataguard_stats to get updated with the correct values. The good news is that the Physical standby does not really have a lag time, it is just that v$dataguard_stats does not update itself when DG STATES change. The bad news is that OEM shows the faulty lag time, generates an alarm and since our alarms are visible to the whole organization we have to explain that it is not really a problem.
To confirm that the problem is in v$dataguard_stats and is not a real reflection of reality, do the following:
So we are only a few seconds behind, but v$dataguard_stats and OEM show otherwise. Bouncing the Physical Standby database fixed this.
What we have found was that we need to bounce the Physical Standby to force v$dataguard_stats to get updated with the correct values. The good news is that the Physical standby does not really have a lag time, it is just that v$dataguard_stats does not update itself when DG STATES change. The bad news is that OEM shows the faulty lag time, generates an alarm and since our alarms are visible to the whole organization we have to explain that it is not really a problem.
To confirm that the problem is in v$dataguard_stats and is not a real reflection of reality, do the following:
On Standby:
SQL>select TIME_COMPUTED from v$dataguard_stats;
TIME_COMPUTED
------------------------------
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
SQL>select current_scn from v$database;
CURRENT_SCN
--------------------------
9661858803219
On Primary:
SQL>select scn_to_timestamp(9661857384219) from dual;
SCN_TO_TIMESTAMP(9661857384219)
---------------------------------------------------------
12-JAN-09 12.26.57.000000000 PM
SQL>select SCN_TO_TIMESTAMP(current_scn) from v$database;
SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------
12-JAN-09 12.30.00.000000000 PM
So we are only a few seconds behind, but v$dataguard_stats and OEM show otherwise. Bouncing the Physical Standby database fixed this.
Wednesday, January 21, 2009
KeePass and Putty
KeePass and Putty are software programs that work great together, especially in an enterprise where you have a lot of passwords to remember. Autotmatic Login to a Unix server is simply a “Ctrl+U” away.
First you need to setup your Putty Configuration, for example:
Servers are saved as sessions and Auto-login username is specified
In KeepPass you need to configure 1 entry per server. The URL entry is what makes KeePass more than a list of passwords.
If putty is in your $PATH then the command you typed in the URL field above (putty -load -pw XX) will also work from the command line. In the main KeepPass window you simply need to highlight the entry and press “Ctrl-U”. 
First you need to setup your Putty Configuration, for example:
Servers are saved as sessions and Auto-login username is specified
In KeepPass you need to configure 1 entry per server. The URL entry is what makes KeePass more than a list of passwords.
If putty is in your $PATH then the command you typed in the URL field above (putty -load
Tuesday, December 23, 2008
Problem setting up Archive Log file management on ASM
I created a ASM diskgroup for FRA and set the relevant parameters. The aim to to write Archive logs, Flashback logs and RMAN backups to this FRA.
My problems started when I changed to Archive Log Mode
So the archive log files should be written to my FRA on ASM, but when I tested it I got a unexpected error:
This error message is misleading because there is nothing wrong with the FRA on ASM. I found the real error message in the alert.log file:
So the archiver also wants to archive to my $ORACLE_HOME/dbs directory, it cannot do that because there is not enough space on that filesystem, but the real question is why does it want to write to that destination?
When I do a show parameter I don't get the answer:
Again I found the answer in the alert.log
It seems to be a DEFAULT destination that does not show up when I use ARCHIVE LOG LIST or SHOW PARAMETER LOG_ARCHIVE_DEST.
It does show up in V$ARCHIVE_DEST though:
To solve the problem:
And to test
Lesson learned.
Keep on looking until you can explain the error.
SQL>select FREE_MB from v$asm_diskgroup where NAME='PRNADGF';
FREE_MB
----------
2,761,992
SQL>show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>alter system set db_recovery_file_dest_size=2700000M;
System altered.
SQL>alter system set db_recovery_file_dest='+PRNADGF';
System altered.
My problems started when I changed to Archive Log Mode
SQL>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Current log sequence 12
SQL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup mount
ORACLE instance started.
Total System Global Area 3156803584 bytes
Fixed Size 2110872 bytes
Variable Size 402655848 bytes
Database Buffers 2734686208 bytes
Redo Buffers 17350656 bytes
Database mounted.
SQL>alter database archivelog;
Database altered.
SQL>alter database open;
Database altered.
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
So the archive log files should be written to my FRA on ASM, but when I tested it I got a unexpected error:
SQL>alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 12 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 3 thread 1: '+PRNADG1/rnatst/onlinelog/group_3.278.672669129'
This error message is misleading because there is nothing wrong with the FRA on ASM. I found the real error message in the alert.log file:
ARCH: Encountered disk I/O error 19502
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch1_12_672668870.dbf' (error 19502)
(rnatst)
Errors in file /opt/apps/oracle/diag/rdbms/rnatst/rnatst/trace/rnatst_ora_21990.trc:
So the archiver also wants to archive to my $ORACLE_HOME/dbs directory, it cannot do that because there is not enough space on that filesystem, but the real question is why does it want to write to that destination?
When I do a show parameter I don't get the answer:
SQL> show parameter log_archive_dest
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_min_succeed_dest integer 1
Again I found the answer in the alert.log
Starting ORACLE instance (normal)
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
It seems to be a DEFAULT destination that does not show up when I use ARCHIVE LOG LIST or SHOW PARAMETER LOG_ARCHIVE_DEST.
It does show up in V$ARCHIVE_DEST though:
SQL> select dest_id, destination, status from V$ARCHIVE_DEST;
DEST_ID DESTINATION STATUS
---------- ------------------------------------------------------- ---------
1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch VALID
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 USE_DB_RECOVERY_FILE_DEST VALID
To solve the problem:
SQL> alter system set log_archive_dest_state_1=DEFER;
And to test
SQL>alter system archive log current;
System altered.
SQL>select dest_id, name from v$archived_log;
DEST_ID NAME
---------- ----------------------------------------------------------------------
10 +PRNADGF/rnatst/archivelog/2008_12_22/thread_1_seq_12.256.674133347
SQL> select dest_id, destination, status from V$ARCHIVE_DEST;
DEST_ID DESTINATION STATUS
---------- ------------------------------------------------------- ---------
1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch DISABLED
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 INACTIVE
6 INACTIVE
7 INACTIVE
8 INACTIVE
9 INACTIVE
10 USE_DB_RECOVERY_FILE_DEST VALID
Lesson learned.
Keep on looking until you can explain the error.
Friday, December 12, 2008
Ora-17505 using RMAN with ASM
I had to create a Data Guard instance on test, so that we can evaluate Transparent Application Failover (TAF) for the app. I wanted to test the same procedure to build Data Guard that I will use for Production, and that meant that I had to do a RMAN backup to ASM. Our Storage team loaned me LUNs of different sizes for my temporary ASM on test.
But my backup command (Backup filesperset 10 database include current controlfile for standby) failed with:
RMAN-03009: failure of backup command on ORA_DISK_13 channel at 10/30/2008 20:21:27
ORA-19510: failed to set size of 5779210 blocks for file "+PCASDGF" (blocksize=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 5779210 blocks
This was a surprise because the test db is 7TB and FRA is 9TB.
confirmed that I had more than enough space available. In fact the backup failed after using only 47% fo the available space.
It turns out that the smallest disk in the diskgroup was the bottleneck.
As you can see disk 13 only had 70MB of space available. I removed all the disks of varying sizes and only kept the disks of 69052 MB Size. The total size of the FRA came down to 8493396 MB, but the RMAN backup completed successfully.
Lesson Learned:
ASM spreads file extents evenly accross all the disks disks on a diskgroup. An ORA-17505 error can still be encountered due to imbalanced free space between disks. The reason for this is that one disk lacking sufficient free space makes it impossible to do any allocation in a disk group because every file must be evenly allocated across all disks.
But my backup command (Backup filesperset 10 database include current controlfile for standby) failed with:
RMAN-03009: failure of backup command on ORA_DISK_13 channel at 10/30/2008 20:21:27
ORA-19510: failed to set size of 5779210 blocks for file "+PCASDGF" (blocksize=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 5779210 blocks
This was a surprise because the test db is 7TB and FRA is 9TB.
SQL> SELECT sum(space), sum(bytes) FROM v$asm_file;
and
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
confirmed that I had more than enough space available. In fact the backup failed after using only 47% fo the available space.
It turns out that the smallest disk in the diskgroup was the bottleneck.
SQL>select group_number, disk_number, total_mb, free_mb from v$asm_disk order by 4;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB
----------------------------------------
1 13 86315 70
1 16 17263 17080
1 14 17263 17080
1 129 34522 34168
1 130 34522 34168
1 131 34522 34168
1 19 34522 69052
...
As you can see disk 13 only had 70MB of space available. I removed all the disks of varying sizes and only kept the disks of 69052 MB Size. The total size of the FRA came down to 8493396 MB, but the RMAN backup completed successfully.
Lesson Learned:
ASM spreads file extents evenly accross all the disks disks on a diskgroup. An ORA-17505 error can still be encountered due to imbalanced free space between disks. The reason for this is that one disk lacking sufficient free space makes it impossible to do any allocation in a disk group because every file must be evenly allocated across all disks.
Subscribe to:
Posts (Atom)