GMANews.TV - Hello, Palace? OPS attention called over P5-M phone bill - Nation - Official Website of GMA News and Public Affairs - Latest Philippine News
“Kung titingnan daw talaga natin, yung P5 million na cellular phone bill over a period of one year for an information and communications office like the OPS is not really big. Yung mga long distance calls…mga interviews, it really racked up costs," said Remonde in a recent interview with reporters.
Shared via AddThis
Sunday, August 23, 2009
Sunday, August 09, 2009
Unix/Linux script to check Oracle standby database
#!/bin/ksh
#Author: Emmanuel Caseñas
#Note: this will give you a wealth of information for your standby database
################################################################################
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba'<set lines 200 pages 2000
select * from v\$archive_gap;
select maX(sequence#),applied,registrar from V\$archived_log group by applied,registrar;
select PROCESS,PID,SEQUENCE#,BLOCK#,BLOCKS,STATUS from v\$managed_standby;
select NAME,DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from V\$database;
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x\$kcccp cp, x\$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
archive log list
exit
!
bd=`echo ${CLUSTER_HOME}/admin/${SID}/bdump`
ll=`grep "^Media Recovery Log" $bd/al*.log | tail -1 |awk -F"/" '{print $NF}'`
lw=`grep "^Media Recovery Waiting for" $bd/al*.log | tail -1 | awk '{print $NF}'`
echo "Latest Log Applied: $ll"
echo "Waiting for Log: $lw"
Check tablespace ts_details.sql
set lines 300
col "Data File" for a75
variable ts_name varchar2(100);
exec :ts_name := '&&tablespace_name';
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",max(fs.bytes) / (1024 * 1024) "Max Chunk Avail",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used",autoextensible
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes,autoextensible
FROM dba_data_files
GROUP BY tablespace_name,autoextensible) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and df.tablespace_name = :ts_name
GROUP BY df.tablespace_name,df.bytes,autoextensible;
select file_name "Data File",bytes/1024/1024 "Size",status from dba_data_files where tablespace_name = :ts_name;
set pages 3000
spool /tmp/tmp.sql
set heading off
set term off
select '!ls -lrt ' || file_name ||'|'|| 'awk ''{print $9,$10,$11}''' from dba_data_files where tablespace_name = :ts_name;
spool off
set heading on
set term on
@/tmp/tmp.sql
col "Data File" for a75
variable ts_name varchar2(100);
exec :ts_name := '&&tablespace_name';
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",max(fs.bytes) / (1024 * 1024) "Max Chunk Avail",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used",autoextensible
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes,autoextensible
FROM dba_data_files
GROUP BY tablespace_name,autoextensible) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and df.tablespace_name = :ts_name
GROUP BY df.tablespace_name,df.bytes,autoextensible;
select file_name "Data File",bytes/1024/1024 "Size",status from dba_data_files where tablespace_name = :ts_name;
set pages 3000
spool /tmp/tmp.sql
set heading off
set term off
select '!ls -lrt ' || file_name ||'|'|| 'awk ''{print $9,$10,$11}''' from dba_data_files where tablespace_name = :ts_name;
spool off
set heading on
set term on
@/tmp/tmp.sql
Adding a datafile after the flashback database is on
1)enable flashback database in your database
*assuming you database is no in archivelog mode else skip this task*
ENABLE ARCHIVELOG MODE
----------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
CREATE A DIRECTORY FOR FLASHBACK AREA
-------------------------------------
UNIX> mkdir -p /sbcdump/01/oradumps/EMINUS/flashback_area
CONFIGURE FLASHBACK
-------------------
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='/sbcdump/01/oradumps/EMINUS/flashback_area' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.
SQL> shutdown immediate;
TURN FLASHBACK DATABASE ON
--------------------------
SQL> alter database mount;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
ADD DATAFILE
------------
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set numwidth 30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
------------------------------ --------------------
9205819379813 09-aug-2009 16:59:32
SQL> @/home/casenaem/ts_details -- this is my script to check on a tablespace
Enter value for tablespace_name: USERS
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
SQL> alter tablespace users
2 add datafile '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10m;
Tablespace altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 510 499.6875 509.375 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf 10 AVAILABLE
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
------------------------------
9205819381790
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------------ ---------
9205819379813 09-AUG-09
FLASHBACK THE DATABASE TO THE OLDEST_FLASHBACK_SCN
--------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 9205819379813;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ------------------------------ ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
Note:
As you can see that oracle will delete the file from dictionary,controlfile and the physical file
*Now if you want to recover the database before you executed flashback database*
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> recover database;
ORA-00279: change 9205819379814 generated at 08/09/2009 16:40:19 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc
ORA-00280: change 9205819379814 for thread 1 is in sequence #17
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 9205819380249 generated at 08/09/2009 17:09:55 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000018_0694319678.arc
ORA-00280: change 9205819380249 for thread 1 is in sequence #18
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc' no longer needed for this recovery
.......
........
..........
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf'
ORA-01112: media recovery not started
SQL> select count(1) from v$datafile_header where fuzzy = 'YES';
COUNT(1)
------------------------------
5
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
6 rows selected.
SQL> !ls -l /sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf: No such file or directory
SQL> !ls -l /sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006: No such file or directory
SQL> alter database create datafile '/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006' as '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10M;
Database altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
6 rows selected.
SQL> recover database;
ORA-00279: change 9205819381724 generated at 08/09/2009 17:41:53 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc
ORA-00280: change 9205819381724 for thread 1 is in sequence #22
Specify log: {=suggested | filename | AUTO | CANCEL}
aUTO
ORA-00279: change 9205819381766 generated at 08/09/2009 17:42:51 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000023_0694319678.arc
ORA-00280: change 9205819381766 for thread 1 is in sequence #23
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc' no longer needed for this recovery
....
......
.......
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
Note: Since Oracle has removed the physical file when I did a flashback you need to create it yourself in order for the recovery to conitnue
*assuming you database is no in archivelog mode else skip this task*
ENABLE ARCHIVELOG MODE
----------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
CREATE A DIRECTORY FOR FLASHBACK AREA
-------------------------------------
UNIX> mkdir -p /sbcdump/01/oradumps/EMINUS/flashback_area
CONFIGURE FLASHBACK
-------------------
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='/sbcdump/01/oradumps/EMINUS/flashback_area' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=10g scope=both;
System altered.
SQL> shutdown immediate;
TURN FLASHBACK DATABASE ON
--------------------------
SQL> alter database mount;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
ADD DATAFILE
------------
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set numwidth 30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
------------------------------ --------------------
9205819379813 09-aug-2009 16:59:32
SQL> @/home/casenaem/ts_details -- this is my script to check on a tablespace
Enter value for tablespace_name: USERS
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
SQL> alter tablespace users
2 add datafile '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10m;
Tablespace altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 510 499.6875 509.375 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf 10 AVAILABLE
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
------------------------------
9205819381790
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------------ ---------
9205819379813 09-AUG-09
FLASHBACK THE DATABASE TO THE OLDEST_FLASHBACK_SCN
--------------------------------------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 9205819379813;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> @/home/casenaem/ts_details
PL/SQL procedure successfully completed.
Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---
USERS 500 499.6875 499.6875 100 0 NO
Data File Size STATUS
--------------------------------------------------------------------------- ------------------------------ ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
Note:
As you can see that oracle will delete the file from dictionary,controlfile and the physical file
*Now if you want to recover the database before you executed flashback database*
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> recover database;
ORA-00279: change 9205819379814 generated at 08/09/2009 16:40:19 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc
ORA-00280: change 9205819379814 for thread 1 is in sequence #17
Specify log: {
AUTO
ORA-00279: change 9205819380249 generated at 08/09/2009 17:09:55 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000018_0694319678.arc
ORA-00280: change 9205819380249 for thread 1 is in sequence #18
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc' no longer needed for this recovery
.......
........
..........
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf'
ORA-01112: media recovery not started
SQL> select count(1) from v$datafile_header where fuzzy = 'YES';
COUNT(1)
------------------------------
5
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
6 rows selected.
SQL> !ls -l /sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf: No such file or directory
SQL> !ls -l /sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006: No such file or directory
SQL> alter database create datafile '/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006' as '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10M;
Database altered.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
6 rows selected.
SQL> recover database;
ORA-00279: change 9205819381724 generated at 08/09/2009 17:41:53 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc
ORA-00280: change 9205819381724 for thread 1 is in sequence #22
Specify log: {
aUTO
ORA-00279: change 9205819381766 generated at 08/09/2009 17:42:51 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000023_0694319678.arc
ORA-00280: change 9205819381766 for thread 1 is in sequence #23
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc' no longer needed for this recovery
....
......
.......
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
Note: Since Oracle has removed the physical file when I did a flashback you need to create it yourself in order for the recovery to conitnue
Subscribe to:
Posts (Atom)