Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF clause. Essentially it is the same as the DBMS_FLASHBACK functionality.
SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN 4933950; COUNT(*)
----------
1SOL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN4934857 ; COUNT(*)
----------
0
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
SQL> select file#,status,name from v$tempfile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORACLEAGENT/temp01.dbf
Create a New Temporary Tablespace
SQL> create temporary tablespace India tempfile '/u01/app/oracle/oradata/ORACLEAGENT/India01.dbf' size 100m;
Tablespace created.
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
Italy
India
Check Property valuein Database
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
-----------------------
Italy
SQL> alter database default temporary tablespace India;
Database altered.
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------
India
CreateTemporary Tablespace Group
SQL> create temporary tablespace France
tempfile '/u01/app/oracle/oradata/ORACLEAGENT/France01.dbf' size 30m tablespace group Tempgroup;
Tablespace created.
SQL> alter tablespace India tablespace group tempgroup;
Tablespace altered.
SQL> alter database default temporary tablespace tempgroup;
Database altered.
SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
---------------------- TEMPGROUP
CheckTemporary tablespace size
SQL> select tablespace_name,TABLESPACE_SIZE,ALLOCATED_SPACE,FREE_SPACE from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- ----------
INDIA 524288000 10485760 517996544
ITALY 104857600 2097152 102760448
FRANCE 31457280 1048576 30408704
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Checking Tablespaces in Database
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
Create New Tablespace
SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
INDIA
Check Datafile in whole Database
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORACLEAGENT/system.dbf
SYSAUX /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
UNDOTBS /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf
DATA /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf
INDIA /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
Check Datafile's in particular Tablespace.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
CheckTablespace size in particular tablespace
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
2048
How toCheckTablespace size in whole Database
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------- --------------------
SYSTEM 2048
INDIA 30
SYSAUX 2048
UNDOTBS 2048
DATA 60
How toResize Datafiles
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' resize60m;
Database altered.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
60
Add New Datafile
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL> alter tablespace india add datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
Rename or Relocate datafilesafter the 12c method
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india03.dbf';
Database altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.
Undo tablespace check undo tablespace:
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
Check to undo retention:
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
Check to undo management:
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> alter system set undo_tablespace='undotbs2' scope=spfile;
System altered.
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undotbs2
change undo_retention
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention=920;
System altered.
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 920
check db block size
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
change undo management { auto | manual }
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL> alter system set undo_management='auto' scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
Undo stages:
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb,
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
TABLESPACE STATUS SUM_IN_MB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 EXPIRED 1399.25 200
UNDOTBS1 UNEXPIRED 168 21
UNDOTBS2 EXPIRED .375 6
UNDOTBS2 UNEXPIRED 5.4375 42
Active, expired and unexpired transaction space usage in Undo Tablespace:-
ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.
UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.
Free blocks : At the time of creating new undo tablespace shows empty blocks
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well as some manual steps after changing the password using FNDCPASS.
Whether you use FNDCPASS or AFPASSWD to change the APPLSYS/APPS password, you must also perform some additional steps. This is because in R12.2, the old AOL/J connection pooling is replaced with Weblogic Connection Pool ( JDBC Data source ). Currently this procedure is not yet automated. It would be good, if this can be automated using some WLS scripting.
Important: These steps must be carried out on the run file system.
Shut down the application tier services using the $INST_TOP/admin/scripts/adstpall.sh script.
Change the APPLSYS password, as described for the utility you are using.
Start Admin Server using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.
Change the “apps” password in WLS Data source as follows:
Log in to WLS Administration Console.
Click Lock & Edit in Change Center.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select the Connection Pool tab.
Enter the new password in the “Password” field.
Enter the new password in the “Confirm Password” field.
Click Save.
Click Activate Changes in Change Center.
Start all the application tier services using the $INST_TOP/admin/scripts/adstrtal.sh script.
Verify the WLS Datastore changes as follows:
Log in to WLS Administration Console.
In the Domain Structure tree, expand Services, then select Data Sources.
On the “Summary of JDBC Data Sources” page, select EBSDataSource.
On the “Settings for EBSDataSource” page, select Monitoring > Testing.
Select “oacore_server1“.
Click Test Data Source
Look for the message “Test of EBSDataSource on server oacore_server1 was successful“.
Important: Steps 4, 5 and 6 are only applicable when changing the APPLSYS password. They are not applicable when changing passwords for product schemas or the SYSTEM schema.
In the next prepare phase after the password change, adop will invoke EBS Domain Configuration to ensure that the WLS data source on the patch file system will be synchronized with the new APPS password.
By changing priority of concurrent request you can run the important concurrent request before non-priority concurrent request.
Open “System Administrator” responsibility. Go to Concurrent > Requests
Select “Specific Requests“. In the field called “Requester” put the User ID for whom you want to change priority.
Select “View Details“. Change the Priority from default “50“. Change it to “10” for high priority. [1 for highest and 99 for lowest].
Change the User priority
You can set the priority of individual user priority. For high priority user [CFO or CEO :-)] the report will get completed the request will soon start to run than a normal priority user. Change the value of profile option “Concurrent:Request Priority” in User level to make him or her a high or low priority user.
Alternatively you can change the priority of a “Concurrent Request” in define concurrent request screen.
You can do the following two steps to increase the performance of concurrent manager.
Schedule the following concurrent request program “Purge Concurrent Requests and / or Manager Data“.
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
By default in R12, the Logs and Output are located in $APPLCSF/$APPLLOG and $APPLCSF/$APPLOUT.
Change the value of APPLCSF, APPLLOG and APPLOUT in the context file to your desired location in context file. Make sure the path you have mentioned is accessible from every application node.
Run autoconfig and bounce the concurrent managers for the changes to be implemented.
After this test whether log is getting generated in desired location or not by submitting some sample request.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
The default password is ‘PUB’ and in case you change it, the password should be always in Upper Case. It is a public schema. which helps in validating Application user credentials when we login to Oracle Application.
How Applsyspub user works?
When we login to applications, initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.
Once authentication is successfully completed, Oracle Application allows to connect to apps schema and allows to choose a responsibility. By using responsibility, we can use HTML or Oracle forms interface to access data that resides in product schema.
How to change password of APPLSYSPUB ?
FNDCPASS utility is used to change the applsyspub password.
$FNDCPASS APPS/[apps_password] 0 Y SYSTEM/[system_passwoord] ORACLE APPLSYSPUB [new_passsword].0 & Y are flags for FNDCPASS0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form)’Y’ indicates that this method is directly invoked from the command-line and not from the Submit Request Form.
Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.
In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.
How to fetch the APPLSYSPUB user password
1. echo $GWYUID
APPLSYSPUB/PUB
2. grep -i applsyspub $FND_SECURE/$TWO_TASK.dbc
GWYUID=APPLSYSPUB/PUB
3. grep -i s_gwyuid_pass $CONTEXT_FILE
password oa_var=”s_gwyuid_pass”– PUB — password
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Below i face an error when i was running adop session.
When i was running adop phase=cleanup i was hit with the below error
[applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Wed May 24 2021 03:34:53
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 62)
Cleanup is not done in earlier session
[START 2021/05/24 03:35:27] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
[START 2021/05/24 03:35:48] cleanup phase
[EVENT] [START 2021/05/24 03:35:52] Performing Cleanup steps
[EVENT] [START 2021/05/24 03:35:57] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/log
[EVENT] [END 2021/05/24 03:39:43] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/05/24 03:39:54] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/adzdallddls_20210524_033957.out
[END 2021/05/24 03:39:58] Generating All DDL Report [EVENT] Calling cleanup in QUICK mode [WARNING] Cleanup may take a while. Please wait. [ERROR] Failed to execute sql statement : declare result varchar2(10); begin ad_zd.cleanup(‘QUICK’); exception when others then raise_application_error(-20001,’Error while calling ad_zd.cleanup’ || sqlerrm); end; [ERROR] SQLPLUS error: buffer=
SQL*Plus: Release 10.1.0.5.0 – Production on Wed May 24 03:40:01 2021
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> declare ERROR at line 1: ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old: rollback segment number 2 with name “_SYSSMU2_735814084$” too small
ORA-06512: at line 7
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[UNEXPECTED]Error occurred while calling cleanup plsql API
[UNEXPECTED]Cleanup phase completed with errors/warnings. Please check logfiles
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
adop exiting with status = 1 (Fail)
[applmgr@r122 ~]$
To overcome this issue.I have extended the undotablespace using below:
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME like ‘APPS_UNDOTS1’;
TABLESPACE_NAME FILE_NAME
APPS_UNDOTS1 /u01/oracle/PROD/data/undo01.dbf
SQL> ALTER DATABASE DATAFILE ‘/u01/oracle/PROD/data/undo01.dbf’ RESIZE 10240M;
Database altered.
SQL>
Then again i executed the adop phase=cleanup
applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…
RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admi
n/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/ad
min/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Fri Feb 28 2021 04:11:18
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 59)
Cleanup is not done in earlier session
[START 2021/02/28 04:11:45] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
[START 2021/02/28 04:12:03] cleanup phase
[EVENT] [START 2021/02/28 04:12:06] Performing Cleanup steps
[EVENT] [START 2021/02/28 04:12:11] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/log
[EVENT] [END 2021/02/28 04:13:26] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/02/28 04:13:32] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdallddls_20210228_041335.out
[END 2021/02/28 04:13:36] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[EVENT] [END 2021/02/28 04:57:56] Performing Cleanup steps
[END 2021/02/28 04:58:05] cleanup phase
[START 2021/02/28 04:58:11] Generating AD_ZD_LOGS Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdshowlog.out
[END 2021/02/28 04:58:24] Generating AD_ZD_LOGS Report
[END 2021/02/28 04:58:25] adzdoptl.pl run
adop phase=cleanup – Completed Successfully
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
adop exiting with status = 0 (Success)
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
