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

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

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