Monday, August 18, 2008

DBID - change DB id using NID

change DB id using NID

shutdown database
startup mount
nid target=/
startup mount
alter database open resetlogs;

Blogged with the Flock Browser

Sunday, August 10, 2008

HOW TO MANAGE DATABASE IN ARCHIVE LOG

HOW TO MANAGE DATABASE IN ARCHIVE LOG

-- init.ora parameters
set log_checkpoints_to_alert=TRUE
remove log_checkpoint_timeout, log_checkpoint_interval

-- general
set numformat 999999999999
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

-- turn on archive log mode
steps

A. configure
1) alter system set db_recovery_file_dest='+ORAFLASH001' scope=both sid='*';
2) alter system set db_recovery_file_dest_size = 20G scope=both sid='*';

B. archivelog mode
1) shutdown database
2) startup database in mount mode
3) alter database archivelog; # as sysdba
4) archive log list # as sysdba
sys > ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 416
Next log sequence to archive 418
Current log sequence 418
5) open database


-- FIX archive log destination to ORAFLASH001 in 10g using ASM, if set incorrectly
steps
# of course db_recovery_file_dest should be set to +ORAFLASH001
1) alter system set log_archive_dest_10='LOCATION=use_db_recovery_file_dest' scope=both sid='*';
2) alter system set log_archive_dest_1='' scope=both sid='*'; # to null out this parameter
3) alter system switch logfile; # to test, check alert log


others
alter system switch logfile;
alter system archive log current;
alter system archive log all;


-- increase archive log destination size. but 1st find out, How much archived
steps
1) select count(*),sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
2) increase db_recovery_file_dest_size
ALTER SYSTEM SET db_recovery_file_dest_size = 30G SCOPE=BOTH SID='*';
3) SHOW PARAMETER RECOVERY

-- Check space utilization
steps
1. col name format a20
set numwidth 18
select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------- ----------- ---------- ----------------- ---------------
+ORAFLASH001 2147483648 1271777792 596992 33

2. select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE .71 0 1
ONLINELOG 0 0 0
ARCHIVELOG 16.81 .03 24
BACKUPPIECE 41.63 0 8
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

-- list archived files not backed-up
select NAME, ARCHIVED , deleted, status
from v$archived_log
where status='A';

others
SELECT SEQUENCE#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE#
FROM v$archived_log;

SELECT sequence#, thread#, first_change#, next_change#
FROM v$archived_log;

SELECT MAX(sequence#)
FROM v$archived_log;

-- find archive logs to be restored

select completion_time,name,sequence#,first_change#,first_time,next_change#,next_time, archived
from v$archived_log;

select f.member,to_char(v.first_change#)
, v.sequence#
from v$log v
, v$logfile f
where v.group# = f.group#
and v.status='CURRENT';


-- for recovery
archive log list # get current sequence number

-- last redo log file that was in use (last sequence number -1)
select recid
, stamp
, sequence#
, first_change#
, next_change#
from v$log_history
where recid = <current log sequence -1) # from last step

This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo
log file. It will also give us the FIRST_CHANGE# SCN in
this redo log file.

-- this gives current redo log file. archive for recovery
select f.member,to_char(v.first_change#)
, v.sequence#
from v$log v
, v$logfile f
where v.group# = f.group#
and v.status='CURRENT';

-- misc
Alter system archive log [start|stop|all|...]
alter system switch logfile;

alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log stop;
alter system archive log current noswitch;


alter system checkpoint;

alter system flush buffer_cache;
alter system flush buffer_pool;

alter system quiesce restricted;
alter system suspend|resume;
alter system switch logfile;

-- how to get current scn
# in 10g
alter system checkpoint;
select current_scn from v$database ;

or To get the current SCN, use the following:

SET SERVEROUTPUT ON
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Current SCN is ' || iscn);
END;
/

# in 9i
To get the time/date for an SCN value in 9i, use the following example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- (a) Get the current SCN base.
SQL>select max(scn_bas) SCN_BASE from smon_scn_time;

1603342197

--(b) Get the complete SCN and the timestamp.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';

SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time
where scn_bas='1603342197';

TIMESTAMP SCN
------------------ ----------
28-JUL-06 05:31:08 8252235517813



-- restore archive log using seqeunce
run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier1)';
restore archivelog from sequence 35347 until sequence 35359;
}

run {
allocate channel demo_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/oraarc01/oracle/arch/demo/' ;
restore archivelog from logseq=70241 until logseq=70250 ;
}

run {
allocate channel demo_t3 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2)';
set archivelog destination to '/opt/splexmv/tmparclog/' ;
restore archivelog sequence between 70276 and 70376 ;
}
-- restore archive log using time
run {
allocate channel icmt_t1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_rs204_Tier2,NB_ORA_CLIENT=rs204-bkup)';
restore archivelog
from time = "to_date('Apr 5 2005 19:20:00','Mon DD YYYY HH24:MI:SS')"
until time = "to_date('Apr 5 2005 20:00:00','Mon DD YYYY HH24:MI:SS')";
}

-- backup archive log
run {
allocate channel ch1 type 'SBT_TAPE'
parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';
set command id to 'rman backup';
backup
maxsetsize = 1G
diskratio = 0
skip inaccessible
tag = 'Archive Logs'
(archivelog all delete input format'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p');
}

-- backup database including archivelog
backup setsize = 62914560
(database)
plus archivelog
skip inaccessible;


backup
full
filesperset = 1024
diskratio = 0
tag = 'Full'
(database format 'SBT_TAPE/RMAN_%d.%t.full.%c.%s.%p')
plus archivelog
skip inaccessible
delete input
format 'SBT_TAPE/RMAN_%d.%t.arch.%c.%s.%p';


rman target / nocatalog <<EOF
backup as copy archivelog all format '$v_rman_loc/%d_AL_%T_%u_s%s_p%p' delete input;
list backupset;
exit
EOF

rman target / nocatalog <<EOF
run {
allocate channel backup_disk1 type disk format '$v_bdest/%U';
allocate channel backup_disk2 type disk format '$v_bdest/%U';
backup as copy archivelog
sequence between $v_scni1 and $v_scnf1 thread 1;
backup as copy archivelog
sequence between $v_scni2 and $v_scnf2 thread 2;
release channel backup_disk1 ;
release channel backup_disk2 ;
}
exit
EOF


run {
# USE THIS COMMAND FILE FOR ARCHIVELOG Backups
# USE THIS COMMAND FILE FOR DATABASES RUNNING IN ARCHIVELOG MODE ONLY !
#backup FILESPERSET = 4
# skip inaccessible
# ( archivelog all ) ;

BACKUP FILESPERSET = 4 skip inaccessible ARCHIVELOG ALL NOT BACKED UP 1 TIMES delete all input ;

sql "ALTER SYSTEM ARCHIVE LOG CURRENT" ;

backup FILESPERSET = 4
skip inaccessible
( archivelog all NOT BACKED UP 1 TIMES );
}

-- dynamic performance views
Dynamic Performance View Description

V$DATABASE
Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode.

V$ARCHIVED_LOG
Displays historical archived log information from the control file.
If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST
Describes the current instance, all archive destinations, and the current value, mode,
and status of these destinations.

V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog,
the RC_BACKUP_REDOLOG contains similar information.

V$LOG
Displays all online redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range
for each archived log.
Blogged with the Flock Browser

Saturday, August 09, 2008

RAC Infrastruture Guildlines

Dell RH Linux non-critical db servers:
lnx521 and lnx522 (dev)
lnx523 and lnx524 (UAT)
lnx525 and lnx526 (prod)


Storage:
OCR and Voting Disks: multiplexed on 2 – 13 GB luns
Data: allocated in 100GB lun chunks. (current sum of target dev/UAT db’s for
Linux: approx 375GB/650GB)
Recovery: 100GB lun each for dev/UAT


Naming Standards
Server: lnx###
Storage
OCR: OCR1, OCR2
Voting Disk: VOTE1, VOTE2, VOTE3
ASM
Disk Group: ORADATA1 and ORAFLASH1
Disks: oradatad###

Oracle Software Install
/ora01/oracle/product/crs_ CRS $ORACLE_HOME
/ora01/oracle/product/asm_ ASM $ORACLE_HOME
/ora01/oracle/product/db_ Database $ORACLE_HOME

Cluster Name (must be unique across enterprise)
CLDEV##, CLUAT##, CLPROD##

Global DB Name: xxx[D,T,P]
Instance Name: xxx[D,T,P][#]



Install Standards/Database creation
Password file – use file created by default
Companion disk – install in database $OH
dbca – use for db creation – (CSX_Default template will be created)
pfile – located in $OB/admin/SID/pfile (one line referencing spfile), link to $OH/dbs
spfile – on ASM (backup pfile kept in $OB location tbd)
Database Control - do not install/configure (Grid control will be used)
ASM instance sys/system passwords – kept in sync with dev/prod database sys/system passwords
Datafiles: created in ORADATA1 diskgroup
Controlfiles: created in ORADATA1 and ORAFLASH1 diskgroups
Redo logs: created in ORADATA1 and ORAFLASH1 diskgroups
Archive logs: created in ORAFLASH1 diskgroup


Suggested standards/will require follow-up:

ASMlibs created on /opt/oracle/extapi – local rpm package install common to scripts
directory – location cannot be changed – create links for all common directories
currently in use on /opt/oracle

CRS, ASM and database $OH – create on local storage - need to determine any
backup/DR implications/revisions to current standard

/oramisc01 – create on local storage <=50GB partition – need to determine any
backup/DR implications/revisions to current standard

Root access needed for cluster install – will need to develop procedure keeping
with Sarbanes Oxley requirements