Whenever I need some commands or scripts, I had to search to find out where it is. I alway forgot to organize my required script and now trying to put everything here so at least I will know where to look into.
These are mostly simple staff, just putting here for reference.
-- Trace files readable by any user SQL> alter system set "_trace_files_public" = true scope=spfile;
-- Insert an alert log entry
SQL> exec dbms_system.ksdwrt(2,'ORA-00600: Test message, verifying alert log monitoring in EM12c');
-- Restart MRP with a specific instance
DGMGRL> EDIT DATABASE 'DR_Sales' SET STATE='ONLINE' WITH APPLY INSTANCE='Chicago_N2';
-- Run DGMGRL commands inline
(oracle)$ dgmgrl / "show database 'North_Sales'"
(oracle)$ dgmgrl / "validate database verbose 'North_Sales'"
-- Multiple Ports in SCAN Listener
(oracle)$ srvctl modify scan_listener -p "TCP:1521/TCP:1522"
OR
(oracle)$ srvctl modify scan_listener -endpoints "TCP:1521/TCP:1522"
-- Add a New LOCAL LISTENER
(oracle)$ srvctl add listener -l LISTENER_1531 -o /u01/app/11.2.0.3/grid -p "TCP:1531" -k 1
-- init.ora
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.223)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.223)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.223)(PORT=1541))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.223)(PORT=1551))))' scope=both sid='DB1';
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.224)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.224)(PORT=1531))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.224)(PORT=1541))(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.224)(PORT=1551))))' scope=both sid='DB2';
-- Oracle: Add multiple ports in LISTENER
(oracle)$ srvctl modify listener -l LISTENER -p "TCP:1521,1522"
-- "listener_networks" parameter for Data Guard when using separate network than SCAN
Reference MOS Notes #
Data Guard Physical Standby 11.2 RAC Primary to RAC Standby using a second network (Doc ID 1349977.1)
Data Guard Transport Considerations on Oracle Database Machine (Exadata) (Doc ID 960510.1)
-- Primary Site
x01g1a-vip, x01g2a-vip = Node VIP
x01g1a-adg-vip, x01g2a-adg-vip = Additional VIP for seperate network
-- Add the required entries in /etc/hosts, DNS, rule & route file for the new network, then the followings
(root)# srvctl add network -k 2 -S 192.168.240.0/255.255.255.240/bondeth1 -w static -v
(root)# crsctl start res ora.net2.network
(root)# srvctl add vip -n x01g1a -A 192.168.240.243/255.255.255.240 -k 2
(root)# srvctl add vip -n x01g2a -A 192.168.240.244/255.255.255.240 -k 2
(root)# srvctl start vip -i x01g1a-adg-vip
(root)# srvctl start vip -i x01g2a-adg-vip
(oracle)$ srvctl add listener -l LISTENER_DG -o /u01/app/12.1.0.2/grid -p 1522 -k 2
-- init.ora
alter system set listener_networks='((NAME=NET1)(LOCAL_LISTENER=PR_01) (REMOTE_LISTENER=PR_02)), ((NAME=ADG_NET1)(LOCAL_LISTENER=PR_ADG01) (REMOTE_LISTENER=PR_ADG02))' scope=both sid='DB1';
alter system set listener_networks='((NAME=NET1)(LOCAL_LISTENER=PR_02) (REMOTE_LISTENER=PR_01)), ((NAME=ADG_NET1)(LOCAL_LISTENER=PR_ADG02) (REMOTE_LISTENER=PR_ADG01))' scope=both sid='DB2';
-- tnsnames.ora (DB HOME)
PR_01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g1a-vip)(PORT = 1521))
)
PR_02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g2a-vip)(PORT = 1521))
)
PR_ADG01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g1a-adg-vip)(PORT = 1522))
)
PR_ADG02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g2a-adg-vip)(PORT = 1522))
)
DBPR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g1a-adg-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g2a-adg-vip)(PORT = 1522))
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB)
)
)
DBDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r1a-adg-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r2a-adg-vip)(PORT = 1522))
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB)
)
) -- DR Site
x01r1a-vip, x01r2a-vip = Node VIP
x01r1a-adg-vip, x01r2a-adg-vip = Additional VIP for seperate network
-- Add the required entries in /etc/hosts, DNS, rule & route file for the new network, then the followings
(root)# srvctl add network -k 2 -S 192.168.240.0/255.255.255.240/bondeth1 -w static -v
(root)# crsctl start res ora.net2.network
(root)# srvctl add vip -n x01r1a -A 192.168.240.250/255.255.255.240 -k 2
(root)# srvctl add vip -n x01r2a -A 192.168.240.251/255.255.255.240 -k 2
(root)# srvctl start vip -i x01r1a-adg-vip
(root)# srvctl start vip -i x01r2a-adg-vip
(oracle)$ srvctl add listener -l LISTENER_DG -o /u01/app/12.1.0.2/grid -p 1522 -k 2
-- init.ora
alter system set listener_networks='((NAME=NET1)(LOCAL_LISTENER=DR_01) (REMOTE_LISTENER=DR_02)), ((NAME=ADG_NET1)(LOCAL_LISTENER=DR_ADG01) (REMOTE_LISTENER=DR_ADG02))' scope=both sid='DB1';
alter system set listener_networks='((NAME=NET1)(LOCAL_LISTENER=DR_02) (REMOTE_LISTENER=DR_01)), ((NAME=ADG_NET1)(LOCAL_LISTENER=DR_ADG02) (REMOTE_LISTENER=DR_ADG01))' scope=both sid='DB2';
-- tnsnames.ora (DB HOME)
DR_01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r1a-vip)(PORT = 1521)))
DR_02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r2a-vip)(PORT = 1521)))
DR_ADG01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r1a-adg-vip)(PORT = 1522)))
DR_ADG02 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r2a-adg-vip)(PORT = 1522)))
DBPR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g1a-adg-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = x01g2a-adg-vip)(PORT = 1522))
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB)
)
)
DBDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r1a-adg-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = x01r2a-adg-vip)(PORT = 1522))
(SEND_BUF_SIZE = 10485760)
(RECV_BUF_SIZE = 10485760)
(LOAD_BALANCE = no)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB)
)
)
-- Adds services to a database and assigns them to instances.
11g
(oracle)$ srvctl add service -d FCPRDSG -s FCPRDSG_SVC -r FCPRDSG1,FCPRDSG2 -P BASIC -e SELECT -m BASIC -z 10 -w 2 12c
(oracle)$ srvctl add service -database DBM -service CORE_BANKING_SVC -preferred DBM1,DBM2 -available DBM3,DBM4 -role "PRIMARY" -tafpolicy BASIC -failovermethod BASIC -failovertype SELECT -failoverretry 10 -failoverdelay 2
(root)# tfactl diagcollect -all -since 2h -nomonitor -z acs.diag.collection
(root)# tfactl purge -older 24h -force
(root)# tfactl print status
(root)# tfactl print config
(root)# tfactl print actions
(root)# tfactl print actions
-- SQL profiles
SQL> COLUMN category FORMAT a10
SQL> COLUMN sql_text FORMAT a20
SQL> SELECT NAME, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;
-- SYSAUX tablespace Retention & Purging related
Reference MOS Note # SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
-- Show the current optimizer statistics history level
SQL> select dbms_stats.get_stats_history_availability from dual; -- To ensure that statistics history will be retained for at least 7 days:
SQL> exec dbms_stats.alter_stats_history_retention(7); -- Truncate stats history tables.
SQL> exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);
-- Gather Stats: Dictionary, System, Fixed Objects
SQL> exec dbms_stats.gather_system_stats ('EXADATA');
SQL> exec dbms_stats.gather_dictionary_stats(options=>'gather', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => 8, cascade => true);
SQL> exec dbms_stats.gather_fixed_objects_stats;
-- Create Database Link
SQL> CREATE DATABASE LINK <target_database_name> CONNECT TO <target_db_user_name> IDENTIFIED BY <password> USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <target_scan_name>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <target_database_service_name>))';
Note: DB Link name is same as target database name as global_names=true.
-- Exadata: Set ASM Diskgroup Attributes
SQL> select name,value,group_number from v$asm_attribute; SQL> ALTER DISKGROUP RECO SET ATTRIBUTE 'compatible.asm' = '12.1.0.2.0';
SQL> ALTER DISKGROUP DBFS_DG SET ATTRIBUTE 'compatible.asm' = '12.1.0.2.0';
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.asm' = '12.1.0.2.0'; SQL> ALTER DISKGROUP RECO SET ATTRIBUTE 'compatible.rdbms' = '12.1.0.2.0';
SQL> ALTER DISKGROUP DBFS_DG SET ATTRIBUTE 'compatible.rdbms' = '12.1.0.2.0';
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.rdbms' = '12.1.0.2.0';
SQL> ALTER DISKGROUP RECO SET ATTRIBUTE 'appliance.mode'='TRUE';
SQL> ALTER DISKGROUP DBFS_DG SET ATTRIBUTE 'appliance.mode'='TRUE';
SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'appliance.mode'='TRUE';
-- Exadata: Verify the oracle binary is linked with the RDS option: 12c
(oracle)$ /u01/app/12.1.0.2/grid/bin/skgxpinfo
(oracle)$ /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/skgxpinfo
-- Deinstall 11.2.0.2, 11.2.0.3, 11.2.0.4 or 12.1.0.1 Database and Grid Homes using deinstall tool (Download from MOS)
(oracle)$ ./deinstall -checkonly -home /u01/app/oracle/product/11.2.0.3/dbhome_1 (oracle)$ ./deinstall -home /u01/app/oracle/product/11.2.0.3/dbhome_1 (root)# dcli -l root -g ~/dbs_group chmod -R 755 /u01/app/11.2.0.3/grid
(root)# dcli -l root -g ~/dbs_group chown -R oracle /u01/app/11.2.0.3/grid
(root)# dcli -l root -g ~/dbs_group chown oracle /u01/app/11.2.0.3
(oracle)$ ./deinstall -checkonly -home /u01/app/11.2.0.3/grid
(oracle)$ ./deinstall -home /u01/app/11.2.0.3/grid
-- Change AWR Snapshot Settings
SQL> col snap_interval format a30
SQL> col retention format a30
SQL> select snap_interval, retention from dba_hist_wr_control; SQL> EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (43200, 30); SQL> exec dbms_stats.gather_fixed_objects_stats;
SQL> exec dbms_workload_repository.create_snapshot;
-- Enable/Disable AutoTasks Jobs
SQL> set lines 200
SQL> COL CLIENT_NAME FORMAT a35
SQL> COL MEAN_JOB_DURATION FORMAT a30
SQL> COL CONSUMER_GROUP FORMAT a40
SQL> SELECT CLIENT_NAME, STATUS, CONSUMER_GROUP, MEAN_JOB_DURATION FROM DBA_AUTOTASK_CLIENT; SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
-- EM12c: delete targets using emcli
(oracle)$ ./emcli get_targets
(oracle)$ ./emcli delete_target -name="LRRDR_sys" -type="oracle_dbsys"
-- EM12c: Change password of SYSMAN user (MOS Note # 1365930.1)
(oracle)$ cd $OMS_HOME/bin
(oracle)$ ./emctl stop oms
(oracle)$ ./emctl config oms -change_repos_pwd
(oracle)$ ./emctl stop oms -all
(oracle)$ ./emctl start oms
-- SQL Tuning Advisor in command line
-- Create the SQL Tuning Task
SQL> set serveroutput on
SQL> declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
sql_id => '<your_sql_id>',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 60,
task_name => '<your_tuning_task_name>',
description => 'tuning task for statement your_sql_id.');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/ -- Executing the tuning task
SQL> exec dbms_sqltune.execute_tuning_task(task_name => '<your_tuning_task_name>'); -- Displaying the recommendations
SQL> set long 100000;
SQL> set longchunksize 1000
SQL> set pagesize 10000
SQL> set linesize 200
SQL> select dbms_sqltune.report_tuning_task('<your_tuning_task_name>') as recommendations from dual;
-- Create database using DBCA -silent
(oracle)$ /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/dbca -createDatabase -silent -gdbName DBFS -sid DBFS -sysPassword <password> -systemPassword <password> -emConfiguration NONE -diskGroupName 'DBFS_DG' -recoveryGroupName 'RECO' -storageType ASM -asmSysPassword <asm_sys_password> -nodelist 'dm01db01,dm01db02' -databaseType MULTIPURPOSE -responseFile NO_VALUE -asmsnmpPassword <asm_snmp_password> -templateName /path/to/templete/dw_x5_2.dbt
-- Delete database using DBCA -silent
(oracle)$ dbca -silent -deleteDatabase -sourceDB DBFS -sysDBAUserName sys -sysDBAPassword <password>
-- Move AUDIT tables into a different tablespace
SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
SQL> select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$'); SQL> create tablespace AUDIT_TBS datafile '+DATA' size 1024m autoextend on next 256m maxsize 32767m;
SQL> alter tablespace AUDIT_TBS add datafile '+DATA' size 1024m autoextend on next 256m maxsize 32767m; SQL> EXEC DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TBS');
SQL> DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,audit_trail_location_value => 'AUDIT_TBS'); SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;
-- Adding OCR Mirror
(root)# /u01/app/12.1.0.2/grid/bin/ocrconfig -add +RECO
(root)# /u01/app/12.1.0.2/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 10856
Available space (kbytes) : 398712
ID : 478107259
Device/File Name : +DBFS_DG
Device/File integrity check succeeded
Device/File Name : +DATA_DM01
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
-- Queryable Patch Inventory: DBMS_QOPATCH
MOS Note: Queryable Patch Inventory -- SQL Interface to view, compare, validate database patches (Doc ID 1585814.1)
Documentation: DBMS_QOPATCH
SQL> set heading off long 50000 lines 150
-- Display ORACLE_HOME details such as patch and inventory location.
SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) from dual;
-- Equivalent of 'opatch lsinv'
SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual;
-- Configuration Data Guard Broker
-- Stop the MRP on standby database
SQL> alter database recover managed standby database cancel;
-- Primary
(oracle)$ asmcmd
ASMCMD> mkdir +DATA/DBPR/BROKER
ASMCMD> mkdir +RECO/DBPR/BROKER
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/DBPR/BROKER/dgb_config01.ora' SCOPE=BOTH sid='*';
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+RECO/DBPR/BROKER/dgb_config02.ora' SCOPE=BOTH sid='*';
SQL> alter system set dg_broker_start=true scope=both sid='*';
SQL> alter system set dg_broker_start=false scope=both sid='*';
SQL> alter system set dg_broker_start=true scope=both sid='*';
-- Standby
(oracle)$ asmcmd
ASMCMD> mkdir +DATA/DBDR/BROKER
ASMCMD> mkdir +RECO/DBDR/BROKER
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/DBDR/BROKER/dgb_config01.ora' SCOPE=BOTH sid='*';
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+RECO/DBDR/BROKER/dgb_config02.ora' SCOPE=BOTH sid='*';
SQL> alter system set dg_broker_start=true scope=both sid='*';
SQL> alter system set dg_broker_start=true scope=both sid='*';
SQL> alter system set dg_broker_start=false scope=both sid='*';
SQL> alter system set dg_broker_start=true scope=both sid='*';
-- Primary, again
-- Create Configuration
(oracle)$ dgmgrl
DGMGRL> CONNECT sys/<password>;
DGMGRL> CREATE CONFIGURATION 'DB_ADG_Config' AS PRIMARY DATABASE IS 'DBPR' CONNECT IDENTIFIER IS 'DBPR';
DGMGRL> ADD DATABASE 'DBDR' AS CONNECT IDENTIFIER IS 'DBDR';
-- Enable Configuration
# dgmgrl
DGMGRL> enable configuration;
-- Verify Configuration
(oracle)$ dgmgrl
DGMGRL> show configuration;
DGMGRL> show database verbose DBPR;
DGMGRL> show instance verbose DB1 on database DBPR
DGMGRL> show instance verbose DB2 on database DBPR
DGMGRL> show database verbose DBDR;
DGMGRL> show instance verbose DB1 on database DBDR
DGMGRL> show instance verbose DB2 on database DBDR
DGMGRL> show fast_start failover
No comments:
Post a Comment