APPSDBA Data Lake

  The following example illustrates the process of moving a SQL Profile from 10.2 onwards. 1. Create SQL Profile in SCOTT schema The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task: DECLARE  my_task_name VARCHAR2(30); my_sqltext CLOB;  my_sqlprofile_name VARCHAR2(30);  BEGIN    my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839';    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,          user_name => 'SCOTT',          scope => 'COMPREHENSIVE',          time_limit => 60,          task_name => 'my_sql_tuning_task',          description => 'Demo Task to tune a query');     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task');  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task',          name => 'my_sql_profile');  END;  / PL/SQL procedure s

 [ortansid@shsebs121db01 orastage]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jun 23 02:30:39 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. connected to target database: TANSID (DBID=3902989410) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name TANSID are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYP

  1. stop the database service. srvctl stop database -d TANSID 2. start the database in mount state. srvctl start database -d TANSID -o mount 3. enable archive log mode. SQL> alter database archivelog; Database altered. 4. Restart the database service srvctl stop database -d TANSID srvctl start database -d TANSID 5. Set the archive log destination to ASM DISK SQL> alter system set log_archive_dest_1='LOCATION=+ARCH/' scope=both sid='*'; System altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +ARCH Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 2 SQL> alter system switch logfile; System altered. Checked the space of ARCH asm diskgroup before setting it for the archive destination. SQL> select NAME,TOTAL_MB/1024 from v$asm_diskgroup; NAME TOTAL_MB/1024 ------------------------------ ------------- ARCH 33.77