Search This Blog

Total Pageviews

Monday, 4 September 2023

How to change retention of securefile Lob segment

  
  
  
  How to change retention of securefile Lob segment


	How to change retention of securefile Lob segment (Doc ID 2175438.1)

we can only specify RETENTION parameter For SECUREFILE LOBs. Also note that you can specify either PCTVERSION or RETENTION for BASICFILE LOBs, but not both.


 show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1




SQL> CREATE test.TABLE cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ;

Table created.


create table test.cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ;

Table created.


set linesize 300
col owner for a20 
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';


OWNER                TABLE_NAME           COLUMN_NAME          PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- ------- ---------------
TEST                 CUST_INT             C_LOB                                      YES DEFAULT


alter system set undo_retention=1500;




[-PRIMARY-]sys@ORCLD> alter system set undo_retention=1500;

System altered.

[-PRIMARY-]sys@ORCLD> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     1500            <<< UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention
undo_tablespace                      string      UNDOTBS1
[-PRIMARY-]sys@ORCLD>




alter table  modify lob () (retention min );  ie.  =1500 

alter table cust_int modify lob (c_lob) (retention sec 1500);

 alter table test.cust_int modify lob (c_lob) (retention min 1500) ;

Table altered.



OWNER                TABLE_NAME           COLUMN_NAME          PCTVERSION  RETENTION SEC RETENTION_TYPE  RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
TEST                 CUST_INT             C_LOB                                      YES MIN                        1500





 alter table test.cust_int modify lob (c_lob) (retention none) ;
 
 
set linesize 300
col owner for a20 
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';


 
 OWNER                TABLE_NAME           COLUMN_NAME          PCTVERSION  RETENTION SEC RETENTION_TYPE  RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
TEST                 CUST_INT             C_LOB                                      YES NONE



set linesize 300
col owner for a20 
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select TABLE_OWNER,COLUMN_NAME,LOB_NAME,DEF_PCTVERSION,DEF_RETENTION,DEF_MINRET from dba_part_lobs where table_name='CUST_INT';







SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean TRUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1


SQL>
SQL>
SQL> alter system set undo_retention=1200;

System altered.

SQL> 

select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';

TABLE_NAME COLUMN_NAM PCTVERSION RETENTION   SEC   RETENTION_TYPE RETENTION_VALUE
-------------------- ---------- ---------- ---------- --- ------- ---------------
CUST_INT   C_LOB                              YES   DEFAULT

SQL>
SQL>


SQL> alter table cust_int modify lob (c_lob) (retention);

Table altered.

 

SQL> select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
------------------------------ ------------------------------ ---------- ----- 
CUST_INT    C_LOB                           YES DEFAULT

 

SQL> alter table cust_int modify lob (c_lob) (retention min 1200);

Table altered.

SQL> select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
------------------------------ ------------------------------ ---------- ----------  
CUST_INT   C_LOB                            YES    MIN            1200 

 

 

 

To change retention of securefiles use (retention min ) clause.

eg:

alter table 
modify lob () (retention min ); alter table test.cust_int modify lob (c_lob) (retention none) ; Table altered. set linesize 300 col owner for a20 col TABLE_NAME for a20 col COLUMN_NAME for a20 col RETENTION_TYPE for a15 select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT'; OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE -------------------- -------------------- -------------------- ---------- ---------- --- --------------- --------------- TEST CUST_INT C_LOB YES NONE alter table test.cust_int modify lob (c_lob) (retention ); Table altered. alter table test.cust_int modify lob (c_lob) (retention default); To check For Non-partitioned LOB select TABLE_NAME,COLUMN_NAME,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name=''; For partitioned LOB select TABLE_OWNER,COLUMN_NAME,LOB_NAME,DEF_PCTVERSION,DEF_RETENTION,DEF_MINRET from dba_part_lobs where table_name=''; select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';

Friday, 1 September 2023

RMAN-08138: warning: archived log not deleted - must create more backups




 delete archivelog until time 'sysdate - 7';

archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1758.12751.1145022793 thread=1 sequence=1758
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1759.13482.1145040079 thread=1 sequence=1759
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1760.10421.1145057009 thread=1 sequence=1760


check 
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;



use force

delete noprompt force archivelog until time 'sysdate - 7';

Thursday, 24 August 2023

OPW-00010: Could not create the password file. This resource has a Password File.

OPW-00010: Could not create the password file. This resource has a Password File.






orapwd file='+DATA/orapwibrac' ENTRIES=10 DBUNIQUENAME=ibrac

Enter password for SYS:

OPW-00010: Could not create the password file. This resource has a Password File.

[grid@irac01 ~]$



srvctl config database -d ibrac
Database unique name: ibrac
Database name:
Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: +data/IBRAC/PARAMETERFILE/spfile.4018.1145682969  --- as password file already in  this config 
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: ibrac1,ibrac2
Configured nodes: ibrac01,ibrac02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@ibrac01:~] $


                                  
modify without password file 
srvctl modify database -d ibrac -pwfile



[oracle@irac01:~] $srvctl config database -d ibrac
Database unique name: ibrac
Database name:
Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1
Oracle user: oracle
Spfile: +data/IBRAC/PARAMETERFILE/spfile.4018.1145682969
Password file:                                             <<<<<<< now no password file !!!
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: oinstall
OSOPER group: oinstall
Database instances: ibrac1,ibrac2
Configured nodes: ibrac01,ibrac02
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed



run again!!!

orapwd file='+DATA/orapwibrac' ENTRIES=10 DBUNIQUENAME=ibrac

Enter password for SYS:


ASMCMD [+] > ls -l +DATA/IBRAC/PASSWORD/
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   AUG 24 08:00:00  Y    pwdibrac.4019.1145693377
ASMCMD [+] >


srvctl modify database -d ibrac -pwfile +DATA/IBRAC/PASSWORD/pwdibrac.4019.1145693377



SQL> alter user sys identified by sys ;  <<< change the password again :)

User altered.

SQL>



Wednesday, 23 August 2023

tfactl – Diagnostic data Collection for troubleshooting the issue of GI, ASM, and RAC

 tfactl – Diagnostic data Collection for troubleshooting the issue of GI, ASM, and RAC



locate file 

locate tfactl
/u01/app/19.0.0/grid/bin/tfactl
/u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl
/u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.bat.tmpl
/u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.pl
/u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.tmpl





GI_HOME/tfa/bin/tfactl diagcollect -from "MMM/dd/yyyy hh:mm:ss" -to "MMM/dd/yyyy hh:mm:ss"

/u01/app/19.0.0/grid/bin/tfactl diagcollect -from "Aug/23/2023 07:00:00" -to "Aug/23/2023 08:00:00"



 /u01/app/19.0.0/grid/bin/tfactl diagcollect -from "Aug/23/2023 07:00:00" -to "Aug/23/2023 08:00:00"
WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version.
Collecting data for all nodes
Scanning files from Aug/23/2023 07:00:00 to Aug/23/2023 08:00:00

Collection Id : 20230823083407ibrac01

Detailed Logging at : /u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all/diagcollect_20230823083407_ibrac01.log
2023/08/23 08:34:12 EDT : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom
2023/08/23 08:34:12 EDT : Collection Name : tfa_Wed_Aug_23_08_34_08_EDT_2023.zip
2023/08/23 08:34:12 EDT : Collecting diagnostics from hosts : [ibrac02, ibrac01]
2023/08/23 08:34:12 EDT : Scanning of files for Collection in progress...
2023/08/23 08:34:12 EDT : Collecting additional diagnostic information...
2023/08/23 08:36:07 EDT : Getting list of files satisfying time range [08/23/2023 07:00:00 EDT, 08/23/2023 08:00:00 EDT]
2023/08/23 08:37:20 EDT : Completed collection of additional diagnostic information...
2023/08/23 08:39:53 EDT : Collecting ADR incident files...
2023/08/23 08:39:53 EDT : Completed Local Collection
2023/08/23 08:39:53 EDT : Remote Collection in Progress...
.-------------------------------------------------------------------.
|                         Collection Summary                        |
+---------+------------------------------------------+-------+------+
| Host    | Status                                   | Size  | Time |
+---------+------------------------------------------+-------+------+
| irac02  | Failed Unable to connect to Node ibrac02 |       |      |
| irac01  | Completed                                | 191MB | 341s |
'---------+------------------------------------------+-------+------'

Logs are being collected to: /u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all
/u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all/irac01.tfa_Wed_Aug_23_08_34_08_EDT_2023.zip

Tuesday, 22 August 2023

ORA-17627: ORA-12543: TNS:destination host unreachable ORA-17629: Cannot connect to the remote database server





[oracle@oracentd19c admin]$ rman target sys/sys@orclprim auxiliary sys/sys@orcldstdy|tee stdy.txt

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 22 06:13:15 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1672814384)
connected to auxiliary database: ORCLS (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 22-08-2023 06:13:21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19c/db_1/dbs/orapworcls'   ;
}
executing Memory Script

Starting backup at 22-08-2023 06:13:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/22/2023 06:13:23
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/22/2023 06:13:23
ORA-17627: ORA-12543: TNS:destination host unreachable
ORA-17629: Cannot connect to the remote database server

RMAN>



from prim to standby 

from prod !!!
[oracle@oracent19c admin]$ tnsping orcldstdy

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2023 06:14:45

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.56)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=orcls)))
TNS-12543: TNS:destination host unreachable







Check the firewall !!!!!

on standby 
[root@oracentd19c ~]# firewall-cmd --state
running
[root@oracentd19c ~]# systemctl stop firewalld     or add rule for 1521 
[root@oracentd19c ~]# firewall-cmd --state
not running
[root@oracentd19c ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@oracentd19c ~]# firewall-cmd --state
not running


from prim now working !!
[oracle@oracent19c ~]$ tnsping orcldstdy

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2023 06:18:44

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.56)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=orcls)))
OK (20 msec)
[oracle@oracent19c ~]$



Sunday, 20 August 2023

RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon denied









RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied




RMAN> duplicate target database to 'orcld' from active database nofilenamecheck;

Starting Duplicate Db at 20-AUG-23
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=38 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    6710885896 bytes

Fixed Size                     9149960 bytes
Variable Size               1174405120 bytes
Database Buffers            5519704064 bytes
Redo Buffers                   7626752 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''orcld'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'orcl3' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''orcld'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    6710885896 bytes

Fixed Size                     9149960 bytes
Variable Size               1174405120 bytes
Database Buffers            5519704064 bytes
Redo Buffers                   7626752 bytes

Starting restore at 20-AUG-23
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/20/2023 19:58:01
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server


**************************************************************

check the password file name  !!!!

[oracle@oracentd19c dbs]$ ps -ef|grep -i smon
oracle   19234     1  0 20:21 ?        00:00:00 ora_smon_orcld


cp orapworcl orapworcld


Monday, 14 August 2023

Create Pluggable Database(PDB) in Oracle 12c


Create Pluggable Database(PDB) in Oracle 12c
 .. 


Create Pluggable Database(PDB) in Oracle 12c



if required!!!
alter system set "_exclude_seed_cdb_view"=FALSE scope=both;

System altered.





select  distinct substr(name, 1, instr(name, '/',-1)) PATH 
                       from  (
                       select name  from v$datafile
                       union all
                       select NAME  from v$controlfile
                       union all
                       select MEMBER  name from v$logfile
                       union all
                       select name from v$tempfile
) order by 1;                   



take seed database file path 

set linesize 400
col datafile_name for a90
col container for a20
select hxfil file#, d.name datafile_name , c.name container, fhsta fh_status, fhscn fh_scn, fhrba_seq fh_seq , fhafs fh_abs from x$kcvfh x, v$datafile d, v$containers c
where d.file# = x.hxfil
and d.con_id = c.con_id
and c.name='PDB$SEED'
order by c.name,fhscn;

     FILE# DATAFILE_NAME                                                                              CONTAINER             FH_STATUS FH_SCN                   FH_SEQ FH_ABS
---------- ------------------------------------------------------------------------------------------ -------------------- ---------- -------------------- ---------- --------------------
        14 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/system.359.1133670139             PDB$SEED                  40960 52755005                   2043 0
        15 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/sysaux.1322.1133670139            PDB$SEED                  32768 52755005                   2043 0
        20 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/pdb9_undotbs.4153.1133670139      PDB$SEED                  32768 52755005                   2043 0
        17 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/users.1419.1133670139             PDB$SEED                  32768 52755005                   2043 0
        16 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/undotbs1.1320.1133670139          PDB$SEED                  32768 52755005                   2043 0



for temp file alter session set container=PDB$SEED ;

 select NAME from  V$TEMPFILE;

NAME
------------------------------------------------------------------------------------------
+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127






ww
create pluggable database anujy admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');

Pluggable database created.


create pluggable database anujz admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');
Pluggable database created.




Oracle DBA

anuj blog Archive